SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Other SQL Server 2012 Topics
 Intersection of polyline and polygon
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

atletico
Starting Member

Greece
6 Posts

Posted - 09/22/2013 :  06:41:38  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 09/22/2013 :  08:36:11  Show Profile  Reply with Quote
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
Go to Top of Page

atletico
Starting Member

Greece
6 Posts

Posted - 09/23/2013 :  03:15:03  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/23/2013 :  13:08:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/23/2013 :  14:11:28  Show Profile  Reply with Quote
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
Go to Top of Page

atletico
Starting Member

Greece
6 Posts

Posted - 09/24/2013 :  03:39:02  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/25/2013 :  07:50:06  Show Profile  Reply with Quote
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
Go to Top of Page

atletico
Starting Member

Greece
6 Posts

Posted - 09/25/2013 :  08:37:21  Show Profile  Reply with Quote
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.

Edited by - atletico on 09/25/2013 09:44:48
Go to Top of Page

robvolk
Most Valuable Yak

USA
15637 Posts

Posted - 09/25/2013 :  15:48:17  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000