-- FUNCTION: public._a_remove_overlaps(bigint, geometry)

-- DROP FUNCTION public._a_remove_overlaps(bigint, geometry);

CREATE OR REPLACE FUNCTION public._a_remove_overlaps(
	osm_id_in bigint,
	geom_in geometry)
    RETURNS geometry
    LANGUAGE 'plpgsql'
    COST 100.0
    VOLATILE 
AS $function$

DECLARE
  overlap geometry;
  diff	geometry;
BEGIN
	
	IF NOT st_isvalid(geom_in) THEN
		RETURN NULL;
		--geom_in:=ST_collectionextract(ST_makevalid(geom_in),3);
	END IF;

	overlap:=(SELECT ST_Collect(b.the_geom) 
				FROM public.naturals b 	
				WHERE 
				osm_id_in != b.osm_id 
				AND geom_in && b.the_geom 
				AND st_isvalid(b.the_geom)
				AND ST_Relate(geom_in, b.the_geom,'T********')
				AND (ST_Area(st_transform(b.the_geom,4326)::geography) <= ST_Area(st_transform(geom_in,4326)::geography) ) 
				AND (b."type" = ANY(ARRAY['residential','garages','railway','port','harbour','industrial','commercial','retail','lock','marina','aerodrome','quarry','landfill','brownfield','construction','allotments','cemetery','garden','raceway','golf_course','park','common','pitch','playground','recreation_ground','sports_centre','stadium','village_green','zoo','track','farm','farmland','farmyard','greenhouse','greenhouse_horticulture','vineyard','orchard','meadow','forest','wood','grass','greenfield','scrub','heath','grassland','cliff','sand','scree','beach','mud','glacier','fell','rock','wetland','march','salt_pond','tidal','water','riverbank','reservoir','basin','dock','canal','pond']) )
				);
	--diff:=coalesce(st_difference(geom_in,overlap),geom_in);
	IF overlap IS NULL THEN
	  RETURN ST_Multi(geom_in);
	ELSEIF st_geometrytype(overlap) = 'ST_GeometryCollection' THEN
	  			overlap:=st_collectionextract(st_makevalid(st_buffer(overlap,0)),3);
	  			IF  st_isvalid(overlap) THEN 
	    			--RETURN st_multi(st_difference(geom_in,overlap));
	    			diff:=st_difference(geom_in,overlap);
	  			END IF;
	  		
	ELSEIF st_geometrytype(overlap) IN ('ST_Polygon','ST_Multipolygon') AND st_isvalid(overlap) THEN 
	  			--RETURN st_multi(st_difference(geom_in,overlap));
	  			diff:=st_difference(geom_in,overlap);
	END IF;

	IF st_geometrytype(diff) IN ('ST_Polygon','ST_Multipolygon') AND st_isvalid(diff) THEN 
		RETURN ST_Multi(diff);
	ELSE 
		diff:=st_buffer(st_collectionextract(st_makevalid(diff),3),0);
		IF st_geometrytype(diff) IN ('ST_Polygon','ST_Multipolygon') AND st_isvalid(diff) THEN 
			RETURN ST_Multi(diff);
		END IF;
		RETURN NULL;
	END IF;
EXCEPTION
	WHEN SQLSTATE 'XX000' THEN 
	  RAISE NOTICE 'RETURNED NULL FOR OSM_ID : % : %',  osm_id_in, SQLERRM;
	  -- Do nothing on error;
	  RETURN NULL; 
END;

$function$;

ALTER FUNCTION public._a_remove_overlaps(bigint, geometry)
    OWNER TO mauer;

