Skip to content

Instantly share code, notes, and snippets.

@andrewxhill
Created September 26, 2012 23:09
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save andrewxhill/3791208 to your computer and use it in GitHub Desktop.
Save andrewxhill/3791208 to your computer and use it in GitHub Desktop.
SQL for building a dynamic topo
WITH
setup AS (
SELECT
ST_Transform(ST_Buffer(the_geom::geography,n*100)::geometry,3857) the_geom_webmercator,
(cars*(10.0 - n)/10.0) as cars
FROM troparevo_nikulino_1, generate_series(1,9) n
),
maxcars AS (
SELECT max(cars)::float as mostcars FROM setup
)
(SELECT
ST_Buffer(ST_Buffer(ST_Union(the_geom_webmercator),400),-400) AS the_geom_webmercator,
round((2*cars/mostcars)::numeric,1)/2 cars,
'topo' as layer
FROM setup,maxcars
GROUP BY round((2*cars/mostcars)::numeric,1)/2
ORDER BY round((2*cars/mostcars)::numeric,1)/2 DESC)
UNION ALL
SELECT
the_geom_webmercator,
round((cars/mostcars)::numeric,1) as cars,
'points' as layer
FROM troparevo_nikulino_1, maxcars
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment