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.
Best Answer
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:
Finds three geometries. The
geom
column has the geometry object, and thegid
is the feature number. TheST_AsText
function shows the WKT equivalent of each geometry. I've also included theproperties
or attributes that can be defined for each geometry, as is shown in the specification.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:
See also Creating GeoJSON Feature Collections with JSON and PostGIS functions from the Postgres OnLine Journal, which does the opposite.