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.

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

share|improve this question

2 Answers 2

up vote 7 down vote accepted

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

Then, use ST_MakePoint:

>>> import psycopg2
>>> conn = psycopg2.connect(dbname=..., port=..., user=...,
                            password=..., host=...)
>>> 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.
share|improve this answer

For more complicated geometries, such as LineString and Polygon geometries, you can handle them with Shapely, then pass them through psycopg2 as hex-encoded WKB. Note that Shapely 1.3 or later is required to handle the export of 3D geometries with the wkb_hex property.

from shapely.geometry import LineString
import psycopg2
conn = psycopg2.connect(...)
curs = conn.cursor()

# Make a Shapely geometry
ls = LineString([(2.2, 4.4, 10.2), (3.3, 5.5, 8.4)])
ls.wkt  # LINESTRING Z (2.2 4.4 10.2, 3.3 5.5 8.4)
ls.wkb_hex  # 0102000080020000009A999999999901409A999999999911406666666666662440666666...

# Send it to PostGIS
curs.execute('CREATE TEMP TABLE my_lines(geom geometry, name text)')
curs.execute(
    'INSERT INTO my_lines(geom, name)'
    'VALUES (ST_SetSRID(%(geom)s::geometry, %(srid)s), %(name)s)',
    {'geom': ls.wkb_hex, 'srid': 4326, 'name': 'First Line'})

conn.commit()  # save data

# Fetch the data from PostGIS
curs.execute('SELECT name, ST_AsText(geom) FROM my_lines')
curs.fetchone()  # ('First Line', 'LINESTRING Z (2.2 4.4 10.2,3.3 5.5 8.4)')

Further note that similar can be accomplished by sending the geometry's WKT, however since it is converted to text, it is lossy and may reduce angstroms of precision. Transferring geometries as hex-encoded WKB is lossless, and preserves the exact precision of each coordinate.

share|improve this answer
    
Excellent, thank you! I wonder if there is a performance difference between these two approaches. –  Adam Matan Aug 5 at 6:43
    
There is a 10% performance advantage building points with ST_MakePoint, which is great for point geometries. However, building more complex geometry types will generally be simpler with Shapely. –  Mike T Aug 5 at 23:45
    
+1 It's a good approach. Thanks! –  Adam Matan Aug 6 at 8:23

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.