postgis Multi类型转simple类型

有些情况获得的数据是Multi类型的，如MultiPolygon和MultiLineString类型，这些类型在空间计算上，很多函数都用不上。像如MultiLineString类型想进行最短路径查询就比较不方便。postgis导入的时候能够选择simple类型，但是有时候就是导不进去。

image.png image.png

如上图这种情况，其实就可以将这些多边形进行打散，进行单独处理。这就需要用到st_numgeometries和st_geometryN两个函数了。

integer ST_NumGeometries(geometry geom);
Returns the number of Geometries. If geometry is a GEOMETRYCOLLECTION (or MULTI*) return the number of geometries, for single geometries will return 1, otherwise return NULL.

SELECT ST_NumGeometries(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));

st_numgeometries 计算Multi的单个数量

geometry ST_GeometryN(geometry geomA, integer n);

Return the 1-based Nth geometry if the geometry is a GEOMETRYCOLLECTION, (MULTI)POINT, (MULTI)LINESTRING, MULTICURVE or (MULTI)POLYGON, POLYHEDRALSURFACE Otherwise, return NULL


SELECT
    n,
    ST_AsEWKT (ST_GeometryN(the_geom, n)) AS geomewkt
FROM
    (

        VALUES
            (
                ST_GeomFromEWKT (
                    'MULTIPOINT(1 2 7, 3 4 7, 5 6 7, 8 9 10)'
                )
            ),
            (
                ST_GeomFromEWKT (
                    'MULTICURVE(CIRCULARSTRING(2.5 2.5,4.5 2.5, 3.5 3.5), (10 11, 12 11))'
                )
            )
    ) AS foo (the_geom)
CROSS JOIN generate_series (1, 100) n
WHERE
    n <= ST_NumGeometries (the_geom);

ST_GeometryN 取index为 n的geometry

    SELECT st_geometryN(geom,n) from grid_class_sure_hex gcsh CROSS JOIN generate_series (1, 100) n where gcsh.name='aaaaa' and n <= ST_NumGeometries (gcsh.geom);
    
    --其中generate_series (1, 100)可以根据情况自己设置

最后的每个小的多边形都可以点击，是能够达到效果的。