[mapserver-users] symbolize a wms map based upon a sql count() query

Thu Oct 30 10:54:15 EDT 2008

Hi Pietro,

Thank you for your response. Sure, I will try below to add some sample data that illustrates what I am trying to do. I have a "votes" table which has a vote for each county (A versus B). This can be joined by a responseid to a table "response", which has the date in which the votes were submitted. Also, countyid references my geometry in a separate table. In summary, the first thing I would like to do is count the number of votes for 'A', and count the number of votes for 'B', both per county and based upon a user entered datesubmitted range; And then symbolize my map based upon these results. Also, and I guess ideally, I'd like to also produce a percentage of votes for 'A', per county, based upon the total votes for A and B for each county. And then color code my counties based upon this percentage.

Thank you for help you may provide.

Scott

Below is some sample data:

votes table:

countyid;vote;responseid
1;"B";"296532B5A40C493592C9E6B480E15E37"
2;"A";"296532B5A40C493592C9E6B480E15E37"
3;"B";"296532B5A40C493592C9E6B480E15E37"
4;"B";"296532B5A40C493592C9E6B480E15E37"
5;"B";"296532B5A40C493592C9E6B480E15E37"
6;"B";"296532B5A40C493592C9E6B480E15E37"
7;"B";"296532B5A40C493592C9E6B480E15E37"
8;"B";"296532B5A40C493592C9E6B480E15E37"
9;"A";"296532B5A40C493592C9E6B480E15E37"
10;"B";"296532B5A40C493592C9E6B480E15E37"
11;"B";"296532B5A40C493592C9E6B480E15E37"
12;"B";"296532B5A40C493592C9E6B480E15E37"
13;"B";"296532B5A40C493592C9E6B480E15E37"
14;"A";"296532B5A40C493592C9E6B480E15E37"
15;"B";"296532B5A40C493592C9E6B480E15E37"
16;"B";"296532B5A40C493592C9E6B480E15E37"
17;"B";"296532B5A40C493592C9E6B480E15E37"
18;"B";"296532B5A40C493592C9E6B480E15E37"
19;"B";"296532B5A40C493592C9E6B480E15E37"
20;"B";"296532B5A40C493592C9E6B480E15E37"
21;"B";"296532B5A40C493592C9E6B480E15E37"
22;"B";"296532B5A40C493592C9E6B480E15E37"
23;"A";"296532B5A40C493592C9E6B480E15E37"
24;"A";"296532B5A40C493592C9E6B480E15E37"
25;"A";"296532B5A40C493592C9E6B480E15E37"
26;"A";"296532B5A40C493592C9E6B480E15E37"
27;"B";"296532B5A40C493592C9E6B480E15E37"
28;"B";"296532B5A40C493592C9E6B480E15E37"
29;"B";"296532B5A40C493592C9E6B480E15E37"
30;"A";"296532B5A40C493592C9E6B480E15E37"
31;"B";"296532B5A40C493592C9E6B480E15E37"
32;"B";"296532B5A40C493592C9E6B480E15E37"
33;"B";"296532B5A40C493592C9E6B480E15E37"
34;"B";"296532B5A40C493592C9E6B480E15E37"
35;"A";"296532B5A40C493592C9E6B480E15E37"
36;"B";"296532B5A40C493592C9E6B480E15E37"
37;"A";"296532B5A40C493592C9E6B480E15E37"
38;"B";"296532B5A40C493592C9E6B480E15E37"
39;"A";"296532B5A40C493592C9E6B480E15E37"
40;"A";"296532B5A40C493592C9E6B480E15E37"
41;"B";"296532B5A40C493592C9E6B480E15E37"
42;"B";"296532B5A40C493592C9E6B480E15E37"
43;"A";"296532B5A40C493592C9E6B480E15E37"
44;"B";"296532B5A40C493592C9E6B480E15E37"
45;"B";"296532B5A40C493592C9E6B480E15E37"
46;"A";"296532B5A40C493592C9E6B480E15E37"
47;"B";"296532B5A40C493592C9E6B480E15E37"
48;"B";"296532B5A40C493592C9E6B480E15E37"
49;"B";"296532B5A40C493592C9E6B480E15E37"
50;"B";"296532B5A40C493592C9E6B480E15E37"
51;"A";"296532B5A40C493592C9E6B480E15E37"
52;"B";"296532B5A40C493592C9E6B480E15E37"
53;"B";"296532B5A40C493592C9E6B480E15E37"
54;"B";"296532B5A40C493592C9E6B480E15E37"
55;"B";"296532B5A40C493592C9E6B480E15E37"
56;"B";"296532B5A40C493592C9E6B480E15E37"
57;"B";"296532B5A40C493592C9E6B480E15E37"
58;"B";"296532B5A40C493592C9E6B480E15E37"
59;"B";"296532B5A40C493592C9E6B480E15E37"
60;"B";"296532B5A40C493592C9E6B480E15E37"
61;"B";"296532B5A40C493592C9E6B480E15E37"
62;"B";"296532B5A40C493592C9E6B480E15E37"
63;"A";"296532B5A40C493592C9E6B480E15E37"
64;"B";"296532B5A40C493592C9E6B480E15E37"
65;"B";"296532B5A40C493592C9E6B480E15E37"
66;"B";"296532B5A40C493592C9E6B480E15E37"
67;"B";"296532B5A40C493592C9E6B480E15E37"
1;"B";"AE2E9A609C65420A9DDF37A9E56A25A8"
2;"B";"AE2E9A609C65420A9DDF37A9E56A25A8"
3;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
4;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
5;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
6;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
7;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
8;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
9;"B";"AE2E9A609C65420A9DDF37A9E56A25A8"
10;"B";"AE2E9A609C65420A9DDF37A9E56A25A8"
11;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
12;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
13;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
14;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
15;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
16;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
17;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
18;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
19;"B";"AE2E9A609C65420A9DDF37A9E56A25A8"
20;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
21;"B";"AE2E9A609C65420A9DDF37A9E56A25A8"
22;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
23;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
24;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
25;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
26;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
27;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
28;"B";"AE2E9A609C65420A9DDF37A9E56A25A8"
29;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
30;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
31;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
32;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"
33;"A";"AE2E9A609C65420A9DDF37A9E56A25A8"

response table:

responseid;datesubmitted
"296532B5A40C493592C9E6B480E15E37";"2008-10-13 18:27:28-04"
"AE2E9A609C65420A9DDF37A9E56A25A8";"2008-10-14 14:01:59-04"
"24165287C9D94F9CA9F6C6BBDEB4622B";"2008-10-13 14:52:40-04"

********************************************* Scott Pezanowski 

email: scottpez at hotmail.com  
*********************************************

> Date: Thu, 30 Oct 2008 11:22:48 +0100
> Subject: Re: [mapserver-users] symbolize a wms map based upon a sql count()      query
> From: pgiannini at bytewise.it
> To: mapserver-users at lists.osgeo.org
> CC: scottpez at hotmail.com
> 
> Maybe a view on postresql, with both count and geometry?
> can you pls post a little sample data?
> ciao
> ............................pg
> 
> -- 
> Pietro Giannini
> Bytewise srl - Area GIS
> 41°50'38.58"N 12°29'13.39"E
> 
> 
> 
> On Gio, Ottobre 30, 2008 03:52, Scott Pezanowski wrote:
> > Hi,
> >
> > I am hoping to be able to symbolize a WMS or image map, based upon the
> > results of a SQL COUNT() query. To be more specific, this query needs to
> > be dynamic based upon user input from my application for specific
> > attributes in the data, count up the number of records that meet that
> > query, and then produce a choropleth map of the resulting counts. So the
> > query would produce, a count of 10 records for one county, 5 records for
> > another county, etc. and my counties will be colored based upon a scheme
> > for these counts. My data is in a PostGIS database.
> >
> > Is there a way to do this with Mapserver? I am currently simply returning
> > all of the records that meet my criteria and counting things up on the
> > client application. But I am hoping there is a way to do this on the
> > server or database-side of things.
> >
> > Thank you very much in advance for any help or tips you may be able to
> > offer. Also, please let me know if you need more info on what I am trying
> > to do.
> >
> > Thanks,
> > Scott
> >
> >
> >
> >
> > ********************************************* Scott Pezanowski
> >
> >
> > email: scottpez at hotmail.com
> > *********************************************
> > _______________________________________________
> > mapserver-users mailing list
> > mapserver-users at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/mapserver-users
> >
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20081030/27440413/attachment-0001.html