3

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

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

0

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.