SQL Server Spatial Data - Working with Circles

• chris.king

  SSCrazy

  Points: 2007

  More actions

  July 15, 2014 at 2:24 am

  #293291

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  July 15, 2014 at 3:02 am

  #1729357

  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

• chris.king

  SSCrazy

  Points: 2007

  More actions

  July 15, 2014 at 3:08 am

  #1729360

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  July 15, 2014 at 3:42 am

  #1729372

  This should get you started, find where lines drawn between every other point defining the circle crosses

  

  USE tempdb;

  GO

  DECLARE @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 C1

  CROSS APPLY C_POINTS C2

  CROSS APPLY C_POINTS C3

  CROSS APPLY C_POINTS C4

  WHERE C1.POINT_NO = 1

  AND C2.POINT_NO = 2

  AND C3.POINT_NO = 3

  AND C4.POINT_NO = 4

  UNION ALL

  SELECT @CIRCLE

• chris.king

  SSCrazy

  Points: 2007

  More actions

  July 15, 2014 at 6:50 am

  #1729425

  Hi Thanks,

  I'll have play around with the code supplied.

  Thanks for the help

  Chris