PostGIS Spatial Queries in PostgreSQL 18 – Practical OpenStreetMap Examples
In the previous articles of this PostgreSQL 18 series, we installed PostGIS, imported OpenStreetMap data for Bavaria, created GiST indexes, and finally visualized our data with Leaflet.
Having your data stored in PostGIS is only the first step. The real power comes from being able to ask spatial questions such as:
- Which parks are within walking distance?
- How far away is the nearest park?
- Which park contains a specific landmark?
- Which roads intersect a park?
These are the kinds of queries used by navigation systems, tourism websites, logistics platforms, and city information systems every day.
In this article, we’ll continue using the OpenStreetMap dataset for Bavaria that we imported in the first article. All examples work directly on the planet_osm_* tables created by osm2pgsql, so you can execute them without importing any additional data.
We’ll focus on the most commonly used PostGIS spatial functions:
ST_DWithin()ST_Distance()ST_Contains()ST_Within()ST_Intersects()
Let’s start with one of the most common GIS queries: finding nearby objects.
Finding Nearby Parks with ST_DWithin()
Imagine you’re building a tourism application for Munich.
A visitor standing on Marienplatz wants to find nearby parks within a radius of 10 kilometres.
First, we create a geometry representing Marienplatz. Since our OpenStreetMap data was imported with the default osm2pgsql settings, the geometries are stored in EPSG:3857. Therefore, we transform the WGS84 coordinates into the same coordinate reference system before performing the spatial query.
WITH munich AS (
SELECT ST_Transform(
ST_SetSRID(
ST_MakePoint(11.57549,48.13743),
4326
),
3857
) AS geom
)
SELECT
p.name,
ROUND(ST_Distance(p.way,m.geom)) AS distance_m
FROM planet_osm_polygon p
CROSS JOIN munich m
WHERE p.leisure='park'
AND p.name IS NOT NULL
AND ST_DWithin(
p.way,
m.geom,
10000
)
ORDER BY distance_m
LIMIT 10;
The result should look similar to this:
name | distance_m
--------------------------+------------
Hofgarten | 835
Maximiliansplatz | 839
Herzog-Wilhelm-Park | 998
Alter Botanischer Garten | 1175
Dichtergarten | 1203
Nußbaumpark | 1299
Auf der Insel | 1453
Posthof | 1484
Englischer Garten | 1498
Königsplatz | 1604
(10 rows)Your exact results may vary slightly depending on the OpenStreetMap data version you imported.
The function ST_DWithin() returns all geometries whose distance from the specified point is less than or equal to the given radius. Since our data is stored in EPSG:3857, the distance is measured in metres, making it ideal for proximity searches.
Typical use cases include finding:
- nearby parks
- restaurants
- hospitals
- charging stations
- bus stops
- hotels
Whenever you need to answer the question “What’s nearby?”, ST_DWithin() is usually the right function.
Why Not Use ST_Distance()?
Many developers start with a query like this:
SELECT
name
FROM planet_osm_polygon
WHERE leisure='park'
AND ST_Distance(
way,
ST_Transform(
ST_SetSRID(
ST_MakePoint(11.57549,48.13743),
4326
),
3857
)
) < 10000;
Although the query produces the correct result, it is not the most efficient solution.
ST_Distance() calculates the exact distance between the search point and every matching geometry before PostgreSQL can determine whether the row belongs in the result set.
For small tables this may not matter, but once your database contains hundreds of thousands or even millions of geometries, these calculations become expensive.
ST_DWithin() is specifically optimized for this use case. Together with a GiST index, PostgreSQL can first eliminate geometries that are clearly outside the search radius and calculate exact distances only for the remaining candidates.
If you followed the previous article on GiST indexes, you’ve already prepared your database for this optimization.
As a general rule:
- Use
ST_DWithin()to filter nearby objects. - Use
ST_Distance()to display or sort by the exact distance.
Calculating Exact Distances
The most common pattern in PostGIS applications is to combine both functions.
ST_DWithin() limits the search to nearby geometries, while ST_Distance() calculates the exact distance for the remaining results.
WITH munich AS (
SELECT ST_Transform(
ST_SetSRID(
ST_MakePoint(11.57549,48.13743),
4326
),
3857
) AS geom
)
SELECT
p.name,
ROUND(ST_Distance(p.way,m.geom)) AS distance_m
FROM planet_osm_polygon p
CROSS JOIN munich m
WHERE p.leisure='park'
AND p.name IS NOT NULL
AND ST_DWithin(
p.way,
m.geom,
10000
)
ORDER BY distance_m
LIMIT 10;
This approach has several advantages:
- the GiST index reduces the number of candidate geometries,
- the exact distance is calculated only for nearby parks,
- and the results can easily be sorted from nearest to farthest.
This is the pattern you’ll encounter in many production GIS applications.
Which Park Contains the Chinesischer Turm?
Another common task is determining which polygon contains a particular object.
Suppose a visitor clicks on the Chinesischer Turm in your Leaflet application.
Rather than searching manually, PostGIS can determine which park contains this landmark.
SELECT
p.name
FROM planet_osm_polygon p
JOIN planet_osm_point o
ON ST_Contains(
p.way,
o.way
)
WHERE p.leisure='park'
AND o.name='Chinesischer Turm';
The query returns:
Englischer Garten
The ST_Contains() function returns TRUE when one geometry completely contains another.
Typical applications include:
- finding the city for a GPS coordinate,
- determining the district containing a building,
- assigning customers to sales territories,
- locating landmarks inside parks,
- or identifying administrative regions for an address.
In combination with interactive maps such as Leaflet, this function makes it easy to answer the question:
“What is located here?”
Instead of maintaining complex lookup tables, PostGIS performs the spatial relationship directly using the stored geometries.
Is the Olympiaturm Inside Olympiapark?
The opposite of ST_Contains() is ST_Within().
Instead of asking:
Which park contains this landmark?
we ask:
Is this landmark located inside the park?
Both functions describe the same spatial relationship, but from different perspectives.
The following example checks whether the Olympiaturm is located inside the Olympiapark.
SELECT
ST_Within(tower.way, park.way)
FROM planet_osm_polygon tower
JOIN planet_osm_polygon park
ON park.name='Olympiapark'
WHERE tower.name='Olympiaturm';
The result is:
st_within ----------- t
Whether you choose ST_Contains() or ST_Within() is mostly a matter of readability.
For example, these two questions are logically equivalent:
- Does Olympiapark contain the Olympiaturm?
- Is the Olympiaturm within Olympiapark?
Choose whichever makes your SQL easier to understand.
Typical applications include:
- checking whether a building lies inside a municipality,
- determining whether a GPS position is inside a geofence,
- verifying that a customer belongs to a delivery zone,
- validating whether an object is inside a protected area.
Which Roads Cross the Englischer Garten?
So far we’ve looked at relationships between points and polygons.
PostGIS can also compare entire geometries.
Suppose we want to know which roads intersect the Englischer Garten.
SELECT DISTINCT
l.name
FROM planet_osm_line l
JOIN planet_osm_polygon p
ON ST_Intersects(
l.way,
p.way
)
WHERE p.name='Englischer Garten'
AND l.highway IS NOT NULL
AND l.name IS NOT NULL
ORDER BY l.name
LIMIT 20;
# Sample Output:
name
------------------------
Alte Lastenstraße
Alte Parkstraße
Am Englischen Garten
Am Hirschanger
Aumeisterbrücke
Baumschulstraße
Baumschulweg
Blaue Brücke
Burgfriedenweg
Carl-August-Sckell-Weg
Carl-Theodor-Brücke
Carl-Theodor-Straße
Dianabadbrücke
Dianabadweg
Diermayerweg
Dietlindenstraße
Effnerbrücke
Effnerstraße
Englischer Garten
Entenfallbrücke
(20 rows)
Unlike ST_Contains(), ST_Intersects() only checks whether two geometries share at least one point.
That makes it one of the most useful functions for spatial joins.
Typical examples include:
- roads crossing parks,
- rivers crossing administrative boundaries,
- buildings intersecting flood zones,
- railway lines crossing municipalities,
- utility lines crossing streets.
Spatial joins are one of the biggest strengths of PostGIS and often replace complicated application logic with a single SQL statement.
Combining PostGIS with Leaflet
In the previous article, we displayed our OpenStreetMap data using Leaflet.
By combining Leaflet with the spatial queries introduced here, you can build surprisingly powerful web applications with only a few SQL statements.
For example:
- show parks near the user’s location,
- display hospitals within 5 km,
- highlight roads crossing a selected park,
- determine which district a clicked point belongs to,
- find cafés located inside a park.
A typical workflow looks like this:
- The user clicks somewhere on the map.
- Leaflet sends the coordinates to your backend.
- PostgreSQL executes one or more PostGIS queries.
- The matching geometries are returned as GeoJSON.
- Leaflet highlights the results on the map.
The combination of PostgreSQL, PostGIS and Leaflet provides an excellent foundation for interactive GIS applications without requiring proprietary software.
Summary
In this article, we used the OpenStreetMap dataset imported in the first part of this series to answer practical spatial questions around Munich.
We looked at the PostGIS functions you’ll use most often in day-to-day applications.
| Function | Purpose |
|---|---|
ST_DWithin() | Find nearby objects |
ST_Distance() | Calculate the exact distance |
ST_Contains() | Determine which polygon contains an object |
ST_Within() | Check whether an object lies inside another |
ST_Intersects() | Find overlapping geometries |
Although PostGIS offers hundreds of functions, these five cover a large percentage of everyday GIS tasks.
Once you become comfortable with them, you’ll be able to build applications such as:
- tourism and city guides,
- route planners,
- logistics platforms,
- delivery services,
- asset management systems,
- emergency response applications,
- interactive web maps.
Conclusion
With only a few SQL statements, PostGIS allows PostgreSQL to answer complex spatial questions that would otherwise require significant application logic.
Using the OpenStreetMap dataset imported earlier in this series, we’ve seen how to:
- search for nearby parks,
- calculate distances,
- determine which park contains a landmark,
- and identify roads intersecting a park.
These same techniques can be applied to virtually any type of geographic data, making PostGIS one of the most powerful spatial databases available today.
