postgresql PostGIS - 将多多边形转换为单多边形
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21719941/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostGIS - convert multipolygon to single polygon
提问by mdomnita
Is it possible to import a shape file containing multipolygons into single polygon in PostGIS? Whenever I try importing a shape file of a polygon, it is stored as a multipolygon (as opposed to a single polygon) in a geomcolumn. Thus, I am unable to extract it as a single polygon value from the multipolygon.
是否可以将包含多多边形的形状文件导入 PostGIS 中的单个多边形？每当我尝试导入多边形的形状文件时，它都会以多多边形（而不是单个多边形）的形式存储在geom列中。因此，我无法从多多边形中将其提取为单个多边形值。
All helpful suggestions much appreciated
非常感谢所有有用的建议
回答by Tyler
I used ST_DUMPto convert a table of multipolygon geometries in PostgreSQL to a new table with polygon geometries and other columns of data.
我使用ST_DUMP将 PostgreSQL 中的多多边形几何表转换为具有多边形几何和其他数据列的新表。
CREATE TABLE poly AS --poly will be the new polygon table
WITH dump AS (
SELECT id, test, --columns from your multipolygon table
(ST_DUMP(geometry)).geom AS geometry
FROM multi --the name of your multipolygon table
)
SELECT id, test,
geometry::geometry(Polygon,4326) --type cast using SRID from multipolygon
FROM dump;
Update:I think this could be accomplished much easier with this query.
更新：我认为使用此查询可以更轻松地完成此操作。
CREATE TABLE polygon_table AS
SELECT id, example_column, (ST_DUMP(geom)).geom::geometry(Polygon,4326) AS geom FROM multipolygon_table
回答by mdomnita
You can use ST_GeometryNtogether with ST_NumGeometriesand the generate_series function to obtain what you need.
您可以将ST_GeometryN与ST_NumGeometries和 generate_series 函数一起使用以获得所需的内容。
Let's assume you have the table from Jakub's example:
假设您有 Jakub 示例中的表格：
CREATE TABLE multi AS(
SELECT 1 as id, 2 as test, ST_GeomFromText('MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0)),((1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))') AS geom
);
This one contains a multipolygon, an id and another column.
这个包含一个多面体、一个 id 和另一列。
To get each single polygon from the table including all other attributes try something like:
要从表中获取每个多边形，包括所有其他属性，请尝试以下操作：
SELECT id, test, ST_GeometryN(geom, generate_series(1, ST_NumGeometries(geom))) AS geom
FROM multi
"id" and "test" are the values for each row in the original table. generate_series creates a series of numbers from 1 to the number of geometries in each row.
“id”和“test”是原始表中每一行的值。generate_series 创建一系列从 1 到每行几何图形数的数字。
Therefore you will split each multi geometry in its separate single geometry parts and the values in the other columns remain the same.
因此，您将在其单独的单个几何部分中拆分每个多几何，并且其他列中的值保持不变。
Just replace the columns and table in the example with the columns from your exported shapefile and you will get the table with the single polygons.
只需用导出的 shapefile 中的列替换示例中的列和表，您将获得带有单个多边形的表。
Hope this answers your question.
希望这能回答你的问题。
回答by Jakub Kania
Import into a staging table and then use ST_DUMPto brake the multigeom into individual pieces and use that to populate the destination table.
导入到临时表中，然后使用ST_DUMP将 multigeom 分成单独的部分，并使用它来填充目标表。
UPDATE
更新
You import all the data you need into a staging table (let's call it multi) and then use ST_DUMPto break the mutligeometry into single geometries:
您将需要的所有数据导入到临时表中（我们称之为multi），然后用于ST_DUMP将多几何分解为单个几何：
WITH multi AS(
SELECT 1 as id, 2 as test, ST_GeomFromText('MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0)),((1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))') as poli
)
,dump AS(
SELECT id
,test
,ST_DUMP(poli) as d
FROM multi)
SELECT id
,test
,(dump.d).path
,ST_AsTEXT((dump.d).geom)
FROM dump
