[Geoserver-users] serving up a Postgresql view that aggregates through geoserver but accessing underlying data with a query

GeoServer GeoServer User
1

Hi,

I hope I am able to explain my issue well enough for someone to suggest a solution.

I have data in Postgresql tables where students vote on selected issues by county. In one of the tables, I also have the date in which the votes are cast. I then have a Postgresql view that defines a count for the number of votes for each issue per county. I serve this up through Geoserver. An example vote is: For all counties in a state will each county vote a majority for republican or democrat in the upcoming election.

Everything is currently working fine with serving the counts of votes up through Geoserver. My issue is that now I would like to pass in a filter that filters the data by time the vote was cast and then returns the counts based upon the time period filtered by. I don’t think I can make my dates field visible in my view since it is not aggregated and my counts are. But then I don’t think I can pass in a filter for dates if Geoserver does not see the dates field in the featuretype.

Based upon my description, can anyone think of a way to solve this issue (either through a Postgresql view or maybe even rearranging my tables). Please let me know if you need more information or if you think this is more of a question for a Postgresql list. I would like to try to do this query on the database side instead of just aggregating things on the client. If you think this is an issue with how my database is set up, I can try to give more info about the schema.

Thanks for any help on this issue,
Scott

Scott Pezanowski

email: scottpez@anonymised.com

2

Hi Scott,

Not sure how helpful this will be but i will give it a shot.

I assume your original table looks something like this:

votes(student,county,time,etc...)

What about exposing this original table via a wfs feature type? You could then do time filtered queries against it, and just count the number of features returned.

Actually in wfs 1.1 there is a convenient way of doing this by specifying "resultType=hits" as a parameter to the request. When specified it will not return actual features, just a count of features.

-Justin

Scott Pezanowski wrote:

Hi,

I hope I am able to explain my issue well enough for someone to suggest a solution.

I have data in Postgresql tables where students vote on selected issues by county. In one of the tables, I also have the date in which the votes are cast. I then have a Postgresql view that defines a count for the number of votes for each issue per county. I serve this up through Geoserver. An example vote is: For all counties in a state will each county vote a majority for republican or democrat in the upcoming election.

Everything is currently working fine with serving the counts of votes up through Geoserver. My issue is that now I would like to pass in a filter that filters the data by time the vote was cast and then returns the counts based upon the time period filtered by. I don't think I can make my dates field visible in my view since it is not aggregated and my counts are. But then I don't think I can pass in a filter for dates if Geoserver does not see the dates field in the featuretype.

Based upon my description, can anyone think of a way to solve this issue (either through a Postgresql view or maybe even rearranging my tables). Please let me know if you need more information or if you think this is more of a question for a Postgresql list. I would like to try to do this query on the database side instead of just aggregating things on the client. If you think this is an issue with how my database is set up, I can try to give more info about the schema.

Thanks for any help on this issue,
Scott

*********************************************
Scott Pezanowski
email: scottpez@anonymised.com *********************************************

------------------------------------------------------------------------

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

------------------------------------------------------------------------

_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

--
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

3

Justin,

Thank you for the suggestions. I will have to think about this a little more whether we would want to go this route. Our current application is drawing a WMS choropleth map with symbology based upon the counts we are producing with the view. So, we would have to rework the app a bit to draw the map as a wfs and symbolize the polygons based upon the hits returned (or count up the features returned).

Thank again for the tips,
Scott

Scott Pezanowski

email: scottpez@anonymised.com

Date: Thu, 23 Oct 2008 18:45:54 -0700
From: jdeolive@anonymised.com
To: scottpez@anonymised.com
CC: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] serving up a Postgresql view that aggregates through geoserver but accessing underlying data with a query

Hi Scott,

Not sure how helpful this will be but i will give it a shot.

I assume your original table looks something like this:

votes(student,county,time,etc…)

What about exposing this original table via a wfs feature type? You
could then do time filtered queries against it, and just count the
number of features returned.

Actually in wfs 1.1 there is a convenient way of doing this by
specifying “resultType=hits” as a parameter to the request. When
specified it will not return actual features, just a count of features.

-Justin

Scott Pezanowski wrote:

Hi,

I hope I am able to explain my issue well enough for someone to suggest
a solution.

I have data in Postgresql tables where students vote on selected issues
by county. In one of the tables, I also have the date in which the votes
are cast. I then have a Postgresql view that defines a count for the
number of votes for each issue per county. I serve this up through
Geoserver. An example vote is: For all counties in a state will each
county vote a majority for republican or democrat in the upcoming election.

Everything is currently working fine with serving the counts of votes up
through Geoserver. My issue is that now I would like to pass in a filter
that filters the data by time the vote was cast and then returns the
counts based upon the time period filtered by. I don’t think I can make
my dates field visible in my view since it is not aggregated and my
counts are. But then I don’t think I can pass in a filter for dates if
Geoserver does not see the dates field in the featuretype.

Based upon my description, can anyone think of a way to solve this issue
(either through a Postgresql view or maybe even rearranging my tables).
Please let me know if you need more information or if you think this is
more of a question for a Postgresql list. I would like to try to do this
query on the database side instead of just aggregating things on the
client. If you think this is an issue with how my database is set up, I
can try to give more info about the schema.

Thanks for any help on this issue,
Scott

Scott Pezanowski
email: scottpez@anonymised.com

This SF.Net email is sponsored by the Moblin Your Move Developer’s challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

–
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.