Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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!

share|improve this question
2  
If you leave out the map, you can get all of this done with just Apache & PHP. You'll just have to create one input form for the PHP script that fetches the data from PostGIS. (You can substitute PHP with Python.) ... But then this would not be a question for GIS.SE –  underdark Aug 19 '11 at 12:05
    
Thanks very much to everyone that responded! These are all helpful answers and you've given me lots of stuff to follow up. I'd like to "accept" all of them, but I've given the points to @canisrufus as I found his last paragraph very useful and I've not come across web.py before. GeoDango looks awesome too. Cheers! –  JamesS Aug 23 '11 at 9:13
    
@jamesS I was going to expand my last paragraph, but it started seeming unjustifiably off topic. If you have questions, or would like a longer monologue on how to get set up, you can hit me up on chat here. –  canisrufus Aug 23 '11 at 14:15
add comment

4 Answers 4

up vote 12 down vote accepted

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 ;)

share|improve this answer
2  
You don't NEED MapServer/GeoServer to create images. You could just get the data directly from PostGIS and display it as vector data on your map. However, depending on the complexity of your data you could be transferring a considerable amount of data. It depends on the situation. Also, displaying them as vector data gives you the option to click on an object to show/edit it's data. –  Mr_Chimp Aug 23 '11 at 14:41
    
@mr_chimp Oh, that's a good point. My concern had been the background layer, but one could just use Google Maps or OSM or etc. In fact, I tend to think the vector layer is generally a nicer solution (for the user), but as you point out there are limitations. And of course, the simplest way to transfer the vectors would be to have something like MapServer or GeoServer serve them up ;) –  canisrufus Aug 23 '11 at 15:24
    
As always it's a case of using the right tool for the job. As JamesS already has a PostGIS database he may as well just grab the data straight from there. Unless I'm forgetting something, setting up MapServer/whatever would just be an extra step (though normally of course it would be the quickest way). –  Mr_Chimp Aug 23 '11 at 16:03
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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

Hope it helps Ezio Crestaz

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.