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 <[email protected]> wrote: > On 30/03/2010, Kevin Neufeld <[email protected]> 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 >> [email protected] >> 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Richard Greenwood [email protected] www.greenwoodmap.com _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
