[postgis-users] Applying weights to data in PostGIS

Dheeraj Chand dheeraj at dheerajchand.com
Tue Jan 31 08:47:18 PST 2012


Hey everyone,

I have a neat problem! I have been asked to make some maps of weighted polling data! So I have attached \d of the table at the end of this email. Don't worry about joining to the geometry_columns. I can handle that pretty easily. 
The problem is specifically that I have a column called finalwt ("Final weight") that tells me what the weight is for any particular record, where one row is the record for one respondent, and while I can get weighted xtabs by doing this in PSQL:
SELECT q1, SUM(1*finalwt) 
FROM table 
GROUP BY q1;
to get the weighted counts for each question. That's not the problem. The problem is that QGIS creates maps for export by saying 
SELECT q1, count(q1) AS numbers
FROM table
GROUP BY q1;
and then looking at the results of numbers to decide which one gets assigned the colour code for the space.
So I have been looking online to see if there's a way to get QGIS to handle weights, and I can't find one.

So is there some way I can prep the table in PSQL to be well suited to mapping in QGIS?

Thanks,

-dx



Table Description:

nm01=# \d weighted_final 
                 Table "public.weighted_final"
          Column          |          Type          | Modifiers 
--------------------------+------------------------+-----------
 phone_to                 | character varying(255) | not null
 phone_from               | character varying(255) | not null
 twilio_sid               | character varying(255) | not null
 call_start_date_utc      | character varying(255) | not null
 call_start_time_utc      | character varying(255) | not null
 call_end_date_utc        | character varying(255) | not null
 call_end_time_utc        | character varying(255) | not null
 q1                       | character varying(255) | not null
 q2                       | character varying(255) | not null
 q3                       | character varying(255) | not null
 q4                       | character varying(255) | not null
 q5                       | character varying(255) | not null
 q6                       | character varying(255) | not null
 q7                       | character varying(255) | not null
 q8                       | character varying(255) | not null
 q9                       | character varying(255) | not null
 q10                      | character varying(255) | not null
 q11                      | character varying(255) | not null
 q12                      | character varying(255) | not null
 q13                      | character varying(255) | not null
 q14                      | character varying(255) | not null
 q15                      | character varying(255) | not null
 q16                      | character varying(255) | not null
 q17                      | character varying(255) | not null
 q18                      | character varying(255) | not null
 q19                      | character varying(255) | not null
 q20                      | character varying(255) | not null
 q21                      | character varying(255) | not null
 q22                      | character varying(255) | not null
 q23                      | character varying(255) | not null
 q24                      | character varying(255) | not null
 q25                      | character varying(255) | not null
 q26                      | character varying(255) | not null
 q27                      | character varying(255) | not null
 q28                      | character varying(255) | not null
 q29                      | character varying(255) | not null
 q30                      | character varying(255) | not null
 q31                      | character varying(255) | not null
 q32                      | character varying(255) | not null
 q33                      | character varying(255) | not null
 q34                      | character varying(255) | not null
 q35                      | character varying(255) | not null
 q36                      | character varying(255) | not null
 qreligion                | character varying(255) | not null
 qunion                   | integer                | not null
 qbirthyear               | integer                | not null
 qrace                    | character varying(255) | not null
 qparty                   | character varying(255) | not null
 qgender                  | character varying(255) | not null
 listid                   | character varying(255) | not null
 clusternumber            | integer                | not null
 dwid                     | integer                | not null
 firstname                | character varying(255) | not null
 middlename               | character varying(255) | not null
 lastname                 | character varying(255) | not null
 namesuffix               | character varying(255) | not null
 fullname                 | character varying(255) | not null
 age                      | integer                | not null
 electiondayage           | integer                | not null
 gender                   | character varying(255) | not null
 gendersource             | character varying(255) | not null
 race                     | character varying(255) | not null
 raceconfidence           | character varying(255) | not null
 ethnicity                | character varying(255) | not null
 religion                 | character varying(255) | not null
 racesource               | character varying(255) | not null
 partyaffiliation         | character varying(255) | not null
 longpartyname            | character varying(255) | not null
 registrationdate         | character varying(255) | not null
 earliestregistrationdate | character varying(255) | not null
 voterstatus              | character varying(255) | not null
 phone                    | character varying(255) | not null
 phonematchscore          | character varying(255) | not null
 regaddrline1             | character varying(255) | not null
 regaddrline2             | character varying(255) | not null
 regaddrcity              | character varying(255) | not null
 regaddrstate             | character varying(255) | not null
 regaddrzip               | character varying(255) | not null
 mailaddrline1            | character varying(255) | not null
 mailaddrline2            | character varying(255) | not null
 mailaddrcity             | character varying(255) | not null
 mailaddrstate            | character varying(255) | not null
 mailaddrzip              | character varying(255) | not null
 mailaddrisdifferent      | character varying(255) | not null
 permanentabsenteevoter   | character varying(255) | not null
 state                    | character varying(255) | not null
 countyfips               | integer                | not null
 fcounty                  | character varying(255) | not null
 soscountycode            | character varying(255) | not null
 ftownship                | character varying(255) | not null
 precinctcode             | integer                | not null
 precinctname             | character varying(255) | not null
 uniqueprecinctcode       | character varying(255) | not null
 ward                     | character varying(255) | not null
 congressionaldistrict    | integer                | not null
 fstatehouse              | integer                | not null
 statesenate              | integer                | not null
 municipaldistrict        | character varying(255) | not null
 countycommission         | character varying(255) | not null
 schoolboard              | character varying(255) | not null
 judicialdistrict         | character varying(255) | not null
 precinctsplit            | integer                | not null
 citycouncil              | character varying(255) | not null
 countylegislative        | character varying(255) | not null
 schooldistrict           | character varying(255) | not null
 supervisordistrict       | character varying(255) | not null
 district1                | real                   | not null
 district2                | character varying(255) | not null
 district3                | character varying(255) | not null
 district4                | character varying(255) | not null
 district5                | character varying(255) | not null
 district6                | character varying(255) | not null
 district7                | character varying(255) | not null
 district8                | character varying(255) | not null
 district9                | character varying(255) | not null
 district10               | character varying(255) | not null
 district11               | character varying(255) | not null
 district12               | character varying(255) | not null
 district13               | character varying(255) | not null
 district14               | character varying(255) | not null
 district15               | character varying(255) | not null
 statecountyfips          | integer                | not null
 name                     | character varying(255) | not null
 e2000g                   | character varying(255) | not null
 e2000p                   | character varying(255) | not null
 e2000s                   | character varying(255) | not null
 e2001g                   | character varying(255) | not null
 e2001p                   | character varying(255) | not null
 e2001s                   | character varying(255) | not null
 e2002g                   | character varying(255) | not null
 e2002p                   | character varying(255) | not null
 e2002s                   | character varying(255) | not null
 e2003g                   | character varying(255) | not null
 e2003p                   | character varying(255) | not null
 e2003s                   | character varying(255) | not null
 e2004g                   | character varying(255) | not null
 e2004p                   | character varying(255) | not null
 e2004s                   | character varying(255) | not null
 e2005g                   | character varying(255) | not null
 e2005p                   | character varying(255) | not null
 e2005s                   | character varying(255) | not null
 e2006g                   | character varying(255) | not null
 e2006p                   | character varying(255) | not null
 e2006s                   | character varying(255) | not null
 e2007g                   | character varying(255) | not null
 e2007p                   | character varying(255) | not null
 e2007s                   | character varying(255) | not null
 e2008g                   | character varying(255) | not null
 e2008p                   | character varying(255) | not null
 e2008s                   | character varying(255) | not null
 e2009g                   | character varying(255) | not null
 e2009p                   | character varying(255) | not null
 e2009s                   | character varying(255) | not null
 e2010g                   | character varying(255) | not null
 e2010p                   | character varying(255) | not null
 e2010s                   | character varying(255) | not null
 married                  | character varying(255) | not null
 age_model2010            | character varying(255) | not null
 partisanscore2010        | real                   | not null
 voteprop2010             | real                   | not null
 voteprop2012             | real                   | not null
 cell_phone               | character varying(255) | not null
 newAge                   | character varying(255) | not null
 voteYear                 | integer                | not null
 racewt                   | real                   | not null
 sexwt                    | real                   | not null
 agewt                    | real                   | not null
 collrace                 | character varying(255) | not null
 compVoteAge              | integer                | not null
 collage                  | integer                | not null
 finalwt                  | real                   | not null

nm01=# 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120131/28ab49d9/attachment.html>


More information about the postgis-users mailing list