Re: [postgis-users] Query using ST_transform fails
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 +nadgri...@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 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 root694 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 root261 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 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
Re: [postgis-users] Query using ST_transform fails
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 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 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@postgis.refractions.net
>> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Query using ST_transform fails
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 root694 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 root261 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 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@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Query using ST_transform fails
Am Dienstag, 2. November 2010 22:48:08 schrieb Mike Toews: > select distinct st_srid(way) from planet_osm_point; > Hello, i have to say i don't know exactly, but placing that query i got: gis=> select distinct st_srid(way) from planet_osm_point; st_srid - 900913 (1 Zeile) Best regards, Torsten. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Query using ST_transform fails
Also, what is the source SRID of your geometry column(s)? If you're not sure, check the geometry_columns table, the constraints used in the DDL, or try: select distinct st_srid(way) from planet_osm_point; -Mike On 2 November 2010 14:09, Torsten Mohr 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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Query using ST_transform fails
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 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@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Query using ST_transform fails
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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
