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.

possible duplicate of How to insert a GeoJSON polygon into a PostGIS table? — Ricardo Oliveira, Apr 13 '15 at 13:01
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. — Jay, Apr 13 '15 at 13:36
@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?) — Jakub Kania, Apr 13 '15 at 13:42
Postgres as of version 9.2 has a JSON datatype, so you could just insert you JSON as is, if that is all you want to do. Version 9.4 has a much more useful JSONB datatype and indexing enhancements. — John Powell, Apr 13 '15 at 13:56
Or do you mean how do you split the GeoJSON into individual components and store those in Postgres, seeing as you haven't stated what the structure of testtable is. — John Powell, Apr 13 '15 at 14:12
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. — Jay, Apr 13 '15 at 14:26
Thank you @Jakub for your response. Those geometries belong to a collection, hence the "type": "FeatureCollection". Is it possible to store them without breaking the GeoJSON into individual components? — Jay, Apr 13 '15 at 14:29
@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. . — Jakub Kania, Apr 13 '15 at 14:33

Mike T · Accepted Answer · 2023-11-14T20:31:29.493

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.

Storing GeoJSON FeatureCollection to PostgreSQL with PostGIS?

1 Answers1

Linked

Related