Hopefully this isn't too dumb a question, but is it possible to define projection at the record level, instead of a whole layer or table? I want to create a "project" database for all the work my company has done, and currently have a table of lat longs. It'd be nice to somehow create a dataset of actual site boundaries (usually in UTM NAD83 projection, various zones), without having to convert everything over to lat long. Ideas?
As whuber pointed out, you don't want to do this.
The reason why it is inefficient is related to how the underlying database implements its indexes. See http://en.wikipedia.org/wiki/R-tree for the details, but the storage basically has an indexed bounding box associated with each geometry, and that allows you to quickly find whether a particular record could be relevant for a query.
The problem is that if all the records aren't in the same projection, then the system would have to reproject every record (to know if the record could be relevant or not) for every query. Almost certainly, you don't want to do that, and I don't know of any general purpose GIS database that is implemented that way.
There are a few options here, such as different tables for each projection, or multiple geometry columns per record (and assuming that if there is no data, then that record couldn't possibly be relevant to the query), or doing the projection once and storing that as the "master geometry".
PostGIS does define the projection at the record level, so yes. Although PostGIS users will usually define a geometry column with a specific SRID, the SRID is actually stored in the record. If you define a field as
geometry(MULTIPOLYGON, 4326) and then try to insert data with a different SRID, the insert will fail, the same as if you tried to insert a 50 character string into a field defined as
If you view the data in QGIS, you will see that each SRID in the table will be presented as a distinct layer. This behavior is based on OGR, so it should work for any software that relies on OGR, including the AmigoCloud GDAL/OGR Plugin for ArcGIS.
drop table if exists scratch.unconstrained_proj; create table scratch.unconstrained_proj ( gid serial, name varchar, geom geometry, primary key (gid) ) ; insert into scratch.unconstrained_proj (name, geom) values ('box4326', ST_GeomFromText('MULTIPOLYGON(((0 0, 0 10, 10 10, 10 0, 0 0)))', 4326)) ; insert into scratch.unconstrained_proj (name, geom) values ('box4269', ST_GeomFromText('MULTIPOLYGON(((0 0, 0 10, -10 10, -10 0, 0 0)))', 4269)) ;
If you view this in a GIS you will see two boxes in West Africa. The example is trivial because 4326 and 4269 are both lat-lon projections, but I also tested it using counties from New York and New Jersey in other projections, and it works.
You could view the data as a single layer if you used a view to transform the data. Transformation would also allow you to create a spatial index on the data as a whole. For example to create a view that returns all geometries in WGS 84, and to index the geometries in WGS 84:
create view vw_sites_4326 as select gid, name, ST_Transform(geom, 4326)::geometry(MULTIPOLYGON, 4326) ; create index unconstrained_proj_geom_4326 ON scratch.unconstrained_proj USING gist(ST_Transform(geom, 4326) ;
The cast in the view definition is necessary for the view to register correctly as a spatial view in
geometry_columns (see http://www.postgis.org/docs/using_postgis_dbmanagement.html#Manual_Register_Spatial_Column).