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
1 Answer 1
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.