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

1. Locate the table flood_zone_2_tutorial
2. Right click, and select View Data
3. Switch to Spatial view
4. Take a look around the map - it should look like this:

5. Click on one of the features, and you will see the associated data e.g. id

Step 4 - Dig into the raw data

1. Switch to Grid View
2. Notice the geometry column
3. Double click on one specific row (feature) geometry column value
4. Explore the Map and Text version of the value - it should look like this:

Step 5 - Explore geospatial types

1. In the Database Navigator, expand the Columns under the flood_zone_2_tutorial table
2. Notice the geometry column, it’s type multipolygon and the SRID number 4326
3. Learn about Coordinate Reference Systems?
   1. An SRID (Spatial Reference Identifier) identifies the coordinate (spatial) reference system for the geospatial data
   2. 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)
4. Explore the available geometry types alongside multipolygon
5. Explore the geography type

Step 6 - Geospatial index

1. Create a New SQL script

2. Create a geospatial index as follows:

   CREATE INDEX my_geo_index_1 ON flood_zone_2_tutorial USING GIST (geometry);

3. Learn more about how geospatial indexes work

4. 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 and St_Within
• Take a look at output formats for PostGIS geospatial data e.g. ST_AsGeoJSON and ST_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!