PostGIS

in the Open Cloud

writing portable open source mapping applications

with

Leaflet

and

OpenShift

bit.ly/1tpBqGA

http://ryanjarvinen.com/presentations/postgis-open-cloud

presented by

ryan jarvinen / @ryanj

Open Source Evangelist

Red Hat

ryanj@redhat.com

Agenda

Open Cloud Overview
Select a language and a lightweight web framwork
Add Leaflet for client-side map interactions
Build a simple API
Install and configure PostgreSQL, PostGIS
Use environment variables to keep your code clean
Bootstrap your DB
Launch your instant mapping solution
Learn about tuning PG on OpenShift

the Cloud

"what is it made of?"

The cloud is:

hot air?
a series of tubes?
mostly cat photos✓

Infrastructure

as a service

inputs:

Hardware: racks, fiber, routers, storage, compute
Software: OpenStack, Eucalyptus, CloudStack, SaltStack

Result:

Easy on-demand Linux environments

Platform

as a service

inputs:

Hardware: IaaS
Software: SELinux, CGroups, Docker, HAProxy, ssh, git

Result:

Horizontally scalable application architectures on-demand

Providing standards-based, open source workflows that answer the question of:

"How do I

Build,

Host,

&

Scale

my solutions

on an

Open Cloud?"

Open platforms provide a peaceful environment for Developers AND Operations teams to work together in

Operations teams can help ensure system-wide stability and performance
Developers can quickly provision environments without waiting
The discussion shifts towards establishing great policies for scaling in response to demand

Terminology (Red Hat)

Broker – Management host, orchestration of Nodes
Node – Compute host containing Gears
Gear – Allocation of fixed memory, compute, and storage resources for running applications (SELinux, CGoups)
Cartridge – A technology, framework, or application: Python, Ruby, Javascript, PHP, Perl, Java/JEE, PG, MySQL, Jenkins, PHPMyAdmin, etc.

An Open Cartridge format

cart developer's guide

OpenShift Release Schedule

Frameworks

NodeJS / Restify:
https://github.com/ryanj/restify-base
Python / Flask:
https://github.com/ryanj/flask-base
PHP / Silex:
https://github.com/ryanj/silex-base
Ruby / Sinatra:
https://github.com/ryanj/sinatra-base

Language-specific Dependencies

Automatic support for dependency resolution using standard packaging, native to each language:

gems (ruby), eggs (python), and npm modules (node.js)

Language-specific DB bindings

For nodejs:

npm install pg-query --save

brianc's pg-query module makes working with PG exceedingly simple

Just map your queries to their related callback functions.

Local development

Resolve dependencies:

npm install

Fire up a local server:

npm start

Leaflet

Include a link to Leaflet's css stylesheet and javascript code in your index.html file:

<link rel="stylesheet" href="//cdn.leafletjs.com/leaflet-0.5.1/leaflet.css" />
<script src="//cdn.leafletjs.com/leaflet-0.5.1/leaflet.js"></script>

Initialize the map:

var map = L.map('map').setView([37.8, -122.3], 10);
var markerLayerGroup = L.layerGroup().addTo(map);
L.tileLayer('http://{s}.tile.stamen.com/terrain/{z}/{x}/{y}.png', {
  maxZoom: 18,
  minZoom: 5,
  attribution: 'Map tiles by <a href="http://stamen.com">Stamen Design</a>, under <a href="http://creativecommons.org/licenses/by/3.0">CC BY 3.0</a>. Data by <a href="http://openstreetmap.org">OpenStreetMap</a>, under <a href="http://creativecommons.org/licenses/by-sa/3.0">CC BY SA</a>.'
}).addTo(map);

Update the Map on load, drag, or zoom:

function getPins(e){
  bounds = map.getBounds();
  url = "parks/within?lat1=" + bounds.getSouthWest().lat + "&lon1=" + bounds.getSouthWest().lng + "&lat2=" + bounds.getNorthEast().lat + "&lon2=" + bounds.getNorthEast().lng;
  $.get(url, pinTheMap, "json")
}
function pinTheMap(data){
  //clear the current pins
  map.removeLayer(markerLayerGroup);
  //add the new pins
  var markerArray = new Array(data.length)
  for (var i = 0; i < data.length; i++){
    park = data[i];
    markerArray[i] = L.marker([park.lat, park.lon]).bindPopup(park.name);
  }
  markerLayerGroup = L.layerGroup(markerArray).addTo(map);
}
map.on('dragend', getPins);
map.on('zoomend', getPins);
map.whenReady(getPins);

Building an API

var config      = require('config'),
    restify     = require('restify'),
    fs          = require('fs'),
    db          = require('./bin/db.js')
var app = restify.createServer()
app.use(restify.queryParser())
app.use(restify.CORS())
app.use(restify.fullResponse())
 
// Routes
app.get('/parks/within', db.selectBox);
app.get('/parks', db.selectAll);
// Static assets
app.get(/\/(css|js|img)\/?.*/, restify.serveStatic({directory: './static/'}));
app.get('/', function (req, res, next)
{
  var data = fs.readFileSync(__dirname + '/index.html');
  res.status(200);
  res.header('Content-Type', 'text/html');
  res.end(data.toString().replace(/host:port/g, req.header('Host')));
});
 
app.listen(config.port, config.ip, function () {
  console.log( "Listening on " + config.ip + ", port " + config.port )
});

PG Setup

Adding Postgres to existing apps:

rhc cartridge add postgres-8.4

rhc cartridge add postgres-9.2

done!

blog post: PostgreSQL 9.2 Comes to OpenShift

function select_box(req, res, next){
  //clean our input variables before forming our DB query:
  var query = req.query;
  var limit = (typeof(query.limit) !== "undefined") ? query.limit : 40;
  if(!(Number(query.lat1) 
    && Number(query.lon1) 
    && Number(query.lat2) 
    && Number(query.lon2)
    && Number(limit)))
  {
    res.send(500, {http_status:400,error_msg: "this endpoint requires two pair of lat, long coordinates: lat1 lon1 lat2 lon2\na query 'limit' parameter can be optionally specified as well."});
    return console.error('could not connect to postgres', err);
  }
  pg('SELECT gid,name,ST_X(the_geom) as lon,ST_Y(the_geom) as lat FROM ' + table_name+ ' t WHERE ST_Intersects( ST_MakeEnvelope('+query.lon1+", "+query.lat1+", "+query.lon2+", "+query.lat2+", 4326), t.the_geom) LIMIT "+limit+';', function(err, rows, result){
    if(err) {
      res.send(500, {http_status:500,error_msg: err})
      return console.error('error running query', err);
    }
    res.send(rows);
    return rows;
  })
};

Env Vars

For writing Clean and Portable Code

rhc app show rss

Or, while connected over ssh:

env | grep DB

OPENSHIFT_POSTGRESQL_DB_PASSWORD=lXcFVx4hIZgR
OPENSHIFT_POSTGRESQL_DB_SOCKET=/var/lib/openshift/523672f7e0b8cd02d70003bc/postgresql/socket/
OPENSHIFT_POSTGRESQL_DB_HOST=127.7.8.130
OPENSHIFT_POSTGRESQL_DB_PID=/var/lib/openshift/523672f7e0b8cd02d70003bc/postgresql/pid/postgres.pid
OPENSHIFT_POSTGRESQL_DB_USERNAME=adminpahue6e
OPENSHIFT_POSTGRESQL_DB_URL=postgresql://adminpahue6e:lXcFVx4hIZgR@127.7.8.130:5432
OPENSHIFT_POSTGRESQL_DB_PORT=5432
OPENSHIFT_POSTGRESQL_DB_LOG_DIR=/var/lib/openshift/523672f7e0b8cd02d70003bc/postgresql/log/

Persist configuration details,
while keeping your source clean:

module.exports = {
  port: process.env.PORT || process.env.OPENSHIFT_NODEJS_PORT || 3000,
  ip: process.env.OPENSHIFT_NODEJS_IP || '127.0.0.1',
  pg_config: process.env.OPENSHIFT_POSTGRESQL_DB_URL || 'postgresql://127.0.0.1:5432',
  table_name: process.env.OPENSHIFT_APP_NAME || process.env.PG_MAP_TABLE_NAME || 'parks'
}

Environment Variables

Listing your custom env vars:

cd myapp 
rhc env list

Setting a variable:

rhc env set SECRET_TOKEN="a1fdacc3b1d14d6a92ed1219ed304d02529f535085262a90c39f072ef6de0ee9fe3a3d0194f02a2a8eb3"

Help with configuration:

rhc help env

Or, supply additional keys during the app creation process:

rhc app create hydrant ruby-1.9 postgresql-8.4 --from=code=http://github.com/ryanj/adopt-a-hydrant.git --env SECRET_TOKEN="YOUR_SECRET_TOKEN"

http://hydrant-shifter.rhcloud.com/

Advanced DB services mapping

Using port-forwarding for local dev

Or, connect to existing hosted PG services

Automate your DB Setup

Action Hooks

enable postgis
create your table schema
add a geospatial index
bootstrap your db

https://github.com/ryanj/restify-postGIS/tree/master/.openshift/action_hooks

var config      = require('config'),
    pg          = require('pg-query')
var pg_config   = config.pg_config,
    table_name  = config.table_name;
pg.connectionParameters = pg_config + '/' +table_name;
var points = require('../parkcoord.json');
 
function initDB(){
  pg('CREATE EXTENSION postgis;', createDBSchema);
} 
function createDBSchema(err, rows, result) {
  if(err && err.code == "ECONNREFUSED"){
    return console.error("DB connection unavailable, see README notes for setup assistance\n", err);
  }
  var query = "CREATE TABLE "+table_name+
    " ( gid serial NOT NULL, name character varying(240), the_geom geometry, CONSTRAINT "+table_name+ "_pkey PRIMARY KEY (gid), CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2), CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL),CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4326) ) WITH ( OIDS=FALSE );";
  pg(query, addSpatialIndex);
};

function addSpatialIndex(err, rows, result) {
  pg("CREATE INDEX "+table_name+"_geom_gist ON "+table_name+" USING gist (the_geom);", importMapPoints);
}
function importMapPoints(err, rows, result) {
  var query = "Insert into "+table_name+" (name, the_geom) VALUES " + points.map(mapPinSQL).join(",") + ';';
  pg(query, function(err, rows, result) {
    var response = 'Data import completed!';
    return response;
  });
};
function mapPinSQL(pin) {
  var query = '';  
  if(typeof(pin) == 'object'){
    query = "('" + pin.Name.replace(/'/g,"''") + "', ST_GeomFromText('POINT(" + pin.pos[0] +" "+ pin.pos[1] + " )', 4326))";  
  }
  return query;
};

Results!

NodeJS / restify:
https://github.com/ryanj/restify-postGIS
Python / Flask:
https://github.com/ryanj/flask-postGIS
Java / Hibernate:
https://github.com/thesteve0/parkshibernatespatial

Launch Time!

Spin up a fresh app from the command line:

rhc app create myapp cart1 cart2 --from-code=http://github.com/user/repo.git

For our nodejs example:

rhc app create nodegis nodejs-0.10 postgres-9.2 --from-code=http://github.com/ryanj/restify-postGIS.git

Live Result

nodegis-shifter.rhcloud.com/

~ related content ~

Tuning PG

aka, where is my pg_hba.conf, and postgresql.conf?

Some PG tuning notes were recently posted in the OpenShift Online release announcement for April 2014

OPENSHIFT_POSTGRESQL_SHARED_BUFFERS

OPENSHIFT_POSTGRESQL_MAX_CONNECTIONS

More general tuning advice: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Advanced configurations

Watch out for statistics collector issues in the pg-9.2 cart

Take a look at the latest from CrunchyData!

HA for PG by CrunchyData

See their release announcement for additional details: crunchydatasolutions.com

Including support for LB, geographic failover, Master-slave replication, and more!

Join the Community

We accept pull requests
Help with anything from core, quickstarts, and cartridges, to small typo fixes in the command line tools
PEPs for major feature enhancements
Contribution Guidelines
Public Trello cards
Public Test plans
Public Bugzilla
Vote on Features

Origin.ly

The Origin.ly Appication and Cartridge index

OpenShift Core Roadmap:

Check out the upstream source:
OpenShift Origin

Try our hosted solution (3 apps free):
OpenShift Online

Request an evaluation for:
OpenShift Enterprise

Thank You!

See my post on this topic for more info: Instant Mapping Applications with PostGIS and Nodejs

Link to these slides: bit.ly/1tpBqGA

See you next time!
--ryanj

PostGIS

in the Open Cloud

Leaflet

OpenShift

Agenda

Infrastructure

as a service

Platform

as a service

Build,

Host,

&

Scale

Terminology (Red Hat)

An Open Cartridge format

OpenShift Release Schedule

Frameworks

Language-specific Dependencies

Language-specific DB bindings

Local development

Leaflet

Building an API

PG Setup

Adding Postgres to existing apps:

Env Vars

For writing Clean and Portable Code

Environment Variables

Advanced DB services mapping

Automate your DB Setup

Action Hooks

Results!

Launch Time!

Web Workflows for Launching Apps

Open Source Ribbons for Launching Apps

Custom Domain Names and SSL

Tuning PG

Advanced configurations

HA for PG by CrunchyData

Join the Community

Origin.ly

OpenShift Core Roadmap:

Thank You!