PostGIS中的缓冲区(圆)

Question

我必须扩展正常的GeoJSON格式以添加一些未受支持的多边形，例如Circle。

{
 "type": "Circle",
 "radius" : 0.001,
 "coordinates": [
        5.417075157165527,
       43.29129488122568
 ]
}

这是一个例子。坐标标记圆心，半径标记半径(以米为单位)。

在PostGis文档和Stackoverflow上搜索，要画一个圆，你必须使用ST_BUFFER。

所以我使用：

ST_Buffer(ST_GeomFromGeoJSON(<center of circle>),0.001, 'quad_segs=16')

你知道吗，这只在0,0（非洲附近）画一个圆圈。其他位置会添加扭曲，从而改变椭圆的形状。

我用的是4326。

我已经尝试过搜索（即使在这里），但我找不到简单地画一个圆的解决方案，避免投影或变换它。

这里唯一的帖子是：如何在postgis中创建一个以米为单位的圆？但它非常陈旧，解决方案不起作用。

Answer 1

也许你可以试试这个，它适用于直径一公里的区域。我测试了它，它对我有用：

SELECT ST_Buffer(  
    ST_MakePoint(-122.325959,47.625138)::geography, 
    1000)::geometry;

请参阅此处以了解更多信息。我没有测试它，但希望它能帮助你。

概括一下Abdullah Alger在解决方案中所做的工作，您可以在上面的链接中看到：

首先，他有一个名为商店的数据库，其中包含纬度和经度列，没有带有星巴克数据集的几何列。

然后，他使用查询添加几何图形:

SELECT AddGeometryColumn('stores', 'geom', 4326, 'POINT', 2); 

之后更新geom专栏

Answer 2

它不是在做应该做的事吗？哪个是为了补偿由于将椭球体投影到2D平面结构中而产生的失真？我相信，你离赤道越远，你会看到更多的椭圆形缓冲区。

示例:

db=# SELECT ST_AsText(
     ST_Buffer(
        ST_GeomFromText('SRID=4326;POINT(43.29 5.41)'),0.001, 'quad_segs=16')
    );

此查询返回埃塞俄比亚东南部的一个圆圈：

POLYGON((43.291 5.41,43.2909951847267
5.40990198285967,43.2909807852804 5.40980490967798,43.2909569403357 5.40970971532275,43.2909238795325 5.40961731656764,43.2908819212644 5.40952860326317,43.2908314696123 5.40944442976698,43.2907730104534 5.40936560671584,43.2907071067812 5.40929289321881,43.2906343932842 5.40922698954664,43.290555570233 5.4091685303877,43.2904713967368 5.40911807873565,43.2903826834324 5.40907612046749,43.2902902846773 5.40904305966427,43.290195090322 5.4090192147196,43.2900980171403 5.40900481527333,43.29 5.409,43.2899019828597 5.40900481527333,43.289804909678 5.4090192147196,43.2897097153227 5.40904305966427,43.2896173165676 5.40907612046749,43.2895286032632 5.40911807873565,43.289444429767 5.4091685303877,43.2893656067158 5.40922698954664,43.2892928932188 5.40929289321881,43.2892269895466 5.40936560671584,43.2891685303877 5.40944442976698,43.2891180787356 5.40952860326317,43.2890761204675 5.40961731656764,43.2890430596643 5.40970971532275,43.2890192147196 5.40980490967798,43.2890048152733 5.40990198285967,43.289 5.41,43.2890048152733 5.41009801714033,43.2890192147196 5.41019509032202,43.2890430596643 5.41029028467725,43.2890761204675 5.41038268343237,43.2891180787356 5.41047139673683,43.2891685303877 5.41055557023302,43.2892269895466 5.41063439328416,43.2892928932188 5.41070710678119,43.2893656067158 5.41077301045336,43.289444429767 5.4108314696123,43.2895286032632 5.41088192126435,43.2896173165676 5.41092387953251,43.2897097153227 5.41095694033573,43.289804909678 5.4109807852804,43.2899019828597 5.41099518472667,43.29 5.411,43.2900980171403 5.41099518472667,43.290195090322 5.4109807852804,43.2902902846773 5.41095694033573,43.2903826834324 5.41092387953251,43.2904713967368 5.41088192126435,43.290555570233 5.4108314696123,43.2906343932842 5.41077301045336,43.2907071067812 5.41070710678119,43.2907730104534 5.41063439328416,43.2908314696123 5.41055557023302,43.2908819212644 5.41047139673683,43.2909238795325 5.41038268343237,43.2909569403357 5.41029028467725,43.2909807852804 5.41019509032202,43.2909951847267 5.41009801714033,43.291 5.41))

在突尼斯POINT（9.76 36.61）中，离赤道稍远的相同程序：

POLYGON((9.761 36.61,9.76099518472667 36.6099019828597,9.7609807852804 36.609804909678,9.76095694033573 36.6097097153227,9.76092387953251 36.6096173165676,9.76088192126435 36.6095286032632,9.7608314696123 36.609444429767,9.76077301045336 36.6093656067158,9.76070710678119 36.6092928932188,9.76063439328416 36.6092269895466,9.76055557023302 36.6091685303877,9.76047139673683 36.6091180787356,9.76038268343236 36.6090761204675,9.76029028467725 36.6090430596643,9.76019509032202 36.6090192147196,9.76009801714033 36.6090048152733,9.76 36.609,9.75990198285967 36.6090048152733,9.75980490967798 36.6090192147196,9.75970971532275 36.6090430596643,9.75961731656763 36.6090761204675,9.75952860326317 36.6091180787356,9.75944442976698 36.6091685303877,9.75936560671584 36.6092269895466,9.75929289321881 36.6092928932188,9.75922698954664 36.6093656067158,9.7591685303877 36.609444429767,9.75911807873565 36.6095286032632,9.75907612046749 36.6096173165676,9.75904305966427 36.6097097153227,9.7590192147196 36.609804909678,9.75900481527333 36.6099019828597,9.759 36.61,9.75900481527333 36.6100980171403,9.7590192147196 36.610195090322,9.75904305966427 36.6102902846773,9.75907612046749 36.6103826834324,9.75911807873565 36.6104713967368,9.7591685303877 36.610555570233,9.75922698954664 36.6106343932842,9.75929289321881 36.6107071067812,9.75936560671584 36.6107730104534,9.75944442976698 36.6108314696123,9.75952860326317 36.6108819212644,9.75961731656763 36.6109238795325,9.75970971532275 36.6109569403357,9.75980490967798 36.6109807852804,9.75990198285967 36.6109951847267,9.76 36.611,9.76009801714033 36.6109951847267,9.76019509032202 36.6109807852804,9.76029028467725 36.6109569403357,9.76038268343236 36.6109238795325,9.76047139673683 36.6108819212644,9.76055557023302 36.6108314696123,9.76063439328416 36.6107730104534,9.76070710678119 36.6107071067812,9.76077301045336 36.6106343932842,9.7608314696123 36.610555570233,9.76088192126435 36.6104713967368,9.76092387953251 36.6103826834324,9.76095694033573 36.6102902846773,9.7609807852804 36.610195090322,9.76099518472667 36.6100980171403,9.761 36.61))

这个更远，在挪威北部的< code >点(23.20 69.94):

POLYGON((23.201 69.94,23.2009951847267 69.9399019828597,23.2009807852804 69.939804909678,23.2009569403357 69.9397097153227,23.2009238795325 69.9396173165676,23.2008819212643 69.9395286032632,23.2008314696123 69.939444429767,23.2007730104534 69.9393656067158,23.2007071067812 69.9392928932188,23.2006343932842 69.9392269895466,23.200555570233 69.9391685303877,23.2004713967368 69.9391180787356,23.2003826834324 69.9390761204675,23.2002902846773 69.9390430596643,23.200195090322 69.9390192147196,23.2000980171403 69.9390048152733,23.2 69.939,23.1999019828597 69.9390048152733,23.199804909678 69.9390192147196,23.1997097153227 69.9390430596643,23.1996173165676 69.9390761204675,23.1995286032632 69.9391180787356,23.199444429767 69.9391685303877,23.1993656067158 69.9392269895466,23.1992928932188 69.9392928932188,23.1992269895466 69.9393656067158,23.1991685303877 69.939444429767,23.1991180787357 69.9395286032632,23.1990761204675 69.9396173165676,23.1990430596643 69.9397097153227,23.1990192147196 69.939804909678,23.1990048152733 69.9399019828597,23.199 69.94,23.1990048152733 69.9400980171403,23.1990192147196 69.940195090322,23.1990430596643 69.9402902846772,23.1990761204675 69.9403826834324,23.1991180787357 69.9404713967368,23.1991685303877 69.940555570233,23.1992269895466 69.9406343932842,23.1992928932188 69.9407071067812,23.1993656067158 69.9407730104534,23.199444429767 69.9408314696123,23.1995286032632 69.9408819212643,23.1996173165676 69.9409238795325,23.1997097153227 69.9409569403357,23.199804909678 69.9409807852804,23.1999019828597 69.9409951847267,23.2 69.941,23.2000980171403 69.9409951847267,23.200195090322 69.9409807852804,23.2002902846773 69.9409569403357,23.2003826834324 69.9409238795325,23.2004713967368 69.9408819212643,23.200555570233 69.9408314696123,23.2006343932842 69.9407730104534,23.2007071067812 69.9407071067812,23.2007730104534 69.9406343932842,23.2008314696123 69.940555570233,23.2008819212643 69.9404713967368,23.2009238795325 69.9403826834324,23.2009569403357 69.9402902846772,23.2009807852804 69.940195090322,23.2009951847267 69.9400980171403,23.201 69.94))

要使您的缓冲区忽略投影造成的失真，请考虑以下查询(意大利的< code>POINT):

SELECT 
  ST_Buffer(
    ST_GeomFromGeoJSON('{"type":"Point","coordinates":[11.26,44.42]}')::geography,
1000,'quad_segs=16')::geometry;

解释:

使用< code>GEOMETRY和< code>GEOGRAPHY的计算方式不同，结果也不同。< code>GEOGRAPHY计算球面上的坐标(可能比< code>GEOMETRY慢得多)，并使用< code >米作为测量单位，而< code>GEOMETRY使用平面投影并使用SRS单位。

注意：最后一个查询末尾的GEOMETRY转换对于OP的用例是必要的，请参阅下面的注释。