An Intro To Geospatial Data With PostGIS
In this tutorial, we will explore how to use PostGIS for storing and querying geospatial data.
It will be a fairly pragmatic tutorial, and the only expectation is that you have some basic SQL knowledge. It is tailored to someone who has little to no geospatial data knowledge. Overall, it should take around 15 minutes to complete - with more time if you read the content on attached links.
We are going to use a subset of the Environment Agency (EA) flood zone 2 data for this tutorial. It basically maps out the areas of land in the UK where there is a risk of flooding.
Step 1 - Create a PostGIS database
We will start by creating a Postgres PostGIS database on your machine, using a community provided Docker image:
# If on an Apple M1 chip (use community image built for ARM) docker run --name geospatial-tutorial-postgis -p 5432:5432 -e POSTGRES_PASSWORD=postgres -e POSTGRES_USER=postgres -d gangstead/postgis:13-3.1-arm # Otherwise docker run --name geospatial-tutorial-postgis -p 5432:5432 -e POSTGRES_PASSWORD=postgres -e POSTGRES_USER=postgres -d postgis/postgis:13-3.1
You can check the container has started and the Postgres service is running as follows:
docker exec -ti /geospatial-tutorial-postgis psql -U postgres
Step 2 - Load the flood zone data into the database
Download this file to your home directory. It is restricted to the wider Shrewsbury area, cut down from the entire UK flood zone data set.
Now load the SQL file into the Postgres database:
psql -h 127.0.0.1 -U postgres < ~/create_flood_zone_data.sql
Check you can see the data by connecting to the database via DBeaver using the following configuration:
host: 127.0.0.1 database: postgres user: postgres password: postgres
Step 3 - Explore the full data
- Locate the table
- Right click, and select
- Switch to
- Take a look around the map - it should look like this:
- Click on one of the features, and you will see the associated data e.g.
Step 4 - Dig into the raw data
- Switch to
- Notice the
- Double click on one specific row (feature)
- Explore the
Textversion of the value - it should look like this:
Step 5 - Explore geospatial types
- In the Database Navigator, expand the Columns under the
- Notice the
geometrycolumn, it’s type
multipolygonand the SRID number
- Learn about Coordinate Reference Systems?
- An SRID (Spatial Reference Identifier) identifies the coordinate (spatial) reference system for the geospatial data
- The CRS of the geospatial data must match that of the base map it is overlaid onto (or transformed to it using the
- Explore the available geometry types alongside
- Explore the geography type
Step 6 - Geospatial index
Create a New SQL script
Create a geospatial index as follows:
CREATE INDEX my_geo_index_1 ON flood_zone_2_tutorial USING GIST (geometry);
Learn more about how geospatial indexes work
Discover the types of geospatial indexes
Step 7 - Geospatial querying
Now it’s time for a geospatial query to find flood risks in a specific area just south of Shrewsbury called Atcham.
The area is identified by this boundary defined via a GIST geoJSON format.
We will use
ST_Intersects which finds all features/rows that share any portion of space of the queried geometry:
SELECT id, layer, source_type, geometry FROM public.flood_zone_2_tutorial WHERE St_Intersects( geometry, -- Corresponds to the polygon defined in the Github gist above 'SRID=4326;POLYGON(( -2.689547538757324 52.684343558062444, -2.693624496459961 52.67633004026696, -2.6851701736450195 52.66995460120601, -2.6721668243408203 52.67031893707165, -2.6683473587036133 52.67874986129621, -2.670750617980957 52.68499394142615, -2.689547538757324 52.684343558062444 ))' );
In DBeaver, run this query and switch to the
Spatial view to see something like:
Step 8 - Learn about more PostGIS functions
- Compare the different topological functions e.g.
- Take a look at output formats for PostGIS geospatial data e.g.
- Explore the complete list of functions in PostGIS
We have just touched on the topic of geospatial data with PostGIS, and hopefully this tutorial has gently introduced you to some basic concepts and set you up to explore deeper into this world!
At LandTech we live and breathe geospatial data - creating pipelines that import geospatial data, building APIs & applications that do interesting geospatial queries, and presenting maps over these datasets.
If geospatial land and property data interests you (no geospatial experience necessary!), come join our team as we are hiring.
We are the engineers behind LandInsight and LandEnhance. We’re helping property professionals build more houses, one line of code at a time. We're a remote-first company with our tech team in Europe, and yes, we're hiring!