match-firms-to-blocks.sql

drop table if exists density_streets cascade;
create table density_streets as
with
    -- сначала выбираются линии, на которых нужные нам тэги
    -- они объединяются в один объект (MultiLineString)
    lines as (
        select st_union(way) way
        from rd_line
        where
            highway in ('trunk', 'trunk_link', 'primary', 'primary_link', 'secondary',
                'secondary_link', 'tertiary', 'tertiary_link', 'unclassified', 'residential', 'pedestrian')
--             waterway is not null or
--             railway is not null or
-- or landuse is not null
            or border='yes'
    ),
    -- все полигоны типа residential и к ним присоединяются линии из предыдущего CTE
    -- CTE - common table expression (with X as (select ...))
    polys as (
        select st_union(way) way
        from rd_polygon
        where
		false
--          border='yes'

        union all

        select way from lines),

    -- теперь всё объединяется в один объект
    streets as (
        select st_union(way) way from polys
    ),

    -- из группы линий и полигонов нарезаются лоскуты полигонов
    polygroup as (select st_polygonize(way) way from streets),

    -- теперь они разбиваются на отдельные строки
    dumped as (select (st_dump(way)).geom poly from polygroup)

-- окончательная выборка: нумерация
select row_number() over () id, poly from dumped;

-- и индексы
create index density_str_gix on density_streets using gist (poly);
create index density_str_id_idx on density_streets (id);


drop materialized view if exists inner_rings cascade;

create materialized view inner_rings as
select
	id,
	st_buffer(poly, - get_aspect_ratio(poly) * 30) inner_poly,
	st_buffer(poly, - get_aspect_ratio(poly) * 25) line_to_draw,
	st_buffer(poly, get_aspect_ratio(poly) * 25) outer_reach

from density_streets;

create materialized view outer_rings as
select id, st_difference(outer_reach, inner_poly) bubble
from inner_rings
where not st_isempty(line_to_draw);

create materialized view drawn_line as
with
	intermedia as (
		select id, (st_dump(line_to_draw)).geom line_to_draw from inner_rings),
	geoms as (
		select g.id raw_id, row_number() over () id,
			st_exteriorRing(g.line_to_draw) line_to_draw,
			bubble
		from intermedia g, outer_rings o
		where g.id=o.id)
select raw_id, id,
	ST_addmeasure(line_to_draw, 0, 1) line_to_draw,
	bubble, st_length(line_to_draw) length
from geoms;

create index drawn_line_id on drawn_line (id);
create index ring_gix on drawn_line using gist (line_to_draw);


/* select firms that are in sections */
drop materialized view if exists uni_firms;
create materialized view uni_firms as
select row_number() over () id, firm_id, subsection_id, centroid_913
from nsk_markers nm --, nsk_sections ns
-- where -- nm.subsection_id=ns.id and
 -- ns.gr is not null
group by firm_id, centroid_913, subsection_id;

create unique index uni_firms_id on uni_firms (id);
create index uni_firms_centr on uni_firms using gist (centroid_913);


drop materialized view if exists projected;
create materialized view projected as
with
	distances as (
		select
			u.id marker_id, subsection_id, l.id line_id, st_distance(centroid_913, line_to_draw) dist
		from uni_firms u, drawn_line l
		where st_within(centroid_913, bubble)),
	ranked as (
		select distinct marker_id, first_value(line_id) over (partition by marker_id order by dist) line_id
		from distances)
select
	marker_id, subsection_id, line_id, st_length(line_to_draw) ln_len, line_to_draw,
	st_closestpoint(line_to_draw, centroid_913) closest, centroid_913,
	st_interpolatepoint(line_to_draw, centroid_913) inter
from ranked r, uni_firms u, drawn_line d
where r.line_id=d.id and r.marker_id=u.id;
--
create unique index proj_id_idx on projected (marker_id);
create index proj_line_idx on projected (line_id);

drop materialized view if exists street_segments;
create materialized view street_segments as
with
	slen as (
		select id, st_length(line_to_draw) l, get_aspect_ratio(line_to_draw) * 25 / st_length(line_to_draw) slen,
			st_length(line_to_draw) / get_aspect_ratio(line_to_draw) / 25 nsects
		from drawn_line),
	seq as (
		select id, generate_series(0, floor(nsects)::int)*slen proj, slen
		from slen
	),
	joined as (
		select marker_id, subsection_id, s.id line_id, proj, slen
		from seq s
			left join projected p
			on p.line_id=s.id and inter between proj and proj+slen
		),
	post_group as (
		select line_id, proj, slen, count(marker_id) totals, count(distinct(subsection_id)) variety
		from joined j
		group by line_id, proj, slen)

select row_number() over () id, line_id, st_force_2d(st_line_substring(line_to_draw, proj, least(1, proj+slen))) ls,
	sum(totals) over (partition by line_id order by proj rows between 2 preceding and 2 following) totals2,
	sum(variety) over (partition by line_id order by proj rows between 2 preceding and 2 following) variety
from post_group, drawn_line d
where line_id=d.id
-- 	straightened as (;
;
-- select * from street_segments2;