Skip to content

Instantly share code, notes, and snippets.

View kgjenkins's full-sized avatar

Keith Jenkins kgjenkins

24 followers · 0 following
  • Ithaca, NY
View GitHub Profile
@kgjenkins
kgjenkins / US48_inset_AK_HI.md
Last active November 26, 2015 14:31
Create CartoDB dataset of US states with inset Alaska and Hawaii (epsg:4326) 
SELECT
cartodb_id,
the_geom,
abbrev, name, postal
FROM ne_50m_admin_1_states
WHERE postal NOT IN ('AK', 'HI')

UNION
@kgjenkins
kgjenkins / carson-projection.png
Last active July 18, 2016 21:48
Carson projection in CartoDB PostGIS
carson-projection.png
@kgjenkins
kgjenkins / us_county_inset_2163.md
Last active November 25, 2015 22:27
Create CartoDB dataset of US counties with inset Alaska, Hawaii, and Puerto Rico (EPSG:2163)

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
kgjenkins / us_states_inset_3857.md
Last active November 25, 2015 22:20
Create CartoDB dataset of US states with inset Alaska, Hawaii, and Puerto Rico (EPSG:3857)

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
kgjenkins / README.md
Last active December 10, 2015 21:52
Customize CartoDB infowindows using Mustache and SQL

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...)

cartodb-infowindow-ui

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
kgjenkins / roman_gardens.md
Last active April 1, 2021 16:29
Mapping gardens of the ancient Roman world

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)
@kgjenkins
kgjenkins / count_within_distance.md
Created March 3, 2016 16:34
Count polygons within a given distance of each feature

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
kgjenkins / openrefine-workshop-2016-03-15.md
Last active March 22, 2016 16:44

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
kgjenkins / medicare-long-stay-county.md
Last active March 30, 2016 19:19
Calculate the county for Medicare Long Stay facilities
@kgjenkins
kgjenkins / medicare-long-stay-providers-take2.md
Last active September 15, 2016 04:32
Calculate the county for Medicare Long Stay facilities