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, please, 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.
Thanks very much!
Best Answer
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 ;)