One of the most enjoyable parts of software development is figuring out the best solution to challenging problems. A good example of such challenging problems was a requirement from one of our customers recently.

Our customer wanted the ability to:

  1. define polygons and store them in a PostgreSQL database and
  2. provide GPS coordinates and check if they are within these polygons

Initially, their proposed solution involved writing a Java method that takes the coordinates and polygons as input and uses a classic algorithm:

Interpret the coordinates as a point in the 2D plane and “shoot” an infinitely long vector in a given direction. And then count the intersections with the polygon. If the number of intersections is zero or an odd number, the point is outside the polygon, otherwise it is inside.

Instead, we were able to propose and implement a solution that was much simpler and more elegant: PostGIS, a PostgreSQL variant specialized to handle geo information.

 

Understanding PostGIS

PostGIS is an open-source software that adds support for geographic objects to the PostgreSQL database. It extends PostgreSQL's capabilities by enabling it to store, query, and manipulate spatial data efficiently.

With PostGIS, users can work with various types of geometric objects such as points, lines, and polygons, allowing for sophisticated spatial analysis within a familiar database environment.

 

The definition of Polygons

A polygon is a closed geometric shape defined by a set of vertices and edges, where the last vertex connects back to the first, forming a closed loop.

Or, more simply put, a polygon is a two-dimensional figure with at least three angles and straight sides that are fully connected to form a closed figure.

In spatial data analysis, polygons are used to represent areas of interest, such as administrative boundaries, land parcels, or natural features like lakes and forests. Their versatility makes them invaluable for tasks ranging from simple area calculations to complex spatial operations.

 

The installation of PostGIS

To enable the PostGIS extension in PostgreSQL, some changes are required. The simplest approach is to use the Docker image provided by Docker Hub. Otherwise you need to install the extension manually.

 

docker-compose.yml:

version: "3.8"
services:
  database:
	image: postgis/postgis:16-3.4-alpine
	ports:
	  - "5432:5432"
	environment:
	  - POSTGRES_PASSWORD=admin
	  - POSTGRES_USER=admin
	  - POSTGRES_DB=db
	volumes:
	  - dbdata:/var/lib/postgresql/data

 

The setup of the database table

To store the spatial data in the database, we need to create a new table. The part where you should pay attention is the second column area, where a special data type GEOMETRY(POLYGON) is used. This type should already exist in your database when you install PostGIS.

 

CREATE TABLE mytable (
	id SERIAL PRIMARY KEY,
	area GEOMETRY(POLYGON)
);

 

Afterwards you need to fill this table with some polygons:

 

INSERT INTO public.mytable(area)
	VALUES
	(ST_GeomFromText('POLYGON((16.3893269 48.2164362, 16.3928138 48.2165363,16.3920199 48.2149135,16.3893269 48.2164362))', 4326));


In our example, we need to tell Postgres that we want to work with geographical coordinates, which can be done by specifying the spatial reference ID 4326, which declares a geographical spatial reference system.

This fulfils the first requirement and we can move on to the second, where we query the data.

 

Fetch intersecting polygons

Luckily, PostGIS already provides some really useful methods that can be used for querying the data.

ST_Intersects: Returns true if two geometries intersect. Geometries intersect if they have any point in common. See PostGIS documentation for more information on this method.

 

SELECT *
FROM mytable
WHERE ST_Intersects(
	ST_GeomFromText('POINT(16.391460939405498 48.21560182444266)', 4326),
	area
);

 

This method met our requirements exactly, but we soon realised that an application in the real world is often not as simple as it sounds. GPS coordinates from devices are often not accurate and it is recommended to apply a distance tolerance that also covers nearby polygons. Luckily, PostGIS again offers this functionality.:

ST_DWithin: Returns true if the geometries are within a given distance. See PostGIS documentation for more details.

 

SELECT *
FROM mytable
WHERE ST_DWithin(
	ST_GeomFromText('POINT(16.391460939405498 48.21560182444266)', 4326),
	area,
	119000, true
);


By defining the third and fourth arguments of the ST_DWithin function, we can specify the distance tolerance in metres.

 

PostGIS and PostgreSQL: Great combination

The combination of PostGIS and polygons equips users with the tools they need to tackle diverse challenges in the modern world. Its integration with PostgreSQL provides a robust foundation, allowing users to harness the power of relational databases for handling complex spatial datasets efficiently.