PostgreSQL via GeoAlchemy2¶
Description:
- GeoAlchemy 2 is a Python toolkit for working with spatial databases.
Resources:
- [GitHub] https://github.com/geoalchemy/geoalchemy2
- [Docs] https://geoalchemy-2.readthedocs.io/en/stable/
Source and license:
- Some of these examples are adapted from the GeoAlchemy 2 ORM tutorial (https://geoalchemy-2.readthedocs.io/en/stable/orm_tutorial.html), Copyright (c) 2013 Eric Lemoine, licensed under MIT. That code stays under its own license; see THIRD_PARTY_NOTICES.md.
Environment variables¶
This notebook reads its settings from environment variables. Set them before starting Jupyter:
POSTGRES_HOST: hostname of your PostgreSQL serverPOSTGRES_USER: PostgreSQL user namePOSTGRES_PASSWORD: password for that userPOSTGRES_DB: database to connect to (must exist)
The database needs the PostGIS extension (CREATE EXTENSION postgis;).
Example 1¶
Connect to a postgres db, create a mapping, and create a database table
In [ ]:
Copied!
import os
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from geoalchemy2 import Geometry
from sqlalchemy.orm import sessionmaker
userpwd = os.environ["POSTGRES_PASSWORD"]
host = os.environ["POSTGRES_HOST"]
user = os.environ["POSTGRES_USER"]
database = os.environ["POSTGRES_DB"]
dsn = f"postgresql://{user}:{userpwd}@{host}/{database}"
engine = create_engine(dsn, echo=True)
Base = declarative_base()
class Lake(Base):
__tablename__ = 'lake'
id = Column(Integer, primary_key=True)
name = Column(String)
geom = Column(Geometry('POLYGON'))
print(Lake.__table__)
Lake.__table__.create(engine)
lake = Lake(name='Majeur', geom='POLYGON((0 0,1 0,1 1,0 1,0 0))')
print(lake.geom)
# Create a Session class to interact with the DB
Session = sessionmaker(bind=engine)
session = Session()
session.add(lake)
session.commit()
# Execute a query
our_lake = session.query(Lake).filter_by(name='Majeur').first()
print(our_lake.name)
print(our_lake.geom)
# Execute a query
query = session.query(Lake).order_by(Lake.name)
for lake in query:
print(lake.name)
import os
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from geoalchemy2 import Geometry
from sqlalchemy.orm import sessionmaker
userpwd = os.environ["POSTGRES_PASSWORD"]
host = os.environ["POSTGRES_HOST"]
user = os.environ["POSTGRES_USER"]
database = os.environ["POSTGRES_DB"]
dsn = f"postgresql://{user}:{userpwd}@{host}/{database}"
engine = create_engine(dsn, echo=True)
Base = declarative_base()
class Lake(Base):
__tablename__ = 'lake'
id = Column(Integer, primary_key=True)
name = Column(String)
geom = Column(Geometry('POLYGON'))
print(Lake.__table__)
Lake.__table__.create(engine)
lake = Lake(name='Majeur', geom='POLYGON((0 0,1 0,1 1,0 1,0 0))')
print(lake.geom)
# Create a Session class to interact with the DB
Session = sessionmaker(bind=engine)
session = Session()
session.add(lake)
session.commit()
# Execute a query
our_lake = session.query(Lake).filter_by(name='Majeur').first()
print(our_lake.name)
print(our_lake.geom)
# Execute a query
query = session.query(Lake).order_by(Lake.name)
for lake in query:
print(lake.name)