How to build a Postgis geodabatase with Python and Geoalchemy (with connection to QGIS3) - Tutorial

As a peruvian slang the word “chicha” means mixture, but a mixture of a high level of heterogeneity, something like doing a pizza with all the ingredients from your refrigerator. This time we have developed a tutorial with this “chicha” spirit since it combines a database, sql language, a virtualization engine, Python, a variant of SQL Alchemy, port forwarding and connection to QGIS in Windows. Certainly, we are not code developers by nature, but we are pretty happy to have achieved this level of complexity or mixture.

We have developed an applied tutorial for the implementation of a Postgresql database with Postgis enabled on a Docker image. Information about groundwater well locations have been inserted from a CSV file with Python and Geoalchemy and Docker ports have been forwarded and open to be accessible from QGIS in Windows.

This is a schema of the developed setup:

Tutorial

Code and useful links

The repository for this tutorial is on this link:

https://github.com/SaulMontoya/buildPostgisGeodabatasewithPythonGeoalchemy

We use this Hakuchik that comes with Postgresql and Postgis databases:

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

This is the Python code that upload the well information into 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()