Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Sep 1 '11 at 14:08

This question came from our site for professional and enthusiast programmers.

    
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
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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.

http://gis.stackexchange.com/questions/9012/geoserver-wfs-insert-error

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.