7

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

  • i didn't get you.how to do setting of SRID – ads Mar 16 '16 at 11:19
  • It is there, look at ST_SetSRID(geom, XXXX. Perhaps you should read also postgis.net/docs/manual-2.0/ST_SetSRID.html. – user30184 Mar 16 '16 at 11:43
  • @user30184 yah i have set that SRID and st_tranform start working but its not converting UTM into LATLONG – ads Mar 16 '16 at 11:49
  • Give me coordinates of one point and the EPSG code of your UTM projection and I can have a try. – user30184 Mar 16 '16 at 11:54
  • 1
    It depends on what crs your coordinates are in. If they're in EPSG:4326, it's gonna work, you'll need to use st_transform otherwise though. – Michal Zimmermann Mar 16 '16 at 12:31
1

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

enter image description here

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

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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