[postgis-users] Help: Reverse Geocoding with OSM data

[Brian Modra]

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 <brian at zwartberg.com>:
> 2009/9/26 Rickus <rickus at mnt-direct.com>:
>> 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
>> postgis-users at postgis.refractions.net
>> 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/