Spatial, lines crossing
-
Hi ... this is what I want to do to thousands of lines in one table .... find all lines with CROSSING line(s)!
DECLARE @g geometry, @l geometry, @l1 geometry;
SELECT @g = geometry::STGeomFromText('LINESTRING(0 0, 4 1)', 0),
@l = geometry::STGeomFromText('LINESTRING(0 1, 4 0)', 0),
@l1 = geometry::STGeomFromText('LINESTRING(4 1, 5 0)', 0)
SELECT @g
UNION ALL SELECT @l
union all select @l1
union all
select @g.STIntersection(@l).STBuffer(1) where @g.STCrosses(@l) = 1
how do I loop throug an intire table with many lines?
Best regards
Christian.
-
Following query does the job, however it is not very efficient because it will not use spatial index.
select geom from tablea as source where exists (select * from tablea as lookuptable where lookuptable.geom.STCrosses(source.geom) = 1 and source.id != lookuptable.id )You could use STIntersects to limit set first and then apply STCrosses.
-
An alternative, without the STCrosses function, filtering using STIntersects.
😎
DECLARE @GEOLINE TABLE(
GEOLINE_ID INT IDENTITY(1,1) NOT NULL
,GEOLINE_LINE GEOMETRY NOT NULL
);
INSERT INTO @GEOLINE (GEOLINE_LINE)
VALUES
(geometry::STGeomFromText('LINESTRING(0 1, 6 0)', 0))
,(geometry::STGeomFromText('LINESTRING(0 2, 5 0)', 0))
,(geometry::STGeomFromText('LINESTRING(0 3, 4 0)', 0))
,(geometry::STGeomFromText('LINESTRING(0 4, 3 0)', 0))
,(geometry::STGeomFromText('LINESTRING(0 5, 2 0)', 0))
,(geometry::STGeomFromText('LINESTRING(0 6, 1 0)', 0));
SELECT
G1.GEOLINE_ID AS START_LINE
,G2.GEOLINE_ID AS COMP_LINE
,G1.GEOLINE_LINE.STIntersection(G2.GEOLINE_LINE).STBuffer(0.1) AS LINE_INTERSECTION
FROM @GEOLINE G1
CROSS APPLY @GEOLINE G2
WHERE G1.GEOLINE_ID > G2.GEOLINE_ID
AND G1.GEOLINE_LINE.STIntersects(G2.GEOLINE_LINE) = 1
UNION ALL
SELECT
G1.GEOLINE_ID
,0
,G1.GEOLINE_LINE AS LINE_INTERSECTION
FROM @GEOLINE G1;
-
i have give three different type of approach tak which one you want
DECLARE @GEOLINE TABLE
(
GEOLINE_ID INT IDENTITY(1,1) NOT NULL
,GEOLINE_LINE GEOMETRY NOT NULL
);
INSERT INTO @GEOLINE (GEOLINE_LINE)
VALUES
(geometry::STGeomFromText('LINESTRING(0 1, 6 0)', 0))
,(geometry::STGeomFromText('LINESTRING(0 2, 5 0)', 0))
,(geometry::STGeomFromText('LINESTRING(0 3, 4 0)', 0))
,(geometry::STGeomFromText('LINESTRING(0 4, 3 0)', 0))
,(geometry::STGeomFromText('LINESTRING(0 5, 2 0)', 0))
,(geometry::STGeomFromText('LINESTRING(0 6, 1 0)', 0))
,(geometry::STGeomFromText('LINESTRING(4 4, 3 6)', 0));
/*******************************************
* for just select the interSEcted lines
*******************************************/
SELECT g.GEOLINE_LINE FROM @GEOLINE g
WHERE EXISTS
(SELECT 1 FROM @GEOLINE g1 WHERE g.GEOLINE_LINE.STIntersects(g1.GEOLINE_LINE)=1)
/*******************************************
* for just select the aLL lines
*******************************************/
SELECT g.GEOLINE_LINE ,g.GEOLINE_LINE.STIntersection(g1.GEOLINE_LINE).STBuffer(0.05) AS Intersctions FROM @GEOLINE g
INNER JOIN @GEOLINE g1 ON g.GEOLINE_LINE.STIntersects(g1.GEOLINE_LINE)=1 AND g.GEOLINE_LINE.ToString()<>g1.GEOLINE_LINE.ToString()
/*******************************************
* Overlayer
*******************************************/
SELECT x.allgeo FROM @GEOLINE g
LEFT JOIN @GEOLINE g1 ON g.GEOLINE_LINE.STIntersects(g1.GEOLINE_LINE)=1 AND g.GEOLINE_LINE.ToString()<>g1.GEOLINE_LINE.ToString()
CROSS APPLY
(
VALUES
(g.GEOLINE_LINE) ,(g.GEOLINE_LINE.STIntersection(g1.GEOLINE_LINE).STBuffer(0.2))
)AS x (AllGeo)
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply