[postgis-users] reverse geocoding

[Stephen Woodbridge]

Richard Greenwood wrote:
> 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.

Richard,

to find the adjacent segments just do:

1) find the nearest segment to you x,y
2) get the start and end points of that segment
3) select * from lines
     where expand(start, 0.000002) && the_geom
       and gid != first_segment_gid
       and name = first_segment_name;
4) do the same for the end point.

0.000002 is just a very small tolerance in case the segments have 
rounding errors and are not exactly the same.

-Steve


> 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
>>
> 
> 
>