[Geoserver-users] Time series in postGIS?

GeoServer GeoServer User
1

All,

I have a question on setting up a postGIS database for use with geoserver;
please pardon the double-posting but I wasn't sure where I should send it.

I have created quite a few postGIS/geoserver services already, but I haven't
yet had a "dynamic" one like this. I have a set of point locations for
which data will be collected daily. In a "normal" (non-spatial) database I
would just have a table with columns for siteID,date,value where siteID and
date make up the primary key. But to use with geoserver, it seems like I
need to have a row for each location, where the columns are the date, like
this:
siteID,date_1,date_2,date_3,.......

What's the best way to do this? I plan to use a GetFeature WFS request with
geoserver, and would like get all the dates/values back for each location so
my client can "animate" through time.

Any thoughts or pointers in the right direction are greatly appreciated.

Thanks,
Steve

Stephen Crawford
Center for Environmental Informatics
GeoVISTA Center
The Pennsylvania State University
814.865.9905
src176@anonymised.com

2

On 5/8/07, Stephen Crawford <src176@anonymised.com> wrote:

All,

I have a question on setting up a postGIS database for use with geoserver;
please pardon the double-posting but I wasn’t sure where I should send it.

I have created quite a few postGIS/geoserver services already, but I haven’t
yet had a “dynamic” one like this. I have a set of point locations for
which data will be collected daily. In a “normal” (non-spatial) database I
would just have a table with columns for siteID,date,value where siteID and
date make up the primary key.

You can still have that and add the geometry column. The only thing is that the location would be repeated for however many dates is associated with…

Alex

3

Stephen Crawford ha scritto:

All,

I have a question on setting up a postGIS database for use with geoserver;
please pardon the double-posting but I wasn't sure where I should send it.

I have created quite a few postGIS/geoserver services already, but I haven't
yet had a "dynamic" one like this. I have a set of point locations for
which data will be collected daily. In a "normal" (non-spatial) database I
would just have a table with columns for siteID,date,value where siteID and
date make up the primary key. But to use with geoserver, it seems like I
need to have a row for each location, where the columns are the date, like
this:
siteID,date_1,date_2,date_3,.......

What's the best way to do this? I plan to use a GetFeature WFS request with
geoserver, and would like get all the dates/values back for each location so
my client can "animate" through time.

Hum, I would model the database in normal form (as you described in the
first sample) and then build views joining the site and data table, and
register it against the geometry colums so that Geoserver can view
that as a table.
Then use a filter to show the situation at a given time. Hope this helps
Cheers
Andrea

4

Andrea,

Is there a good way to define the view so that the dates, which are rows in
the normal table, become columns in the view, and also that the view is
always updated with the most recent date? What I hope to have is a feature
layer on Geoserver where a GetFeature request will always return the entire
"year to date" without having to use a filter.

Thanks Again,
Steve

-----Original Message-----
From: geoserver-users-bounces@lists.sourceforge.net
[mailto:geoserver-users-bounces@lists.sourceforge.net] On Behalf Of Andrea
Aime
Sent: Tuesday, May 08, 2007 5:00 PM
To: Stephen Crawford
Cc: geoserver-users@lists.sourceforge.net; 'PostGIS Users Discussion'
Subject: Re: [Geoserver-users] Time series in postGIS?

Stephen Crawford ha scritto:

All,

I have a question on setting up a postGIS database for use with
geoserver; please pardon the double-posting but I wasn't sure where I

should send it.

I have created quite a few postGIS/geoserver services already, but I
haven't yet had a "dynamic" one like this. I have a set of point
locations for which data will be collected daily. In a "normal"
(non-spatial) database I would just have a table with columns for
siteID,date,value where siteID and date make up the primary key. But
to use with geoserver, it seems like I need to have a row for each
location, where the columns are the date, like
this:
siteID,date_1,date_2,date_3,.......

What's the best way to do this? I plan to use a GetFeature WFS
request with geoserver, and would like get all the dates/values back
for each location so my client can "animate" through time.

Hum, I would model the database in normal form (as you described in the
first sample) and then build views joining the site and data table, and
register it against the geometry colums so that Geoserver can view that as a
table.
Then use a filter to show the situation at a given time. Hope this helps
Cheers Andrea

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express Download DB2 Express C - the
FREE version of DB2 express and take control of your XML. No limits. Just
data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

5

Stephen Crawford ha scritto:

Andrea,

Is there a good way to define the view so that the dates, which are rows in
the normal table, become columns in the view, and also that the view is
always updated with the most recent date?

Not sure I understand. Can you paste the current table structure (more than one table I guess) and the structure you want as a result?
Cheers
Andrea

6

The crux of my problem is that I would like a WFS request to return, as one
record per site, the values for each of the days in the table (to date).
So, if I have a table like this:

siteID,date,value
1,May 7 2007,345
1,May 6 2007, 4756
2,May 7 2007,687
2,May 6 2007,765

...where siteID and date make up the primary key. I would then join this to
the site table containing the geometry to create a view that I think should
look something like this:

siteID,the_geom,value_for_date1,value_for_date2
1,aPoint,345,4756
2,aPoint,687,765

...where there is only one row per site. This view would be the layer in
geoserver. Someone mentioned that I should just join the_geom to the first
table, but that seems very redundant, in that the_geom would be returned for
every point for every date.

I don't know too much about database structure, but it seem that this would
be a very common problem, that for a every site on a map I want to see the
values for each day over time X.

I can probably use a servlet to hit postgres directly, but I really want to
have this up as a web service.

Thanks,
Steve

-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]
Sent: Wednesday, May 09, 2007 12:25 PM
To: Stephen Crawford
Cc: geoserver-users@lists.sourceforge.net; 'PostGIS Users Discussion'
Subject: Re: [Geoserver-users] Time series in postGIS?

Stephen Crawford ha scritto:

Andrea,

Is there a good way to define the view so that the dates, which are
rows in the normal table, become columns in the view, and also that
the view is always updated with the most recent date?

Not sure I understand. Can you paste the current table structure (more than
one table I guess) and the structure you want as a result?
Cheers
Andrea

7

Stephen Crawford ha scritto:

The crux of my problem is that I would like a WFS request to return, as one
record per site, the values for each of the days in the table (to date).
So, if I have a table like this:

siteID,date,value
1,May 7 2007,345
1,May 6 2007, 4756
2,May 7 2007,687
2,May 6 2007,765

...where siteID and date make up the primary key. I would then join this to
the site table containing the geometry to create a view that I think should
look something like this:

siteID,the_geom,value_for_date1,value_for_date2
1,aPoint,345,4756
2,aPoint,687,765

...where there is only one row per site. This view would be the layer in
geoserver. Someone mentioned that I should just join the_geom to the first
table, but that seems very redundant, in that the_geom would be returned for
every point for every date.

I don't know too much about database structure, but it seem that this would
be a very common problem, that for a every site on a map I want to see the
values for each day over time X.

A good solution would require dealing with complex features, so that you could return a list of values as an attribute of your feature.
What you want could be achieved using some kind of a view, but it would
be very expensive since you would need to join the time series against
itself n times, in order to gather the n values. For example, if you
want the last 3 values as column, the query would be:

select dt1.siteId, dt1.value, dt2.value, dt3.value
from DataTable dt1, DataTable dt2, DataTable dt3
where dt1.time = today()
and dt2.siteId = dt1.siteId and dt2.date = dt1.date - 1
and dt3.siteId = dt1.siteId and dt3.date = dt1.date - 2

where I supposed, to simplify things, that you have a "today"
function to get the current day (to avoid doing another select max(date).... gathering the last value) and that you can subtract
1 to get the day before, whilst in practice you'll probably need
to use some date functions.

The above query will probably be terribly slow since it
would be a n-way join over a very big table... but I don't
know other easier ways. Maybe using a stored procedure you
can cook up something faster.

Cheers
Andrea