0

I would like to store GeoJSON data into PostgreSQL database.

WITH data AS (SELECT '{ "type": "FeatureCollection",
    "features": [
      { "type": "Feature",
        "geometry": {"type": "Point", "coordinates": [102.0, 0.5]},
        "properties": {"prop0": "value0"}
        },
      { "type": "Feature",
        "geometry": {
          "type": "LineString",
          "coordinates": [
            [102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0]
            ]
          },
        "properties": {
          "prop0": "value0",
          "prop1": 0.0
          }
        },
      { "type": "Feature",
         "geometry": {
           "type": "Polygon",
           "coordinates": [
             [ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0],
               [100.0, 1.0], [100.0, 0.0] ]
             ]
         },
         "properties": {
           "prop0": "value0",
           "prop1": {"this": "that"}
           }
         }
       ]
     }'::json AS fc)

SELECT
  row_number() OVER () AS gid,
  ST_AsText(ST_GeomFromGeoJSON(feat->>'geometry')) AS geom,
  feat->'properties' AS properties
FROM (
  SELECT json_array_elements(fc->'features') AS feat
  FROM data
) AS f;

I can query but I need to save data as a table. Does anyone know how to store this kind data into a table in PostgreSQL.?

Reference to Storing GeoJSON FeatureCollection to PostgreSQL with PostGIS?

This reference does only query but I need save the layer as a table.

Ian Turton
  • 81,417
  • 6
  • 84
  • 185
Elvin
  • 231
  • 1
  • 10
  • 2
    What have you tried? It's very simple to convert JSON to an INSERT statement. – Vince Dec 18 '19 at 12:24
  • I do not have enough experience with the database, I need just store features to table in PostgreSQL. Such as creating a table, and inserting values for JSON type data. Could you please share a sample queries? – Elvin Dec 18 '19 at 12:27
  • once you have a select as shown in the other question creating a table is simple – Ian Turton Dec 18 '19 at 13:05
  • Okay, simple how? I can not find any real example of that issue. – Elvin Dec 18 '19 at 13:10
  • 1
    Create the table first, with an auto-populated primary key, a geometry column (of the geometry type, not text) and a jsonb properties field. Then, from your current query, remove the gid generation and also remove st_asText. At last, add INSERT INTO mytable (geometry, properties) just before the SELECT statement. – JGH Dec 18 '19 at 13:13
  • While there are thousands of answers here that include CREATE TABLE and INSERT statements, those all assume basic SQL competency and focus on geometry management and manipulation. GIS SE is not a place for basic intro SQL. There's plenty of documentation and intro primers on the web. Once you have some experience, this won't be a daunting task at all. – Vince Dec 18 '19 at 13:25
  • Thanks a lot JGH, Okay guys, sorry for disturbing I am new in that field therefore sometimes, I can not find the best way to solve issues. – Elvin Dec 18 '19 at 13:50

0 Answers0