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