[postgis-users] SQL Help selecting intersections

[Rodrigo Martín LÓPEZ GREGORIO]

Hi Rob.

You can make something like this:

SELECT *, Centroid(Intersection(road1.the_geom,road2.the_geom)) AS
intersectionPoint FROM
(SELECT * FROM roads WHERE name LIKE 'Township Road 524') AS road1
JOIN
(SELECT * FROM roads WHERE name LIKE 'Range Road 20') AS road2
ON
road1.the_geom && road2.the_geom AND Intersects(road1.the_geom,
road2.the_geom)

Obviously you can select just the columns you want o get extra info like X
and Y coordinates of the intersectionPoint doing something like:

SELECT *, X(Centroid(Intersection(road1.the_geom,road2.the_geom))) AS
xcoord, Y(Centroid(Intersection(road1.the_geom,road2.the_geom))) AS Ycoord
FROM
(SELECT * FROM roads WHERE name LIKE 'Township Road 524') AS road1
JOIN
(SELECT * FROM roads WHERE name LIKE 'Range Road 20') AS road2
ON
road1.the_geom && road2.the_geom AND Intersects(road1.the_geom,
road2.the_geom)

You can also modify the WHERE condition in the search of each individual
road like using ILIKE instead of LIKE to make the search case insensitive,
use % in the LIKE condition to search with just part of the road name, etc.

(This is just a simple solution, you can get better results doing more
complex things like having a separated table with road names and road id,
making the search in this table and getting the ids of the matched roads and
then, having a index on your road table by road_id, accessing just the roads
that have matched the previous search, etc.)

If you have any question, ask me again.

Rodrigo.

On 8/8/07, Rob McCulley <RMcCulley at county24.com> wrote:
>
>  Hi there,
>
>
>
> I'm looking for a little help with some SQL.  I've got a table of roads
> with names.  I need to be able to get the intersection of two roads given
> the two names of the roads.  The problem I have is, most of my roads have
> multiple segments with the same name.
>
>
>
> Currently I'm doing this in essentially three steps.  For example, lets
> say I'm looking for the intersection of Township Road 524 and Range Road
> 20.  I just took a look at my database, and there are two segments for Twp
> Rd 524 and there are 5 segments for Rge Rd 20.  My current solution is:
>
>
>
> Step 1:  Select all segments of Twp Rd 524.
>
> Step 2: Iterate through each segment of Twp Rd 524 and select any segment
> of Rge Rd 20 that intersects it.  After I'm finished the iteration, I have
> two geometries that intersect, one from Twp Rd 524 and one from Rge Rd 20.
>
> Step 3:  Select the intersection of the two geometries to get a point
> geometry.
>
>
>
> This seems like a bit of a brute force method of doing things.  In the
> example case, I end up making four separate queries to the database.  In
> some cases it could be quite a few more.  I'm sure there is a more elegant
> solution, but I can't quite get my head around how to do it.  Is it possible
> to get the intersection of the roads given the names in a single SQL
> statement?
>
>
>
> Thank You,
>
> Rob McCulley
>
> County of Vermilion River
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070808/7e466458/attachment.html>