1

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]
  • what does type(point) return? – camdenl Mar 9 '15 at 12:45
4

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.

  • 1
    thanks for the answer. I emailed the developer and he says it could be a bug. – Vineet Menon Mar 10 '15 at 6:08
  • 1
    If you don't want to use the PPyGIS library, you can insert a point with the ST_GeomFromText method postgis.org/docs/ST_GeomFromText.html – Martin Mar 10 '15 at 7:53
  • 1
    Can you comment on my edit? How good/bad is it? I really don't want to parse the string which get from ST_AsText – Vineet Menon Mar 10 '15 at 8:32
  • 1
    If you have only points you can use ST_X and ST_Y as well to get the XY values. postgis.net/docs/ST_X.html SELECT ST_X(geometry), ST_Y(geometry) FROM test; – Martin Mar 10 '15 at 8:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.