PostGIS – How to Insert a Point Using Python

postgispsycopg2python

What is the right way to insert a Point to a PostGIS database using Python?

Best Answer

First, install the psycopg2 package, a Pythonic interface for PostgreSQL.

Then, use ST_MakePoint: 

>>> import psycopg2
>>> conn = psycopg2.connect(dbname=..., port=..., user=...,
                            password=..., host=...)
>>> cur = conn.cursor()
>>> x, y, z, = 32, 34, 0
>>> cur.execute("SELECT ST_SetSRID(ST_MakePoint(%s, %s, %s),4326);", (x, y, z))
>>> cur.fetchall()
[('01010000A0E6100000000000000000404000000000000041400000000000000000',)]

ST_AsText can be used to validate the values:

>>> cur.execute("SELECT ST_AsText(ST_SetSRID(ST_MakePoint(%s, %s, %s),4326));", (x, y, z))
>>> cur.fetchall()
[('POINT Z (32 34 0)',)]

Notes

  • Remember that (lat, lon) is (y, x), not (x, y).
  • Always use parameters, rather than string manipulations, to prevent SQL injection. In this examples we tupled (x, y, z) at the end so that psycopg2 can handle the substitution.
Related Question