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