kgjenkins

Intended to be displayed as epsg:3857 or epsg:4326

SELECT
ST_Transform((CASE stusps
  WHEN 'AK' THEN
    ST_TransScale(ST_Rotate(ST_Transform(the_geom, 2163),0.51),-600000,-6400000,0.35,0.35)
  WHEN 'HI' THEN
    ST_TransScale(ST_Rotate(ST_Transform(the_geom, 2163),0.51),4000000,1830000,1.2,1.2)
 WHEN 'PR' THEN

kgjenkins

Intended to be displayed as epsg:2163 (National Atlas Equal Area)
(if displayed as epsg:4326 or epsg:3857, PR will be tilted)

SELECT
(CASE statefp
  WHEN '02' THEN
    ST_TransScale(ST_Rotate(ST_Transform(the_geom, 2163),0.51),-600000,-6400000,0.35,0.35)
  WHEN '15' THEN
 ST_TransScale(ST_Rotate(ST_Transform(the_geom, 2163),0.51),4000000,1850000,1.2,1.2)

kgjenkins

SELECT
cartodb_id,
the_geom,
abbrev, name, postal
FROM ne_50m_admin_1_states
WHERE postal NOT IN ('AK', 'HI')

UNION

kgjenkins

Customize CartoDB infowindows using Mustache and SQL

Infowindows are those little windows that can be configured to pop up when we click or hover over a feature (point, line, or polygon) in CartoDB. One of the great things about CartoDB is that it provides a simple interface that lets us control which attributes appear in the infowindow and which ones are hidden. (Because no one looking at your map really wants to see the value of FID, OBJECTID, ID2, AREA, AND PERIMETER, especially when FID, OBJECTID, and ID2 are all the same, and AREA and PERIMETER are incorrect because they weren't recalculated after clipping...)

But CartoDB also gives us full access to the HTML behind the infowindow. (They added this feature back in 2013.) So if we know even just a little bit about HTML, we can

kgjenkins

Given a layer called parcels that has a column called abandoned (0 or 1), we add a column called abandoned70ft and to store the number of abandoned parcels within 70 feet.

UPDATE parcels
SET abandoned70ft = (
  SELECT count(*)
  FROM parcels a
  WHERE a.abandoned = 1
  AND ST_Distance(parcels.the_geom::geography, a.the_geom::geography)*3.28084 < 70
)

kgjenkins

Data Cleaning: an Introduction to OpenRefine

Got messy data? OpenRefine is a free and powerful tool for exploring, normalizing, and manipulating tabular data. This introductory workshop will teach you the basics of OpenRefine, including how to import data, discover and clean up inconsistent values, reformat dates, and expand values into multiple columns.

Why OpenRefine?

• Free, open-source software
• Runs on Windows, Mac, and Linux
• Explore data through faceting and scatterplots
• Normalize data by clustering similar text values
• Track all changes to the data, with full undo/redo

kgjenkins

Calculate the county for Medicare Long Stay facilities

We want to calculate the county (name, FIPS) that corresponds to each row of this dataset: https://data.medicare.gov/Nursing-Home-Compare/Quality-Measures-Long-Stay/iqd3-nsf3

We will use CartoDB to add new columns that will contain the latitude, longitude, county name, and county FIPS code.

Step 1: Extract the point coordinates

kgjenkins

CartoDB

What is CartoDB? It's a popular online tool for creating dynamic maps and analyzing spatial data. CartoDB includes:

• a simple interface for creating a variety of beautiful maps based upon your data. Share links directly to your maps, or embed them in a webpage, or export static map images.
• a powerful PostGIS spatial database for processing and analyzing data.
• an infrastructure for creating more complex maps using custom styles (using CartoCSS), custom popups (using HTML), custom map projections (using PostGIS SQL), and custom interactions (using cartodb.js)

Cornell-affiliated CartoDB accounts

kgjenkins

kgjenkins

Method using Carto SQL

WITH buf AS (
  -- 2mi (3218.69m) point buffers
  SELECT
  ST_Collect(ST_Buffer(the_geom::geography, 3218.69)::geometry) AS the_geom
  FROM sites
)
SELECT