11

I am working on a Weather based GIS App.

I have the data from several weather stations, and this data will updated daily (by a web service).

The obstacles I am facing are:

  • There are currently 40 recording stations, but that might change
  • Different Stations record different number of parameter, some record 5, some record 7. ect
  • Some of the parameters are recorded daily (ex: Max Temperature), some are recorded hourly (current temperature) while others are recorded Weekly.
  • Some facilities at a particular recording station might be decommissioned (for eg: A station that is currently reporting 7 parameters, might report only 5 next year)
  • Sometimes a parameter might not be reported due to technical issues; Hence I should be able to differentiate between, value=0, null value & value not recorded.

I am working with ArcGIS (desktop & Server) for the spatial part of this web app & using ArcSDE is not a problem , but I think that this kind of database is best left as a pure Database & not bring ArcSDE into it.

Can anybody suggest any books or links that would help me?

closed as too broad by PolyGeo Mar 31 at 3:30

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

7

The simplest approach seems to be three tables:

  • station (id, name, position, ...)
  • parameter (id, name, unit, ...)
  • reading (station_id, parameter_id, timestamp, value, ...)
  • There are currently 40 recording stations, but that might change

You can add any number of stations. It might be interesting to add information about operating time of a station to the table.

  • Different Stations record different number of parameter, some record 5, some record 7. ect.
  • Some facilities at a particular recording station might be decommissioned

Not a problem, as relationship between recorded parameters and stations is implicitly stored in reading table.

  • Some of the parameters are recorded daily (ex: Max Temperature), some are recorded hourly (current temperature) while others are recorded Weekly.

Every reading will be represented by one entry in reading table. Different intervals are no problem.

  • Sometimes a parameter might not be reported due to technical issues

In that case, there would simply be no entry in reading table.

Additionally, I'd suggest looking into OGC Sensor Observation Standard. There are many examples covering weather station recordings. Implementations like 52°North come with a good generic database schema (for PostGIS in this case). While this standard (an the other SWE standards) take some effort to learn, I'm convinced that the investment will pay back.

7

I've been doing my own research on temporal databases this week. I found this answer on StackOverflow very helpful. For a fundamental understanding of the principles, it's worthwhile to read the introductory chapters of Developing Time-Oriented Database Applications in SQL by Snodgrass. I'm finding that true temporal databases are rather complex, but a simpler solution - such as underdark suggests - might suffice.

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