[postgis-users] SQL Help selecting intersections

Fri Aug 10 14:38:22 PDT 2007

Hi  Rob... here is what you can do:

SELECT AsText(line_substring(road2.the_geom,
LEAST(line_locate_point(road2.the_geom,Centroid(Intersection(road1.the_geom,
road2.the_geom))),line_locate_point(LineMerge(road2.the_geom
),Centroid(Intersection(road3.the_geom,road2.the_geom)))),
GREATEST(line_locate_point(road2.the_geom,Centroid(Intersection(
road1.the_geom,road2.the_geom))),line_locate_point(LineMerge(road2.the_geom
),Centroid(Intersection(road3.the_geom,road2.the_geom))))
)) AS substring
FROM
(SELECT * FROM roads WHERE name LIKE 'Rge 20') AS road1
JOIN
(SELECT * FROM roads WHERE name LIKE 'Twp 480') AS road2
ON
road1.the_geom && road2.the_geom AND Intersects(road1.the_geom,
road2.the_geom)
JOIN (SELECT * FROM roads WHERE name LIKE 'Rge 30') AS road3
ON
road2.the_geom && road3.the_geom AND Intersects(road2.the_geom,
road3.the_geom)

Now, how does it works? First I add a second JOIN in the FORM clause to
include the third road. The road2 subquery will have the result for the Twp
480 road so it must intersect with the road1 and with the road3. All this is
achieved through JOINs with the Intersection condition in the ON clause. Now
you want to get the substring, but first you need to know the start and end
values so you can then call to the line_substring function. So what you need
to do is call the line_locate_point with the road2 geometry and the
Intersection point with each one of the others geometries (road1 and road3).
Then with this values you can call the line_substring function, but not
yet... to call the line_substring function, the start value must be smaller
than the end value and this is done with the functions LEAST and GREATEST.
This two function will return the smaller value for the 2 argument of the
line_substring function and the greater for the 3rd argument. So I think
this is it. I don't know how fast is this query in your server... obviously
as I told you in the previous mail there may be several things to do to get
this query go faster like changing the LIKE comparision for = if you dont
need to use the LIKE capabilities and also in this case, building an index
in your table for the name column can make a difference. I hope it work for
you... in my case I had to use the LineMerge() function in the geom columns
cause my geometries are 'MULTILINESTRING' even when actually all of them
have just one LINESTRING... If you get an error like:

line_interpolate_point: 1st arg isnt a line

or similar, you can try with this version of the query:

SELECT AsText(line_substring(LineMerge(road2.the_geom),
LEAST(line_locate_point(LineMerge(road2.the_geom),Centroid(Intersection(
road1.the_geom,road2.the_geom))),line_locate_point(LineMerge(road2.the_geom
),Centroid(Intersection(road3.the_geom,road2.the_geom)))),
GREATEST(line_locate_point(LineMerge(road2.the_geom),Centroid(Intersection(
road1.the_geom,road2.the_geom))),line_locate_point(LineMerge(road2.the_geom
),Centroid(Intersection(road3.the_geom,road2.the_geom))))
)) AS substring
FROM
(SELECT * FROM roads WHERE name LIKE 'Rge 20') AS road1
JOIN
(SELECT * FROM roads WHERE name LIKE 'Twp 480') AS road2
ON
road1.the_geom && road2.the_geom AND Intersects(road1.the_geom,
road2.the_geom)
JOIN (SELECT * FROM roads WHERE name LIKE 'Rge 30') AS road3
ON
road2.the_geom && road3.the_geom AND Intersects(road2.the_geom,
road3.the_geom)

I hope it works. Again, if you have any question, ask me again.

Rodrigo.

On 8/10/07, Rob McCulley <RMcCulley at county24.com> wrote:
>
>  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
>
>
>
> _______________________________________________
> 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/45339075/attachment.html>