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.