ST_MAKEVALID
📄字数 4.7K
👁️阅读量 加载中...
功能描述
在不丢失顶点的情况下，把输入的无效几何体转换为有效的几何体。
语法格式
sql
GEOMETRY ST_MAKEVALID(GEOMETRY input);
GEOMETRY ST_MAKEVALID(GEOMETRY input, VARCHAR params);参数说明
- input：目标 GEOMETRY 对象。输入是点、多点、线串、多线串、多边形、多多边形、几何集合及其混合。
- params：选项字符串，用于构建有效几何图形的方法。
- 格式：
method=linework|structure keepcollapsed=true|false。 - 如果未提供params参数，则将使用linework算法作为默认值。
- method:
- linework：是原始算法，它通过首先提取所有线条、将线条节点在一起、然后从线条构建值输出来构建有效的几何图形。
- structure：是一种区分内环和外环的算法，通过合并外环来构建新的几何形状，然后区分所有内环。
- keepcollapsed：仅对 structure算法有效，其取值为 true 或 false。当设置为 false 时，会删除折叠到更低维度的几何组件，例如一维线串（LINESTRING）会被丢弃。
- 格式：
函数返回类型
GEOMETRY 类型
使用说明
- 有效几何图形原封不动地返回。
- 在完全或部分尺寸折叠的情况下，输出几何是相同或较低维度的几何集合，或者是较低维度几何的集合。
- 在自相交的情况下，单个多边形可能会变成多重几何图形。
- 该函数是由 GEOS 模块执行的。
- 该函数支持 3d 并且不会丢失 z-index。
示例
sql
SQL> SELECT TO_CHAR(ST_ASTEXT(ST_MAKEVALID('LINESTRING(0 0, 0 0)','method=structure keepcollapsed=true')));
+------------+
| EXPR1 |
+------------+
| POINT(0 0) |
+------------+
SQL> SELECT TO_CHAR(ST_ASTEXT(ST_MAKEVALID('LINESTRING(0 0, 0 0)','method=structure keepcollapsed=false')));
+------------------+
| EXPR1 |
+------------------+
| LINESTRING EMPTY |
+------------------+before_geom：由两个重叠多边形组成的多多边形
after_geom：具有四个非重叠多边形的多多边形
after_geom_structure：由单个非重叠多边形组成的多多边形
sql
SQL> SELECT TO_CHAR(ST_ASTEXT(f.geom)) AS before_geom, TO_CHAR(ST_ASTEXT(ST_MAKEVALID(f.geom))) AS after_geom, TO_CHAR(ST_ASTEXT(ST_MAKEVALID(f.geom, 'method=structure'))) AS after_geom_structure
FROM (SELECT 'MULTIPOLYGON(((186 194,187 194,188 195,189 195,190 195,
191 195,192 195,193 194,194 194,194 193,195 192,195 191,
195 190,195 189,195 188,194 187,194 186,14 6,13 6,12 5,11 5,
10 5,9 5,8 5,7 6,6 6,6 7,5 8,5 9,5 10,5 11,5 12,6 13,6 14,186 194)),
((150 90,149 80,146 71,142 62,135 55,128 48,119 44,110 41,100 40,
90 41,81 44,72 48,65 55,58 62,54 71,51 80,50 90,51 100,
54 109,58 118,65 125,72 132,81 136,90 139,100 140,110 139,
119 136,128 132,135 125,142 118,146 109,149 100,150 90)))'::geometry AS geom) AS f;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| BEFORE_GEOM | AFTER_GEOM | AFTER_GEOM_STRUCTURE |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| MULTIPOLYGON(((186 194,187 194,188 195,189 195,190 195,191 195,192 195,193 194,194 194,194 193,195 192,195 191,195 190,195 189,195 188,194 187,194 186,14 6,13 6,12 5,11 5,10 5,9 5,8 5,7 6,6 6,6 7,5 8,5 9,5 10,5 11,5 12,6 13,6 14,186 194)),((150 90,149 80,146 71,142 62,135 55,128 48,119 44,110 41,100 40,90 41,81 44,72 48,65 55,58 62,54 71,51 80,50 90,51 100,54 109,58 118,65 125,72 132,81 136,90 139,100 140,110 139,119 136,128 132,135 125,142 118,146 109,149 100,150 90))) | MULTIPOLYGON(((11 5,10 5,9 5,8 5,7 6,6 6,6 7,5 8,5 9,5 10,5 11,5 12,6 13,6 14,56.76923076923077 64.76923076923077,58 62,64 56,14 6,13 6,12 5,11 5)),((54 71,51 80,50 90,51 100,54 109,58 118,65 125,72 132,81 136,90 139,100 140,110 139,119 136,125.23076923076923 133.23076923076923,56.76923076923077 64.76923076923077,54 71)),((186 194,187 194,188 195,189 195,190 195,191 195,192 195,193 194,194 194,194 193,195 192,195 191,195 190,195 189,195 188,194 187,194 186,134 126,128 132,125.23076923076923 133.23076923076923,186 194)),((135 125,142 118,146 109,149 100,150 90,149 80,146 71,142 62,135 55,128 48,119 44,110 41,100 40,90 41,81 44,72 48,65 55,64 56,134 126,135 125))) | MULTIPOLYGON(((149 80,146 71,142 62,135 55,128 48,119 44,110 41,100 40,90 41,81 44,72 48,65 55,64 56,14 6,13 6,12 5,11 5,10 5,9 5,8 5,7 6,6 6,6 7,5 8,5 9,5 10,5 11,5 12,6 13,6 14,56.76923076923077 64.76923076923077,54 71,51 80,50 90,51 100,54 109,58 118,65 125,72 132,81 136,90 139,100 140,110 139,119 136,125.23076923076923 133.23076923076923,186 194,187 194,188 195,189 195,190 195,191 195,192 195,193 194,194 194,194 193,195 192,195 191,195 190,195 189,195 188,194 187,194 186,134 126,135 125,142 118,146 109,149 100,150 90,149 80))) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+before_geom：由六个重叠多边形组成的多多边形
after_geom：由 14 个不重叠多边形组成的多多边形
after_geom_structure：由单个非重叠多边形组成的多多边形
sql
SQL> SELECT TO_CHAR(st_astext(c.geom)) AS before_geom, TO_CHAR(st_astext(ST_MakeValid(c.geom))) AS after_geom,
TO_CHAR(st_astext(ST_MakeValid(c.geom, 'method=structure'))) AS after_geom_structure
FROM (SELECT 'MULTIPOLYGON(((91 50,79 22,51 10,23 22,11 50,23 78,51 90,79 78,91 50)),
((91 100,79 72,51 60,23 72,11 100,23 128,51 140,79 128,91 100)),
((91 150,79 122,51 110,23 122,11 150,23 178,51 190,79 178,91 150)),
((141 50,129 22,101 10,73 22,61 50,73 78,101 90,129 78,141 50)),
((141 100,129 72,101 60,73 72,61 100,73 128,101 140,129 128,141 100)),
((141 150,129 122,101 110,73 122,61 150,73 178,101 190,129 178,141 150)))'::geometry AS geom) AS c;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| BEFORE_GEOM | AFTER_GEOM | AFTER_GEOM_STRUCTURE |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| MULTIPOLYGON(((91 50,79 22,51 10,23 22,11 50,23 78,51 90,79 78,91 50)),((91 100,79 72,51 60,23 72,11 100,23 128,51 140,79 128,91 100)),((91 150,79 122,51 110,23 122,11 150,23 178,51 190,79 178,91 150)),((141 50,129 22,101 10,73 22,61 50,73 78,101 90,129 78,141 50)),((141 100,129 72,101 60,73 72,61 100,73 128,101 140,129 128,141 100)),((141 150,129 122,101 110,73 122,61 150,73 178,101 190,129 178,141 150))) | MULTIPOLYGON(((51 110,68.5 117.5,61 100,68.5 82.5,51 90,23 78,21.714285714285715 75,11 100,21.714285714285715 125,23 122,51 110)),((51 190,76 179.28571428571428,73 178,61 150,68.5 132.5,51 140,23 128,21.714285714285715 125,11 150,23 178,51 190)),((141 150,130.28571428571428 125,129 128,101 140,83.5 132.5,91 150,79 178,76 179.28571428571428,101 190,129 178,141 150)),((129 78,101 90,83.5 82.5,91 100,83.5 117.5,101 110,129 122,130.28571428571428 125,141 100,130.28571428571428 75,129 78)),((101 10,76 20.714285714285715,79 22,91 50,83.5 67.5,101 60,129 72,130.28571428571428 75,141 50,129 22,101 10)),((11 50,21.714285714285715 75,23 72,51 60,68.5 67.5,61 50,73 22,76 20.714285714285715,51 10,23 22,11 50)),((83.5 82.5,80.28571428571429 75,79 78,76 79.28571428571429,83.5 82.5)),((83.5 67.5,76 70.71428571428571,79 72,80.28571428571429 75,83.5 67.5)),((68.5 67.5,71.71428571428571 75,73 72,76 70.71428571428571,68.5 67.5)),((68.5 82.5,76 79.28571428571429,73 78,71.71428571428571 75,68.5 82.5)),((83.5 132.5,80.28571428571429 125,79 128,76 129.28571428571428,83.5 132.5)),((83.5 117.5,76 120.71428571428571,79 122,80.28571428571429 125,83.5 117.5)),((68.5 117.5,71.71428571428571 125,73 122,76 120.71428571428571,68.5 117.5)),((68.5 132.5,76 129.28571428571428,73 128,71.71428571428571 125,68.5 132.5))) | MULTIPOLYGON(((23 22,11 50,21.714285714285715 75,11 100,21.714285714285715 125,11 150,23 178,51 190,76 179.28571428571428,101 190,129 178,141 150,130.28571428571428 125,141 100,130.28571428571428 75,141 50,129 22,101 10,76 20.714285714285715,51 10,23 22))) |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+