Oops thats what happens when I post too quickly. Ignore my mention of the "id" column, and replace "id" with "name" in the example...
2009/9/27 Brian Modra <[email protected]>: > 2009/9/26 Rickus <[email protected]>: >> Hi >> I hope someone can put me on the right track. I'm new to this list and new >> to PostGis and to Spatial Databases. >> >> I want to do Reverse Geocoding on OpenStreetMap.org data. The requirement >> is: >> Given a lat/long the query needs to return the closest road and point and >> suburb, city etc. The biggest requirement is that it should be very fast. >> >> I have a PgSQL/PostGis DB Server running with a subset of the OpenStreetMap >> database. In the OSM DB there are tables with 'planet_osm_point', >> 'planet_osm_roads', and 'planet_osm_line' data. I also added a 900913 >> mercator projection to the 'spatial_ref_sys' table. >> >> I have been exploring queries similar to this one: >> "Select name FROM planet_osm_line WHERE distance (way, >> geomfromtext('POINT(-9393780.6113322 3993956.8318506)', 900913)) < 200;" >> >> But I guess where I get lost is that: >> - I need to search in all the relevant tables for line, point and roads? >> - I need to sort according to distance from the given lat/long to get the >> closest point? >> - I need to use a 'bounding box' but not sure how to do it. I guess a >> bounding box is needed to limit the search to a restricted area? > > Your SQL will do a sequential read of the entire table, calculating > the distances of each. > What you need to do is: > 1) narrow the search using the spatial index > 2) then get the one thats closest > You need to use plpgsql. > > e.g. I'm assuming the table has a unique ID for each row, called "ID", > replace this with the actual ID, and check that your table has an > index on that column. > I'm also assuming your table has a "way" geometry column as you used > in your example above: > > create or replace function getClosest( > lat double precision, > lon double precision, > tol double precision) > returns text as $$ > declare > point geometry; > rec record; > begin > point := geomfromtext('POINT('||lon||' '||lon||')', 900913); > select id, distance(way, point) < tol as dist > into rec > from planet_osm_line where way && point > order by dist asc limit 1; > return rec.name; > end; > $$ language plpgsql; > > Please excuse me if this does not (quite) work, I have not tried it... > only typed it into this email rather quuickly. But in any case, it > should put you on the right track. >> Any help will much be appreciated! >> Rickus >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> > > > > -- > Brian Modra Land line: +27 23 5411 462 > Mobile: +27 79 69 77 082 > 5 Jan Louw Str, Prince Albert, 6930 > Postal: P.O. Box 2, Prince Albert 6930 > South Africa > http://www.zwartberg.com/ > -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/ _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
