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
at
Red Hat

ryanj@redhat.com

Agenda

  1. Open Cloud Overview
  2. Select a language and a lightweight web framwork
  3. Add Leaflet for client-side map interactions
  4. Build a simple API
  5. Install and configure PostgreSQL, PostGIS
  6. Use environment variables to keep your code clean
  7. Bootstrap your DB
  8. Launch your instant mapping solution
  9. 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

OpenShift Cartridge

cart developer's guide


OpenShift Release Schedule

Frameworks

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

or

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

  1. enable postgis
  2. create your table schema
  3. add a geospatial index
  4. 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!

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 ~

Web Workflows for Launching Apps

Customizing OpenShift's Web-based App Creation Workflow


Open Source Ribbons for Launching Apps

Instant Hosting of Open Source Projects with GitHub-style Ribbons


Custom Domain Names and SSL

Domain Names and SSL in the OpenShift Web Console

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

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