Designing Spatial Database for temporal Data? [closed]

Question

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?

Answer 1

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.

Answer 2

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.