[GIS] How to load GeoJSON into PostgreSQL database using Python

geojsonpostgispostgresqlpython-2.7

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))

Best Answer

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

Related Question