[GIS] GeoJSON MultiPolygon/Polygon to PostgreSQL using Python/Psycopg2

geojsonpostgispostgresqlpython

I am writing a python script attempting to insert GeoJSON Polygon coordinates into a postgres table. The script is getting tied up with the string formatting.
I only included the necessary part of the code where it throwing an error. 

coords = """ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":{}}')""".format(i['geometry']["coordinates"])
cur.execute("""insert into AbeBuildings (geom)
values(%s)""",(coords))

this is what the raw GeoJSON looks like when I just print out i['geometry']["coordinates"]

[[[-74.0410635, 40.7112018], [-74.0409077, 40.7119448], [-74.0408936, 40.7119431], [-74.0402319, 40.7118572], [-74.0403913, 40.7111239], [-74.0406334, 40.711153], [-74.0405859, 40.7113794], [-74.0406154, 40.7113997], [-74.0407442, 40.7114119], [-74.0407737, 40.7113936], [-74.04082, 40.7111725], [-74.0410635, 40.7112018]], [[-74.0406941, 40.7118065], [-74.0407382, 40.7116013], [-74.0405315, 40.7115758], [-74.0404874, 40.711781], [-74.0406941, 40.7118065]]]

this is the error I keep getting 

[[[-74.0410635, 40.7112018], [-74.0409077, 40.7119448], [-74.0408936, 40.7119431], [-74.0402319, 40.7118572], [-74.0403913, 40.7111239], [-74.0406334, 40.711153], [-74.0405859, 40.7113794], [-74.0406154, 40.7113997], [-74.0407442, 40.7114119], [-74.0407737, 40.7113936], [-74.04082, 40.7111725], [-74.0410635, 40.7112018]], [[-74.0406941, 40.7118065], [-74.0407382, 40.7116013], [-74.0405315, 40.7115758], [-74.0404874, 40.711781], [-74.0406941, 40.7118065]]]
Traceback (most recent call last):
  File "<module1>", line 33, in <module>
KeyError: '"type"'

How should I properly be constructing this geometry? Also this seems to be a multipolygon but the actual GeoJSON file says Polygon. I ran it both with multipolygon and polygon, getting the same error

here is a link to the data http://data.jerseycitynj.gov/dataset/8f6afe86-917f-47c0-bc65-c81c4ef139d5/resource/f5e538c7-867a-421b-8f06-2be19a26c314/download/jc-buildings.geojson

I have also tried this for the first coordinate on that link

geojson = json.dumps(i['geometry'])
qry = "insert into AbeBuildings (geom) values(ST_SetSRID(ST_GeomFromGeoJSON(%s), 4326));"
cur.execute(qry,(geojson))
conn.commit()

get this error 

{"type": "Polygon", "coordinates": [[[-74.0410635, 40.7112018], [-74.0409077, 40.7119448], [-74.0408936, 40.7119431], [-74.0402319, 40.7118572], [-74.0403913, 40.7111239], [-74.0406334, 40.711153], [-74.0405859, 40.7113794], [-74.0406154, 40.7113997], [-74.0407442, 40.7114119], [-74.0407737, 40.7113936], [-74.04082, 40.7111725], [-74.0410635, 40.7112018]], [[-74.0406941, 40.7118065], [-74.0407382, 40.7116013], [-74.0405315, 40.7115758], [-74.0404874, 40.711781], [-74.0406941, 40.7118065]]]}
Traceback (most recent call last):
  File "C:\Users\Ralph\Desktop\hmm.py", line 28, in <module>
    cur.execute(qry,(geojson))
TypeError: not all arguments converted during string formatting

Best Answer

I think the only thing you are missing is a comma inside the tuple representing the GeoJSON object that gets passed to the %s placeholder. From the docs, the key line is:

For positional variables binding, the second argument must always be a sequence, even if it contains a single variable. And remember that Python requires a comma to create a single element tuple.

So, the following works:

geom = [[[-74.0410635, 40.7112018], [-74.0409077, 40.7119448], [-74.0408936, 40.7119431], [-74.0402319, 40.7118572],
      [-74.0403913, 40.7111239], [-74.0406334, 40.711153], [-74.0405859, 40.7113794], [-74.0406154, 40.7113997],
      [-74.0407442, 40.7114119], [-74.0407737, 40.7113936], [-74.04082, 40.7111725], [-74.0410635, 40.7112018]],
     [[-74.0406941, 40.7118065], [-74.0407382, 40.7116013], [-74.0405315, 40.7115758], [-74.0404874, 40.711781],
      [-74.0406941, 40.7118065]]]

data = {"type": "Polygon",
        "coordinates": geom}

cur.execute("""INSERT INTO json_table (geom)  VALUES(ST_GeomFromGeoJSON(%s))""" ,
               (json.dumps(data), ))

whereas

cur.execute("""INSERT INTO json_table (geom)  VALUES(ST_GeomFromGeoJSON(%s))""" ,
               (json.dumps(data)))

fails with

not all arguments converted during string formatting.

Related Question