Intersection of polyline and polygon

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Other SQL Server 2012 Topics

Intersection of polyline and polygon

Author

Topic

atletico
Starting Member

6 Posts

Posted - 2013-09-22 : 06:41:38

Hello to all

I would like to find the first point of intersection between a poliline and a polygon using tsql. At this time i am able to find only if they are intersect or not. But is it possible to get also the exactly point that they are intersect on?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 08:36:11

see this illustration

do you mean something like this?


--sample table to illustrate geography data type
IF OBJECT_ID ( 'dbo.SpatialTableGeog', 'U' ) IS NOT NULL 
    DROP TABLE dbo.SpatialTableGeog;
GO

CREATE TABLE SpatialTableGeog 
    ( id int IDENTITY (1,1),
    GeogCol1 geography, 
    GeogCol2 AS GeogCol1.STAsText() );
GO

INSERT INTO SpatialTableGeog (GeogCol1)
VALUES (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326));

INSERT INTO SpatialTableGeog (GeogCol1)
VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326));
GO

--select * from SpatialTableGeog

--Find intersection
DECLARE @geog1 geography;
DECLARE @geog2 geography;
DECLARE @result geography;

SELECT @geog1 = GeogCol1 FROM SpatialTableGeog WHERE id = 1;
SELECT @geog2 = GeogCol1 FROM SpatialTableGeog WHERE id = 2;
SELECT @result = @geog1.STIntersection(@geog2);
SELECT @result,@result.STAsText();

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

atletico
Starting Member

6 Posts

Posted - 2013-09-23 : 03:15:03

Hi visakh16 and thanks for your answer.

No this is not what i am looking for. I don't want to get as a result a whole line but only the first point of intersection between the polyline and polygon.Using your example below i want to get the first point in the left in which the polyline and the polygon are being intersected.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-23 : 13:08:10

See "Intersection point of two lines in 2 dimensions"
here http://paulbourke.net/geometry/pointlineplane/

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-23 : 14:11:28

quote:
Originally posted by atletico

Hi visakh16 and thanks for your answer.

No this is not what i am looking for. I don't want to get as a result a whole line but only the first point of intersection between the polyline and polygon.Using your example below i want to get the first point in the left in which the polyline and the polygon are being intersected.

Thanks.

try


...
SELECT @result = @geog1.STIntersection(@geog2).STStartPoint();
SELECT @result,@result.STAsText();

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

atletico
Starting Member

6 Posts

Posted - 2013-09-24 : 03:39:02

Thanks visakh16 this is exactly what i am looking for.

Seperatelly for this, do you know how hard is to get all the intersected points. I mean, not only the first time of intersection, but all the points where polyline goes in and out of the polygon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-25 : 07:50:06

thats what the initial suggestion gave you right? do you mean you want to take each and every point out and report?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

atletico
Starting Member

6 Posts

Posted - 2013-09-25 : 08:37:21

The initial suggestion provides all the points of the line that created when the polyline goes through the polygon.

What i mean is to get the pair of points everytime that the polyline goes in or out of the polygon.

For example if the polyline passes (goes in and out) once from a polygon to get 2 pairs of points like 1st(lat,lon), 2nd(lat,lon). The 1st will be the intsection point when polyline goes in the polygon and the 2nd will be the intersection point when the polyline goes out of the polygon.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-09-25 : 15:48:17

Slight rewrite of Visakh's code:

DECLARE @line geography=geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
DECLARE @polygon geography=geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);
DECLARE @intersection geography=@line.STIntersection(@polygon);
SELECT @intersection.STAsText() Intersection, 
@intersection.STStartPoint().STAsText() EntryPoint, 
@intersection.STEndPoint().STAsText() ExitPoint;

I have no idea if (or how) it would work if you had a complex polygon with more than 2 intersection points. I imagine it might return a multilinestring but you'd have to test that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources