Cómo construir una base de datos Postgis con Python y Geoalchemy (con conexión a QGIS3) - Tutorial

En la jerga peruana la palabra “chicha” significa mezcla, pero una mezcla de un alto nivel de heterogeneidad, algo así como hacer una pizza con todos los ingredientes de tu refrigerador. En esta ocasión hemos desarrollado un tutorial con ese espíritu “chicha” ya que combina base de datos, lenguaje sql, motor de virtualización, Python, una variante de SQL Alchemy, reenvío de puertos y conexión a QGIS en Windows. Ciertamente, no somos desarrolladores de código por naturaleza, pero estamos muy contentos de haber alcanzado este nivel de complejidad o mezcla.

Hemos desarrollado un tutorial aplicado para la implementación de una base de datos Postgresql con Postgis habilitado en una imagen de Docker. La información sobre las ubicaciones de pozos de agua subterránea se ha insertado desde un archivo CSV con Python y Geoalchemy, los puertos de Docker se han reenviado y abierto para que sea accesible desde QGIS en Windows.

Este es un esquema de la configuración desarrollada:

Tutorial

Código y enlaces útiles

El reposito del tutorial esta en este enlace:

https://github.com/SaulMontoya/buildPostgisGeodabatasewithPythonGeoalchemy

Usamos la imagen de Docker llamada Hakuchik que viene con Postgresql y Postgis:

https://hub.docker.com/r/hatarilabs/hakuchik

Este es código de Python para subir la informacion de los pozos en Postgis:

#import required libraries
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, Float, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from geoalchemy2 import Geometry
import pandas as pd

Connect with Postgresql

engine = create_engine('postgresql://gis:gis@localhost:5432/geodatabase', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

Define and create table

class GwWell(Base):
    __tablename__ = 'gwwells'
    id = Column(Integer, primary_key=True)
    station = Column(String)
    lat = Column(Float)
    lon = Column(Float)
    date = Column(DateTime)
    geom = Column(Geometry(geometry_type='POINT', srid='4269'))

GwWell.__table__.create(engine)
GwWell.__table__

Table('gwwells', MetaData(bind=None), Column('id', Integer(), table=<gwwells>, primary_key=True, nullable=False), Column('station', String(), table=<gwwells>), Column('lat', Float(), table=<gwwells>), Column('lon', Float(), table=<gwwells>), Column('date', DateTime(), table=<gwwells>), Column('geom', Geometry(geometry_type='POINT', srid=4269, from_text='ST_GeomFromEWKT', name='geometry'), table=<gwwells>), schema=None)

Create a groundwater well database

#Open a CSV as a Pandas dataframe
stationsDf = pd.read_csv('../Csv/stations.csv', index_col=0, parse_dates=[4,6])
stationsDf.head()

	STATION_NM	dec_lat_va	dec_long_v	DTdeparsed	WLA_DEM	Date
0	JL-49-13-509	31.815556	-106.434167	1913-07-27	3686.72	1913-07-27
1	JL-49-13-521	31.826944	-106.425000	1915-10-20	3688.98	1915-10-20
2	JL-49-13-816	31.788611	-106.453334	1919-05-19	3673.88	1919-05-19
3	JL-49-13-523	31.826944	-106.422778	1920-01-06	3660.38	1920-01-06
4	20N.08E.15.134	35.966111	-106.086667	1921-05-03	5571.08	1921-05-03

# iterate over the datagrame to add well objects to the session
for index, row in stationsDf.iterrows():
    rowLat = row.dec_lat_va,
    rowLon = row.dec_long_v,
    well = GwWell(
        id = index,
        station = row.STATION_NM,
        lat = rowLat,
        lon = rowLon,
        date = row.Date,
        geom = 'SRID=4269;POINT(%.8f %.8f)'%(rowLon+rowLat)
    )
    session.add(well)

# save changes on the session
session.commit()