The development of tutorials for the Open Energy Family takes place publicly in a dedicated tutorial repository. Please report bugs and suggestions as new issues. If Jupyter Notebooks are new to you and you'd like to get an introduction, have a look at this less than 10 minute introduction video. Official installation instructions are available on jupyter's readthedocs page.

license: GNU Affero General Public License Version 3 (AGPL-3.0)
copyright: Reiner Lemoine Institut
authors: jh-RLI

Getting started with postgis spatial functions using python¶

Takeaways¶

• Connect to OEP and query data form sql (postgresql) table using python.
• Use python (sqlalchemy and geoalchemy2) to query spatial data and apply spatial functions.
• Example use case for spatial functions and data preprocessing.
• Plot result using geopandas.

Introduction¶

This tutorial introduces to sptail functions in a PostgresSQL database. In gernal spatial functions enable us to apply functions to spatial data. As a result we could find out about helpful information in the data like how many points are insde a polygon or how many shapes are included within a 50m buffer zone around some point.

You learn how to use python using mainly two packages: SqlAlchemy - for database connection and interaction, Geoalchemy2 - as extension to use spatial functions. To ease the process of quering a table on the database we will use another python package: saio - will import a table schema from the database as sqlalchemy compatible table object. The data is stored at and retrieved form the https://openenergy-platform.org/

Setup¶

You need the following things to run through the entire tutorial:

• An account at the OEP to access your username and API Token.
• An environment with the following dependencies installed:
  • oedialect
  • saio
  • sqlalchemy
  • geoalchemy2
• Example geospatial data
  • link to data

In [ ]:

# Gerneral imports
import getpass
import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
import oedialect
import saio

# Gerneral imports import getpass import sqlalchemy as sa from sqlalchemy.orm import sessionmaker import oedialect import saio

Recap: How to connect to the OEP using SQLAlchemy and the oedialect¶

If we want to upload data to the OEP we first need to connect to it, using our OEP user name and token.

Note: You can view your token on your OEP profile page. Please visit https://openenergy-platform.org/

In [ ]:

# Whitespaces are not a problem for setting up the url!
user = input('Enter OEP-username:')
token = getpass.getpass('Token:')

# Whitespaces are not a problem for setting up the url! user = input('Enter OEP-username:') token = getpass.getpass('Token:')

Create a SQLAlchemy connection using the oedialect and your OEP-credentials.

In [ ]:

# Create Engine:
OEP_URL = 'openenergy-platform.org'
OED_STRING = f'postgresql+oedialect://{user}:{token}@{OEP_URL}'

engine = sa.create_engine(OED_STRING)
metadata = sa.MetaData(bind=engine)

# Create Engine: OEP_URL = 'openenergy-platform.org' OED_STRING = f'postgresql+oedialect://{user}:{token}@{OEP_URL}' engine = sa.create_engine(OED_STRING) metadata = sa.MetaData(bind=engine)

Import a table model from the database¶

Currently there is a bug in the oedialect that throws an execption when executing the code below. This will be fixed, you can view the current state by visiting this issue: https://github.com/OpenEnergyPlatform/oedialect/issues/35.

We select a table that contains spatial data, it can also contain other data types. A table containing spatial data often includes a column name like geom or geometry. The example table geometry cloumn contains data that is stored as well-known-binary (wkb) or well-known-text (wkt) format.

You can view all available schemas on the oep: https://openenergy-platform.org/dataedit/schemas

In [ ]:

# register the oep-schema that contains the example table on the database
# saio.register_schema("schema-name", engine)
saio.register_schema("climate", engine)

# import the sqlalchemy table model
# usage:
# from saio.registrated_table import table_name as MyTableName-Class
from saio.climate import openfred_locations as locations

# register the oep-schema that contains the example table on the database # saio.register_schema("schema-name", engine) saio.register_schema("climate", engine) # import the sqlalchemy table model # usage: # from saio.registrated_table import table_name as MyTableName-Class from saio.climate import openfred_locations as locations

Query the table with spatial functions¶

We already setup the table model that is required to query a table with sqlalachemy. This table model is used as a mapping between the table model in python and the table on the database. By that we can use sql query using the sqlalchemy magic and also add spatial functions to the query using the geoalchemy2 extension. The query is then executed on the database.

See all available spatial functions in the geoalemy2 documentation: https://geoalchemy-2.readthedocs.io/en/latest/spatial_functions.html

Requrements on input variables for spatial functions¶

The spatial function is applied to the whole geometry column. Basically the input is a different geometry. To get an expected result it is important to note that the input has the same data format and the same georeference system as the data in the table.

For the application of the spatial function an input variable is required. Spatial data can be calculated with data from the same table. But often another data source is included. For example, if you want to find all points which are located in a federal state, the geometry for the federal state is needed as input. The oep has the necessary data in another table. ______ . Another possibility is to import a file e.g. in geojson format that stores nuts-regions into python. The problem is that there are two different data formats (geojson vs wkb/wkt. The geojson must therefore be converted into the well kown text format.

In [ ]:

from geoalchemy2.elements import WKTElement
clicked_p = WKTElement(f'POINT (51.1642292 10.4541194)', srid=4326)

# Find the 5 nearest points from the mouse click in the oedb table
# model_draft.openfred_locations, which has an 'id' and a 'point' column.
# The point column is the geographical coordinates in a binary format
oep_query = Serializer.session.query(locations).order_by(
    locations.point.distance_centroid(clicked_p)
    ).limit(5)
print(oep_query)

from geoalchemy2.elements import WKTElement clicked_p = WKTElement(f'POINT (51.1642292 10.4541194)', srid=4326) # Find the 5 nearest points from the mouse click in the oedb table # model_draft.openfred_locations, which has an 'id' and a 'point' column. # The point column is the geographical coordinates in a binary format oep_query = Serializer.session.query(locations).order_by( locations.point.distance_centroid(clicked_p) ).limit(5) print(oep_query)

Use Cases: Data preprocessing using spatial functions.¶

Since we know how to apply spatial functions to a query we are able to process the spatial data in the table. The following examples show a few spatial functions that can be used in several tasks like data preprocessing.

Info: A Posgresql database with an install postgis extension enables the database to use spatial functions.

1. Example:¶

Add a point to a geometry

In [ ]:

2. Example:¶

This example shows how to filter all points that are inside of the shape of a geometry like polygon. This use case could be usefull if you want to know how many points are included in a singel region like a Federal State.

In [ ]:

Styling help for tutorial creators¶

Running the following commands will delete the tables from the database which you have in your ordered ORM. Take care that you only delete tables you actually want to have removed.

This shows up as a blue block. Suitable styling for tips / notes / infos.

This shows up as a yellow block. Suitable styling for warnings.

This shows up as a red block. Suitable styling for marking danger. Use sparingly e.g. when data loss is a possible risk.

This shows up as a green block. Suitable styling for success messages.

• item in list
  • item in sublist
    1. first item in subsublist
    2. second item in subsublist
  • another item in sublist

This is a link and it refers to a markdown cheatsheet for Jupyter notebooks, so you can look up more styling options.

In [ ]: