[GIS] How ensure data put into PostGIS gets proper CRS attached

coordinate systempostgisqgis

I recently had an intractable problem that we finally traced back to the fact that some points in a PostGIS table had a CRS associated with them and others did not. This resulted in some points being displayed in QGIS while other were not, even when they were they were in different layers and all the coordinates were actually using the same CRS.

I ended up solving this problem by using postgis functions ST_SetSRID and ST_TRANSFORM to set all the point to the same CSR but I am still not sure if these functions correctly set the CRS and whether or not I will not have the same problem after I next add data into the table.

My question now is how do I make sure that all data I put into PostGIS gets the proper CRS attached to the_geom?

Doing this has two advantages as I see it:

  1. you don't get prompted to set the CRS each time you build a layer from the table (I was puzzled why this happened with my 'old' data).
  2. you don't get wierd problems with QGIS when you try and build layers from the data in the tables 😉

The points that had no CRS associated with them were all entered into PostGIS using the SPIT plugin from shape layers that I had saved to disk. Recently some kind soul on stackGIS pointed out that I could short circuit the process by using the database manager to save a layer to PostGIS. This appears to save layer CRS with the data into PostGIS.

From now on I intend to use the database manager to save data into PostGIS an the assumption that this will protect me from headaches like this in future. Is this a reasonable approach?

Apart from getting answers to the question I was also motivated to write this up as an easier to read summary of the question and answers referenced above. In getting to the answers there we found several red herrings that have confused the basic issue and I have left those out here.

Best Answer

Add Constraint like : ALTER xxxx ADD CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 28355) And if you want be more smart about it , try add TRIGGER before INSERT which read existing srid , fails on 0 or -1 , with all others srid does ST_Transform(xxx, your_srid), but this would probably slow system down

Related Question