Skip to content

Instantly share code, notes, and snippets.

@kgjenkins
Last active November 26, 2015 14:31
Show Gist options
  • Save kgjenkins/1eef19f2be4673f1964f to your computer and use it in GitHub Desktop.
Save kgjenkins/1eef19f2be4673f1964f to your computer and use it in GitHub Desktop.
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

SELECT
cartodb_id,
ST_Transform(ST_Scale(ST_Translate(the_geom_webmercator,-48000000,6500000),0.2,0.2),4326)
AS the_geom,
abbrev, name, postal
FROM ne_50m_admin_1_states
WHERE postal = 'AK'

UNION

SELECT
cartodb_id,
ST_Multi(ST_Transform(ST_Scale(ST_Translate(the_geom_webmercator,9000000,-120000), 1.4, 1.4),4326))
AS the_geom,
abbrev, name, postal
FROM ne_50m_admin_1_states
WHERE postal = 'HI'
@andy-esch
Copy link

And PostGIS has ST_TransScale which I keep meaning to use to reduce complexity of my code: http://postgis.net/docs/ST_TransScale.html

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment