将圆插入几何数据类型 [英] Insert a circle into geometry data type

问题描述

我即将开始第一次使用几何或地理数据类型，因为我们的开发基线是 2008R2(！)我正在努力寻找如何存储一个圆圈的表示.我们目前有圆心的纬度和经度以及半径，例如:-

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,

有谁知道使用 STGeomFromText 方法存储它的等效方法，即使用哪种众所周知的文本 (WKT) 表示?我查看了圆形字符串(LINESTRING)和曲线，但找不到任何示例....

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....

谢谢.

推荐答案

如果您使用的是 SQL Server 2008，您可以做的一件事是缓冲一个点并存储生成的多边形(作为众所周知的二进制文件，在内部).例如，

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()

这个输出，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))

点数，129，从中可以看出缓冲一个圆用了128个点加上一个重复的起点和面积，3.1412，精确到小数点后3位，与实际值相差0.01%，这对于许多用例来说是可以接受的.

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.

如果你想要更少的精度(即更少的点)，你可以使用 Reduce 函数来减少点的数量，例如，

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) 

现在生成一个具有 33 个点和 3.122 面积的近似圆(现在比 PI 的实际值小 0.6%).

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).

更少的点会减少存储空间，并使 STIntersects 和 STIntersection 等查询更快，但显然是以准确性为代价的.

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

编辑 1: 正如 Jon Bellamy 所指出的，如果您选择使用 Reduce 函数，则该参数需要与圆/缓冲区半径成比例缩放，因为它是删除点，基于 Ramer-Douglas-Peucker 算法

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

编辑 2: 还有一个函数，BufferWithTolerance，可以用多边形来近似圆.第二个参数，公差影响这个近似值的接近程度:值越低，点越多，近似值越好.第三个参数是位，表示公差相对于缓冲区半径是相对的还是绝对的.可以使用此函数代替 STBuffer，Reduce组合创建一个点数更多的圆.

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.

以下查询产生，

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

一个由 321 个点组成的圆"，面积为 3.1424，即在 PI 真实值的 0.02% 以内(但现在更大)，实际上不如上面的简单缓冲区准确.进一步增加容差并不会显着提高准确性，这表明这种方法存在上限.

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.

正如 MvG 所说，没有 CircularString 或 CompoundCurve 直到 SQL Server 2012，通过构建由两个半圆组成的 CompoundCurve，即使用两个 CircularStrings，可以更紧凑、更准确地存储圆.

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.

这篇关于将圆插入几何数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！