Report not found

There was a problem finding this report.
Congressional Districts - State Gerrymandering Rankings
April 16, 2014 · Refreshed almost 2 years ago
Description
Full post: http://blog.modeanalytics.com/finding-the-most-gerrymandered-districts/

Collaborators

Run History
SELECT z.state_name, AVG(CASE WHEN ratio < 0 THEN 0 ELSE ratio END) AS state_index FROM ( SELECT x.state_name, x.district_name, x.da, x.dp, x.da/x.sa::FLOAT AS percent_of_state, (x.dp - (x.sp * (x.da/x.sa::FLOAT))) / ((SQRT(x.da/PI()) * 2) * PI())::FLOAT AS ratio, (x.dp - (x.sp / x.districts::FLOAT)) / ((SQRT(x.da/PI()) * 2) * PI())::FLOAT AS ratio_simple FROM ( SELECT d.state_name, d.district_name, d.perimeter AS dp, d.area AS da, s.perimeter AS sp, t.area AS sa, t.districts FROM benn.congressional_district_geographies d JOIN benn.congressional_district_geographies s ON s.state_name = d.state_name AND s.geography = 'state' JOIN (SELECT t.state_name, COUNT(*) AS districts, SUM(t.area) AS area FROM benn.congressional_district_geographies t WHERE t.geography = 'district' GROUP BY 1 ) t ON t.state_name = d.state_name WHERE d.geography = 'district' ) x ) z GROUP BY 1 ORDER BY 2 DESC
SELECT z.state_name, AVG(CASE WHEN ratio < 0 THEN 0 ELSE ratio END) AS state_index FROM ( SELECT x.state_name, x.district_name, x.da, x.dp, x.da/x.sa::FLOAT AS percent_of_state, (x.dp - (x.sp * (x.da/x.sa::FLOAT))) / ((SQRT(x.da/PI()) * 2) * PI())::FLOAT AS ratio, (x.dp - (x.sp / x.districts::FLOAT)) / ((SQRT(x.da/PI()) * 2) * PI())::FLOAT AS ratio_simple FROM ( SELECT d.state_name, d.district_name, d.perimeter AS dp, d.area AS da, s.perimeter AS sp, t.area AS sa, t.districts FROM benn.congressional_district_geographies d JOIN benn.congressional_district_geographies s ON s.state_name = d.state_name AND s.geography = 'state' JOIN (SELECT t.state_name, COUNT(*) AS districts, SUM(t.area) AS area FROM benn.congressional_district_geographies t WHERE t.geography = 'district' GROUP BY 1 ) t ON t.state_name = d.state_name WHERE d.geography = 'district' ) x ) z GROUP BY 1 ORDER BY 2 DESC
<style> body { font-family: Helvetica, Arial, sans-serif; font-size: 12px; text-align:center; } h2 {margin-bottom:4px;} p {margin:4px;} .r { fill: #dc7062; } .d { fill: #66a8d4; } .p { fill: #7E6D8D; } .n { fill: #7dac9f;} .axis path, .axis line { fill: none; stroke: #000; shape-rendering: crispEdges; } </style> <div style="width:600px;" id="chart"> <h2>State Gerrymandering Scores</h2> <p>Index ranges from 0 (least gerrymandered) to 10 (most gerrymandered)</p> </div> <script> var data = dataset.content; var maxValue = 0; data.forEach(function(d) { if (d.state_index > maxValue) { maxValue = d.state_index; } }) var margin = {top: 130, right: 20, bottom: 10, left: 150}, width = 600 - margin.left - margin.right, height = 1000 - margin.top - margin.bottom; var x = d3.scale.linear() .domain([0,10]) .range([0, width]) var xTransform = d3.scale.linear() .domain([0,maxValue]) .range([0, 10]) var y = d3.scale.ordinal() .rangeRoundBands([0, height], .2); var xAxis = d3.svg.axis() .scale(x) .orient("top"); var yAxis = d3.svg.axis() .scale(y) .orient("left"); var svg = d3.select("#chart").append("svg") .attr("width", width + margin.left + margin.right) .attr("height", height + margin.top + margin.bottom) .append("g") .attr("transform", "translate(" + margin.left + "," + margin.top + ")"); var data = dataset.content; y.domain(data.map(function(d) { return d.state_name; })); svg.selectAll(".bar") .data(data) .enter().append("rect") .attr("class", function(d) { return districtType(d.state_name); }) .attr("x", function(d) { return x(0); }) .attr("y", function(d) { return y(d.state_name); }) .attr("width", function(d) { return Math.abs(x(xTransform(d.state_index)) - x(xTransform(0))); }) .attr("height", y.rangeBand()); svg.append("g") .attr("class", "x axis") .call(xAxis); svg.append("g") .attr("class", "y axis") .call(yAxis); svg.append("g") .attr("class", "y axis") .append("line") .attr("x1", x(0)) .attr("x2", x(0)) .attr("y2", height); var legend = svg.selectAll(".legend") .data([ {d:'Republican controlled',c:'#dc7062'}, {d:'Democract controlled',c:'#66a8d4'}, {d:'Split control',c:'#7E6D8D'}, {d:'Independent commission',c:'#7dac9f'} ]) .enter().append("g") .attr("class", "legend") .attr("transform", function(d, i) { return "translate(0," + (i - 6) * 20 + ")"; }); legend.append("rect") .attr("x", width/2 - 20) .attr("width",18) .attr("height", 18) .style("fill", function(d) { return d.c; }); legend.append("text") .attr("x", width/2 - 25) .attr("y", 9) .attr("dy", ".35em") .style("text-anchor", "end") .text(function(d) { return d.d; }); function type(d) { d.value = +d.value; return d; } function districtType(name) { rep = ['Alabama','Florida','Georgia','Indiana','Kansas','Louisiana','Maine','Michigan','Nebraska','North Carolina','Ohio','Oklahoma','Pennsylvania','South Carolina','Tennessee','Texas','Utah','Virginia','Wisconsin'] dem = ['Arkansas','Connecticut','Illinois','Maryland','Massachusetts','Oregon','Rhode Island','West Virginia'] spt = ['Colorado','Iowa','Kentucky','Minnesota','Mississippi','Missouri','Nevada','New Hampshire','New Mexico','New York'] nne = ['Alaska','Arizona','California','Delaware','Hawaii','Idaho','Montana','New Jersey','North Dakota','South Dakota','Vermont','Washington'] if (rep.indexOf(name) >= 0) { return "r"; } if (dem.indexOf(name) >= 0) { return "d"; } if (spt.indexOf(name) >= 0) { return "p"; } if (nne.indexOf(name) >= 0) { return "n"; } } </script>
{{ dataSourceName(params.queryId) }}

The dataset is too large to view in browser

Export

Looks like something went wrong with your query.

{{ DS.queryRuns[params.queryId].errorMessage }}
This query was cancelled