Complicated SQL to do raster magic

raster_magic.sql

/* get the 32x32 data for a specific chunk */
CREATE OR REPLACE FUNCTION get_chunk_data(in v_x numeric , in v_y numeric, in chunk_size integer default 32)
RETURNS REFCURSOR
LANGUAGE PLPGSQL
AS 
$$
  DECLARE srtm_1m_rid INTEGER := 0;
  DECLARE rows REFCURSOR;
  BEGIN
      v_x := CEIL(v_x);
      v_y := CEIL(v_y);

	  OPEN rows 
	  FOR
	  select t.*
		from srtm_1m s 
		join srtm_1m_tiles t 
	   using (rid) 
	   where st_intersects(s.rast,create_point(v_x, v_y)) 
	   order by x,y desc;
   IF NOT FOUND THEN 
     SELECT interpolate_at(v_x/chunk_size,v_y/chunk_size)
       INTO srtm_1m_rid;
	   
	  WITH lands as
		(select (st_pixelaspoints(st_clip(rast, geometry),1,false)).x, (st_pixelaspoints(st_clip(rast,geometry),1,false)).y, array_accum(type) as "types", array_accum(name) as names, array_accum(z_order) as z_orders    
		   from import.osm_landusages
		   join srtm_1m on st_intersects(rast, geometry)
		  where rid = srtm_1m_rid
		  group by 1,2
		),
		buildings as
		( select * from srtm_buildings where rid = srtm_1m_rid),
		places_and_amenities as
		(select x,y, array_accum(name) thing_name, array_accum(type) thing_type 
		   from
		  ( select (st_pixelaspoints(st_clip(rast, geometry),1,false)).x, (st_pixelaspoints(st_clip(rast,geometry),1,false)).y, 'place::' || name as name, 'place::' || type as type
			  from import.osm_places
			  join srtm_1m on st_intersects(rast, geometry)
			 where rid = srtm_1m_rid
			union all 
			select (st_pixelaspoints(st_clip(rast, geometry),1,false)).x, (st_pixelaspoints(st_clip(rast,geometry),1,false)).y, 'place::' || name as name, 'place::' || type as type
			  from import.osm_amenities
			  join srtm_1m on st_intersects(rast, geometry)
			 where rid = srtm_1m_rid
		  ) foo   
		  group by 1,2
		)   
	  insert into srtm_1m_tiles  
		select p.rid,p.x, 
			   p.y,                             
			   p.val height, 
			   p.geom,
			   floor(b.area) building_area,
			   b.id building_id, 
			   r.id road_ids ,
			   r.z_order road_z_order,
			   r.bridge road_bridge, 
			   r.tunnel road_tunnel,  
			   r.rtype road_type,
			   w.val is not null has_water,
			   lands.names as land_names,
			   lands.types as land_types,
			   lands.z_orders as land_z,
			   pa.thing_name, 
			   pa.thing_type
		  from srtm_1m_pixels p
		  left join buildings b 
			on b.rast_geom && p.geom
		   and st_contains(b.geometry, p.geom)
		   and p.rid = b.rid
		  left join srtm_road_accum r 
				 on b.rid = r.rid
				and p.x = r.x 
				and p.y = r.y
		  left join srtm_water_pixels w 
				 on b.rid = w.rid
				and p.x = w.x 
				and p.y = w.y
		  left join srtm_transport_pixels tp 
				 on b.rid = tp.rid
				and p.x = tp.x 
				and p.y = tp.y
		  left join lands 
				 on p.x = lands.x
				and p.y = lands.y
		  left join places_and_amenities pa
				 on p.x = pa.x
				and p.y = pa.y
		where p.rid=srtm_1m_rid
		  -- and not exists (select 1 from srtm_1m_tiles t2 where p.rid = t2.rid and p.x = t2.x and p.y = t2.y)
		;
		
        OPEN rows
		 FOR 
		SELECT * 
          FROM srtm_1m_tiles 
		 WHERE srtm_rid = srtm_1m_rid 
	 	 ORDER BY x, y desc;
    END IF;	
    RETURN rows;
  END;
$$;