在几何数据类型中插入一个圆 [英] Insert a circle into geometry data type

问题描述

我即将开始使用几何或地理数据类型，现在我们的开发基线为2008R2（！）
我正在努力寻找如何存储圆的表示。我们现在有半径和圆的中心的长度，如下所示： - $ / $>
$ b

  [Lat] [ float] NOT NULL，
 [Long] [float] NOT NULL，
 [Radius] [decimal]（9，4）NOT NULL，
  

有谁知道使用STGeomFromText方法来存储它的等效方法，即使用哪种知名文本（WKT）表示法？我已经看过圆形字符串（LINESTRING）和曲线，但找不到任何示例....

谢谢。

<如果您使用的是SQL Server 2008，您可以做的一件事是缓冲一个点并存储生成的Polygon（在内部是已知的二进制文件）。例如，

  declare @g geometry 
 set @ g = geometry :: STGeomFromText（'POINT（0 0） '，4326）.STBuffer（1）
 select @ g.ToString（）
 select @ g.STNumPoints（）
 select @ g.STArea（）
  
 
 $ b 
这个输出，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，精确到小数点后三位，并且不同于实际值减少0.01％，这对于许多用例来说是可以接受的。 
 
 
如果您希望精度更低（即点数更少），您可以使用减少函数以减少点数，例如， 
 
  
现在产生一个33点的圆近似值和3.122的面积（现在比PI的实际值小0.6％）。 
 
 
 
点数减少会减少存储空间，并使STIntersects和STIntersection等查询速度加快，但显然要以准确性为代价。 
 
 
 编辑1：正如Jon Bellamy指出的那样，如果您选择使用Reduce功能，则参数需要按比例缩放到圆/缓冲区半径，因为它是灵敏度基于 Ramer-Douglas-Peucker算法 
 
 
 
 编辑2：还有一个函数，  BufferWit hTrance  ，可用于近似具有多边形的圆。第二个参数容差会影响这个近似值有多接近：值越低，点越多，近似值越好。第三个参数是一个位，表示容差是相对于缓冲区半径的相对还是绝对值。这个函数可以用来代替  STBuffer  ， 减少 组合创建一个有更多分数的圆圈。
 
 
 
以下查询产生，
 
 
  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％以内（但现在更大），实际上不如简单缓冲区准确以上。增加宽容度并不会导致准确性的显着提高，这表明这种方法存在上限。 
 
 
正如MvG所说，没有  CircularString或CompoundCurve  直到SQL Server 2012，通过构建由两个半圆组成的 CompoundCurve ，即使用两个 CircularStrings 。
 
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.
解决方案
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.

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