Importing GeoJson data to postgresql using ogr2ogr command

Question

I am using following ogr2ogr command to import GeoJson data to Postgres DB. The command runs successfully but the geometry column is null.

ogr2ogr -f PostgreSQL PG:"dbname=test user=postgres password=test" sample3.geojson -nln test.sample3

The geojson file is big so putting some starting lines.

{ "type": "FeatureCollection", "properties": {
"bounds": "0.000001,0.000000,6.343452,51.486215",
"center": "3.779297,51.013722,11",
"description": "sample3.mbtiles",
"format": "pbf",
"json": "{\"vector_layers\": [ { \"id\": \"motorwaygeojson\", \"description\": \"\", \"minzoom\": 11, \"maxzoom\": 11, \"fields\": {} } ],\"tilestats\": {\"layerCount\": 1,\"layers\": [{\"layer\": \"motorwaygeojson\",\"count\": 21144,\"geometry\": \"LineString\",\"attributeCount\": 0,\"attributes\": []}]}}",
"maxzoom": "11",
"minzoom": "11",
"name": "sample3.mbtiles",
"type": "overlay",
"version": "2"
}, "features": [
{ "type": "FeatureCollection", "properties": { "zoom": 11, "x": 1023, "y": 1024 }, "features": [
{ "type": "FeatureCollection", "properties": { "layer": "motorwaygeojson", "version": 2, "extent": 4096 }, "features": [
{ "type": "Feature", "properties": { }, "geometry": { "type": "LineString", "coordinates": [ [ 0.000000, 0.000043 ], [ 0.000000, 0.000000 ], [ 0.000000, 0.000043 ] ] } }
] }
] }
,
{ "type": "FeatureCollection", "properties": { "zoom": 11, "x": 1024, "y": 1024 }, "features": [
{ "type": "FeatureCollection", "properties": { "layer": "motorwaygeojson", "version": 2, "extent": 4096 }, "features": [
{ "type": "Feature", "properties": { }, "geometry": { "type": "LineString", "coordinates": [ [ 0.000000, 0.000043 ], [ 0.000000, 0.000000 ], [ 0.000000, 0.000043 ] ] } }
] }
] }

As you can see I have nested FeatureCollections, so what needs to be done to handle this scenario?

My postgres version is 9.3 and gdal version is 2.2.1

• user30184: Does ogrinfo -so -al sample3.geojson show that it finds the geometry attribute?
• Ajinkya: After using ogrinfo command i am getting "ERROR 1: Invalid Feature object. Missing 'geometry' member". But as you can see in geojson file geometry is there.

Answer 1

Your data are valid JSON but not valid GeoJSON. You can test yourself by pasting this into https://geojsonlint.com

{ "type": "FeatureCollection", "properties": {
"bounds": "0.000001,0.000000,6.343452,51.486215",
"center": "3.779297,51.013722,11",
"description": "sample3.mbtiles",
"format": "pbf",
"json": "{\"vector_layers\": [ { \"id\": \"motorwaygeojson\", \"description\": \"\", \"minzoom\": 11, \"maxzoom\": 11, \"fields\": {} } ],\"tilestats\": {\"layerCount\": 1,\"layers\": [{\"layer\": \"motorwaygeojson\",\"count\": 21144,\"geometry\": \"LineString\",\"attributeCount\": 0,\"attributes\": []}]}}",
"maxzoom": "11",
"minzoom": "11",
"name": "sample3.mbtiles",
"type": "overlay",
"version": "2"
}, "features": [
{ "type": "FeatureCollection", "properties": { "zoom": 11, "x": 1023, "y": 1024 }, "features": [
{ "type": "FeatureCollection", "properties": { "layer": "motorwaygeojson", "version": 2, "extent": 4096 }, "features": [
{ "type": "Feature", "properties": { }, "geometry": { "type": "LineString", "coordinates": [ [ 0.000000, 0.000043 ], [ 0.000000, 0.000000 ], [ 0.000000, 0.000043 ] ] } }
] }
] }
,
{ "type": "FeatureCollection", "properties": { "zoom": 11, "x": 1024, "y": 1024 }, "features": [
{ "type": "FeatureCollection", "properties": { "layer": "motorwaygeojson", "version": 2, "extent": 4096 }, "features": [
{ "type": "Feature", "properties": { }, "geometry": { "type": "LineString", "coordinates": [ [ 0.000000, 0.000043 ], [ 0.000000, 0.000000 ], [ 0.000000, 0.000043 ] ] } }
] }
] }
]}

The report from the site is

Invalid GeoJSON

Line 1: FeatureCollection object cannot contain a "properties" member

Line 13: Feature object cannot contain a "features" member

Line 13: GeoJSON features must have a type=feature member

Line 13: "geometry" member required

Line 19: Feature object cannot contain a "features" member

Line 19: GeoJSON features must have a type=feature member

Line > 19: "geometry" member required

If you have custom JSON then I fear you must make a custom parser for you. Standard GeoJSON libraries and programs cannot handle those data.