Inserting GeoJSON in PostgreSQL using laravel controller

Question

Actually.. I have GeoJSON that I send from javascript into controller. My GeoJSON:

{
 "type": "FeatureCollection",
 "features": [{
 "type": "Feature",
 "geometry": {
 "type": "Point",
 "coordinates": [101.21719328770531, 3.113987284801912]
 },
 "properties": null
 }, {
 "type": "Feature",
 "geometry": {
 "type": "LineString",
 "coordinates": [
 [101.18330230120145, 3.046205311794175],
 [101.13585492009604, 2.985201536087212],
 [101.19008049850223, 2.9174195630794753]
 ]
 },
 "properties": null
 }]
}

and then I found this single heterogeneous GEOMETRYCOLLECTION from this link

So, My SQL something like this:

SELECT public.ST_SetSRID(public.ST_Collect(public.ST_GeomFromGeoJSON(feat->>'geometry')), 4326) as geom
FROM (
 SELECT json_array_elements('{
 "type":"FeatureCollection",
 "features":[{
 "type":"Feature",
 "geometry":{
 "type":"Point",
 "coordinates":[101.21719328770531,3.113987284801912]
 },
 "properties":null
 },{
 "type":"Feature",
 "geometry":{
 "type":"LineString",
 "coordinates":[[101.18330230120145,3.046205311794175],
 [101.13585492009604,2.985201536087212],
 [101.19008049850223,2.9174195630794753]
 ]
 },
 "properties":null
 }]
 }'::json->'features') AS feat
) AS f;

It can run it ... but, how can I insert this into my column geom in my table by using something like this 'geom' => db::raw("from my sql")?

• geozelot: INSERT INTO <your_table> (geom) (<your_query_above>);? Or UPDATE <your_table> SET geom = <your_query_above_without_select> WHERE <you_want_it>;?

Answer 1

Actually I already got answer..hahah.. first I send type string only to check type of GeoJson either Point, LineString, or Polygon.. the code something like this:

 $GeoJsonToData = json_decode($req->feature, true);
 $typeFeature = $GeoJsonToData["type"];

after that, Using condition if else to post that geometry into Table postgres db (Point/LineString/Polygon).. My code:

if($typeFeature == 'Point'){
 $savePointData = array(
 'name' => $req->name,
 'details' => $req->details,
 'geom' => \DB::raw("public.ST_SetSRID(public.ST_GeomFromGeoJSON('$req->feature'), 4326)")
 );
 $allDataRekod = DrawPoint::create($savePointData);
 return "DrawSaving";
 }
 else if($typeFeature == 'LineString'){
 $saveLineStringData = array(
 'name' => $req->name,
 'details' => $req->details,
 'geom' => \DB::raw("public.ST_SetSRID(public.ST_GeomFromGeoJSON('$req->feature'), 4326)")
 );
 $allDataRekod = DrawLineString::create($saveLineStringData);
 return "DrawSaving";
 }
 else if($typeFeature == 'Polygon'){
 $savePolygonData = array(
 'name' => $req->name,
 'details' => $req->details,
 'geom' => \DB::raw("public.ST_SetSRID(public.ST_GeomFromGeoJSON('$req->feature'), 4326)")
 );
 $allDataRekod = DrawPolygon::create($savePolygonData);
 return "DrawSaving";
 }

And..Success!