3

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
4

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.

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.