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.
As prerequisites, you will need Docker, Postgres client and DBeaver on your machine - all of which are freely available.
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
flood_zone_2_tutorial
- Right click, and select
View Data
- Switch to
Spatial
view - 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.
id
Step 4 - Dig into the raw data
- Switch to
Grid
View - Notice the
geometry
column - Double click on one specific row (feature)
geometry
column value - Explore the
Map
andText
version of the value - it should look like this:
Step 5 - Explore geospatial types
- In the Database Navigator, expand the Columns under the
flood_zone_2_tutorial
table - Notice the
geometry
column, it’s typemultipolygon
and the SRID number4326
- 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
ST_Transform
function)
- Explore the available geometry types alongside
multipolygon
- 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.
St_Intersects
,St_Contains
andSt_Within
- Take a look at output formats for PostGIS geospatial data e.g.
ST_AsGeoJSON
andST_AsMVTGeom
- Explore the complete list of functions in PostGIS
Summary
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!