Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a PostGIS database with SRID=900913 (Google Mercator) and I would like to know the latitude and longitude of points.

I use this command:

ST_X(ST_TRANSFORM(the_geom,4283))

Following what is written here, but it doesn't work. I get the error:

ERROR: AddToPROJ4SRSCache: Cannot find SRID (4283) in spatial_ref_sys
SQL state: XX000. 

I tried to add 4283 in the spatial_ref_sys:

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 94283, 'epsg', 4283, '+proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs ', 'GEOGCS["GDA94",DATUM["Geocentric_Datum_of_Australia_1994",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6283"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4283"]]');

But I still have the same error.

What does that mean?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

The ST_Transform function takes the output coordinate system as the second parameter - see ST_Transform. The input co-ordinates should already be stored in the GEOMETRY_COLUMNS table / view.

Also, you seem to have the SRID wrong, it is 4326 for standard WGS84 lat/lon.

share|improve this answer
    
If I change the SRID and use SELECT ST_X(ST_Transform(the_geom,4326)) from table, I have the same error message, ERROR: AddToPROJ4SRSCache: Cannot find SRID (4326) in spatial_ref_sys –  Antonin Apr 15 '12 at 14:20
    
What is the output of "select * from spatial_ref_sys where srid = 4326"? –  Stev_k Apr 15 '12 at 14:27
    
No output, nothing. –  Antonin Apr 15 '12 at 19:23
    
And for select * from spatial_ref_sys: srid 94283 and 96627 (resp. auth_name epsg and sr-org, resp. auth_srid 4283 and 6627) –  Antonin Apr 15 '12 at 19:57
    
I found the solution: first I used the [code from spatialreference.org in order to add SRID 4326] (spatialreference.org/ref/epsg/4326/postgis) and then I used ST_X(ST_TRANSFORM(the_geom,94326)) to get my result. –  Antonin Apr 15 '12 at 20:15
add comment

You need to populate your spatial_ref_sys table, e.g.:

psql -d your_db -f spatial_ref_sys.sql

This will give you SRID 4326, but not 94283, which doesn't appear to be any publicly recognized SRID.

To add 900913 (i.e. leetspeek for GOOGLE), see the PostGIS instructions at http://trac.osgeo.org/openlayers/wiki/SphericalMercator

share|improve this answer
    
Oh, I see you found 94283 from gis.stackexchange.com/a/23477/1872 .. oh buoy. –  Mike T Apr 15 '12 at 19:39
    
In order to populate my spatial_ref_sys, I don't understand why you suggest the first line of code (psql...). Why isn't it possible to simply use the code from spatialreference.org? –  Antonin Apr 15 '12 at 20:07
    
I found the solution: first I used the [code from spatialreference.org in order to add SRID 4326] (spatialreference.org/ref/epsg/4326/postgis) and then I used ST_X(ST_TRANSFORM(the_geom,94326)) to get my result. –  Antonin Apr 15 '12 at 20:16
    
You can use 94326 for an SRID, you will have less troubles with 4326, since that's what most people know. (I think 94326 is a bug on spatialreference.org ). I.e., to fix the problem, rename 94326 as 4326. I recommend using spatial_ref_sys.sql since it isn't broke like spatialreference.org –  Mike T Apr 15 '12 at 21:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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