5

I am just starting out designing my first GIS database, using PostGIS / PostgreSQL, and I have a question regarding geometry and geography, specifically whether it is more efficient to store geometry (because it supports more functions) and then project into geography when I need it, or whether I should store both geometry and the re-projected geography alongside?

To elaborate a little more, the database will be populated with pre-existing data that contains both British National Grid (EPSG:27700) (OSGR) and converted WGS84 (EPSG:4326) values, and there will be a regular update of that data from external sources. This would argue for two columns, because I don't need to do the calculation.

Going forward, I want to allow data to be edited through a "point and click" web-based map application, using OSM, or text input as either OSGR or via GPS coordinates. Search facilities would also allow either OSGR or lat/lon. Having two columns would mean that I would always have to perform at least one calculation for input (though not search) while having a single column would mean that I may sometimes require a calculation for entry, and again maybe one calculation for search.

I think I've argued myself into wanting both geometry and geography columns in my table (and probably projection columns for each, too, so that I can deal with Ireland at some future time), but is that sensible?

Thanks.

Improve this question

1 Answer 1

Reset to default
3

To elaborate a little more, the database will be populated with pre-existing data that contains both British National Grid (EPSG:27700) (OSGR) and converted WGS84 (EPSG:4326) values, and there will be a regular update of that data from external sources. This would argue for two columns, because I don't need to do the calculation.

You can always reproject with ST_Transform (though you may have minor rounding errors). I would convert and store in WGS84 explicitly and reproject to EPSG:27700 when you need those advantages. The geography features are really useful, and it makes life massively simpler to have them.

Not to mention, you can indexes to cover that conversion too if you frequently query on it.

Improve this answer
4
  • when you say that the geography features are really useful and that it makes life simpler to have them, how does that compare with the number of functions that geography doesn't support?
    – StuartR143
    Jun 2, 2017 at 15:04
  • @StuartR143 honestly, there are a lot of functions geography doesn't support, but don't let that overwhelm you -- you'll almost never use any of them. The list is shrinking -- knn now works with geography. Most of those functions are for modification. And, when you do use them (which will happen), you'll find it quite simple to do geog::geometry and ST_Transform. If you're working with GPS and more than one SRS, I would certainly use geography.
    – Evan Carroll
    Jun 2, 2017 at 15:16
  • @StuartR143 check out PostGIS in Action Second Edition, if you want to learn more about this very topic. It's covered in detail in that book.
    – Evan Carroll
    Jun 2, 2017 at 15:18
  • OK, thanks. Very useful. As you say, I imagine that a lot of what I want will be best achieved with WGS84.
    – StuartR143
    Jun 2, 2017 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.