Skip to content

Instantly share code, notes, and snippets.

@andrewxhill
Created October 11, 2012 22:17
  • Star 2 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save andrewxhill/3875882 to your computer and use it in GitHub Desktop.
Order polygons by size/height
WITH RECURSIVE dims AS (SELECT 2*sqrt(sum(ST_Area(the_geom))) as d, sqrt(sum(ST_Area(the_geom)))/20 as w, count(*) as rows FROM osm_export_polygon WHERE the_geom IS NOT NULL),
geoms AS (SELECT the_geom, ST_YMax(the_geom)-ST_YMin(the_geom) as height FROM osm_export_polygon WHERE the_geom IS NOT NULL ORDER BY ST_YMax(the_geom)-ST_YMin(the_geom) DESC),
geomval AS (SELECT the_geom, row_number() OVER (ORDER BY height DESC) as id from geoms),
positions(the_geom,x_offset,y_offset,new_row,row_offset) AS (
(SELECT the_geom, 0.0::float, 0.0::float, FALSE, 2 from geomval limit 1)
UNION ALL
(
SELECT
(SELECT the_geom FROM geomval WHERE id = p.row_offset),
CASE WHEN
p.x_offset < s.d
THEN
(SELECT (s.w+(ST_XMax(the_geom) - ST_XMin(the_geom)))+p.x_offset FROM geomval WHERE id = p.row_offset)
ELSE
0
END as x_offset
,
CASE WHEN
p.x_offset < s.d
THEN
p.y_offset
ELSE
(SELECT (-1*s.w+(ST_YMin(the_geom) - ST_YMax(the_geom)))+p.y_offset FROM geomval WHERE id = p.row_offset)
END as y_offset
,
FALSE,
p.row_offset+1
FROM
positions p, dims s
WHERE
p.row_offset < s.rows
)
),
sfact AS (
SELECT ST_XMin(the_geom) as x, ST_YMin(the_geom) as y FROM geomval LIMIT 1
)
SELECT ST_Transform(ST_Translate(
the_geom,
(x - ST_XMin(the_geom) + x_offset),
(y - ST_YMin(the_geom) + y_offset)),3857) as the_geom_webmercator FROM positions,sfact order by row_offset asc
@javisantana
Copy link

you are crazy

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