php - 如何在postgis中从php和insrt poligon转换geojson中的几何图形
问题描述
我是 postgis 和 geojson 的初学者,
我应该从geojson中提取每个几何图形并将它们作为多边形存储在postgis数据库中
对于 geojson 中的每个条目,我希望能够将其几何图形单独存储在数据库中,就像它在 json 中一样
在数据库中,几何类型为:
ALTER TABLE public.a_final ADD COLUMN geometry geometry (Polygon, 4326);
到目前为止我做了什么:
$geojson = file_get_contents("o/1.geojson");
//echo $geojson;
$array = json_decode($geojson, TRUE);
echo "<pre>";
print_r($array);
echo "</pre>";
foreach($array['features'] as $type){
$layer = $type['properties']['Layer'];
$SubClasses = $type['properties']['SubClasses'];
$ExtendedEntity = $type['properties']['ExtendedEntity'];
$Linetype = $type['properties']['Linetype'];
$EntityHandle = $type['properties']['EntityHandle'];
$Text = $type['properties']['Text'];
$geometry = $type['geometry'];
}
地理JSON:
Array
(
[type] => FeatureCollection
[features] => Array
(
[0] => Array
(
[type] => Feature
[properties] => Array
(
[Layer] => Green Area
[SubClasses] => AcDbEntity: AcDbPolyline
[ExtendedEntity] =>
[Linetype] =>
[EntityHandle] => 6B
[Text] =>
)
[geometry] => Array
(
[type] => LineString
[coordinates] => Array
(
[0] => Array
(
[0] => 708305.979
[1] => 385139.794
)
[1] => Array
(
[0] => 708433.3
[1] => 384989.4
)
[2] => Array
(
[0] => 708434.8
[1] => 384987.7
)
[3] => Array
(
[0] => 708400.454
[1] => 384958.526
)
[4] => Array
(
[0] => 708259.989
[1] => 385100.729
)
[5] => Array
(
[0] => 708305.979
[1] => 385139.794
)
)
)
)
[1] => Array
(
[type] => Feature
[properties] => Array
(
[Layer] => Green Area
[SubClasses] => AcDbEntity: AcDbPolyline
[ExtendedEntity] =>
[Linetype] =>
[EntityHandle] => 6C
[Text] =>
)
[geometry] => Array
(
[type] => LineString
[coordinates] => Array
(
[0] => Array
(
[0] => 702461.518
[1] => 381980.706
)
[1] => Array
(
[0] => 702436.783
[1] => 381924.255
)
[2] => Array
(
[0] => 702427.496
[1] => 381927.398
)
[3] => Array
(
[0] => 702452.871
[1] => 381985.312
)
[4] => Array
(
[0] => 702461.518
[1] => 381980.706
)
)
)
)
解决方案
您不需要将 GeoJSON 转换为 SQL 的所有繁重工作,您可以简单地将其扔给 PostgreSQL,使用ST_GeomFromGeoJSON()
. 然后,您只需做一些事情,例如:
<?php
//load GeoJSON
$geojson = file_get_contents("o/1.geojson");
//Translate that into JSON-compliant array of features
$features = json_decode($geojson, TRUE)->features;
//Connect to your database
$connection = pg_connect(....);
//Iterate over features within FeatureCollection
foreach($features as $feature) {
//Extract necessary attributes
$layer = $feature->properties["Layer"];
$subClasses = $feature->properties["SubClasses"];
....
//Extract geometry attribute as a string
$geomJson = json_encode($feature->geometry);
//Make up SQL-query
$sql = "insert into mytable (layer, subclasses, ...., geom) values ('".$layer."','".$subClasses."',..., st_geomfromgeojson('".$geomJson."')";
//Execute the query
pg_query($connection, $sql);
}
?>
推荐阅读
- android - RecyclerView 中的 LinearLayout 不滚动
- python - 如何在 Tensorflow 中使用 SWA 实现 Batch Norm?
- r - 用 ggplot 绘制一个自由度为 1 的卡方图
- node.js - 使用确认时的 Alexa 递归意图委托
- css - 网格列未按预期换行
- powershell - 这堂课有什么问题?
- android - Firebase Cloud Functions 是否在 Android 的主线程之外运行?
- objective-c - Obj-C - 无法访问 NSArray 中的值?
- swift - 如何将自定义 UIImage 设置为标签栏项目
- ios - 获取图像后如何更新 tableViewCell 高度?