Import GeoJSON into PostGIS

Question

I want to import GeoJSON avaliable in the following dump into PostGIS but I am not able to import it. I already imported geousa data available in this public dump into MongoDB smoothly. Let me know is there a way to import this data directly or through MongoDB?

As this data is huge please check following data:

{"geometry": {"type": "Point", "coordinates": [19.056792, 47.490894]}, "type": "Feature",
"id": "SG_1iYphlxn9BSHyGrpv1aXKc_47.490894_19.056792@1308163237",
"properties": {"website": "http://mito.hu", "city": "Budapest",
"name": "Mito Europe", "tags": ["online", "communication", "design",
"branding", "development", "mito"], "country": "HU", "classifiers":
[{"category": "Professional", "type": "Services", "subcategory": "Advertising"}], "href": 
"http://api.simplegeo.com/1.0/features/SG_1iYphlxn9BSHyGrpv1aXKc_47.490894_19.056792@1308163237.json",
"address": "N\u00e1dor u. 23.", "owner": "simplegeo", "postcode": "1051"}}

http://s3.amazonaws.com/simplegeo-public/places_dump_20110628.zip

You should warn that your dataset is 2.2 GB. For making it more attractive to help you, could you prepare test data with just couple of features? That is well enough for testing. — user30184, Dec 01 '15 at 06:45
How have you tried to import the data, and what is the error message? — til_b, Dec 01 '15 at 07:46
GDAL reads that one feature fine. You can test it yourself by saving it on the disk and running ogrinfo -al sample.json. You should really tell what you did and how did it fail. — user30184, Dec 01 '15 at 12:22
I executed:
ogr2ogr -f "PostgreSQL" PG:"dbname=my_database user=postgres" "source.geojson" but I am getting error as:

FAILURE Unable to open datasource `source.geojson' with the following drivers. (showing list of drivers) — work_in_progress, Dec 01 '15 at 18:24

score 34 · Answer 1 · answered Dec 01 '15 at 06:08

34

You can use PostGIS's ST_GeomFromGeoJSON to bring in just the geometry part of the GeoJSON.

Better yet, you can use ogr2ogr to import the entire JSON document:

ogr2ogr -f "PostgreSQL" PG:"dbname=my_database user=postgres" "source_data.json" -nln destination_table -append

(I haven't tested this with your data, add a comment if you have issues.)

answered Dec 01 '15 at 06:08

alphabetasoup

8,718
4
38
78

2

I am getting error as :
FAILURE Unable to open datasource `source.geojson' with the following drivers. (showing list of drivers).
– work_in_progress Dec 01 '15 at 18:22
That has to be the full path to your geojson – alphabetasoup Dec 01 '15 at 18:49
I added full path as well, still I am getting the same error. – work_in_progress Dec 01 '15 at 18:50
You need the GeoJSON GDAL driver: http://www.gdal.org/drv_geojson.html – alphabetasoup Dec 01 '15 at 18:55
1

gdal-bin is already installed in my system (ubuntu). – work_in_progress Dec 01 '15 at 19:21
Finally I am able to execute this command but it stores only one record in table. But I can't see any error message while executing this command. What would be the reason for such unexpected execution? – work_in_progress Dec 01 '15 at 20:10
2

I'm not going to download 2GB to look, but are your features a feature collection? – alphabetasoup Dec 01 '15 at 21:06

score 5 · Answer 2 · edited May 01 '17 at 16:18

5

./ogr2ogr -f "PostgreSQL" PG:"dbname=theDatabaseName user=postgres" "/Users/dev/Downloads/theGeometry.json" -where "name not like 'Whatever%'" -nln theFeatureName -overwrite

ogr2ogr displays a list of drivers when the file is not found. in that case just pass in the full path to the json file

edited May 01 '17 at 16:18

tinlyx

11,057
18
71
119

answered May 01 '17 at 16:14

acmeincorporated

51
1
1

Import GeoJSON into PostGIS

2 Answers2

Linked