Nominatim Foreign Data Wrapper for PostgreSQL (nominatim_fdw)

The nominatim_fdw is a PostgreSQL Foreign Data Wrapper to access data from Nominatim servers using simple function calls.

Index

Requirements
Build and Install
Update
Usage
Examples
Deploy with Docker

Requirements

libxml2: version 2.5.0 or higher.
libcurl: version 7.74.0 or higher.
PostgreSQL: version 11 or higher.

Build and Install

To compile the source code you need to ensure the pg_config executable is properly set when you run make - this executable is typically in your PostgreSQL installation's bin directory. After that, just run make in the root directory:

$ cd nominatim_fdw
$ make

After compilation, just run make install to install the Foreign Data Wrapper:

$ make install

After building and installing the extension you're ready to create the extension in a PostgreSQL database with CREATE EXTENSION:

CREATE EXTENSION nominatim_fdw;

To install an specific version add the full version number in the WITH VERSION clause

CREATE EXTENSION nominatim_fdw WITH VERSION '1.0';

To run the predefined regression tests run make installcheck with the user postgres:

$ make PGUSER=postgres installcheck

Update

To update the extension's version you must first build and install the binaries and then run ALTER EXTENSION:

ALTER EXTENSION nominatim_fdw UPDATE;

To update to an specific version use UPDATE TO and the full version number

ALTER EXTENSION nominatim_fdw UPDATE TO '1.1';

Usage

To use the nominatim_fdw you must first create a SERVER to connect to a Nominatim endpoint. After that, you can retrieve the data using the nominatim_fdw functions.

CREATE SERVER

The SQL command CREATE SERVER defines a new foreign server, which in this case means a Nominatim server. The user who defines the server becomes its owner. A SERVER requires an url, so that nominatim_fdw knows where to sent the requests.

The following example creates a SERVER that connects to the OpenStreetMap Nominatim Server:

CREATE SERVER osm 
FOREIGN DATA WRAPPER nominatim_fdw 
OPTIONS (url 'https://nominatim.openstreetmap.org');

Server Options

Server Option	Type	Description
`url`	required	URL address of the Nominatim endpoint.
`http_proxy`	optional	Proxy for HTTP requests.
`proxy_user`	optional	User for proxy server authentication.
`proxy_user_password`	optional	Password for proxy server authentication.
`connect_timeout`	optional	Connection timeout for HTTP requests in seconds (default `300` seconds).
`max_connect_retry`	optional	Number of attempts to retry a request in case of failure (default `3` times).
`max_request_redirect`	optional	Limit of how many times the URL redirection may occur. If that many redirections have been followed, the next redirect will cause an error. Not setting this parameter or setting it to `0` will allow an infinite number of redirects.

ALTER SERVER

All options and parameters set to a SERVER can be changed, dropped, and new ones can be added using ALTER SERVER statements.

Adding options

ALTER SERVER osm OPTIONS (ADD max_connect_rety '5');

Changing previously configured options

ALTER SERVER osm OPTIONS (SET url 'https://a.new.url');

Dropping options

ALTER SERVER osm OPTIONS (DROP http_proxy);

Functions

This section describes the nominatim_fdw functions, which are mapped to the Nominatim standard search endpoints search, reverse and lookup.

Nominatim_Search

Description

The search API allows you to look up a location from a textual description or address. Just like the Nominatim API, the foreign data wrapper supports structured and free-form search queries, which are distinguished by either spliting the address components into different paramenteres, such as street, county, state, or providing a single string in the parameter q.

Availability: 1.0.0

Synopsis

SETOF Record nominatim_search(parameters)

Parameters

Parameter	Type	Description
`server_name`	required	Foreign Data Wrapper server created using the CREATE SERVER statement.
`q`	optional	Free-form query string to search for (default unset)
`amenity`	optional	name and/or type of POI (default unset)
`street`	optional	housenumber and streetname (default unset)
`city`	optional	city (default unset)
`county`	optional	county (default unset)
`state`	optional	state (default unset)
`country`	optional	country (default unset)
`postalcode`	optional	postal code (default unset)
`limit`	optional	limits the maximum number of returned results (default `10`)
`addressdetails`	optional	includes a breakdown of the address into elements (default `false`)
`extratags`	optional	additional information in the result that is available in the database, e.g. wikipedia link, opening hours. (default `false`)
`namedetails`	optional	include a full list of names for the result. (default `false`)
`accept_language`	optional	language string as in "Accept-Language" HTTP header (default `en_US`). This overrides the `accept_language` set in the `CREATE SERVER` statement
`countrycodes`	optional	comma-separated list of country codes (default unset)
`layer`	optional	comma-separated list of: `address`, `poi`, `railway`, `natural`, `manmade` (default unset)
`featureType`	optional	one of: `country`, `state`, `city`, `settlement` (default unset)
`exclude_place_ids`	optional	comma-separeted list of place ids (default unset)
`viewbox`	optional	bounding box as in `<x1>,<y1>,<x2>,<y2>` (default unset)
`bounded`	optional	When `bounded` is set to `true` and the `viewbox` is small enough, then an amenity-only search is allowed. Give the special keyword for the amenity in square brackets, e.g. [pub] and a selection of objects of this type is returned. There is no guarantee that the result returns all objects in the area. (default `false`)
`polygon_type`	optional	one of: `polygon_geojson`, `polygon_kml`, `polygon_svg`, `polygon_text (default unset)
`polygon_treshold`	optional	floating-point number (default `0.0`)
`email`	optional	valid email address (default unset)
`dedupe`	optional	discards duplicated entries (default `true`)

As in the Nominatim API, the free-form query string parameter q cannot be combined with the parameters amenity, street, city, county, state, country and postalcode, as they are used in structured calls.

Usage

For these examples we assume the following SERVER:

CREATE SERVER osm 
FOREIGN DATA WRAPPER nominatim_fdw 
OPTIONS (url 'https://nominatim.openstreetmap.org');

Free-form search

SELECT osm_id, ref, lon, lat, boundingbox 
FROM nominatim_search(server_name => 'osm', 
                      q => 'Neubrückenstraße 63, münster, germany');

  osm_id   |       ref       |    lon    |    lat     |                boundingbox                
-----------+-----------------+-----------+------------+-------------------------------------------
 121736959 | Theater Münster | 7.6293918 | 51.9648162 | 51.9644060,51.9652417,7.6286897,7.6304381
(1 row)

Structured search

SELECT osm_id, ref, lon, lat, boundingbox 
FROM nominatim_search(server_name => 'osm', 
                      street => 'neubrückenstraße 63', 
                      city => 'münster');

  osm_id   |       ref       |    lon    |    lat     |                boundingbox                
-----------+-----------------+-----------+------------+-------------------------------------------
 121736959 | Theater Münster | 7.6293918 | 51.9648162 | 51.9644060,51.9652417,7.6286897,7.6304381
(1 row)

Strcutured search with extratags

SELECT osm_id, ref, lon, lat, jsonb_pretty(extratags) AS extratags 
FROM nominatim_search(server_name => 'osm', 
                      street => 'neubrückenstraße 63', 
                      city => 'münster',
                      extratags => true);
  osm_id   |       ref       |    lon    |    lat     |                                               extratags                                                
-----------+-----------------+-----------+------------+--------------------------------------------------------------------------------------------------------
 121736959 | Theater Münster | 7.6293918 | 51.9648162 | {                                                                                                     +
           |                 |           |            |     "image": "https://upload.wikimedia.org/wikipedia/commons/6/64/Muenster_Stadttheater_%2881%29.JPG",+
           |                 |           |            |     "layer": "-1",                                                                                    +
           |                 |           |            |     "toilets": "customers",                                                                           +
           |                 |           |            |     "building": "civic",                                                                              +
           |                 |           |            |     "location": "surface",                                                                            +
           |                 |           |            |     "wikidata": "Q2415904",                                                                           +
           |                 |           |            |     "wikipedia": "de:Theater Münster",                                                                +
           |                 |           |            |     "roof:shape": "flat",                                                                             +
           |                 |           |            |     "start_date": "1956",                                                                             +
           |                 |           |            |     "wheelchair": "yes",                                                                              +
           |                 |           |            |     "contact:fax": "+49 251 5909202",                                                                 +
           |                 |           |            |     "roof:colour": "#F5F5DC",                                                                         +
           |                 |           |            |     "contact:email": "[email protected]",                                                +
           |                 |           |            |     "contact:phone": "+49 251 5909205",                                                               +
           |                 |           |            |     "roof:material": "gravel",                                                                        +
           |                 |           |            |     "building:colour": "silver",                                                                      +
           |                 |           |            |     "building:levels": "2",                                                                           +
           |                 |           |            |     "contact:website": "https://www.theater-muenster.com/start/index.html",                           +
           |                 |           |            |     "building:material": "concrete",                                                                  +
           |                 |           |            |     "construction_date": "1956",                                                                      +
           |                 |           |            |     "wikimedia_commons": "Category:Theater Münster",                                                  +
           |                 |           |            |     "toilets:wheelchair": "yes"                                                                       +
           |                 |           |            | }
(1 row)

Nominatim_Reverse

Description

Reverse geocoding generates an address from a coordinate given as latitude and longitude. The reverse geocoding API does not exactly compute the address for the coordinate it receives. It works by finding the closest suitable OSM object and returning its address information. This may occasionally lead to unexpected results.

Availability: 1.0.0

Synopsis

SETOF Record nominatim_reverse(parameters)

Parameters

Parameter	Type	Description
`server_name`	required	Foreign Data Wrapper server created using the `CREATE SERVER` statement.
`addressdetails`	optional	includes a breakdown of the address into elements (default `false`)
`extratags`	optional	additional information in the result that is available in the database, e.g. wikipedia link, opening hours. (default `false`)
`namedetails`	optional	includes a full list of names for the result. (default `false`)
`accept_language`	optional	language string as in "Accept-Language" HTTP header (default `en_US`). This overrides the `accept_language` set in the `CREATE SERVER` statement
`zoom`	optional	Level of detail required for the address. This is a number that corresponds roughly to the zoom level used in XYZ tile sources in frameworks like Leaflet.js, Openlayers etc. In terms of address details the zoom levels are as follows: `3` country, `5` state, `8` county, `10` city, `12` town / borough, `13` village / suburb, `14` neighbourhood, `15` any settlement, `16` major streets, `17` major and minor streets, `18` building (default `18`)
`layer`	optional	comma-separated list of: `address`, `poi`, `railway`, `natural`, `manmade` (default unset)
`polygon_type`	optional	one of: `polygon_geojson`, `polygon_kml`, `polygon_svg`, `polygon_text` (default unset)
`polygon_treshold`	optional	floating-point number. When one of the `polygon_*` outputs is chosen, return a simplified version of the output geometry. The parameter describes the tolerance in degrees with which the geometry may differ from the original geometry. Topology is preserved in the geometry. (default `0.0`)
`email`	optional	In case you're using the public Nominatim service: If you are making large numbers of requests, please include an appropriate email address to identify your requests. See Nominatim's Usage Policy for more details. You may ignore this parameter if you're hosting your server (default unset)

Usage

For these examples we assume the following SERVER:

CREATE SERVER osm 
FOREIGN DATA WRAPPER nominatim_fdw 
OPTIONS (url 'https://nominatim.openstreetmap.org');

Address generation for the coordinates 7.6293 longitude and 51.9648 latitude:

SELECT osm_id, result, boundingbox
FROM nominatim_reverse(
        server_name => 'osm', 
        lon => 7.6293,
        lat => 51.9648,        
        extratags => true);

  osm_id   |                                                          result                                                          |                boundingbox                
-----------+--------------------------------------------------------------------------------------------------------------------------+-------------------------------------------
 121736959 | Theater Münster, 63, Neubrückenstraße, Martini, Altstadt, Münster-Mitte, Münster, North Rhine-Westphalia, 48143, Germany | 51.9644060,51.9652417,7.6286897,7.6304381
(1 row)

Nominatim_Lookup

Description

The lookup API allows to query the address and other details of one or multiple OSM objects like node, way or relation.

Availability: 1.0.0

Synopsis

SETOF Record nominatim_lookup(parameters)

Parameters

Parameter	Type	Description
`server_name`	required	Foreign Data Wrapper server created using the CREATE SERVER statement.
`addressdetails`	optional	includes a breakdown of the address into elements (default `false`)
`extratags`	optional	additional information in the result that is available in the database, e.g. wikipedia link, opening hours. (default `false`)
`namedetails`	optional	include a full list of names for the result. (default `false`)
`accept_language`	optional	language string as in "Accept-Language" HTTP header (default `en_US`). This overrides the `accept_language` set in the `CREATE SERVER`
`polygon_type`	optional	one of: `polygon_geojson`, `polygon_kml`, `polygon_svg`, `polygon_text (default unset)
`polygon_treshold`	optional	floating-point number (default `0.0`)
`email`	optional	valid email address (default unset)

Usage

For these examples we assume the following SERVER:

CREATE SERVER osm 
FOREIGN DATA WRAPPER nominatim_fdw 
OPTIONS (url 'https://nominatim.openstreetmap.org');

SELECT osm_id, display_name 
FROM nominatim_lookup(
      server_name => 'osm',
      osm_ids => 'W121736959');

  osm_id   |                                                       display_name                                                       
-----------+--------------------------------------------------------------------------------------------------------------------------
 121736959 | Theater Münster, 63, Neubrückenstraße, Martini, Altstadt, Münster-Mitte, Münster, North Rhine-Westphalia, 48143, Germany
(1 row)

nominatim_fdw_version

Description

Shows the version of the installed nominatim_fdw and its main libraries.

Availability: 1.0.0

Synopsis

text nominatim_fdw_version();

Usage

SELECT nominatim_fdw_version();
                                                                               nominatim_fdw_version                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 nominatim_fdw = 1.0.0, libxml/2.9.10 libcurl/7.74.0 OpenSSL/1.1.1w zlib/1.2.11 brotli/1.0.9 libidn2/2.3.0 libpsl/0.21.0 (+libidn2/2.3.0) libssh2/1.9.0 nghttp2/1.43.0 librtmp/2.3
(1 row)

Deploy with Docker

To deploy nominatim_fdw with docker just pick one of the supported PostgreSQL versions, install the requirements and compile the source code. For instance, a nominatim_fdw Dockerfile for PostgreSQL 15 should look like this (minimal example):

FROM postgres:15

RUN apt-get update && \
    apt-get install -y make gcc postgresql-server-dev-15 libxml2-dev libcurl4-openssl-dev

RUN tar xvzf nominatim_fdw-[VERSION].tar.gz && \
    cd nominatim_fdw-[VERSION] && \
    make -j && \
    make install

To build the image save it in a Dockerfile and run the following command in the root directory - this will create an image called nominatim_fdw_image.:

 $ docker build -t nominatim_fdw_image .

After successfully building the image you're ready to run or create the container ..

$ docker run --name my_container -e POSTGRES_HOST_AUTH_METHOD=trust nominatim_fdw_image

.. and then finally you're able to create and use the extension!

$ docker exec -u postgres my_container psql -d mydatabase -c "CREATE EXTENSION nominatim_fdw;"

For testers and developers

Deploying the latest development version straight from the source:

Dockerfile

FROM postgres:15

RUN apt-get update && \
    apt-get install -y git make gcc postgresql-server-dev-15 libxml2-dev libcurl4-openssl-dev

WORKDIR /

RUN git clone https://github.com/jimjonesbr/nominatim_fdw.git && \
    cd nominatim_fdw && \
    make -j && \
    make install

Deployment

 $ docker build -t nominatim_fdw_image .
 $ docker run --name my_container -e POSTGRES_HOST_AUTH_METHOD=trust nominatim_fdw_image
 $ docker exec -u postgres my_container psql -d mydatabase -c "CREATE EXTENSION nominatim_fdw;"

Name		Name	Last commit message	Last commit date
Latest commit History 55 Commits
.github/workflows		.github/workflows
examples		examples
expected		expected
sql		sql
.gitignore		.gitignore
LICENSE		LICENSE
META.json		META.json
Makefile		Makefile
README.md		README.md
nominatim_fdw--1.0.sql		nominatim_fdw--1.0.sql
nominatim_fdw.c		nominatim_fdw.c
nominatim_fdw.control		nominatim_fdw.control

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

Nominatim Foreign Data Wrapper for PostgreSQL (nominatim_fdw)

Index

Requirements

Build and Install

Update

Usage

CREATE SERVER

ALTER SERVER

Functions

Nominatim_Search

Nominatim_Reverse

Nominatim_Lookup

nominatim_fdw_version

Deploy with Docker

For testers and developers

About

Releases 1

Packages

Languages

License

jimjonesbr/nominatim_fdw

Folders and files

Latest commit

History

Repository files navigation

Nominatim Foreign Data Wrapper for PostgreSQL (nominatim_fdw)

Index

About

Resources

License

Stars

Watchers

Forks

Languages