How to load GeoJSON into PostgreSQL database using Python?

Question

What I have tried to do is (In Python IDLE):

import psycopg2
import json
try:
  conn = psycopg2.connect("dbname='carto' user='postgres' host= localhost password='password'")
except:
  print "I am unable to connect to the database"
cur=conn.cursor()

All the above worked fine, what did not work and need help on is the below script:

cur.execute("INSERT INTO gd_hydro_medio_staging (geom) VALUES" (ST_SetSRID(ST_GeomFromGeoJSON(
'{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[-122.501984586959,37.7338390080962],[-122.502067586959,37.7338444747629],[-122.502150586959,37.7339050080961],[-122.502143586959,37.733987408096],[-122.502095120292,37.7340918080958],[-122.502067320292,37.7342070747622],[-122.502122520292,37.7343446080954],[-122.502260720292,37.734569808095],[-122.502246920292,37.7346358080949],[-122.502184520292,37.7346742080949],[-122.502129186959,37.7346576747616],[-122.502066920292,37.734608208095],[-122.501984120292,37.7344598747618],[-122.501859720293,37.7343552747621],[-122.50161758696,37.7342562747621],[-122.50142398696,37.7341134080957],[-122.501403320293,37.7340310080959],[-122.50148638696,37.7339924747626],[-122.501797720293,37.733970808096],[-122.501859986959,37.733948808096],[-122.501984586959,37.7338390080962]]]},
"properties": {"Name":"","legacyCartoID":"150","featureClass":"LAKE","displayClass":"8"}}'),4326))

Answer 1

A full GeoJSON object can't be inserted that way. ST_GeomFromGeoJSON is only for creating a geometry from a GeoJSON geometry string.

http://www.postgis.org/docs/ST_GeomFromGeoJSON.html

To insert the geom - you'll need to get just the 'geometry' value in your JSON and use that in the query - e.g:

SELECT ST_SetSRID(ST_GeomFromGeoJSON(
'{"type":"Polygon","coordinates":[[[-122.501984586959,37.7338390080962],[-122.502067586959,37.7338444747629],[-122.502150586959,37.7339050080961],[-122.502143586959,37.733987408096],[-122.502095120292,37.7340918080958],[-122.502067320292,37.7342070747622],[-122.502122520292,37.7343446080954],[-122.502260720292,37.734569808095],[-122.502246920292,37.7346358080949],[-122.502184520292,37.7346742080949],[-122.502129186959,37.7346576747616],[-122.502066920292,37.734608208095],[-122.501984120292,37.7344598747618],[-122.501859720293,37.7343552747621],[-122.50161758696,37.7342562747621],[-122.50142398696,37.7341134080957],[-122.501403320293,37.7340310080959],[-122.50148638696,37.7339924747626],[-122.501797720293,37.733970808096],[-122.501859986959,37.733948808096],[-122.501984586959,37.7338390080962]]]}'),4326)

To insert the whole record, you'll need to get all the property values from the JSON in Python before assembling the SQL statement

Answer 2

Just one notice concerning using of Psycopg2. It's wrong to use cur.execute for calling stored database procedures (like ST_GeomFromGeoJSON). To use them you need to turn to cur.callproc:

cur.calproc(ST_GeomFromGeoJSON, {params}) #this is just example for one stored procedure. {params} is the dictionary with named parameters of called procedure like {"param_name": param_value}

then get result:

val = cur.fetchone()[0] #if your answer contains only one element

and put it to the value part of execute() method:

cur.execute("INSERT INTO tab_name (col) VALUES (%s)",(val))

for details refer to http://www.psycopg.org/psycopg/docs/usage.html?highlight=callproc

Answer 3

It works best to use the geoJSON package and validate the geojson. i.e. validation = geojson.is_valid(geojson.Point((-3.68,40.41,25.14,10.34)))

I also use the pygeoj package, which loads the features to a dictionary, and the geoalchemy ORM.

I originally tried json and psycopg2 - the code was huge. Using geojson, pygeoj and geoalchemy2, I got the code down to less than 100 lines.