[OSM-dev] querying mapnik data

[Joerg Ostertag (OSM Munich/Germany)]

I tried something like:

echo "select osm_id,highway,name from planet_osm_line where highway is not null and way && transform(GeomFromText('POLYGON((48.18605 11.86398,48.18632 11.86398,48.18632 11.86884,48.18605 11.86884,48.18605 11.86398))',4326),3395) limit 5;" | psql  gis
FEHLER:  AddToPROJ4SRSCache: Cannot find SRID (3395) in spatial_ref_sys

but the following seems to work ....

echo "SELECT name , distance(way,     GeomFromText('POINT(856371.58 6683083.41)', 3395)) FROM planet_osm_roads WHERE distance(way,     GeomFromText('POINT(856371.58 6683083.41)', 3395)) < 500 order by distance(way,     GeomFromText('POINT(856371.58 6683083.41)', 3395));" | psql  gis
     name      |     distance     
---------------+------------------
 Ostring       |                0
 Morgenstrasse |                0
 Südring       | 402.053901112776
 Wasserstrasse | 402.053901112776
 Ostring       | 476.563405330174
(5 rows)

This leads me to the conclusion, that I either have to transform 
coordinates myself or I'll have to find the apropriate conversion in postgis. 
The transform seems not to work on my machine. Anyone any ideas what I'm doing wrong?


On Donnerstag 27 Dezember 2007, Jon Burgess wrote:
> On 27/12/2007, Joerg Ostertag (OSM Munich/Germany)
>
> <openstreetmap at ostertag.name> wrote:
> > I would like to query mapnik data directly from the postgis Database. Can
> > anyone give me a hint which SELECT statements I would need to querey all
> > streets in a bounding Box or in the (surrounding) area of a point. I want
> > to use this inside of osmtrackfilter.pl to get and compare against the
> > already existing osm segments(ways)
> > Any hints are welcome.
> >
> > I already  tried to set up an SQL select to retrieve all polylines in a
> > certain boundingbox or specified distance from other points/lines. But I
> > was not really successfull. I tried to issue some simple SELECT ...
> > Statements but most of them resuled in error messages.
> > I think I'm missing some understanding of which columns in the
> > osm-mapnik-postgis database holds which type of objects. Maybe someone
> > can give me a short hint where to start with.
> >
> > googling arround I only found stuff like the following ...
> >
> > ... WHERE  the_geom && setsrid('BOX(47.36 21.40,51.185 21.53)'::box2d,
> > 42102) ... WHERE GeomFromText('POINT(25.3 25.40)', 42102) && the_geom \
> > AND distance(the_geom, GeomFromText('POINT(25.7 5.3)', 42102)) = 0 ...
> > WHERE distance(the_geom, setsrid('BOX(4.36 2.3,1.5 2.8)'::box2d, 42102))
> > = 0
> > ..  WHERE  the_point && setsrid('BOX(295149 2315499, 465992
> > 2163790)'::box2d, -1)
>
> Have you looked at
> http://trac.openstreetmap.org/browser/applications/utils/export/osm2pgsql/R
>EADME.txt
>
> You may need to do some reprojection of the bounding box if the mapnik
> data is in the default mercator projection and you want to specify the
> co-ordinates in latlong.
>
> Assuming you have the spatial information configured in Postgresql:
>
> gis=> select osm_id,highway,name from planet_osm_line where highway is
> not null and way && transform(GeomFromText('POLYGON((0 52, 0.1 52, 0.1
> 52.1, 0 52.1, 0 52))',4326),3395) limit 5;

I tried the following: