Insert a circle into geometry data type

I'm about to start using geometry or geography data types for the first time now that we have a development baseline of 2008R2 (!) I'm struggling to find how to store the representation for a circle. We currently have the lat and long of the centre of the circle along with the radius, something like :-

[Lat] [float] NOT NULL,
[Long] [float] NOT NULL,
[Radius] [decimal](9, 4) NOT NULL,

Does anyone know the equivalent way to store this using the STGeomFromText method, ie which Well-Known Text (WKT) representation to use? I've looked at circular string (LINESTRING) and curve, but can't find any examples....

Thanks.

回答1:

One thing you can do if you are using SQL Server 2008 is to buffer a point and store the resulting Polygon (as well-known binary, internally). For example,

declare @g geometry
set @g=geometry::STGeomFromText('POINT(0 0)', 4326).STBuffer(1)
select @g.ToString()
select @g.STNumPoints()
select @g.STArea()

This outputs, the WKT,

POLYGON ((0 -1, 0.051459848880767822 -0.99869883060455322, 0.10224419832229614 -0.99483710527420044, 0.15229016542434692 -0.98847776651382446, 0.20153486728668213 -0.97968357801437378, 0.24991559982299805 -0.96851736307144165,... , 0 -1))

the number of points, 129, from which it can be seen that buffering a circle uses 128 points plus a repeated start point and and the area, 3.1412, which is accurate to 3 decimal places, and differs from the real value by 0.01%, which would be acceptable for many use cases.

If you want less accuracy (ie, less points), you can use the Reduce function to decrease the number of points, eg,

declare @g geometry
set @g=geometry::STGeomFromText('POINT(0 0)', 4326).STBuffer(1).Reduce(0.01)

which now produces a circle approximation with 33 points and an area of 3.122 (now 0.6% less than the real value of PI).

Less points will reduce storage and make queries such as STIntersects and STIntersection faster, but, obviously, at the cost of accuracy.

EDIT 1: As Jon Bellamy has pointed out, if you choose to use the Reduce function, the parameter needs to be scaled proportionally to the circle/buffer radius, as it is a sensitivity factor for removing points, based on the Ramer-Douglas-Peucker algorithm

EDIT 2: There is also a function, BufferWithTolerance, which can be used to approximate a circle with a polygon. The second parameter, tolerance effects how close this approximation will be: the lower the value, the more points and better approximation. The 3rd parameter is a bit, indicating whether the tolerance is relative or absolute in relation to the buffer radius. This function could be used instead of the STBuffer, Reduce combination to create a circle with more points.

The following query produces,

declare @g geometry
set @g=geometry::STGeomFromText('POINT(0 0)', 4326).BufferWithTolerance(1,0.0001,1)
select @g.STNumPoints()
select @g.STArea()

a "circle" of 321 points with an area of 3.1424, ie, within 0.02% of the true value of PI (but now larger) and actually less accurate than the simple buffer above. Increasing the tolerance further does not lead to any significant improvement in accuracy, which suggests there is an upper limit to this approach.

As MvG has said, there is no CircularString or CompoundCurve until SQL Server 2012, which would allow you to store circles more compactly and accurately, by building a CompoundCurve made up of two semi-circles, ie, using two CircularStrings.

回答2:

As far as I can tell from the docs, CircularString was only added for SQL Server 2012. The only other instantiable curve appears to be LineString which, as the name suggests, encodes a sequence of line segments. So your best bet would be approximating the circle as a (possibly regular) polygon with a sufficient number of corners. If that is not acceptable, you might have to keep your current data structures in place, either exclusively or in addition to spatial data types to verify that a match there indeed matches the circle.

This answer was written purely from the docs, with no experience to support it.