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

[Brian Modra]

Ricus, sorry... I really did write that example way too quickly.
Please ignore my previous posts and rather use this:

create or replace function getGeocode(
 lat double precision,
 lon double precision)
returns text as $$
declare
 point geometry;
 rec record;
 geocode text;
begin
 point := geomfromtext('POINT('||lon||' '||lon||')', 900913);
 select name, distance(way, point) as dist
 into rec
 from planet_osm_line where way && point
 order by dist asc limit 1;
 if found then
  geocode := rec.name;
 else
  geocode := 'unknown';
 end if;
 select name from suburb
 into rec
 where the_geom && point and within(point, the_geom);
 if found then
  geocode := geocode || ', ' || rec.name;
 end if;
 return geocode;
end;
$$ language plpgsql;

This assumes that you have a polygon table called "suburb", that it
has a geometry column "the _geom", and a suburb name column "name"...
I have not tested this either... so I hope its not as full of bugs as
my earlier example
2009/9/27 Brian Modra <brian at zwartberg.com>:
> 2009/9/27 Rickus <rickus at mnt-direct.com>:
>> Thanks for helping...
>> I'm reading up on PL/pgSQL now.
>> I'm not sure where I would get the 'tol' value from. I suppose I just choose
>> something like 3000.
>
> If 3000 metres, then you need to use length_spheroid().
> distance() returns in the units of the coords, i.e. in this case degrees.
>
> But thats another error in my example, the tol is never used, because
> rather than do that, I ordered the results in order of the distance
> and said return only 1. I.e. the closest. Then you can check the
> distance.
> You probably will want to modify the function and add a call to
> within() to the where, if the granularity of your spatial index is
> large.... just to narrow down the search and avoid all those sqrt()s
> that distance() has to do.
>
>> Yes, there is an ID column. What is the 'actual id' that you mention that I
>> have to replace it with?
>
> Sorry, I mentioned the ID column by mistake. When I first started
> answering your post, I was thinking of doing a second select (on ID or
> whartever the ID really was) but then realised I did not need to do
> that. See my follow-up post.
>
>> The table in the OpenStreetMap data has the following populated fields:
>>
>> Table: planet_osm_line:
>> Fields:
>> 1) osm_id(integer):
>> 2) highway(text): example 'tertiary'
>> 3) name(text): example 'First Street' (The 'name' field is only populated if
>> there is a name of a road)
>> 4) way(geometry)
>>
>> Thanks
>> Rickus
>>
>> -----Original Message-----
>> From: postgis-users-bounces at postgis.refractions.net
>> [mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of Brian
>> Modra
>> Sent: Sunday, September 27, 2009 12:33 AM
>> To: PostGIS Users Discussion
>> Subject: Re: [postgis-users] Help: Reverse Geocoding with OSM data
>>
>>
>> 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/
>> _______________________________________________
>> 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
>>
>
>
>
> --
> 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/