Convert JSON containing geometry to postgreSQL

Question

I have a JSON file that contains the attribute of several parks along with location (geometry) as a point. I was wondering how to convert the JSON to postgreSQL format. Indeed, I have tried several ways such as SQLizer and MapForce, but I was not able to convert them. Is there any way to convert this JSON which has geometry, to postgreSQL format?

I appreciate any help.

Below you can find the script.

var lenneparks = {
  "type": "FeatureCollection",
  "crs": {
    "type": "name",
    "properties": {
      "name": "urn:ogc:def:crs:OGC:1.3:CRS84"
    }
  },
  "features": [
    {
      "type": "Feature",
      "properties": {
        "place": "Aachen Kurpark",
        "year": "1853 (Hi)",
        "text": "Elisengarten, kleine Parkanlage in der Innenstadt, rückwärtig vom Elisenbrunnen"
      },
      "geometry": {
        "type": "Point",
        "coordinates": [
          6.086027,
          50.774247
        ]
      }
    },
    {
      "type": "Feature",
      "properties": {
        "place": "Aachen",
        "year": "ca. 1862 (Hi)",
        "text": "Staatsprokurator Dubusc"
      },
      "geometry": {
        "type": "Point",
        "coordinates": [
          6.0838868,
          50.7653455
        ]
      }
    }
      ]
};

Answer 1

EDIT 1: Corrected SQL comments

Have you tried the PostGIS extension? It comes with really handy functions to import such data, such as:

-- To create a geometry object from your GeoJSON
SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}') As geometry;

-- To see the WKT of your GeoJSON
SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}')) As geometry;

EDIT 2: Creating records for each geometry

This function will create a table containing one record for each json element in the array features, from there you can start parsing the data you need for creating your tables... I hope it helps:

CREATE TEMPORARY TABLE features AS 
SELECT json_array_elements('{
  "type": "FeatureCollection",
  "crs": {
    "type": "name",
    "properties": {
      "name": "urn:ogc:def:crs:OGC:1.3:CRS84"
    }
  },
  "features": [
    {
      "type": "Feature",
      "properties": {
        "place": "Aachen Kurpark",
        "year": "1853 (Hi)",
        "text": "Elisengarten, kleine Parkanlage in der Innenstadt, rückwärtig vom Elisenbrunnen"
      },
      "geometry": {
        "type": "Point",
        "coordinates": [
          6.086027,
          50.774247
        ]
      }
    },
    {
      "type": "Feature",
      "properties": {
        "place": "Aachen",
        "year": "ca. 1862 (Hi)",
        "text": "Staatsprokurator Dubusc"
      },
      "geometry": {
        "type": "Point",
        "coordinates": [
          6.0838868,
          50.7653455
        ]
      }
    }
      ]
}'::JSON -> 'features') as features;


SELECT * FROM features;

EDIT 3: Query to extract info from json table

SELECT 
  features -> 'geometry' -> 'coordinates' -> 0 AS lat,
  features -> 'geometry' -> 'coordinates' -> 1 AS lon,
  features -> 'properties' -> 'place'::TEXT,
  features -> 'properties' -> 'year'::TEXT,
  features -> 'properties' -> 'text'::TEXT
  FROM features;

EDIT 4: Extracting geometries from the json table and converting them into WKT and Geometry

SELECT ST_GeomFromGeoJSON((features -> 'geometry')::text)
FROM features;  

SELECT ST_AsText(ST_GeomFromGeoJSON((features -> 'geometry')::TEXT))
FROM features;