[GIS] Parameterized insert query to PostGIS fails

postgispythonsql

I have a PostGIS database table that I need to insert data into. I'm using Python 3 and the pg8000 interface to Postgres. Somehow, parameterized inserts do not work. If I do

iq="INSERT INTO points(location) VALUES(ST_GeographyFromText('SRID=4326;POINT(:lat :lon)'))"
cursor.execute(iq, {"lat":lat,"lon":lon})

or

iq="INSERT INTO points(location) VALUES(ST_GeographyFromText('SRID=4326;POINT(? ?)'))"
cursor.execute(iq, (lat, lon))

Then the query fails with a ProgrammingError:

pg8000.errors.ProgrammingError: (b'ERROR', b'XX000', b'parse error - invalid geometry')

This, however, works:

iq="INSERT INTO points(location) VALUES(ST_GeographyFromText('SRID=4326;POINT({0} {1})'))".format(lat,lon)
cursor.execute(iq)

What am I doing wrong in the parameterized query?

Edit:
Forgot to mention that, the database query works, if done with PgAdmin against the PostGIS base with some random point coordinates.

Best Answer

You can't insert a parameter as part of the text input parameter (i.e., ST_GeographyFromText expects one text parameter, not two double precision parameters).

Rather than messing around with string formatting, I recommend that you parameterize the double precision coordinate (avoiding "binary -> text -> binary" conversions) with something like:

iq = "INSERT INTO points(location) VALUES(ST_MakePoint(?, ?)::geography)"
cursor.execute(iq, (lon, lat))

NOTE: I've reversed your coords to "lon, lat", which was incorrectly "lat, lon" in your question.

Related Question