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
