kgjenkins/us_county_inset_2163.md

Last active November 25, 2015 22:27

Star () You must be signed in to star a gist
Fork () You must be signed in to fork a gist

Learn more about clone URLs
Clone this repository at <script src="https://gist.github.com/kgjenkins/3ce08a7a70ab2167525d.js"></script>
Save kgjenkins/3ce08a7a70ab2167525d to your computer and use it in GitHub Desktop.

Create CartoDB dataset of US counties with inset Alaska, Hawaii, and Puerto Rico (EPSG:2163)

Raw

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)
  WHEN '72' THEN
    ST_TransScale(ST_Rotate(ST_Transform(the_geom, 2163),-0.37),-1350000,2750000,2,2)
  ELSE
    ST_Transform(the_geom, 2163)
END) AS the_geom,
name,
geoid,
statefp,
countyfp
FROM cb_2014_us_county_20m