CODEWITHSUNDEEP
pythonpostgresqlpostgispsycopg2postgresql-9.3
Justin Manley
Justin Manley

Reputation: 258

Is there a way to get psycopg2 to return the points as python tuples?

I want to retrieve a PostGIS point column from Postgres as a python tuple using psycopg2.

This is proving surprisingly difficult. I'm puzzled that psycopg2 does not automatically read Postgres point types (setting aside PostGIS point geometries) as python tuples.

For example, I would expect row['latlng_tuple'] in the following code to be a python tuple of floats. 

cursor.execute("SELECT \
    ( CAST (ST_X(latlng) AS double precision) \
    , CAST (ST_Y(latlng) AS double precision) \
    ) \
    AS latlng_tuple \
    FROM my_table;"

for row in cursor:
    print row['latlng_tuple']

Instead, I find that the above code returns row['latlng_tuple'] as a string. This is consistent with the way that the psycopg2 documentation describes the conversion between Postgres and python types.

Why is this the case? Is there a way to get psycopg2 to return the points as python tuples, perhaps using a custom converter / adapter, as described here?

Alternatively, is there an easy way to return PostGIS point geometries as python tuples? I've tried ppygis and found that it doesn't work.

Upvotes: 2

Views: 2160

Answers (1)

Mike T
Mike T

Reputation: 43642

The SQL in the question returns a composite record type with (...), which is cast to text. For example, using native double precision types:

import psycopg2
import psycopg2.extras
conn = psycopg2.connect(...)
cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cursor.execute("SELECT (1.0::double precision, 2.0::double precision) AS db_tuple;")
for row in cursor:
    print(repr(row['db_tuple']))  # '(1,2)'

So you can't build tuples in SQL for Python. Build the tuples with Python:

cursor.execute("SELECT 1.0::double precision AS x, 2.0::double precision AS y;")
for row in cursor:
    xy_tuple = (row['x'], row['y'])
    print(repr(xy_tuple ))  # (1.0, 2.0)

To get data from PostGIS for other software, use the geometry accessor or output functions. For instance ST_X(geom) returns the x-coordinate for a Point geometry as a double precision type.

cursor.execute("SELECT ST_X(latlng) AS lng, ST_Y(latlng) AS lat FROM my_table;")
for row in cursor:
    latlng_tuple = (row['lat'], row['lng'])
    print(repr(latlng_tuple))

# (1.0, 2.0)
# (3.0, 4.0)

Also, don't confuse PostGIS's geometry(Point) type with PostgreSQL's point type. They are very different. Furthermore, packages likes ppygis are not required to get geometries from Python to PostGIS.

Upvotes: 2

Related Questions