Insert data in Table using a pre-specified json Postgresql

Question

I have a geojson pre-specified as data in PostgreSQL pgAdmin4 query tool.I am trying to insert data into table but as my JSON is large so instead of manually doing Insert into I do this:

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
      }
    }
   ]
 }'::json AS fc)

Now trying to insert this data into table like this:

INSERT INTO locations (gid, geom, properties) VALUES ( data );

It gives me an error that column data doesn't exist.But when I do this:

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;

This query shows data. My issue is that when I want I can select and see data but as this is not stores anywhere so everytime I need to do WITH data as declare json and apply the select query. So I want to inset value in to my table so I can call this anytime.

Also see stackoverflow.com/q/3306096/1965736

philngo
– philngo

2018-02-12 06:22:39 +00:00
Commented Feb 12, 2018 at 6:22 — philngo
– philngo, Commented Feb 12, 2018 at 6:22

philngo · Accepted Answer · 2018-02-12 06:21:27Z

1

You should be able to directly use your SELECT in place of the VALUES statement, like so:

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
      }
    }
   ]
 }'::json AS fc)
INSERT INTO locations (gid, geom, properties)
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;

answered Feb 12, 2018 at 6:21

philngo

9417 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert data in Table using a pre-specified json Postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related