Example: Raw Data Publication

Exploring the raw OpenStreetMap data can be done in the database itself, but this limits the audience of users. To enable public access we need a web API that allows exploring, filtering and downloading of the data. This objective is achieved through the use of the established OGC API Features standard in conjunction with the pg_featureserv program. The following example showcases this process. We want to analyse restaurants, so we create the Lua file restaurants.lua with this content:

local restaurants = osm2pgsql.define_table({
    name = 'restaurants',
    ids = { type = 'any', id_column = 'osm_id' },
    columns = {
        { column = 'name',    type = 'text' },
        { column = 'cuisine', type = 'text' },
        { column = 'geom',    type = 'point', projection = 4326, not_null = true }
    }
})

function osm2pgsql.process_node(object)
    if object.tags.amenity == 'restaurant' then
        restaurants:insert(
            {
                name = object.tags.name,
                cuisine = object.tags.cuisine,
                geom = object:as_point()
            }
        )
    end
end

function osm2pgsql.process_way(object)
    if object.tags.amenity == 'restaurant' and object.is_closed then
        restaurants:insert(
            {
                name = object.tags.name,
                cuisine = object.tags.cuisine,
                geom = object:as_polygon():centroid()
            }
        )
    end
end

function osm2pgsql.process_relation(object)
    if object.tags.amenity == 'restaurant' and object.tags.type == 'multipolygon' then
        restaurants:insert(
            {
                name = object.tags.name,
                cuisine = object.tags.cuisine,
                geom = object:as_multipolygon():centroid()
            }
        )
    end
end

Then we load the data in our database.

osm2pgsql -d osm -O flex -S restaurants.lua OSMDATA.osm.pbf

We can install pg_featureserv by downloading the binary from here. Normally, pg_featureserv can run without any extra settings. But because we plan to show a simple map on the website later, we require further configuration. So, we create a configuration file called pg_featureserv.toml that has this content:

[Website]
# Add a source URL of maptiles to be shown as background for our features
BasemapUrl = "http://a.tile.openstreetmap.fr/hot/{z}/{x}/{y}.png"

We must adjust the DATABASE_URL to match our setup. Then, we can initiate the API using this command:

DATABASE_URL=postgres://username:password@localhost/osm ./pg_featureserv --config=./pg_featureserv.toml

This service starts at the address http://localhost:9000/ and can be accessed directly from the browser. The metadata for our table, like the the extent or available attribute names, can be viewed on this page:

http://localhost:9000/collections/public.restaurants.html

The link below shows the data on the map. Please note that only a subset of all the features are displayed, but this can be changed in the user interface of the map. Clicking on a feature shows provides additional information of it:

http://localhost:9000/collections/public.restaurants/items.html

By changing the .html ending to .json, the data will be returned as GeoJSON. It includes all raw data like attributes or geometry. A GeoJSON can be processed with many clients such as OpenLayers, Leaflet, MapLibre, or QGIS. For performance reasons, in default mode pg_featureserv returns only 10 features per request. With the parameter limit we can increase this count :

http://localhost:9000/collections/public.restaurants/items.json?
limit=100

The response can be filtered by both spatial and non-spatial properties. Here we request restaurants with Indian cuisine in an specific area.

http://localhost:9000/collections/public.restaurants/items.json?
limit=100&
bbox=8.4877,49.2583,9.1773,49.4579&
cuisine=indian

The response is a GeoJSON like this (download). Many programs or websites can display it as table like this:

More advanced filtering can be done by using the filter keyword in CQL (Common Query Language). With the parameter filter=name ILIKE '%restaurant%' we can retrieve all features that have the word restaurant in their name. Note the value of filter must be encoded.

http://localhost:9000/collections/public.restaurants/items.json?
filter=name%20ILIKE%20%27%25restaurant%25%27%