locked
Points + intersections + distance + degree = cupcake RRS feed

  • 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 for 1st task

     

    Sample 2nd task

    Sample code will be much appreciated. :-)
    Thank you in advance.

     

     


    Theo Fernandes
    Monday, 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/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
    Answerer

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

    Code result


    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
    Answerer
  • 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