# Points + intersections + distance + degree = cupcake

• ### Question

•

I need a BIG help.

I have pre-defined 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 code will be much appreciated. :-)

Theo Fernandes
Monday, August 22, 2011 9:27 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/make-geometry-stcentroid-method-work-on-geometries-other-than-polygons

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

Friday, September 28, 2012 9:38 AM

### 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/make-geometry-stcentroid-method-work-on-geometries-other-than-polygons

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

Friday, September 28, 2012 9:38 AM
• 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.

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