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

[Brian Modra]

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/