This is the kind of thing you can do in PostgreSQL and it is amazingly very fast. I like MySQL but come on guys, this is 2015. SQL92 is 23 years old. By the way this adds two more requests: stored aggregate functions and stored table functions
-- Table stored function which combines shuttle radar topography raster data (SRTM)
-- and Open street map geometry data
CREATE FUNCTION cache_1m_tile(integer) RETURNS SETOF tile
LANGUAGE sql
AS $_$
with lands as -- common table expressions
-- Use raster and geometry objects together with GiST index
(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 = $1
group by 1,2
),
buildings as
( select * from srtm_buildings where rid = $1),
places_and_amenities as
-- array_accum is a stored AGGREGATE function that works like group_concat
(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 = $1
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 = $1
) 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,
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=$1
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)
returning *;
$_$;