Creating simple front-end for PostGIS database

Question

My organisation collects a lot of environmental monitoring data (stream flow, water chemistry etc.) most of which is currently stored in Excel. I’d like to get everything into a database – probably PostGIS – but I need the data to be accessible to people with no knowledge of databasing principles (no SQL or anything like that).

I’d like to have some kind of simple front-end for my database where the user selects a monitoring station of interest, variable type (e.g. stream nitrate concentration) and a time interval. The database would then return the requested data in csv format, which the user could download to their computer. In an ideal world the front-end would display a clickable map of all the monitoring stations, but if it’s easier then a drop-down list of available sites would be fine to begin with. I guess this front-end would probably be a website, but it doesn’t have to be.

How difficult is this, and what are my various options?

I’m not a databasing expert by any means, but I do have a solid background in Desktop GIS and I’m happy writing simple Python scripts and playing with PostGIS, SpatiaLite and ESRI Personal geodatabases. I’ve never done any web development or web GIS, but I’m keen to learn.

I spent part of yesterday exploring some of the excellent OpenGeo tutorials. This approach looks promising, and at least I can now see how to get my data displayed on a website. What’s less obvious to me is how I let the user query the database and get results back.

My question: What kind of software structure should I be thinking about for a project like this? e.g.

PostGIS + GeoServer + Something else?

What other options are available that I should research?

A simple but functional solution would be preferred initially: if I can put together a basic prototype I can use it to justify spending more time learning how to do things “properly”. Open source is also a requirement as I don’t have any budget for this until I can prove some benefits.

Answer 1

If you already know your way around Python and SQL, it wouldn't be too hard to build a website using a python web framework. Two simple ones - cherry.py and web.py - come to mind. I think learning your way around Django's object relational mapper might be more work than it's worth.

Python has a library called psycopg2 (http://initd.org/psycopg/, and a tutorial: http://wiki.postgresql.org/wiki/Psycopg2_Tutorial) which lets you easily run queries against PostGRESQL/PostGIS. It also has a CSV module (http://docs.python.org/library/csv.html) which will make creating your CSV from said query a breeze.

As other people have said, creating a map on the front end adds reams of complexity. You'll need something like OpenLayers to display the maps, and something like MapServer or GeoServer to create map images from your database (or other data sources). That would probably be better as a second step.

For now, "all" it seems like you need is one web page: a form that lets you select which query paremeters you want. They they hit "submit", the query parameters are sent to the server (Apache would be a good choice), which then runs your script, which queries the database, creates an appropriate CSV file and returns it to the browser. Easy as py ;)

Answer 2

GeoDjango

It's pretty easy easy to setup and gets some basic functionality. It's Python, so at least you're familiar with the language. It may be overkill for your needs. But, if you ever want to grow the site, you've a got a mature framework on which to build.

Answer 3

If you don't want/need to create a map. All you need is a web server and programming language (I prefer Apache because its easy to use and set up, but there are other options) and then Postgres with PostGIS or some other database.

If your excel files are formatted nicely, importing the data into the database should be simple. You can write a script that can generate the INSERT statements. The queries you describe sound like they are also manageable.

If / when you want to do the mapping portion, Apache, GeoServer and OpenLayers would be a simple and free solution. GeoServer (as you know) can easily use PostGIS for WMS or other output format. All you have to do is add store that connects to PostGIS, and then add layers from the tables in PostGIS.

Other free mapping tools you can look at would be Mapserver and MapGuide. MapGuide Open Source does some pretty cool stuff but its more complicated and its documentation is a little behind.

MySQL also has a Spatial extension, but it doesn't have as many of the spatial features and functions that PostGIS does.

If you're familiar with python I would look at GeoDjango... I don't know much about it personally but I've heard good things

Answer 4

You can find an overview of web mapping applications design and development issues, further to fully documented source code for an environmental web mapping application based on Google Maps and spatio-temporal database PostgreSQL/PostGIS, in my relatively recent MSc in Computer Science dissertation: "Design and development of a prototype addressing spatio-temporal environmental vector data management, analysis and delivery using Open Source technology. General framework and case study focused on groundwater management in a coastal area"

The dissertation can be downloaded from http://www.giscience.it/it/pdf/DissertationMscComputerScience_CrestazEzio_Supervisors.pdf