Answered by:
Points + intersections + distance + degree = cupcake
Question

I need a BIG help.
I have predefined points: A, B and C.
1st task  Intersect 2 lines
For A point, I'll set degree and distance (100km default). Expected result: 2nd point to create the line;
Do the same for B point;
Intersect A and B. Expected result: point of intersection.
2nd task  Intersect 3 lines
For A point, I'll set degree and distance (100km default). Expected result: 2nd point to create the line;
Do the same for B and C points;
Intersect A, B and C. Expected result: center point of intersection.
Sample for 1st task
Sample 2nd task
Sample code will be much appreciated. :)Thank you in advance.
Theo FernandesMonday, August 22, 2011 9:27 PM
Answers

For #1, To get the point of intersection between the two lines, you need only @l1.STIntersection(@l2).
For #2, To determine the "center" of all points of intersection, you should be able to use STUnion() to create a MultiPoint geometry from all the individual intersection points, and then use STCentroid() to calculate the centre of that MultiPoint. That's how you'd do it in any other spatial database :) Unfortunately, the STCentroid() function in SQL Server only works on Polygons. I filed a Connect request over two years ago to request the ability to calculate the centroid of a MultiPoint, but unfortunately it was closed as "Won't Fix". Please feel free to go and upvote the issue though, and make a comment that you would find it useful too!: https://connect.microsoft.com/SQLServer/feedback/details/588316/makegeometrystcentroidmethodworkongeometriesotherthanpolygons
Instead, you'll have to construct a polygon from the individual points, which probably means creating a custom SQLCLR function, which is a bit of a PITA.
twitter: @alastaira blog: http://alastaira.wordpress.com/  Pro Spatial with SQL Server 2012
 Edited by tanoshimiEditor Friday, September 28, 2012 9:38 AM
 Marked as answer by Theo Fernandes Friday, September 28, 2012 11:51 AM
Friday, September 28, 2012 9:38 AMAnswerer
All replies

I'm back to this project and now I need some help to solve it.
1st problem:
When I intersect 2 lines, the return is 1 line. I need to know the exact point of intersection.2nd problem:
I need all intersection points to create a polygon and after it get the center.Thank you.
Theo Fernandes
Wednesday, September 26, 2012 12:54 PM 
I'm using this code:
BEGIN declare @l1 geometry declare @l2 geometry declare @i geometry set @l1 = geometry::STGeomFromText('LINESTRING(52.345918 31.759111, 52.343513 31.759688)', 4326) set @l2 = geometry::STGeomFromText('LINESTRING(52.344435 31.759889, 52.344084 31.75925)', 4326) set @i = @l1.STIntersection(@l1.STIntersection(@l2).STBuffer(1)).STStartPoint() great buffer only to see the position set @i = @l1.STIntersection(@l1.STIntersection(@l2).STBuffer(10000))  create a test table DECLARE @test TABLE(seg GEOMETRY); INSERT INTO @test VALUES(@l1) INSERT INTO @test VALUES(@l2) INSERT INTO @test VALUES(@i) SELECT seg.STAsText() FROM @test DECLARE @geom GEOMETRY SELECT @geom = (SELECT TOP 1 seg FROM @test)  union all the linestring points SELECT @geom = @geom.STUnion([seg]) FROM @test  do what you want with the results SELECT @geom print(@geom.STAsText()) END
Theo Fernandes
Thursday, September 27, 2012 2:05 PM 
Maybe I'm looking for a elephant code size, when in fact I need a ant code size.
I tried several different functions, but I could not get the expected result.
It's possible to do?
Theo Fernandes
Thursday, September 27, 2012 8:28 PM 
For #1, To get the point of intersection between the two lines, you need only @l1.STIntersection(@l2).
For #2, To determine the "center" of all points of intersection, you should be able to use STUnion() to create a MultiPoint geometry from all the individual intersection points, and then use STCentroid() to calculate the centre of that MultiPoint. That's how you'd do it in any other spatial database :) Unfortunately, the STCentroid() function in SQL Server only works on Polygons. I filed a Connect request over two years ago to request the ability to calculate the centroid of a MultiPoint, but unfortunately it was closed as "Won't Fix". Please feel free to go and upvote the issue though, and make a comment that you would find it useful too!: https://connect.microsoft.com/SQLServer/feedback/details/588316/makegeometrystcentroidmethodworkongeometriesotherthanpolygons
Instead, you'll have to construct a polygon from the individual points, which probably means creating a custom SQLCLR function, which is a bit of a PITA.
twitter: @alastaira blog: http://alastaira.wordpress.com/  Pro Spatial with SQL Server 2012
 Edited by tanoshimiEditor Friday, September 28, 2012 9:38 AM
 Marked as answer by Theo Fernandes Friday, September 28, 2012 11:51 AM
Friday, September 28, 2012 9:38 AMAnswerer 
I did not understand why I was not getting the result of this intersection before since I had tried a number of ways.
Now is working.
I read in somewhere that for intersections between two lines return is always a line. And that's what was happening in the code I was using up there.
Thank you for your help.Theo Fernandes
Friday, September 28, 2012 11:51 AM 
For future needs...
I hope there's a better way to create the polygon, but it was the way I found and it's working.
BEGIN declare @l1 geometry, @l2 geometry, @l3 geometry declare @p1 geometry, @p2 geometry declare @c geometry, @u geometry declare @polstr varchar(max) declare @pol geometry set @l1 = geometry::STGeomFromText('LINESTRING(52.345918 31.759111, 52.343513 31.759688)', 4326) set @l2 = geometry::STGeomFromText('LINESTRING(52.344435 31.759889, 52.344084 31.75925)', 4326) set @l3 = geometry::STGeomFromText('LINESTRING(52.345 31.7593, 52.3442 31.7598)', 4326) set @p1 = @l1.STIntersection(@l2).STBuffer(0.00002) set @p2 = @l3.STIntersection(@l1.STUnion(@l2)).STBuffer(0.00002) print @p1.ToString() print @p2.ToString() union for set @u = @p1.STUnion(@p2) create polygon set @polstr = '' + 'POLYGON((' + REPLACE(REPLACE(REPLACE(@u.ToString(), 'MULTIPOINT ', ''), '(', ''), ')', '') + ', ' + REPLACE(REPLACE(REPLACE(@u.STStartPoint().ToString(), 'POINT ', ''), '(', ''), ')', '') + '))' set @pol = geometry::STGeomFromText(@polstr, 4326) select @pol print @polstr get center from polygon SELECT @c = @pol.STCentroid().STBuffer(0.00002) DECLARE @tbl TABLE(seg GEOMETRY); INSERT INTO @tbl VALUES(@l1) INSERT INTO @tbl VALUES(@l2) INSERT INTO @tbl VALUES(@l3) INSERT INTO @tbl VALUES(@p1) INSERT INTO @tbl VALUES(@p2) INSERT INTO @tbl VALUES(@c) DECLARE @geom GEOMETRY SELECT @geom = (SELECT TOP 1 seg FROM @tbl) SELECT @geom = @geom.STUnion([seg]) FROM @tbl SELECT @geom END
Theo Fernandes
Friday, September 28, 2012 1:07 PM