[GIS] Get a point value with psycopg2 and PPyGIS

postgresqlppygispsycopg2python

I'm trying to setup a basic working postgis setup with python ppygis package.

>>> import psycopg2
>>> import ppygis
>>> connection = psycopg2.connect(database='spre', user='postgres')
>>> cursor = connection.cursor()
>>> cursor.execute('CREATE TABLE test (geometry GEOMETRY)')
>>> cursor.execute('INSERT INTO test VALUES(%s)', (ppygis.Point(1.0, 2.0),))
>>> cursor.execute('SELECT * from test')
>>> point = cursor.fetchone()[0]
>>> print point
0101000000000000000000F03F0000000000000040
>>>

I should have got a python object with separate X and Y coordinate. Something like 

>>> Point(X: 1.0, Y: 2.0)

What am I doing wrong? I'm following http://www.fabianowski.eu/projects/ppygis/usage.html#installation

For posterity. I couldn't make it work, so to get point data this was what I did,

>>> cursor.execute('SELECT ST_AsGeoJSON(geometry) FROM test')
>>> point = cursor.fetchone()[0]
>>> print json.loads(point)['coordinates']
[1,2]

Best Answer

You have to convert your geometry column to WKT to get the point (or any geometry) as text format.

https://postgis.net/docs/ST_AsText.html

cursor.execute('SELECT ST_AsText(geometry) from test')

Probably something in ppygis is missing, because it seems like it converts the point geometry to the PostGIS ST_Geometry format but there is no function to convert it back (at least I didn't find any in the documentation).
If you use the normal SELECT statement it returns the geometry as PostGis ST_Geometry as well and does not convert it back. cursor.fetchone()[0] is a psycopg2 function and won't convert it back to the point format.

Related Question