2

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.

1
  • I considered it, but thought that it was more of a Python+pg8000 problem rather than with PostGIS, seeing that direct queries against the db worked. – Manjabes Sep 1 '11 at 5:11
3

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.

0

Here's an answer about inserts that failed on PostGIS from http://gis.stackexchange.com. The answer talks about some deprecated features, and checks for different versions. Perhaps this could point you in the right direction.

geoserver wfs insert error

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.