Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Load US state bounding boxes from CSV - Source: https://anthonylouisdagostino.com/bounding-boxes-for-all-us-states/
CREATE EXTENSION IF NOT EXISTS file_fdw;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE SERVER fdw_files FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE public.fdw_us_state_bbox
(
id INT,
fips TEXT,
state_code TEXT,
state_name TEXT,
x_min FLOAT,
y_min FLOAT,
x_max FLOAT,
y_max FLOAT
)
SERVER fdw_files OPTIONS
(
program 'wget -q -O - "https://gist.githubusercontent.com/a8dx/2340f9527af64f8ef8439366de981168/raw/81d876daea10eab5c2675811c39bcd18a79a9212/US_State_Bounding_Boxes.csv"',
format 'csv',
header 'true'
);
CREATE MATERIALIZED VIEW public.us_state_bbox AS
SELECT id, fips, state_code, state_name,
CASE WHEN state_code NOT IN ('AK', 'HI', 'AS', 'PR', 'MP', 'GU', 'VI') THEN True
ELSE False
END AS lower48,
ST_Transform(
ST_SetSRID(
ST_MakeBox2D(
ST_Point(x_min, y_min),
ST_Point(x_max, y_max)),
4326),
3857)
AS geom
FROM public.fdw_us_state_bbox
;
@rustprooflabs

This comment has been minimized.

Copy link
Owner Author

@rustprooflabs rustprooflabs commented Feb 22, 2021

Example usage, return only lower 48 states + D.C..

SELECT *
	FROM public.us_state_bbox 
	WHERE lower48
;

image

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