Storing GeoJSON FeatureCollection to PostgreSQL with PostGIS?

Question

I am new to GeoJSON. I have a GeoJSON features collection as shown and would like to store it in postgres table (testtable). My postgres table has a serial id and geometry column.

{
 "type": "FeatureCollection",
 "features": [
 {
 "type": "Feature",
 "geometry": {
 "type": "Point",
 "coordinates": [
 2565453.1826721914,
 -3835048.659760314
 ]
 }
 },
 {
 "type": "Feature",
 "geometry": {
 "type": "LineString",
 "coordinates": [
 [
 2727584.7219710173,
 -3713449.1942418693
 ],
 [
 2732476.691781269,
 -3992291.473426192
 ]
 ]
 }
 },
 {
 "type": "Feature",
 "geometry": {
 "type": "Polygon",
 "coordinates": [
 [
 [
 2442627.9025405287,
 -3705499.954308534
 ],
 [
 2425506.008204649,
 -3886502.837287831
 ],
 [
 2425506.008204649,
 -3886502.837287831
 ],
 [
 2555143.2081763083,
 -3910962.686339088
 ],
 [
 2442627.9025405287,
 -3705499.954308534
 ]
 ]
 ]
 }
 }
 ]
}

I would like to insert the GeoJSON data into the table testtable.

How do I go about it?

I am using postgres version 9.3.5 with postgis version 2.1.3

---

I have been directed to previously asked questions which answer how to store a single feature eg a point or polygon. My question asks how to save multiple features in the GeoJSON file. By multiple features I mean a mixture of points, lines and polygon feature types in one file.

• Ricardo Oliveira: possible duplicate of How to insert a GeoJSON polygon into a PostGIS table?
• Jay: Hi Ricardo, I have seen that question but it does not address my problem. I want to save a list of features, not just a single feature type. Please have a look at my GeoJSON features collection in my question.
• Jakub Kania: @Jay So now either your question is "How do I break up a geojson collection into single features or you need to add more information (maybe storing information that those geometries belong to some sort of collection?)
• Jay: Thank you @John for your response. Since I am new to GIS and GeoJSON, I wanted some pointers with my problem at hand. Background of the question: A user draws the features on a map and I capture the collection of features drawn. I would like to save this collection in a DB with a unique id. Later, the saved data can be fetched for a supplied id. The testable in postgres has 2 columns. gid column that is a serial type, to hold the id, and geom column that is of geometry type.
• Jakub Kania: @Jay Yes, you can store the JSON but then it won't be a geometry so you won' be able to query for nearest neighbour easily etc. .

Answer 1

There are several JSON functions and operators that can be used to extract the relevant parts of the GeoJSON specification required by ST_GeomFromGeoJSON to create geometries.

Try the following, where you can replace the JSON in the top part:

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;

Finds three geometries. The geom column has the geometry object, and the gid is the feature number. The ST_AsText function shows the WKT equivalent of each geometry. I've also included the properties or attributes that can be defined for each geometry, as is shown in the specification.

 gid | geom | properties
-----+------------------------------------------+--------------------------------------
 1 | POINT(102 0.5) | {"prop0": "value0"}
 2 | LINESTRING(102 0,103 1,104 0,105 1) | { +
 | | "prop0": "value0", +
 | | "prop1": 0.0 +
 | | }
 3 | POLYGON((100 0,101 0,101 1,100 1,100 0)) | { +
 | | "prop0": "value0", +
 | | "prop1": {"this": "that"}+
 | | }
(3 rows)

You should assign an SRID for the geometry, using ST_SetSRID.

Or if you simply need a single heterogeneous GEOMETRYCOLLECTION, you can make it compact like this:

SELECT ST_AsText(ST_Collect(ST_GeomFromGeoJSON(feat->>'geometry')))
FROM (
 SELECT json_array_elements('{ ... put JSON here ... }'::json->'features') AS feat
) AS f;
GEOMETRYCOLLECTION(POINT(2565453.18267219 -3835048.65976031),LINESTRING(2727584.72197102 -3713449.19424187,2732476.69178127 -3992291.47342619),POLYGON((2442627.90254053 -3705499.95430853,2425506.00820465 -3886502.83728783,2555143.20817631 -3910962.68633909,2442627.90254053 -3705499.95430853)))

See also Creating GeoJSON Feature Collections with JSON and PostGIS functions from the Postgres OnLine Journal, which does the opposite.