[postgis-users] reverse geocoding

[Richard Greenwood]

Steve, Kevin, Brian -

Thank you for the pointers. They provide a good starting point. Eric
Randall also provided a very interesting outline that takes it quite a
bit further. In addition to finding the street which is nearest to a
given point, I need to determine what the min and max address values
are of the points which are adjacent to a street segment, and also
determine whether odd or even numbers are to the left or right side of
each segment.

I'm not sure how far I will ultimately get with this, but I do
appreciate all of your help.

Best regards,
Rich


On Tue, Mar 30, 2010 at 12:29 AM, Brian Modra <brian at zwartberg.com> wrote:
> 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/
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



-- 
Richard Greenwood
richard.greenwood at gmail.com
www.greenwoodmap.com