Skip to content

PostGIS and pgAdmin

Ecoact CDA team uses (among other things) Postgis to interact with geospatial data.

What for?

Postgis essentially adds two new data types:

  • Geometry: aimed at describing a local metric coordinate system (like the different UTM coordinate systems).
  • Geography: aimed at describing a large portion of the world.

This drastically eases all geospatial computation, that can now be done directly withing sql queries instead of relying on python libraries like shapely.

In particular, operations that require a spatial indexing and that could have relied on shapely STRTree can now direclty be done by the database, with the help of the GeoAlchemy 2 packages. This improves RAM consumption by a more than significant amount (not having to load all the geographies in memory).

Simple python example compatible with SQLModel.

We first define a WorldPoint class like so

Info

note the need of model_config

"""
Module implementing the world point db associated class
"""
from geoalchemy2 import Geography
from pydantic import ConfigDict
from sqlmodel import Column
from sqlmodel import Field
from sqlmodel import SQLModel

class WorldPointBase(SQLModel):  # type: ignore
    """
    a World Point.

    Attributes are:
        - point_id: a unique point identifier
        - location: the geometrical definition of the point

    NB: model_config has to be use because of geoalchemy2 Geography
    """
    point_id: int = Field(index=True)
    model_config = ConfigDict(from_attributes=True, arbitrary_types_allowed=True)
    location: Geography = Field(sa_column=Column(Geography(geometry_type='POINT', srid=4326)))


class WorldPoint(WorldPointBase, table=True):  # type: ignore
    """
    DB version of a world point.
    """
    __tablename__ = 'world_point'
    id: int | None = Field(default=None, primary_key=True)

and can then use it like so (having inserted into db points every 0.1 lat/lon):

%%time
from geoalchemy2 import WKTElement
from geoalchemy2 import functions
from ecodev_core import engine
from sqlmodel import select
from sqlmodel Session
from geoalchemy2.comparator import Comparator
from geoalchemy2.shape import to_shape

with Session(engine) as session:
    query = select(functions.ST_AsText(WorldPoint.location)).order_by(
        Comparator.distance_centroid(
            WorldPoint.location,
            from_shape(Point(4.03,45.57)))).limit(1)
    result = to_shape(WKTElement(session.exec(query).first()))
print(result, type(result))

Giving the result 

POINT (4 45.6) <class 'shapely.geometry.point.Point'>
CPU times: user 2.9 ms, sys: 0 ns, total: 2.9 ms
Wall time: 3.05 ms

Other interesting use cases can involve

  • func.ST_Intersects from SQLModel, to check if two polygons intersect for instance (or if a point is inside a polygon).
  • func.ST_Distance from SQLModel, to calculate the geodesic distance between two points (equivalent of geopy geodesic distance.)

The Geoalchemy 2 documentation proves invaluable here.

Warning

type_coerce and the type_ argument is needed for most of the functions as they only work for Geogmetry by default.

Setting up the db stack

All relevant information can be found in the docker-compose.postgis.yml file (here).

Production mode

Remember that you need to setup traefik when in production before launching this stack. Go read the traefik page if not already done.

To start the stack in production, you can make use of the Makefile provided in the repo (make help) to list available commands.

To use it you will need to create one folder in the folder containing docker-compose.yml

  • postgis_db_data

you will need to create a .postgis.env file and setting

  • pgadmin_url entry to the DNS address you created in order to reach the pgadmin interface.
  • POSTGRES_DB: the db name to which your application will connect to
  • POSTGRES_USER: the user authorized to connect to POSTGRES_DB
  • POSTGRES_PASSWORD: the password associated to POSTGRES_USER
  • PGADMIN_DEFAULT_EMAIL: the username to connect to the pgAdmin web interface (accessible at pgadmin_url)
  • PGADMIN_DEFAULT_PASSWORD: the password to connect to the pgAdmin web interface (accessible at pgadmin_url)

You can then safely launch 

make postgisprod-launch


$ make postgisprod-launch
---> 100%
Production Postgis DB successfully setup!

pgAdmin will be accessible at pgadmin_url

Local dev mode

In this case you will make use of the additional docker-compose.postgis.override.yml file. Here you will set in your .env all parameters of the previous section (bare pgadmin_url, since traefik is not used locally) plus - postgis_pgadmin_port: the local port on which you want to access to pgAdmin. If not set, 5052 is used by default.

Then launch 

make postgisdev-launch


$ make postgisdev-launch
---> 100%
Local dev Postgis DB successfully setup!

pgAdmin will be accessible at http://127.0.0.1:postgis_pgadmin_port

Add pgadmin extension

If you manually create a new database, you will need to recreate the Postgis extension. This can be done via pgadmin.

 CREATE EXTENSION Postgis;