Keith Jenkins kgjenkins

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

UNION

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)

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

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

LA5450 - mapping gardens of the ancient Roman world

Log in to CartoDB at http://cartodb.com

When you first log in, you'll see a list of maps that you have created. At the top of the page, where it says "cornell / Your Name / Maps", click the word "Maps" and select "Your datasets".

Two datasets have been shared with you for this project. Click "2 Shared with you" to view them.

The main dataset we will be editing collaboratively is called roman_gardens. Click it to view the dataset. There are two views (with a selector at the top of the page):

data view (as a table)

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
)

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

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

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: Filter the data