Linux, PostGIS, PostgreSQL, Windows

Building an Interactive Map with Leaflet and PostGIS on PostgreSQL 18

by jb

In the previous article, Building a Production-Ready PostGIS Environment on PostgreSQL 18, we installed PostGIS, created a spatial database, and imported OpenStreetMap data. While querying the database with SQL is useful, spatial data becomes much more valuable once it can be visualized on an interactive map.

In this article, we’ll build a simple web application using Leaflet, the most popular open-source JavaScript mapping library. The application will retrieve data directly from PostgreSQL/PostGIS and display it on an interactive OpenStreetMap layer. This environment will become the foundation for future articles covering performance tuning, GiST indexes, query optimization, monitoring, and database maintenance.

Architecture

Our application consists of four components:

Browser
    │
    ▼
Leaflet (JavaScript)
    │
    ▼
PHP Web Server
    │
    ▼
PostgreSQL 18 + PostGIS

The browser requests GeoJSON data from a PHP script, which executes a SQL query against PostgreSQL and returns the results in JSON format.

Prerequisites

This article assumes you already have:

  • PostgreSQL 18 installed
  • PostGIS enabled
  • OpenStreetMap data imported using osm2pgsql

If not, please read the previous article first.

Installing Apache and PHP

On RHEL 10 install Apache, PHP and the PostgreSQL driver and perform additional tasks.

# install packages
dnf -y install httpd php php-pgsql
# enable Apache
systemctl enable httpd --now
# open the firewall
firewall-cmd --add-service=http --permanent
firewall-cmd --reload

Preparing PostgreSQL

Create a dedicated database user for the web application.

# run as the postgres user
psql gisdemo <<EOF
-- create role
CREATE ROLE leaflet LOGIN PASSWORD 'changeme';
-- grant access
GRANT CONNECT ON DATABASE gisdemo TO leaflet;
GRANT USAGE ON SCHEMA public TO leaflet;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO leaflet;
\q
EOF

Creating the GeoJSON API

Create the following file as root

mkdir /var/www/html/api
vi /var/www/html/api/parks.php

with the following content:

<?php

$conn = pg_connect("
host=localhost
dbname=gisdemo
user=leaflet
password=changeme
");

$sql = "
SELECT
  json_build_object(
    'type','FeatureCollection',
    'features', json_agg(feature)
  )
FROM (
  SELECT
    json_build_object(
      'type','Feature',
      'geometry', ST_AsGeoJSON(ST_Transform(way, 4326))::json,
      'properties', json_build_object('name',name,'type',leisure)
    ) feature
  FROM planet_osm_polygon
  WHERE leisure='park'
    AND ST_DWithin(
          ST_Transform(way, 4326)::geography,
          ST_SetSRID(ST_MakePoint(11.576124, 48.137154), 4326)::geography,
          10000   -- 10 km radius
        )
) t;
";

$result = pg_query($conn, $sql);
header("Content-Type: application/json");
echo pg_fetch_result($result, 0, 0);
?>

To test the endpoint point a browser to:

http://lin6.fritz.box/api/parks.php

You should receive a GeoJSON FeatureCollection.

Creating the Leaflet Application

Create the following file as the root user

vi /var/www/html/index.html

with this content:

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Leaflet + PostGIS</title>
<link rel="stylesheet" href="https://unpkg.com/leaflet/dist/leaflet.css" />
<style>
html, body { height:100%; margin:0; }
#map { height:100%; }
</style>
</head>
<body>
<div id="map"></div>
<script src="https://unpkg.com/leaflet/dist/leaflet.js"></script>
<script>
const map = L.map('map').setView([48.137154, 11.576124], 12);

L.tileLayer('https://tile.openstreetmap.org/{z}/{x}/{y}.png', {
    maxZoom: 19,
    attribution: '© OpenStreetMap contributors'
}).addTo(map);

fetch('api/parks.php')
    .then(response => response.json())
    .then(data => {
        console.log('Received GeoJSON:', data); // Check console
        const layer = L.geoJSON(data, {
            style: { color: 'green', weight: 2, fillOpacity: 0.5 },
            onEachFeature: function(feature, layer) {
                layer.bindPopup(feature.properties.name || 'Unnamed park');
            }
        }).addTo(map);

        // Zoom to the data extent
        const bounds = layer.getBounds();
        if (bounds.isValid()) {
            map.fitBounds(bounds);
            console.log('Zoomed to data bounds');
        } else {
            console.warn('No valid bounds from data');
        }
    })
    .catch(err => console.error('Fetch error:', err));
</script>
</body>
</html>

Viewing the Map

Point your browser to:

http://lin6.fritz.box/

You should now see:

  • OpenStreetMap
  • Munich
  • Parks imported from OpenStreetMap
  • Clickable polygons
  • Popups displaying park names

Unlike many tutorials, every object displayed comes directly from your OpenStreetMap import.

Understanding the Data Flow

The request follows a simple path.

Browser

↓

Leaflet

↓

parks.php

↓

PostgreSQL

↓

PostGIS

↓

ST_AsGeoJSON()

↓

GeoJSON

↓

Leaflet

The browser never communicates directly with PostgreSQL. Instead, PHP acts as a lightweight API that retrieves spatial data and converts it into GeoJSON.

Why GeoJSON?

GeoJSON has become the standard format for exchanging geographic information between databases and web applications.

Advantages include:

  • Native Leaflet support
  • Human-readable JSON
  • Generated directly by PostGIS
  • Supported by most GIS software
  • Lightweight and easy to debug

Exploring the Database

Let’s execute a few SQL statements to understand the imported dataset.

How many parks are available?

psql gisdemo <<EOF
SELECT count(*)
FROM planet_osm_polygon
WHERE leisure='park';
EOF
Sample Output (click to expand): 
[postgres@lin6 ~]$ psql gisdemo <<EOF
SELECT count(*)
FROM planet_osm_polygon
WHERE leisure='park';
EOF
 count
-------
  6247
(1 row)

[postgres@lin6 ~]$

List some park names.

psql gisdemo <<EOF
SELECT
    name
FROM planet_osm_polygon
WHERE leisure='park'
ORDER BY name
LIMIT 20;
EOF
Sample Output (click to expand): 
[postgres@lin6 ~]$ psql gisdemo <<EOF
SELECT
    name
FROM planet_osm_polygon
WHERE leisure='park'
ORDER BY name
LIMIT 20;
EOF
                          name
--------------------------------------------------------
 1A Hundegarten Kompetenzzentrum und Hundenasenparadies
 Abdurrahim-Özüdoğru-Park
 Aberdeenpark
 Abloner-Garten
 Achenpark
 Achenseeplatz
 Achenseeplatz
 Adamiwiese
 Adelheidpark
 AGFA-Park
 Ägidienplatz
 Ägidiuspark
 Agricolaplatz
 Aktiv Park
 Aktiv-Park
 Albert-Schweitzer-Park
 Albert-Schweitzer-Seniorenzentrum
 Albin-Lang-Stadtpark Landshut
 Alfons Halbig Platz
 Alfred-Dick-Park
(20 rows)

[postgres@lin6 ~]$

Calculate the area of the English Garden.

psql gisdemo <<EOF
SELECT 
  name,
  round(ST_Area(ST_Transform(way, 4326)::geography)) AS square_meters
FROM planet_osm_polygon
WHERE name = 'Englischer Garten'
ORDER BY square_meters DESC
LIMIT 1;
EOF
Output (click to expand): 
[postgres@lin6 ~]$ psql gisdemo <<EOF
SELECT
  name,
  round(ST_Area(ST_Transform(way, 4326)::geography)) AS square_meters
FROM planet_osm_polygon
WHERE name = 'Englischer Garten'
ORDER BY square_meters DESC
LIMIT 1;
EOF
       name        | square_meters
-------------------+---------------
 Englischer Garten |       3931062
(1 row)

[postgres@lin6 ~]$

These examples demonstrate that PostGIS is much more than a storage engine—it provides advanced spatial analysis directly within PostgreSQL.

Preparing for Performance Tuning

The application is intentionally simple, but it already gives us a realistic workload that we can optimize.

In the upcoming articles we’ll use this same application to demonstrate:

  • GiST indexes
  • Bounding box queries
  • ST_DWithin
  • ST_Intersects
  • EXPLAIN ANALYZE
  • VACUUM
  • REINDEX
  • Autovacuum tuning
  • Streaming Replication
  • Backup and recovery
  • Monitoring

Every optimization will be measured by observing how quickly the map loads.

What’s Next?

In the next article we’ll investigate why some spatial queries become slow as the amount of OpenStreetMap data increases.

We’ll create our first GiST index, compare execution plans with EXPLAIN ANALYZE, and measure the impact on map loading times.

Conclusion

By combining PostgreSQL, PostGIS, OpenStreetMap, and Leaflet, we’ve built the foundation of a real-world GIS application. Rather than relying on artificial sample data, our application visualizes actual OpenStreetMap features stored in PostgreSQL, making it an ideal environment for exploring spatial queries, database administration, and performance tuning.

This architecture closely resembles those used by municipalities, utility companies, logistics providers, and many enterprise GIS platforms. Throughout the remainder of this series, we’ll continue extending and optimizing this application while learning how PostgreSQL and PostGIS handle large-scale spatial workloads.

0