如何将GeoJSON多边形插入PostGIS表中？

debugcn 发表于 Dev

哈维尔

我是postgis的新手，我不知道为什么在将mulipolygon插入数据库时，该值为空。

我正在将postgressql与postgis插件一起使用。

我使用以下代码创建表： CREATE TABLE IF NOT EXISTS countries ( table_id SERIAL, properties jsonb not null, geom geometry(GeometryZ,4326), primary key (table_id));

我读了一个文件，然后按功能插入。所有不是multipolygon的要素都可以很好地插入，但是当我尝试插入multipolygon时，插入的值为空。

我试着插入这个例子，它的工作原理是：

INSERT INTO test ( properties, geom ) VALUES ( '{"name":"Argentina"}', ST_Force3D(ST_SetSRID(ST_GeomFromGeoJSON('{ 
  "type": "MultiPolygon",
  "coordinates": 
    [[[[-66.45, -55.25], [-66.45, -55.25], [-66.45, -55.25], [-66.45, -55.25], [-66.45, -55.25]]],
    [[[-66.45, -55.25], [-66.45, -55.25], [-66.45, -55.25], [-66.45, -55.25], [-66.45, -55.25]],
    [[-66.45, -55.25], [-66.45, -55.25], [-66.45, -55.25], [-66.45, -55.25], [-66.45, -55.25]]]]
}'), 4326) ) );

但是，当我尝试从文件中插入实值时，请插入一个空值，但我不知道为什么。

INSERT INTO test ( properties, geom ) VALUES ( '{"name":"Argentina"}', ST_Force3D(ST_SetSRID(ST_GeomFromGeoJSON('{ 
  "type": "MultiPolygon",
  "coordinates": [[[ [-65.5, -55.2],[-66.45, -55.25],[-66.95992, -54.89681],[-67.56244, -54.87001],[-68.63335, -54.8695],[-68.63401, -52.63637],[-68.25, -53.1],[-67.75, -53.85],[-66.45, -54.45],[-65.05, -54.7],[-65.5, -55.2]]],[[[-64.964892,-22.075862],[-64.377021,-22.798091],[-63.986838,-21.993644],[-62.846468,-22.034985],[-62.685057,-22.249029],[-60.846565,-23.880713],[-60.028966,-24.032796],[-58.807128,-24.771459],[-57.777217,-25.16234],[-57.63366,-25.603657],[-58.618174,-27.123719],[-57.60976,-27.395899],[-56.486702,-27.548499],[-55.695846,-27.387837],[-54.788795,-26.621786],[-54.625291,-25.739255],[-54.13005,-25.547639],[-53.628349,-26.124865],[-53.648735,-26.923473],[-54.490725,-27.474757],[-55.162286,-27.881915],[-56.2909,-28.852761],[-57.625133,-30.216295],[-57.874937,-31.016556],[-58.14244,-32.044504],[-58.132648,-33.040567],[-58.349611,-33.263189],[-58.427074,-33.909454],[-58.495442,-34.43149],[-57.22583,-35.288027],[-57.362359,-35.97739],[-56.737487,-36.413126],[-56.788285,-36.901572],[-57.749157,-38.183871],[-59.231857,-38.72022],[-61.237445,-38.928425],[-62.335957,-38.827707],[-62.125763,-39.424105],[-62.330531,-40.172586],[-62.145994,-40.676897],[-62.745803,-41.028761],[-63.770495,-41.166789],[-64.73209,-40.802677],[-65.118035,-41.064315],[-64.978561,-42.058001],[-64.303408,-42.359016],[-63.755948,-42.043687],[-63.458059,-42.563138],[-64.378804,-42.873558],[-65.181804,-43.495381],[-65.328823,-44.501366],[-65.565269,-45.036786],[-66.509966,-45.039628],[-67.293794,-45.551896],[-67.580546,-46.301773],[-66.597066,-47.033925],[-65.641027,-47.236135],[-65.985088,-48.133289],[-67.166179,-48.697337],[-67.816088,-49.869669],[-68.728745,-50.264218],[-69.138539,-50.73251],[-68.815561,-51.771104],[-68.149995,-52.349983],[-68.571545,-52.299444],[-69.498362,-52.142761],[-71.914804,-52.009022],[-72.329404,-51.425956],[-72.309974,-50.67701],[-72.975747,-50.74145],[-73.328051,-50.378785],[-73.415436,-49.318436],[-72.648247,-48.878618],[-72.331161,-48.244238],[-72.447355,-47.738533],[-71.917258,-46.884838],[-71.552009,-45.560733],[-71.659316,-44.973689],[-71.222779,-44.784243],[-71.329801,-44.407522],[-71.793623,-44.207172],[-71.464056,-43.787611],[-71.915424,-43.408565],[-72.148898,-42.254888],[-71.746804,-42.051386],[-71.915734,-40.832339],[-71.680761,-39.808164],[-71.413517,-38.916022],[-70.814664,-38.552995],[-71.118625,-37.576827],[-71.121881,-36.658124],[-70.364769,-36.005089],[-70.388049,-35.169688],[-69.817309,-34.193571],[-69.814777,-33.273886],[-70.074399,-33.09121],[-70.535069,-31.36501],[-69.919008,-30.336339],[-70.01355,-29.367923],[-69.65613,-28.459141],[-69.001235,-27.521214],[-68.295542,-26.89934],[-68.5948,-26.506909],[-68.386001,-26.185016],[-68.417653,-24.518555],[-67.328443,-24.025303],[-66.985234,-22.986349],[-67.106674,-22.735925],[-66.273339,-21.83231],[-64.964892,-22.075862] ]]]}},
}'), 4326) ) );

这是文件：https : //pastebin.com/MRcQDV5s

编辑：

有用！

此功能运行完美。

CREATE TABLE IF NOT EXISTS test ( table_id SERIAL, properties jsonb not null, geom geometry(GeometryZ,4326), primary key (table_id))

INSERT INTO test ( properties, geom ) VALUES ( '" + property + "', ST_Force3D(ST_SetSRID(ST_GeomFromGeoJSON('" + geometry + "'), 4326) ));

每种不同的类型都可以完美地存储。

问题是pgAdmin版本不显示数据库中的数据。

JGH

该示例几何是无效的，因此其与任何PostGIS功能的（不）可用性不可信。

忽略Z组件，表中的列为简单多边形，而输入为多多边形。根据您的需求，您有两种选择

1）更改表定义，以便它可以存储多面并使用当前查询填充它（geometry(MultiPolygonZ,4326)）

2）保留当前表的定义，但将多部分拆分为单个部分。也就是说，如果源多边形具有3个部分，则它将在表中创建3个记录。使用st_dump此。

INSERT INTO test ( properties, geom ) 
VALUES ( '{"name":"Argentina"}', 
ST_Force3D(
  (ST_DUMP(
    ST_SetSRID( 
        ST_GeomFromGeoJSON('{...}'),
    4326)
  )).geom ) 
);

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。