3

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.

1
  • I have finally tracked down how I got the data into PostGIS with no CRS attached. I was importing the data from shape file using the SPIT plugin and leaving the "Use Default SRID" in the belief that it would use the SRID from the shape file. I just tried importing a shape file first relying on the "Default" and again specifying the SRID. The one imported with "default" had no SRID associated with it unlike the one where I had explicitly gave it the SRID. I have no idea if this is a bug or a feature ;) Feb 1, 2014 at 1:54

3 Answers 3

3

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

1
  • 1
    This is the best solution. PostgreSQL is full fledged database, and using inbuilt functionality like constraints is the most efficient solution. Jan 30, 2014 at 10:10
1

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.

enter image description here

1
  • I am using a Mac and PostGIS plugin for pgAdmin appears to be Windows only. You can do the same thing with the qgis database manager Import options which is how I am now importing stuff to PostGIS rather than using SPIT. Feb 1, 2014 at 18:28
0

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.

2
  • Ironically it was the CSV data that had a CRS attached (I set it on import) the data I imported from the GPS via GPS Tool, save as Shape file then load into PostGIS with SPIT did not have a CRS associated with it. When I created a layer from this data I was always prompted for the CRS. I agree that there was a bug in my data import procedures but it was with the GPS data. Jan 31, 2014 at 19:40
  • You have an example of the CVS?
    – huckfinn
    Feb 1, 2014 at 1:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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