[GIS] PostGIS: ST_Transform function conversion problem

coordinate systemgmlpostgis

I'm inserting map data from a GML file with srid EPSG:27700 into a PostGIS database table with srid EPSG:4326. I'm using the ST_Transform function as in this snippet:

 ST_Transform(ST_GeomFromGML('<gml:Point srsName="EPSG:27700">
    <gml:coordinates>527347.0,181346.0</gml:coordinates></gml:Point>'), 4326);

After the data was inserted in the database I tried to find if its geometry intersects with an existing table's geometry using the ST_Intersects function. The function returned FALSE in all cases, but I knew that the two geometry layers should intersect.

So, I loaded the layers in QuantumGIS to see what's happening. The PostGIS layers indeed don't intersect, but when I loaded the GML data directly from the GML file using the original srid (EPSG:27700) the map data correctly appeared on top of the pre-existing layer with srid 4326, with which I knew it should intersect. The problem is that the ST_Intersects function (as well as all the others of the same kind) works only for geometries with the same srid, so I need to solve the transformation issue.

Apparently there's either a problem with the ST_Transform function, or I'm missing something that I should know. I use PostGIS 1.5.3. Any help will be greatly appreciated.

Best Answer

I've been having the same problem for a few days now. I found a solution, however, I didn't use PostGIS. Instead, I used QGIS (2.0.1 - Dufour) to do so.

My problem was the same as yours. I imported a shapefile of road network that I have previously displayed in QGIS, where it would display correctly. The original SRID was 3347 (EPSG:3347 - NAD83 / Statistics Canada Lambert). I was trying to change it to 32188.

I used ST_Transform() and UpdateGeometrySRID(), in multiple orders, reverting back to the original SRID at times, but to no avail. The output of ST_SRID was displaying the correct value of 32188. But, when I tried to display it in QGIS, the layer wouldn't appear. Moreso, displaying the geometry using ST_AsText() would show the original coordinates (SRID=3347), not those expected from a SRID=32188.

The solution

I simply opened the shapefile in QGIS. Right-click > Save as... >

  1. Format: ESRI Shapefile
  2. Save as: Choose a new name
  3. CRS > Browse > Selected the correct CRS (32188)
  4. Add saved file to map: Check
  5. Ok

After that, I used the PostGIS Shapefile Import/Export Manager to import that shapefile. Now, everything works fine. The coordinates and the SRID are both correct.

Related Question