Skip to content

Instantly share code, notes, and snippets.

@hjanetzek
Last active Dec 18, 2015
Embed
What would you like to do?
CREATE OR REPLACE FUNCTION map.tile_building_test(IN bbox geometry, IN pixel double precision)
RETURNS TABLE(osm_id bigint, tags hstore, geom geometry)
language sql AS
$BODY$
WITH
buildings AS
(SELECT (ROW_NUMBER() OVER(ORDER BY way DESC))::integer AS id, osm_id, way, tags, way_area
FROM osm_polygon
WHERE way && $1
AND (building is not null and not building in ('roof'))),
parts AS
(SELECT b.id, b.osm_id, p.way, p.tags, b.way_area
FROM osm_polygon p, buildings as b
WHERE p.way && b.way AND ST_Contains(b.way, p.way)
AND (building is null and p.tags->'building:part' = 'yes')),
overlap AS
(SELECT b.id, st_union(p.way) part FROM buildings b, parts p
WHERE p.id = b.id
group by b.id, b.way),
-- symdifference removes the overlapping area of building:part from building
outline AS
(select b.osm_id, st_symdifference(b.way, o.part) way, tags, way_area
from buildings b, overlap o where b.id = o.id
union
select b.osm_id, b.way, b.tags, b.way_area from buildings b where b.id not in (select id from overlap)
union
select p.osm_id, p.way, p.tags, p.way_area from parts p
)
SELECT osm_id, tags,
st_forcerhr(st_buffer(geom,0)) FROM
(select osm_id, tags, (ST_Dump(ST_Intersection(geom, $1))).geom geom from
-- remove small kinks
(SELECT osm_id, st_buffer(st_buffer(way,-$2/8,'join=mitre mitre_limit=1.5'), $2/8, 'join=mitre mitre_limit=1.5') geom, tags
FROM outline
) p
) p where GeometryType(geom) = 'POLYGON'
$BODY$
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment