This is here mostly to remind myself when I forget later this week, but here’s a look at some problems I had trying to insert geometry into a PostGIS database with python and psycopg2.
Basically in trying to update Matt Waite’s TweetsWithR repo I kept running into the same errors, first with spatialite and then with PostGIS. My goal was to take a pair of tweet coordinates from the Twitter api and turn them into point geometry in the database.
My initial (read wrong) plan was to store the coordinates wrapped in the sql call to ST_GeomFromText:
coordinates = "ST_GeomFromText(POINT(%s %s))" % (x,y)
then pass that to the database in an insert along with some other data from the tweet I’d gathered earlier:
c.execute('INSERT INTO tweets(tweet_id, created_date, text, screen_name, geom) VALUES (?,?,?,?,?)', (long(tweet.id), tweet_date, tweet_text, user, coordinates))
Unfortunately that greeted me with geometry error:
pysqlite2.dbapi2.IntegrityError: tweets.geom violates Geometry constraint [geom-type or SRID not allowed]
I tried this first in spatialite and then got the same in PostGIS, which I already had running, so I knew it wasn’t an issue with pyspatialite or the like.
Eventually I found this stackoverflow thread, which lays out the correct way to geometry inserts from psycopg2. The key is to have the PostGIS function in the actual insert and not as part of the python variable you pass to the insert:
coordinates = "POINT(%s %s)" % (x,y)
c.execute('''INSERT INTO tweets(tweet_id, created_date, text, screen_name, geom) VALUES (%s,%s,%s,%s,ST_GeomFromText(%s,4326))''', (long(tweet.id), tweet_date, tweet_text, user, coordinates))
Unfortunately no one on the internet has ever had this issue with SQLite3 or spatialite apparently, so it wasn’t until I started searching for PostGIS results that I found this.