culebron · March 14, 2017 20:46
diff --git a/match-firms-to-blocks.sql b/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;
	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;