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 <[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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
