A Quick and Simple PostGIS/Flask App on OpenShift

For the past couple years much of the GIS and programming I’ve done has been applied to biospheric modeling using raster data. I enjoy my work, but sometimes wish I could spend a little more time working with PostGIS and developing web applications. Yesterday I mentioned this to my friend Steve CP (OpenShift tech evangelist) and he kindly offered to stop by and help get me started with an app on OpenShift. The process was a piece of cake, but there were quite a few steps to remember. Here are my notes:

1. Open an OpenShift account and create a Python-2.6 app. https://openshift.redhat.com/app/account/new

2. Create a local git repository for your app. You’ll need to install git locally if it’s not installed already. After you create an app on the OpenShift site, OpenShift will give you the following command line to create a local repository for your app.

cyrus@gazelle:~$ git clone ssh://your_application_ssh_key@npm-cyrus.rhcloud.com/~/git/npm.git/

This will create your application directory at the root of your home directory, so you may want to move it to a more logical location like so:

mv npm Projects/OpenShift/npm

3. Add the postgresql-8.4 cartridge.

cyrus@gazelle:~$ rhc app cartridge add -a npm -c postgresql-8.4

Once your cartidge is added you will see your connection infomation (user, password, etc). You can save this information in a local text file for convenience, but you don’t need to. We will retreive it later.

4. Log into the OpenShift instance for your app and setup postgis.

cyrus@gazelle:~$ ssh your_application_ssh_key@npm-cyrus.rhcloud.com

Once you’ve successfully logged in run the following 3 psql commands:

[npm-cyrus.rhcloud.com ~]\> psql npm -c "create language plpgsql;"
[npm-cyrus.rhcloud.com ~]\> psql -d npm -f /usr/share/pgsql/contrib/postgis-64.sql
[npm-cyrus.rhcloud.com ~]\> psql -d npm -f /usr/share/pgsql/contrib/spatial_ref_sys.sql

PostGIS is now ready so you can hit Ctrl + D to close the connection to your OpenShift instance.

5. Setup the flask and psycopg installations. First navigate to your local app directory and get the openshift flask example (more details here: https://openshift.redhat.com/community/blogs/rest-web-services-with-python-mongodb-and-spatial-data-in-the-cloud-part-2)

cyrus@gazelle:~$ cd Projects/OpenShift/npm/
cyrus@gazelle:~$ git remote add upstream -m master git://github.com/openshift/flask-example.git
cyrus@gazelle:~$ git pull -s recursive -X theirs upstream master
cyrus@gazelle:~$ git push

You’ll now find a setup.py file in your app directory. Pushing this up to your app installs Flask, but we need to edit it so that it will install psycopg as well. Open the setup.py file and in the “install_requires” list add ‘psycopg2>=2.4.4’ like so:

from setuptools import setup

      description='OpenShift App',
      author='Your Name',
      install_requires=['Flask>=0.7.2', 'psycopg2>=2.4.4'],

Now save your file, commit and push:

cyrus@gazelle:~$ git commit -am "adding psycopg"
cyrus@gazelle:~$ git push

6. Set up a test database. You may be content to do this using psql commands in your OpenShift instance, but I prefer to manage my database locally using pgadmin. To do this ssh back into your OpenShift app and run the following command to get your database connection information:

cyrus@gazelle:~$ ssh your_application_ssh_key@npm-cyrus.rhcloud.com
[npm-cyrus.rhcloud.com ~]\> env | grep DB

Ctrl+D to close your connection and then open a port forward to your app like so:

cyrus@gazelle:~$ rhc-port-forward -a npm

Now, open pgadmin and create a database connection:

I have named my database ‘npm’ and created a table called ‘test’ with columns ‘name’ and ‘value’ and a serial column for the primary key. Then I added a geometry column in the SQL Editor and added a couple of points with the following:

SELECT AddGeometryColumn('test', 'geom', 4326, 'POINT', 2);
INSERT INTO test (geom, name, value)
VALUES (GeomFromText('POINT(-122.18064449999997 37.3456834)', 4326),'Acterra', 0);
INSERT INTO test (geom, name, value)
VALUES (GeomFromText('POINT(-122.16763550000002 37.7298953)', 4326),'Evergreen', 1);

If you would like all of the SQL for creating and populating this table, you can find it at the bottom of this post.

7. Prepare our flask app. Open “myflaskapp.py” in the wsgi folder in your local project directory and edit it like so:

from flask import Flask
import psycopg2
import sys
import os

app = Flask(__name__)
app.config['PROPAGATE_EXCEPTIONS'] = True # Prevents flask from swallowing error messages

def hello():

    ip = os.environ['OPENSHIFT_DB_HOST'] #Get an OpenShift environmental variable
    pw = os.environ['OPENSHIFT_DB_PASSWORD']
    conn_string = "host=" + ip + " dbname='npm' user='admin' password=" + pw +""
    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor()
    cursor.execute("SELECT id, name, value, AsText(geom) FROM test")
    records = cursor.fetchall() # retrieve the records from the database
    return str(records)

if __name__ == "__main__":

If your table schema is different than mine, be sure those differences are reflected in the select statement passed in the cursor.execute() method.

Navigate back to your local project directory, commit your changes, and push.

cd Projects/Openshift/npm
cyrus@gazelle:~/Projects/OpenShift/npm$ git commit -am "Modified my flask app"
cyrus@gazelle:~/Projects/OpenShift/npm$ git push

Now, your app should be available at your project url. In this case:

..and the result should look something like this:

sql code for creating a postgis table:

-- Table: test

-- DROP TABLE test;

"name" text,
"value" integer DEFAULT 0,
id serial NOT NULL,
geom geometry,
CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL),
CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326)
ALTER TABLE test OWNER TO "admin";

SELECT AddGeometryColumn('test', 'geom', 4326, 'POINT', 2);

INSERT INTO test (geom, name, value)
VALUES (GeomFromText('POINT(-122.18064449999997 37.3456834)', 4326),'Acterra', 0);
INSERT INTO test (geom, name, value)
VALUES (GeomFromText('POINT(-122.16763550000002 37.7298953)', 4326),'Evergreen', 1);