How ensure data put into PostGIS gets proper CRS attached?

Question

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.

Answer 1

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

Answer 2

If you have data as a shapefile, you can use the PostGIS Shapefile and DBF loader of the pgAdmin. Set the SRID to the EPSG code of the spatial data.

Answer 3

I think the bug is in your workflow from CSV data into postgis. CSV is the only datasource without native CRS information, and a plugin using external commands on files can not access the layer CRS information assigned in QGIS.

Saving the CSV layer to disk in some other format with a dedicated CRS and import that into postgis should avoid your problem.