[postgis-users] SQL Help selecting intersections

[Rob McCulley]

Hi Rodrigo, 

 

That worked perfectly.  Now I've tried to take it a step further and I'm stuck.  The ultimate reason I'm trying to get the intersection is to select a segment of road using line_substring given three road names.  For example, a user specifies Twp 480 from Rge 20 to Rge 30.  I want to extract that segment of road as a geometry.

 

Once you showed me how to select the intersection, it seemed like it should be easy to add that to a line_locate_point query and save another step, but I'm stuck.

 

This is the query I tried:

 

SELECT line_locate_point(road_geom,IntersectionPoint) AS start FROM (SELECT road1.geom FROM (SELECT * FROM roads WHERE name = 'Twp 480') AS road1 JOIN (SELECT * FROM roads WHERE name = 'Rge 20') AS road2 ON road1.geom && road2.geom AND Intersects(road1.geom,road2.geom)) AS road_geom, (SELECT Centroid(Intersection(road1.geom,road2.geom)) FROM (SELECT * FROM roads WHERE name = 'Twp 480') AS road1 JOIN (SELECT * FROM roads WHERE name = 'Rge 20') AS road2 ON road1.geom && road2.geom AND Intersects(road1.geom,road2.geom)) AS IntersectionPoint;

 

The error message I get is:

 

ERROR: function line_locate_point(record, record) does not exist

SQL state: 42883

Hint: No function matches the given name and argument types. You may need to add explicit type casts.

Character: 8

 

I'm assuming the error is related to how I am selected the road geometry for the line_locate_point function.  If I break it into three stages, and select the road geometry, and then select the point using the sql you showed me, and then select the line_locate_point function, it works fine.

 

If I can get this working, I'm hoping to go another step further and wind up with a sql statement something like:

 

SELECT line_substring(road_geom, start_measure, end_measure) FROM ( ... sql to select road geometry ..., sql to select line_locate_point for start point as above, sql to select line_locate_point for end point as above).

 

My other question is, is this the best way to go about this?

 

Thanks in advance,

Rob

________________________________

From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Rodrigo Martín LÓPEZ GREGORIO
Sent: Wednesday, August 08, 2007 1:00 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] SQL Help selecting intersections

 

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/20070810/2a15e5a6/attachment.html>