[postgis-users] Query using ST_transform fails

Fri Nov 5 10:57:19 PDT 2010

Hello,

> 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

placing your query i get:

       st_x       |       st_y
------------------+------------------
 5.86692407004312 | 51.0512259090808
(1 Zeile)

> 
> 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;
> 
here i get:
  srid  |                                                    proj4text
--------+------------------------------------------------------------------------------------------------------------------
   4326 | +proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs
 900913 | +proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 
+y_0=0 +units=m +k=1.0 +nadgrids=@null +no_defs
(2 Zeilen)

Hmm, i wonder what is wrong with my installation.  I have to admit i don't 
know what happens in a row using the query i originally asked about.

Seems the data come out of PostgreSQL ok, but converting the data fails.
If the queries above ruled that out then i don't know what is the problem.

Any hints would be just great.  I'm not sure what makes most sense to
re-install.

Best regards,
Torsten.

> 
> 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
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 