Move Alaska and Hawaii under AZ/NM

sql.sql

--  Convert your states (or other geometries id'd by state) to 
--    display as with Alaska, Hawaii, and Puerto Rico transcaled
--
--  @param g: input geometry
--  @param state: column identifying the state (name, postal abbreviation, state FP)
--  
--  output: geometries of states in albers projections of the states
-- 
-- Projections:
--		- Lower 48 states: http://epsg.io/42303
-- 		- Alaska: http://spatialreference.org/ref/epsg/3338/
--		- Puerto Rico: http://www.spatialreference.org/ref/epsg/nad83-puerto-rico-virgin-is/
--		- Hawaii: http://epsg.io/102007


CREATE OR REPLACE FUNCTION CDB_AlbersUSA (g geometry, state text) RETURNS geometry as $$
DECLARE
	reply geometry;
	srid INT;
	alaska text[] = '{"Alaska","AK","02"}'::text[];
	hawaii text[] = '{"Hawaii","HI","15"}'::text[];
	puertorico text[] = '{"Puerto Rico","PR","72"}'::text[];
BEGIN
	
	-- convert to wgs84
	IF ST_SRID(g) != 4326 THEN g = ST_Transform(g,4326); END IF;

	EXECUTE 'SELECT
	    ST_SetSRID(
	    	CASE 
  				WHEN $2 = any($3)
  					THEN
					ST_Scale(
						ST_Translate(
							ST_Transform(
								$1
								, 3338
							)
							, -3800000
							, -900000
						)
						, 0.7
						, 0.7
					)
				WHEN $2 = any($4)
					THEN 
  					ST_Scale(
  						ST_Transform(
  							ST_Translate(
  								$1
			                    , -8
			                    , -5
			                )
			                , 102007
			            )
			            , 1.2
			            , 1.2
			        )
				WHEN $2 = any($5)
					THEN 
  					ST_Scale(
  						ST_Transform(
  							ST_Translate(
  								$1
			                    , 10
			                    , -1.5
			                )
			                , 32161
						)
						, 1.5
						, 1.5
					)
				ELSE
					ST_Transform($1,42303)
  				END
  				, 3857
  			)'
	INTO reply
	USING g, state, alaska, hawaii, puertorico;

	reply = ST_Transform(reply,4326);

	RETURN reply;

END; 
$$ language plpgsql IMMUTABLE;

Hi Andy, the use of EPSG:3857 results is a squished-looking Alaska, not the nice-looking one in your CartoDB viz: https://team.cartodb.com/u/eschbacher/viz/8a5b427a-c12e-11e4-ace3-0e018d66dc29/public_map

Looks like maybe you meant to use a different projection there?

Hey Alan! Sorry I didn't see this -- yes, 100% agree that it is not the best projection for AK. There's an updated version that uses an Albers that's meant for AK (Alaska Albers SRID 3338).

https://github.com/CartoDB/cartodb-postgresql/pull/167/files

Version above updated too.