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.
Best Answer
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.