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

Thu Oct 30 09:02:15 PDT 2008

Hi scott,

> 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;

a query like this:

SELECT countyid,vote,count(*) as count
FROM votes
WHERE vote='A'
GROUP BY countyid, vote
ORDER BY count desc, countyid asc

allow you to count the votes for 'A'. You can relate the query with the
response table adding a where clause on "responseid" and then filter by
the 'datesubmitted' field:

SELECT  countyid,vote,count(*) as votes_for_A
FROM votes, response
WHERE vote='A'
AND response.responseid = votes.responseid
AND response.datesubmitted BETWEEN <lower datetime> AND <upper datetime>
GROUP BY countyid, vote
ORDER BY count desc, countyid asc

now you can simbolize the map according to the "votes_for_A" alias field.

> produce a percentage of votes for 'A', per county, based upon the total
> votes for A and B for each county.

How to obtain this with a single query, is a little over my knowledge.
I'll try to find...

ciao
............................pg

-- 
Pietro Giannini
Bytewise srl - Area GIS
41°50'38.58"N 12°29'13.39"E

On Gio, Ottobre 30, 2008 15:54, Scott Pezanowski wrote:
> 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
>> >
>>
>