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?

  • 1
    What software are you planning to use? I'm not aware of any that permit this with vector data. – Vince Oct 10 '13 at 21:24
  • 1
    Sure it's possible. I doubt there is any GIS software that does it, though, because it would be so inconvenient and inefficient for almost any purpose, cartographic or analytic. What GIS software did you have in mind? – whuber Oct 10 '13 at 21:24
  • I'm working in ArcGIS, but also use QGIS. Data is usually stored in SQL Server or Shapefile. – monkey5672 Oct 10 '13 at 21:28

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".

  • 1
    Thanks to all who've replied/answered. I kind of figured as much, that's its not a road I should go down. -jm – monkey5672 Oct 10 '13 at 21:45
  • 1
    +1 Good point about the indexes. In principle one could index records based on a common coordinate system (requiring only a one-time reprojection of each record, if necessary), so there is no significant technical barrier to having record-specific projections. The added complexity of managing the data in such a granular way, though, may be the greater concern. – whuber Oct 10 '13 at 22:36
  • The indexes could be pre-projected, but you would still need to project every (potentially) matching record to actually do the geometry tests at query time (which might be very cheap, but could easily get very expensive), so it would probably be easier to just project the underlying feature at storage time. – BradHards Oct 10 '13 at 22:49

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 varchar(40).

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.

An example:

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).

  • Interesting, because you could just drop blobs (which are geometry at the application level, but not at the database level) into a table. – BradHards Oct 16 '13 at 23:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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