Using PostGIS on Python 3

Question

I'm using Python 3 and need to connect to postGre with postGIS extensions. I'm intending to use a psycopg2 driver.
This PPyGIS is the only extension I found, but it works on python 2.7 not 3.3.0.
Any one knows a solution working on 3.3.0 ?

Answer 1

If you are not doing anything fancy with the geometry objects on the client side (Python), psycopg2 can get most basic info using native data types with geometry accessors, or other GIS output formats like GeoJSON. Let the server (PostgreSQL/PostGIS) do the hard work.

Here is a random example to return the GeoJSON to shapes that are within 1 km of a point of interest:

import psycopg2
conn = psycopg2.connect(database='postgis', user='postgres')
curs = conn.cursor()
# Find the distance within 1 km of point-of-interest
poi = (-124.3, 53.2) # longitude, latitude
# Table 'my_points' has a geography column 'geog'
curs.execute("""\
SELECT gid, ST_AsGeoJSON(geog), ST_Distance(geog, poi)
FROM my_points, (SELECT ST_MakePoint(%s, %s)::geography AS poi) AS f
WHERE ST_DWithin(geog, poi, 1000);""", poi)
for row in curs.fetchall():
 print(row)

• Mille Bii: It will basic, having a layer of points on a map. As an option measure distance between two points.
• Mike T: @MilleBii if all you need is the distance, then psycopg2 is all you need. PPyGIS is only useful if you need to do some custom things on the actual geometry/geography on the client side with Python, rather than on the server side with PostgreSQL/PostGIS.
• Rmatt: You may also define specific adapters to let psycopg2 format the geo-queries. Using for example geometries from pygeoif: def adapt_point(pt): return AsIs("ST_SetSRID(ST_MakePoint({}, {}), 4326)".format(adapt(pt.x), adapt(pt.y))); register_adapter(Point, adapt_point)

Answer 2

You may actually use Shapely or GDAL/OGR, but both libraries have a long list of dependencies.

If you have only very few usecases, you might also implement a small protocol yourself, based on the super slick pygeoif library, like the example below

from psycopg2.extensions import register_adapter, AsIs, adapt
from pygeoif.geometry import Point
def adapt_point(pt):
 return AsIs("ST_SetSRID(ST_MakePoint({}, {}), 4326)".format(adapt(pt.x), adapt(pt.y)))
register_adapter(Point, adapt_point)

Answer 3

Since this question was asked the Geopandas package added

classmethod GeoDataFrame.from_postgis(sql, con, geom_col='geom', 
 crs=None, index_col=None, coerce_float=True, parse_dates=None, params=None)

which will retrieve a geodataframe from a sql table with a geometry column

http://geopandas.org/reference.html#geopandas.GeoDataFrame.from_postgis

Answer 4

The easiest way is to give the geometry as wkb. With it you can unse all functunalites of psycopg2 like execute_values

import psycopg2
from psycopg2.extras import execute_values
from osgeo import ogr
d = [{'id' : 1, 'name' : 'A', 'the_geom': ogr.CreateGeometryFromWkt('POINT({} {})'.format(5.085679, 45.042005)).ExportToWkb()},
{'id' : 2, 'name' : 'B','the_geom': ogr.CreateGeometryFromWkt('POINT({} {})'.format(-1.182751, 46.170237)).ExportToWkb() }]
sql = 'INSERT INTO mon_schema.ma_table (id, name the_geom) VALUES %s'
psycopg2.extras.execute_values(pgCur, sql, d)