Skip to content

Instantly share code, notes, and snippets.

@antichaos

antichaos/tableau.sql

Last active Dec 29, 2020
Embed
What would you like to do?
Postgres/PostGIS query for buffer and spatial 'shifted' point
CAST(
-- make collection
st_collect(ARRAY[
-- make line from two range polygons
st_difference( CAST(
st_buffer(
CAST(
ST_MakePoint(
"postinfo"."lon",
"postinfo"."lat") AS geography(POINT, 4326)),
"ranges"."range"+1,
8)::geometry AS geometry(POLYGON, 4326)) , CAST(
st_buffer(
CAST(
ST_MakePoint(
"postinfo"."lon",
"postinfo"."lat") AS geography(POINT, 4326)),
"ranges"."range",
8)::geometry AS geometry(POLYGON, 4326)) )
,
-- create polygon (Tableau can't handle mixed geography types)
ST_buffer(
-- create point at distance from mission to create 'air' on dashboard
ST_Project(
CAST(
ST_MakePoint(
"postinfo"."lon",
"postinfo"."lat") AS geography(POINT, 4326)),
"ranges"."range" *
3,
radians(
90.0))::geometry
-- end st_project
,0.001)
])
AS geography(MultiPolygon, 4326))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment