Skip to content

Instantly share code, notes, and snippets.

@rustprooflabs

rustprooflabs/fdw_us_state_bbox.sql

Last active February 22, 2021 17:16
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save rustprooflabs/0cd8c3cb3f8516db3f079c86ccc98a6b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save rustprooflabs/0cd8c3cb3f8516db3f079c86ccc98a6b to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. rustprooflabs revised this gist Feb 22, 2021. 1 changed file with 6 additions and 7 deletions.
    13 changes: 6 additions & 7 deletions fdw_us_state_bbox.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,7 @@

    CREATE EXTENSION file_fdw;
    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
    @@ -22,6 +24,9 @@ SERVER fdw_files OPTIONS

    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(
    @@ -32,9 +37,3 @@ SELECT id, fips, state_code, state_name,
    AS geom
    FROM public.fdw_us_state_bbox
    ;

    -- Bounding boxes for lower-48
    SELECT *
    FROM public.us_state_bbox
    WHERE state_code NOT IN ('AK', 'HI', 'AS', 'PR', 'MP', 'GU', 'VI')
    ;
  2. rustprooflabs created this gist Feb 11, 2021.
    40 changes: 40 additions & 0 deletions fdw_us_state_bbox.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,40 @@

    CREATE EXTENSION file_fdw;
    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.github.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,
    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
    ;

    -- Bounding boxes for lower-48
    SELECT *
    FROM public.us_state_bbox
    WHERE state_code NOT IN ('AK', 'HI', 'AS', 'PR', 'MP', 'GU', 'VI')
    ;