gistfile1.txt

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$