Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 SQL Server Spatial Data - Working with Circles Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, July 15, 2014 2:24 AM
 SSC-Addicted Group: General Forum Members Last Login: Wednesday, October 22, 2014 3:03 AM Points: 437, Visits: 325
 Hi there,I am not very familiar with working with spatial data and I am currently trying to work out how to obtain some properties of a circle defined as a geometry data type.Specifically, I need to determine x and y co-ordinates for the centre point and the diameter of the circle.I have had a look at the MSDN reference for spatial data and done some general googling but I must admit I am struggling to find anything relevant.Any advice or guidance would be greatly appreciated.TIA,Chris
Post #1592446
 Posted Tuesday, July 15, 2014 3:02 AM
 Hall of Fame Group: General Forum Members Last Login: Today @ 1:42 PM Points: 3,503, Visits: 9,349
 Simplest method is to use STBuffer, the parameter passed is effectively the radius.`DECLARE @CIRCLE GEOMETRY = geometry::Point(1.00,1.00,0).STBuffer(1);SELECT @CIRCLE`
Post #1592454
 Posted Tuesday, July 15, 2014 3:08 AM
 SSC-Addicted Group: General Forum Members Last Login: Wednesday, October 22, 2014 3:03 AM Points: 437, Visits: 325
 Hi, Thanks for your reply.If I understand correctly, I would need the centre point in order to use STBuffer().I don't currently understand how to find the centre point from the existing shape stored as a CIRCULARSTRING. Here is some example data I am working with:CIRCULARSTRING (52.6107417597068 -1.19053984363516, 52.6107417597068 -1.19185949047026, 52.6114844449159 -1.19185949047026, 52.6114844449159 -1.19053984363516, 52.6107417597068 -1.19053984363516)CIRCULARSTRING (0 4, 4 0, 8 4, 4 8, 0 4)Based on this, is there an easy way to find the centre point?TIA,Chris
Post #1592458
 Posted Tuesday, July 15, 2014 3:42 AM
 Hall of Fame Group: General Forum Members Last Login: Today @ 1:42 PM Points: 3,503, Visits: 9,349
 This should get you started, find where lines drawn between every other point defining the circle crosses`USE tempdb;GODECLARE @CIRCLE GEOMETRY = 'CIRCULARSTRING (52.6107417597068 -1.19053984363516, 52.6107417597068 -1.19185949047026, 52.6114844449159 -1.19185949047026, 52.6114844449159 -1.19053984363516, 52.6107417597068 -1.19053984363516)';WITH T(N) AS (SELECT X.N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N)),NUMS(N) AS (SELECT TOP(@CIRCLE.STNumPoints() - 1 ) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T,T T2,T T3,T T4),C_POINTS AS ( SELECT NM.N AS POINT_NO ,@CIRCLE.STPointN(N) /*.STBuffer(0.0001)*/ AS POINT_GEO FROM NUMS NM)SELECT C1.POINT_GEO.ShortestLineTo(C3.POINT_GEO).STIntersection(C2.POINT_GEO.ShortestLineTo(C4.POINT_GEO)).STBuffer(0.0001)FROM C_POINTS C1CROSS APPLY C_POINTS C2CROSS APPLY C_POINTS C3CROSS APPLY C_POINTS C4WHERE C1.POINT_NO = 1AND C2.POINT_NO = 2AND C3.POINT_NO = 3AND C4.POINT_NO = 4UNION ALLSELECT @CIRCLE `
Post #1592472
 Posted Tuesday, July 15, 2014 6:50 AM
 SSC-Addicted Group: General Forum Members Last Login: Wednesday, October 22, 2014 3:03 AM Points: 437, Visits: 325
 Hi Thanks, I'll have play around with the code supplied.Thanks for the help Chris
Post #1592532

 Permissions