koop-provider-postgre

This is a draft of a Koop provider to illustrate how to enable the data stored in a PostgreSQL / PostGIS database to be consumed as feature layers.

Table of Contents

• Setup
  • Connection to PostgreSQL
  • Secure the API
  • Development Server
  • Sample application
  • Deploy
• How to use it
  • Sample queries

Setup

Connection to PostgreSQL

To connect to you PostgreSQL database you need to change config/default.json:

{
    "user": "vagrant",
    "host": "127.0.0.1",
    "database": "devsummit2021",
    "password": "vagrant",
    "port": 6543
}

Note: if you are looking for a sample database to test this provider you can use: DevSummitKoopPostgres.backup. And if you also do not have a PostGIS you can also use vagrant-postgis.

Secure the API

Besides using an user with restricted permission you add security to Koop using the auth-direct-file provider (step by step guide).

Development Server

This project by default uses the Koop CLI to set up the dev server. It can be invoded via:

$ koop serve

The server will be running at http://localhost:8080 or at the port specified at the configuration.

Enable debugging mode

You can use the debugger running:

$ koop serve --debug

After that you will be able to use the developer tools of your browser to debug (for Chrome go to: chrome://inspect, Firefox about:debugging, ...)

Sample application

To preview/test this provider there is an interface at test/index.html.

Deploy

In order to use a provider in a production environment you need to publish in on NPM and afterwards follow these steps.

How to use it

After running Koop you will need to use a service URL that will look like this:

http://localhost:8080/koop-provider-postgresql/<SQL_STATEMENT_IN_BASE64>/FeatureServer/0/query?f=geojson.

To enconde the SQL statement you can use these snippets:

// In the browser
let encodedStatement = btoa("SELECT .....");
console.log(encodedStatement.replaceAll('/','_'));

// In nodejs
const btoa = require('btoa');
let encodedStatement = btoa("SELECT .....");
console.log(encodedStatement.replaceAll('/','_'));

The provider will decode the SQL statement that has been previously encoded and will parse all tuples in the results to generate a GeoJSON.

Important: To genereate the geometry it will expect to find field called st_asgeojson as you can see in model.js:

const geojson = {
    type: 'FeatureCollection',
    features: []
}
//...
res.rows.forEach(r => {
    let newObj = {
        "type": "Feature",
        "geometry": JSON.parse(r.st_asgeojson)
    };

    delete r.st_asgeojson;
    newObj.properties = {
        ...r
    };

    geojson.features.push(newObj);
});

Sample queries

These ara sample queries that would work:

SELECT St_asgeojson(St_transform(parks.geometry, 4326)),
    parks.*
FROM
    parks
;

SELECT St_asgeojson(St_transform(trees.geometry, 4326)),
    trees.*
FROM   trees,
    parks
WHERE  St_intersects(trees.geometry, parks.geometry)
    AND parks.name = 'Baker Park';

SELECT St_asgeojson(St_transform(P.geometry, 4326)),
       P.*
FROM   parcels AS P
       INNER JOIN (SELECT parcel_id
                   FROM   owners
                          INNER JOIN owners_parcels
                                  ON owners.id = (SELECT id
                                                  FROM   owners
                                                  WHERE  email =
                                                 'raul.jimenez@esri.es'
                                                 )) AS R
               ON P.parcel_id = R.parcel_id;