Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Spatial Data - Working with Circles Expand / Collapse
Author
Message
Posted Tuesday, July 15, 2014 2:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 6:40 AM
Points: 437, Visits: 312
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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:48 PM
Points: 1,343, Visits: 3,809
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

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 6:40 AM
Points: 437, Visits: 312
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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:48 PM
Points: 1,343, Visits: 3,809
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







Post #1592472
Posted Tuesday, July 15, 2014 6:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 6:40 AM
Points: 437, Visits: 312
Hi Thanks,

I'll have play around with the code supplied.

Thanks for the help

Chris
Post #1592532
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse