Psycopg ppygis select query
Question: 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?


Answer: You are doing nothing wrong. Following [the same steps as the PPyGIS basic
example](http://www.fabianowski.eu/projects/ppygis/usage.html#basic-usage), I
get the same [hex-encoded EWKB](https://en.wikipedia.org/wiki/Well-
known_binary) as shown in the question (010100...), which is normally
expected. Perhaps this worked with an older version of PPyGIS / Psycopg? It
doesn't today.

The package does not appear to properly register itself as an adapter for
PostGIS types, so my advice is to not use the package. Besides, you don't need
additional packages to use PostGIS from Psycopg2.

* * *

Here is the normal approach to read/write points, without any extra packages:

    
    
    # Assuming PostGIS 2.x, use a typmod
    cursor.execute('CREATE TEMP TABLE test (geom geometry(PointZ,4326));')
    # Longyearbyen, 78.22°N 15.65°E, altitude 10 m
    cursor.execute('''\
        INSERT INTO test (geom)
        VALUES(ST_SetSRID(ST_MakePoint(%s, %s, %s), 4326));
    ''', (15.65, 78.22, 10.0))
    cursor.execute('''\
        SELECT ST_Y(geom) AS latitude, ST_X(geom) AS longitude, ST_Z(geom) AS altitude
        FROM test;
    ''')
    print(cursor.fetchone())  # (78.22, 15.65, 10.0)
    cursor.execute('SELECT ST_AsText(geom) FROM test;')
    print(cursor.fetchone()[0])  # POINT Z (15.65 78.22 10)
    cursor.execute('SELECT ST_AsLatLonText(geom) FROM test;')
    print(cursor.fetchone()[0])  # 78°13'12.000"N 15°39'0.000"E
    

If you want a geometry object on the client-side to do more work with the
actual geometry, consider using Shapely, which can be interfaced using WKB
data:

    
    
    from shapely.wkb import loads
    cursor.execute('SELECT geom FROM test;')
    pt = loads(cursor.fetchone()[0], hex=True)
    print(pt)  # POINT Z (15.65 78.22 10)
    

