F Setting up an SQL API

Last updated: 2018-11-11 20:44:59

F.1 PostgreSQL and PostGIS

The first step is to install the PostgreSQL database and enable the PostGIS extension.

The following instructions are based on -

http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS23UbuntuPGSQL96Apt

And suitable for Ubuntu 16.04 xenial.

First, add the respository to sources.list -

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt xenial-pgdg main" >> /etc/apt/sources.list'
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

Install the required packages -

sudo apt-get install postgresql-9.6
sudo apt-get install postgresql-9.6-postgis-2.3 postgresql-contrib-9.6 postgresql-9.6-postgis-scripts
sudo apt-get install postgis
# sudo apt-get install postgresql-9.6-pgrouting

Login to the database -

sudo -u postgres psql

Enable adminpack -

CREATE EXTENSION adminpack;
\q

Login once again -

sudo -u postgres psql

Create a database named gisdb and enable extensions -

CREATE DATABASE gisdb;
\connect gisdb;

CREATE SCHEMA postgis;
ALTER DATABASE gisdb SET search_path=public, postgis, contrib;
<!-- \connect gisdb;   -->
CREATE EXTENSION postgis SCHEMA postgis;

CREATE EXTENSION postgis_sfcgal SCHEMA postgis;
SELECT postgis_full_version();

<!-- CREATE EXTENSION pgrouting; -->
<!-- SELECT * FROM pgr_version(); -->
\q

Login -

sudo -u postgres psql

Open access to clients -

ALTER SYSTEM SET listen_addresses='*'; 
\q

Edit the pg_hba.conf file -

sudo gedit /etc/postgresql/9.6/main/pg_hba.conf

Add this line -

host    all             all             0.0.0.0/0               md5

Then edit the postgresql.conf file -

sudo gedit /etc/postgresql/9.6/main/postgresql.conf

And add this line -

listen_addresses = '*'

Restart the database -

sudo service postgresql restart

Login to the database -

sudo -u postgres psql

Create a superuser -

CREATE ROLE mysuperuser LOGIN PASSWORD 'whatever' SUPERUSER;
\q

Login as the new user -

psql -h localhost -U mysuperuser gisdb

https://dba.stackexchange.com/questions/35316/why-is-a-new-user-allowed-to-create-a-table

REVOKE ALL ON schema public FROM public;
GRANT ALL ON schema public TO mysuperuser;

F.2 Adding data

Now that the database is set, you need to add some data. There are many ways to do that. We will use R.

Read the plants.geojson file (download) and import it to the database from R -

library(sf)
library(RPostgreSQL) 

# Connect
con = dbConnect(PostgreSQL(), dbname = "gisdb", host = "localhost", port = 5432, user = "mysuperuser", password = "whatever")

# Import table to database
dat = st_read("plants.geojson", stringsAsFactors = FALSE)
st_write_db(con, dat, "plants", drop = TRUE)

# Disconnect
dbDisconnect(con)

F.3 Testing

Let’s test if the database works properly.

While logged in to the database, run the following query -

select id, name_lat, ST_AsText(geometry) from plants limit 5;

If all works well you should see a result such as the following -

   id   |    name_lat    |                st_astext                 
--------+----------------+------------------------------------------
 321432 | Iris haynei    | POINT(35.6797610296996 32.7701334254926)
 321433 | Iris haynei    | POINT(35.6540048061212 32.7413715482281)
 321456 | Iris atrofusca | POINT(35.1933666886313 31.447109876834)
 321457 | Iris atrofusca | POINT(35.1891423219429 31.5147536901285)
 321459 | Iris vartanii  | POINT(35.1396955200257 31.4741486701326)
(5 rows)

F.4 Adding read-only user

CREATE ROLE readonlyuser WITH LOGIN PASSWORD 'readonlyuser1'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

Assign permission to this read only user:

GRANT CONNECT ON DATABASE gisdb TO readonlyuser;
GRANT USAGE ON SCHEMA public TO readonlyuser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonlyuser;

Possibly need to update search path -

select e.extname,n.* 
from pg_extension e, pg_namespace n 
where e.extnamespace = n.oid and e.extname='postgis';

show search_path;

ALTER USER readonlyuser SET search_path TO "$user", public, postgis;

(https://serverfault.com/questions/60500/crate-a-new-read-only-user-in-postgres)

And/or move the postgis extension to shema public -

UPDATE pg_extension 
  SET extrelocatable = TRUE 
    WHERE extname = 'postgis';
 
ALTER EXTENSION postgis 
  SET SCHEMA public;

(https://postgis.net/2017/11/07/tip-move-postgis-schema/)

Now, connect as the new user -

psql -h localhost -U readonlyuser gisdb

And try -

DROP TABLE PLANTS;

You should see an error -

ERROR:  must be owner of relation plants

Also -

delete from plants where name_lat='Iris haynei';

ERROR:  permission denied for relation plants

F.5 Node.js

Next, we need to install Node.js.

Run the following commands to get the required packages -

sudo apt-get update
sudo apt-get install nodejs
sudo apt-get install nodejs-legacy
sudo apt-get install npm

Check that Node.js is installed with the following command, which sould print the installed version -

nodejs -v

F.6 Server

We will use a Node.js script to create an SQL API for our database.

This is based on the postgis-preview example from here -

https://chriswhong.com/data-visualization/introducing-postgis-preview/

Create a new folder for your server app. Run the following commands to initialize the server and download the required dependencies -

mkdir node
cd node

npm init
npm install express --save
npm install pg-promise --save
npm install dbgeo --save

Now, inside the directory creare a new file named index.js and paste the following content inside it -

nano index.js

// Dependencies
var express = require("express"),
    pgp = require("pg-promise")(),
    dbgeo = require("dbgeo");
    
// CORS
var allowCrossDomain = function(req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Methods", "GET");
    res.header("Access-Control-Allow-Headers", "Content-Type");
    next();
}

// Create express app and prepare db connection
var app = express(),
    port = 4000,
    connectionString = "postgres://readonlyuser:readonlyuser1@localhost:5432/gisdb",
    db = pgp(connectionString);

app.use(allowCrossDomain);

// Expose sql endpoint, grab query as URL parameter and send it to the database
app.get("/sql", function (req, res) {
    var sql = req.query.q;
    console.log("Executing SQL: " + sql);

    //query using pg-promise
    db.any(sql)
        .then(function (data) { //use dbgeo to convert WKB from PostGIS into topojson
            return dbGeoParse(data);
        })
        .then(function (data) {
            res.send(data);
        })
        .catch(function (err) { //send the error message if the query didn't work
            var msg = err.message || err;
            console.log("ERROR:", msg);
            res.send({
                error: msg
            });
        });
});

// Parse to GeoJSON
function dbGeoParse(data) {
    return new Promise(function (resolve, reject) {
        dbgeo.parse(data, {
            outputFormat: "geojson"
        }, function (err, result) {
            if (err) {
                reject(err);
            } else {
                resolve(result);
            }
        });
    });
}

// Start the server
var server = app.listen(port, "0.0.0.0", function () {
  var host = server.address().address;
  var port = server.address().port;
  console.log("App listening at http://%s:%s", host, port);
});

While still inside the directory, type the following command to start the server -

node index.js

May need to install a later version of node -

https://www.digitalocean.com/community/tutorials/how-to-install-node-js-on-ubuntu-16-04

F.7 Testing

Enter the following URL in your browser -

http://localhost:8000/sql?q=select id, name_lat, geometry AS geom from plants limit 2

Or on the test DigitalOcean server -

http://159.89.13.241:8000/sql?q=select id, name_lat, geometry AS geom from plants limit 2

If the SQL API is working properly, you should see the following GeoJSON content in the response -

{
   "type":"FeatureCollection",
   "features":[
      {
         "type":"Feature",
         "geometry":{
            "type":"Point",
            "coordinates":[
               35.389282569732835,
               31.579570927419955
            ]
         },
         "properties":{
            "id":326579,
            "lat":"Cyperus jeminicus"
         }
      },
      {
         "type":"Feature",
         "geometry":{
            "type":"Point",
            "coordinates":[
               35.44762877883449,
               31.739893751235616
            ]
         },
         "properties":{
            "id":338947,
            "lat":"Cyperus jeminicus"
         }
      }
   ]
}

F.8 Setup for production

https://www.digitalocean.com/community/tutorials/how-to-set-up-a-node-js-application-for-production-on-ubuntu-16-04

---

Bassett, Lindsay. 2015. Introduction to Javascript Object Notation: A to-the-Point Guide to Json. “ O’Reilly Media, Inc.”

Crickard III, Paul. 2014. Leaflet. Js Essentials. Packt Publishing Ltd.

Dent, Borden D, Jeffrey S Torguson, and Thomas W Hodler. 2008. Cartography: Thematic Map Design. 6th ed. WCB/McGraw-Hill Boston.

Dincer, Alper, and Balkan Uraz. 2013. Google Maps Javascript Api Cookbook. Packt Publishing Ltd.

Farkas, Gábor. 2016. Mastering Openlayers 3. Packt Publishing Ltd.

Gratier, Thomas, Paul Spencer, and Erik Hazzard. 2015. OpenLayers 3: Beginner’s Guide. Packt Publishing Ltd.

Langley, Peter J, and Antonio Santiago Perez. 2016. OpenLayers 3. X Cookbook. 2nd ed. Packt Publishing Ltd.

Murray, Scott. 2017. Interactive Data Visualization for the Web: An Introduction to Designing with D3. “O’Reilly Media, Inc.”

Newton, Thomas, and Oscar Villarreal. 2014. Learning D3. Js Mapping. Packt Publishing Ltd.

Obe, R, and L Hsu. 2015. PostGIS in Action. 2nd ed. Manning Publications Co., USA. http://www.postgis.us/.

Rubalcava, Rene. 2015. ArcGIS Web Development. Manning Publications Company.

Tufte, Edward. 2001. The Quantitative Display of Information. 2nd ed. Graphics Press, Cheshire, Connecticut.