[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