I'm very beginner in postgis and geojson,
I should extract every geometry from the geojson and store them in the postgis database as polygons
For each entry in geojson I would like to be able to store its geometry in the database separately as it is in json
In the database the geometry type is:
ALTER TABLE public.a_final ADD COLUMN geometry geometry (Polygon, 4326);
What I have done so far:
$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'];
}
GeoJSON:
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
)
)
)
)
You don't need to do all the heavy lifting of translating GeoJSON into SQL, you may simply throw it to PostgreSQL, using ST_GeomFromGeoJSON(). Then, you simply do something, like:
<?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);
}
?>