21

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
  • 1
    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
  • 1
    @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
26

Assuming you have at least PostgreSQL version 9.3, you can use a few JSON functions and operators 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.