mysql - 如何在mysql中访问多边形坐标

Question

能否请您帮我找到正确的解决方案，如何访问 mysql 中的多边形对象的各个点？这是对象:

"geometry": {
        "type": "MultiPolygon",
        "coordinates": [
            [
                [
                    [-105.00432014465332, 39.74732195489861],
                    [-105.00715255737305, 39.74620006835170],
                    [-105.00921249389647, 39.74468219277038],
                    [-105.01067161560059, 39.74362625960105],
                    [-105.01195907592773, 39.74290029616054],
                    [-105.00989913940431, 39.74078835902781],
                    [-105.00758171081543, 39.74059036160317],
                    [-105.00346183776855, 39.74059036160317],
                    [-105.00097274780272, 39.74059036160317],
                    [-105.00062942504881, 39.74072235994946],
                    [-105.00020027160645, 39.74191033368865],
                    [-105.00071525573731, 39.74276830198601],
                    [-105.00097274780272, 39.74369225589818],
                    [-105.00097274780272, 39.74461619742136],
                    [-105.00123023986816, 39.74534214278395],
                    [-105.00183105468751, 39.74613407445653],
                    [-105.00432014465332, 39.74732195489861]
                ],[
                    [-105.00361204147337, 39.74354376414072],
                    [-105.00301122665405, 39.74278480127163],
                    [-105.00221729278564, 39.74316428375108],
                    [-105.00283956527711, 39.74390674342741],
                    [-105.00361204147337, 39.74354376414072]
                ]
            ],[
                [
                    [-105.00942707061768, 39.73989736613708],
                    [-105.00942707061768, 39.73910536278566],
                    [-105.00685214996338, 39.73923736397631],
                    [-105.00384807586671, 39.73910536278566],
                    [-105.00174522399902, 39.73903936209552],
                    [-105.00041484832764, 39.73910536278566],
                    [-105.00041484832764, 39.73979836621592],
                    [-105.00535011291504, 39.73986436617916],
                    [-105.00942707061768, 39.73989736613708]
                ]
            ]
        ]
    }

这实际上是两个对象的数组，其中第一个对象有两个几何图形，第二个对象有两个几何图形。我可以使用这些查询访问第一个对象的几何图形 SELECT AsText( GeometryN( geo_type, 1)) FROM polygon_park;和 SELECT AsText( GeometryN( geo_type, 1)) FROM polygon_park; 我得到了这个结果

 POLYGON((-105.00432014465332 39.74732195489861),(-105.00715255737305 39.7462000683517),(-105.00921249389647 39.74468219277038),(-105.01067161560059 39.74362625960105),(-105.01195907592773 39.74290029616054),(-105.00989913940431 39.74078835902781),(-105.00758171081543 39.74059036160317),(-105.00346183776855 39.74059036160317),(-105.00097274780272 39.74059036160317),(-105.00062942504881 39.74072235994946),(-105.00020027160645 39.74191033368865),(-105.0007152557373 39.74276830198601),(-105.00097274780272 39.74369225589818),(-105.00097274780272 39.74461619742136),(-105.00123023986816 39.74534214278395),(-105.00183105468751 39.74613407445653),(-105.00432014465332 39.74732195489861))

和

POLYGON((-105.00361204147337 39.74354376414072),(-105.00301122665405 39.74278480127163),(-105.00221729278564 39.74316428375108),(-105.00283956527711 39.74390674342741),(-105.00361204147337 39.74354376414072))

，但我不知道如何访问具有以下坐标的第二个对象。

 "[-105.00361204147337, 39.74354376414072],
                        [-105.00301122665405, 39.74278480127163],
                        [-105.00221729278564, 39.74316428375108],
                        [-105.00283956527711, 39.74390674342741],
                        [-105.00361204147337, 39.74354376414072]"

然而，在这两种情况下，我都无法访问每个几何体中的各个点。我需要知道这一点，以便在 php 中将此对象解析为 json。

非常感谢您!

Answer 1

要访问第一个多边形的第二个对象，您可以使用 InteriorRingN(poly, index)，其中索引基于 1，请参阅 the docs for Polygon functions .

所以，在你的情况下，你会这样做:

SELECT AsText( InteriorRingN(GeometryN(geo_type, 1), 1)) FROM polygon_park;

要获得个人积分，请使用 PointN function一个线串。您首先必须将多边形环转换为线串，为此您可以使用 Exteriorring 或 InteriorRingN 函数，然后您可以访问这些点。

因此，例如，要获得第一个多边形外环的第 4 个点，您可以这样做:

SELECT AsText( PointN(ExteriorRing(GeometryN(geo_type, 1)), 4)) FROM polygon_park;

要获取实际值而不是文本表示，您可以使用 X 和 Y 函数而不是 AsText。

对于您的示例，使用 WKT 而不是 GeoJSON 可能会更清楚，但是由于 WKT 中圆括号的含义与 GeoJSON 中方括号的含义之间存在一对一的映射，因此在翻译中不会丢失任何内容，因此说话。