[postgis-users] Query using ST_transform fails

[Mike Toews]

I'm not sure why proj would require the NAD gridshift files for
converting a google (900913) projection to WGS84 long/lat. Do you get
any errors with this:

select st_X(wayLL), st_Y(wayLL)
from (
  select ST_Transform('SRID=900913;POINT(653103 6.63036e+06 0)'::geometry,
    4326) as wayLL
) as foo;

-- I get 5.86692407004312; 51.0512259090808

If there are no errors, then there are a few other things that could
be wrong. What do you get from:

select srid, proj4text
from spatial_ref_sys
where srid=900913 or srid=4326;


You can re-install proj without touching anything else. If you have
the source files still, then unzip the grid-shift files in the nad
directory, then:

make clean
./configure
make
make install


-Mike

On 4 November 2010 13:37, Torsten Mohr <tmohr at s.netic.de> wrote:
> Hello,
>
> thanks for your help.
>
> Regarding your questions:
>
> No, i don't use www.pgrpms.org, my system is openSuse 11.2, i installed their
> postgresql, which is version 8.4.4.
>
> Yes, i installed Proj4 from source.  I have to say i'm not sure any more if i
> installed Proj-datumgrid in the /nad subdirectory of Proj 4 before
> configuring, but in /usr/local/share/proj i got:
>
> -rw-r--r-- 1 root root    694 10. Jan 2010  GL27
> -rw-r--r-- 1 root root   6385 10. Jan 2010  nad.lst
> -rw-r--r-- 1 root root  19501 10. Jan 2010  nad27
> -rw-r--r-- 1 root root  16559 10. Jan 2010  nad83
> -rw-r--r-- 1 root root   7043 10. Jan 2010  world
> -rw-r--r-- 1 root root    261 10. Jan 2010  proj_def.dat
> -rw-r--r-- 1 root root 551012 10. Jan 2010  epsg
> -rw-r--r-- 1 root root 453436 10. Jan 2010  esri
> -rw-r--r-- 1 root root  76843 10. Jan 2010  esri.extra
> -rw-r--r-- 1 root root   3702 10. Jan 2010  other.extra
> -rw-r--r-- 1 root root  77820 10. Jan 2010  IGNF
>
>
> Would it help to re-install Proj 4 ?
>
> Would you expect problems doing so as i installed PostGis after Proj 4 ?
>
>
> Best regards,
> Torsten.
>
>
> Am Dienstag, 2. November 2010 22:34:05 schrieb Mike Toews:
>> It appears the grid shift files are missing. These are not necessarily
>> installed by default.
>>
>> Are you using http://www.pgrpms.org/ ? Is proj-nad installed? Did you
>> install proj.4 from source? If so, did you get the ZIP files and put
>> them in the right place before configuring?
>>
>> -Mike
>>
>> On 2 November 2010 14:09, Torsten Mohr <tmohr at s.netic.de> wrote:
>> > Hello,
>> >
>> > (i accidentially posted this on the openstreetmap mailing list, but it
>> > belongs here:)
>> >
>> >
>> > I once got a hint on this mailing list to use a query like this to get
>> > the lat/lon of the world capitals:
>> >
>> > A)
>> > select st_X(wayLL), st_Y(wayLL), name from (select
>> > ST_AsText(ST_Transform(way,4326)) as wayLL, name from planet_osm_point
>> > where capital='yes') as foo limit 5;
>> >
>> > B)
>> > Based on that hint i used this query:
>> > select st_X(st_transform(way,4326)), st_Y(st_transform(way,4326)), name
>> > from planet_osm_point where place='city' and capital='yes';
>> >
>> > That query worked fine and i did not change my system since then (that
>> > somehow can't be true).  I now get errors for both queries:
>> >
>> > FEHLER:  transform: couldn't project point (653103 6.63036e+06 0): failed
>> > to load NAD27-83 correction file (-38)
>> > TIP:  PostGIS was unable to transform the point because either no grid
>> > shift files were found, or the point does not lie within the range for
>> > which the grid shift is defined. Refer to the ST_Transform() section of
>> > the PostGIS manual for details on how to configure PostGIS to alter this
>> > behaviour.
>> >
>> >
>> > Could it be that due to an RPM update of PostgreSQL some scripts need to
>> > be reinstalled?  I can still generate maps using mapnik.
>> >
>> >
>> > What do i need to do to make those queries work again?
>> >
>> >
>> > In a second step i'd like to extract the polygons that make up the
>> > borders of a country as lat/lon values.  I wonder if this is possible by
>> > just applying the SQL query above to the way of a polygon?  Or is there
>> > another way to get the data i want?
>> >
>> >
>> > Thanks for any hints,
>> > Torsten.
>> > _______________________________________________
>> > postgis-users mailing list
>> > postgis-users at postgis.refractions.net
>> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>