[postgis-users] reverse geocoding

[Brian Modra]

On 30/03/2010, Kevin Neufeld <kneufeld at refractions.net> wrote:
> On 3/29/2010 9:58 PM, Stephen Woodbridge wrote:
>> If you have a POINT then you can find the closest centerline within
>> some RADIUS with:
>>
>> select *, distance(POINT, the_geom) as dist
>>   from lines
>>  where expand(POINT, RADIUS) && the_geom
>>  order by dist limit 1;
>>
>> -Steve
>
> Or to find the closest centerline for all points in your point table:
>
> SELECT DISTINCT ON (a.gid) a.*, b.*, ST_Distance(a.geom, b.geom) AS dist
> FROM points a, lines b
> WHERE ST_Expand(a.geom, RADIUS) && b.geom
> ORDER BY a.gid, dist ASC;

I've found some data sets have very long road linestrings, and this
makes the spatial index less effective, so if this is the case, you
want to create a new table of roads (for search purposes only) and
break the linestrings up into smaller linestrings.

I used a plpgsql function that did a select on the entire roads line
table, and row by row did this:

calculate the length of the linestring using length2d
do simple maths to work out how many segments I wanted
then in a for loop, used line_substring to create the segments.
Insert each segment into a new table, copying across the UID of the
original linsestring

Then of course, create a spatial index on this new (larger) table.

To do the reverse geocoding, I then search using a SQL very similar to
Kevin's, and also search on a points table (points of interest such as
Church, Petrol Station etc)
Then also on the polygons tables to get the suburb etc.

The hardest part of reverse geocoding is that you need to become very
fmiliar with your data set, and set up the parameters of your search
so that the results are sensible. Sometimes the data set is not so
good, and you need to make a lot of tweaks to work around this.

>
> Cheers,
> Kevin
> _______________________________________________
> 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/