ST_Transform doesn't work in PostgreSQL

Question

When i run the query with ST_Tranform I get this error:

Input geometry has unknown (0) SRID SQL state: XX000

How can I resolve this error? I want to convert from UTM into geographic lat/lon.

This is the query that gives me the error:

select st_asgeojson(st_transform(geom, 4326)) AS geojson FROM ptable where level_1='Built Up'

Answer 1

select st_asgeojson(st_transform(ST_SetSRID(geom, XXXX), 4326)) AS geojson FROM  ptable where level_1='Built Up'

Try setting SRID beforehand by replacing XXXX with the code of your SRID.

Answer 2

when you import shape file to PostGIS, did you set SRID?

My example is srid=2230, the default is 0, you must manually type in. The postgis import manager did NOT recognize the esri xxx.prj file, which has projection info, if you open that file, you can NOT see srid=2230. There are 2 ways you can get srid= 2230.

1) go to http://prj2epsg.org/search , upload your xxx.prj file, it will search for it and show you result 2230.

2)Or you can use arcCatalog, right click shape file, choose properties, you can find WKID:2230

If you failed to manually set SRID, while import, no worry,

you can use sql to update SRID. By doing this, you set all geometry in this table srid=2230.

Note: no actual coordinate transformation occur, only update the metadata about SRID to 2230, default is 0.

For my case, I excute SQL:

   SELECT UpdateGeometrySRID('city_parks','geom',2230);

Finally, your geometry table has SRID=2230 Now you can output geojson to SRID=4326 by doing this:

   select st_asgeojson(st_transform(geom,4326)) from city_parks;

This is give you correct lat, long coordinate which you can put on any web map, google, bing, leaflet, openlayers...etc...

Previous solution also work, because st_setsrid will literally update each of your geometry srid to 2230.

     select st_asgeojson(st_transform(st_setsrid(geom,2230),4326)) from city_parks;

Summary:
You should manually set SRID while you import shape file, (in future, I hope POSTGIS import tool should automatically read esri's xxx.prj file and automatically popup the 2230 for me).

      If you forget to set SRID while import, you can always update your table's SRID metadata by SQL : 

            SELECT UpdateGeometrySRID('city_parks','geom',2230);

When you table's SRID is set correctly, you will be able to export to geojson by transform to SRID=4326.