Databases

Overview

MapServer can connect to most geospatial databases. There are native MapServer drivers for PostgreSQL/PostGIS, Oracle, and Microsoft SQL Server. Other databases can be accessed through OGR e.g. MySQL.

In this exercise we'll be connecting to a PostGIS database to display water polygon features using a MapServer WMS. PostGIS spatially enables the PostgreSQL databases.

Docker Setup

To avoid having to install and setup a database we'll be using the PostGIS Docker image provided by Kartoza.

Info

We will use a different Docker Compose file, which includes a PostGIS container for this exercise. We can stop the current Docker containers, and run the alternate Docker containers with the following commands:

cd ./getting-started-with-mapserver/workshop/exercises
docker compose down
docker compose -f docker-db-compose.yml up -d
# when finished
docker compose -f docker-db-compose.yml down

Checking the Database Connection with QGIS

If you have QGIS installed on your machine you can check you can successfully connect to the database by opening the browser panel, and creating a new PostgreSQL connection:

image showing the QGIS browser — The QGIS Browser Panel

image showing successful connection to a postgis database using QGIS — Connecting to the Database with QGIS

Tip

When connecting to the database from another Docker container port 5432 is used, and the host name is db as defined in the docker-compose.yml file.
When connecting from your own machine port 25434 is used (this is chosen to avoid clashes with any locally running databases).

Adding Data to The Database

We can use the OGR tool ogr2ogr installed on the MapServer container to add datasets to the PostgreSQL database.

# connect to the MapServer Docker image which includes OGR tools for importing data
docker exec -it mapserver2 bash
# navigate to the folder containing the OSM FlatGeobuf files
cd /etc/mapserver/data/osm
# import the water polygons dataset to the Postgres database in the Postgres Docker image
ogr2ogr -f "PostgreSQL" PG:"dbname=gis user=docker password=docker host=db port=5432" -nln water_a water_a.fgb
# now check the data details in the database
ogrinfo PG:"host=db user=docker password=docker dbname=gis port=5432" water_a -summary

Code

Exercise Links

MapServer request: http://localhost:5000/?map=/etc/mapserver/postgis.map&mode=map&layer=water
OpenLayers example: http://localhost:5001/postgis.html

postgis.js

import '../css/style.css';
import ImageWMS from 'ol/source/ImageWMS.js';
import Map from 'ol/Map.js';
import OSM from 'ol/source/OSM.js';
import View from 'ol/View.js';
import { Image as ImageLayer, Tile as TileLayer } from 'ol/layer.js';

const mapserverUrl = import.meta.env.VITE_MAPSERVER_BASE_URL;
const mapfilesPath = import.meta.env.VITE_MAPFILES_PATH;

const layers = [
    new TileLayer({
        source: new OSM(),
        className: 'bw'
    }),
    new ImageLayer({
        extent: [2968743.65508978, 8038921.67212233, 2982981.8632402, 8053818.05714347],
        source: new ImageWMS({
            url: mapserverUrl + mapfilesPath + 'postgis.map&',
            params: { 'LAYERS': 'water', 'STYLES': '' }
        }),
    }),
];
const map = new Map({
    layers: layers,
    target: 'map',
    view: new View({
        center: [2975862.75916499, 8046369.8646329],
        zoom: 14,
    }),
});

postgis.map

MAP
  NAME "PostGIS"
  EXTENT 26.668678 58.339241 26.796582 58.409410
  UNITS DD
  SIZE 800 600

  PROJECTION
    "init=epsg:4326"
  END

  WEB
    METADATA
      "ows_title" "OSM Water"
      "ows_enable_request" "*"
      "ows_srs" "EPSG:4326 EPSG:3857"
    END
  END

  LAYER
    NAME "water"
    STATUS OFF
    TYPE POLYGON
    CONNECTIONTYPE POSTGIS
    CONNECTION "host=db user=docker password=docker port=5432 dbname=gis"
    DATA "wkb_geometry FROM water_a USING UNIQUE ogc_fid USING srid=4326"

    PROCESSING "CLOSE_CONNECTION=DEFER" # for maximum performance
    CLASS
      GROUP "default"
      STYLE
        COLOR "#d4f1f9"
        OUTLINECOLOR "#918151"
      END
    END
  END

END

Exercises

Try and load another dataset into the database using the ogr2ogr approach above.
Now add a new layer to the Mapfile to display the layer. You can make a direct request to MapServer in the form: http://localhost:5000/?map=/etc/mapserver/postgis.map&mode=map&layers=water%20NEWLAYERNAME.

Now update the JS file so the layer is visible as part of the interactive map, through WMS:

  source: new ImageWMS({
      url: mapserverUrl + mapfilesPath + 'postgis.map&',
      params: { 'LAYERS': 'water,NEWLAYERNAME', 'STYLES': '' },
      ratio: 1
  }),