Hi all,
I have a couple of related questions to ask. They both involve spatial
datasets that also have a time component, which I'd like to keep in
PostGIS and serve up via GeoServer.
Use case 1: I'm mapping a series of model results (Runoff Risk
Advisory Forecast). For each of the days of the forecast (today,
tomorrow, day after that) I have a layer defined, each with its own
table; the one you see on the page is chosen by the radio buttons to
the upper right. Every day a big script chugs through our forecast
intake and updates the "three_day_risk" and "snowmelt" columns for
each polygon in each of the three tables. Just for reference, the
tables each look like this:
Column | Type | Modifiers
------------------------+-----------------------+-----------
gid | integer | not null
the_geom | geometry | not null
name | character varying(65) | not null
descriptio | character varying(81) |
three_day_risk | integer |
ten_day_snowmelt_alert | integer |
What I would like to do is be able to easily map any day's model
results. When I'm setting up a layer in GeoServer, I have to point it
at a table, not at a query, so the dataset is pretty much fixed at
that point.
I've tried creating a view, part of which incorporates a date held in
a separate table which the user can control. Here's the schema I used.
The runoff_forecasts table contains a date, the risk, and the
snowmelt, and wisconsin_watersheds contains all the geometry, and
runoff_forecast_valid_time just has a datestamp in it:
Column | Type | Modifiers
------------+--------------------------+-----------
gid | integer |
the_geom | geometry |
name | character varying(65) |
validstart | timestamp with time zone |
risk | integer |
snowmelt | integer |
View definition:
SELECT w.gid, w.the_geom, w.name, r.validstart, r.day1 AS risk,
r.day1_snowmelt AS snowmelt
FROM wisconsin_watersheds w, runoff_forecasts r,
runoff_retrospect_valid_time rfvt
WHERE r.name::text = w.name::text AND r.validstart =
rfvt.current_validstart;
But that's (1) unbearably slow, and (2) has no concurrency -- User A
might set the date in the in the valid-time table, then try to view
the map just as User B sets her own value into it. BZZT!
Am I missing something really obvious? Or is this just hard?
Use case 2: I have a bunch of other datasets that are 3D, i.e. they
are lat, lon, time, with nice square pixels. Seems a natural for a
raster, except that I want to store the values in a database, not in
an ever-expanding cloud of GeoTIFF files. (I have to do other things
with the data besides mapping them.) We're using PostGIS right now,
and I hope I don't offend anyone by saying that raster support for
PostGIS seems like fusion power, it's always just around the corner.
Can I make it work today? And does [insert workable PostGIS raster
implementation here] allow me to easily query the data as data,
without a ton of rigamarole? For example, I'd like to say "give me the
time series of values for the pixel at [lat,lon] between April 1 and
June 30 2011".
Thanks (as always!) for your knowledgeable assistance,
Rick Wayne
Sr. Systems Programmer
Univ. of WI Soil Science
------------------------------------------------------------------------------
Got Input? Slashdot Needs You.
Take our quick survey online. Come on, we don't ask for help often.
Plus, you'll get a chance to win $100 to spend on ThinkGeek.
http://p.sf.net/sfu/slashdot-survey
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
