10.1 Data preparation¶

In the previous two lessons, we used file-based database SQLite and GeoPackage format which is based on this database.

In this lesson we will use server-based object-relational database PostgreSQL (https://postgresql.org/) together with spatial extension PostGIS (http://postgis.net/).

Data¶

Similarly to lesson 9 we will use OpenStreetMap dataset downloaded from GeoFabrik server in Esri Shapefile format: https://download.geofabrik.de/europe.html.

Instead of converting data in Esri Shapefile format into database manually we will use a Python script which enables us to automate conversion of input data into PostGIS database.

Run Python script below from Python Console in QGIS:

Content¶

Note

Compare SQL performance on GPKG created in the previous lesson and PostGIS database. Let's take SQL query below as an example:

with cinema as
(select * from pois where fclass = 'cinema')
select * from buildings_a as b
join cinema as c
on st_contains(b.geom, c.geom);

GPKG: more than 700 sec

PostGIS: less than 1 sec