суммарная площадь пересечения

Ответить
VorSer
Интересующийся
Сообщения: 31
Зарегистрирован: 18 июл 2017, 12:26
Репутация: 0

суммарная площадь пересечения

Сообщение VorSer » 25 ноя 2017, 01:40

Пытаюсь сделать сводный отчет, суть которого в том, чтобы отобразить в одной таблице по каждому административному району области общую площадь находящихся в его границах полигонов( в дальнейшем, за вычетом площади всех населенных пунктов)

сконструировал вот это:

Код: Выделить всё

Create OR replace view report AS select 
	mo_cs1.cad_num as id,
    mo_cs1.obj_label as name,
    round(mo_cs1.map_area/1000) as region_area,
    round(ST_area(ST_Intersection(mo_cs1.geom, sh2017_cs1.geom))/10000000) as res
    from mo_cs1, sh2017_cs1
    GROUP by mo_cs1.cad_num, name, region_area, res
    ORDER by name;
И оно даже сработало, но не совсем так как надо. Вывел длинную такую таблицу:

Код: Выделить всё

1,'Андреапольский','306','0'
1,'Андреапольский','306','17'
1,'Андреапольский','306','20'
1,'Андреапольский','306','22'
1,'Андреапольский','306','23'
1,'Андреапольский','306','27'
1,'Андреапольский','306','43'
1,'Андреапольский','306','45'
1,'Андреапольский','306','93'
3,'Бельский','214','0'
3,'Бельский','214','3'
3,'Бельский','214','9'
3,'Бельский','214','13'
3,'Бельский','214','18'
3,'Бельский','214','32'
3,'Бельский','214','33'
3,'Бельский','214','107'
7,'Жарковский','163','0'
7,'Жарковский','163','2'
7,'Жарковский','163','9'
7,'Жарковский','163','12'
7,'Жарковский','163','14'
7,'Жарковский','163','15'
7,'Жарковский','163','23'
7,'Жарковский','163','24'
7,'Жарковский','163','29'
7,'Жарковский','163','35'
8,'Западнодвинский','282','0'
8,'Западнодвинский','282','7'
8,'Западнодвинский','282','14'
8,'Западнодвинский','282','15'
8,'Западнодвинский','282','31'
8,'Западнодвинский','282','38'
8,'Западнодвинский','282','39'
8,'Западнодвинский','282','44'
8,'Западнодвинский','282','47'
22,'Нелидовский','264','0'
22,'Нелидовский','264','4'
22,'Нелидовский','264','6'
22,'Нелидовский','264','7'
22,'Нелидовский','264','9'
22,'Нелидовский','264','13'
22,'Нелидовский','264','18'
22,'Нелидовский','264','19'
22,'Нелидовский','264','37'
22,'Нелидовский','264','97'
23,'Оленинский','267','0'
23,'Оленинский','267','5'
23,'Оленинский','267','7'
23,'Оленинский','267','8'
23,'Оленинский','267','9'
23,'Оленинский','267','10'
23,'Оленинский','267','17'
23,'Оленинский','267','18'
23,'Оленинский','267','19'
23,'Оленинский','267','23'
23,'Оленинский','267','31'
23,'Оленинский','267','40'
23,'Оленинский','267','56'
24,'Осташковский','312','0'
24,'Осташковский','312','8'
24,'Осташковский','312','16'
24,'Осташковский','312','25'
24,'Осташковский','312','129'
24,'Осташковский','312','134'
25,'Пеновский','242','0'
25,'Пеновский','242','3'
25,'Пеновский','242','4'
25,'Пеновский','242','7'
25,'Пеновский','242','11'
25,'Пеновский','242','12'
25,'Пеновский','242','13'
25,'Пеновский','242','18'
25,'Пеновский','242','22'
25,'Пеновский','242','23'
25,'Пеновский','242','26'
25,'Пеновский','242','79'
29,'Селижаровский','312','0'
29,'Селижаровский','312','9'
29,'Селижаровский','312','19'
29,'Селижаровский','312','27'
29,'Селижаровский','312','118'
29,'Селижаровский','312','121'
34,'Торопецкий','338','0'
34,'Торопецкий','338','12'
34,'Торопецкий','338','14'
34,'Торопецкий','338','20'
34,'Торопецкий','338','23'
34,'Торопецкий','338','31'
34,'Торопецкий','338','32'
34,'Торопецкий','338','163'
Как мне сделать, чтобы название района встречалось 1 раз, а в последнем столбце была сумма по району?

trir
Гуру
Сообщения: 5271
Зарегистрирован: 09 апр 2010, 19:30
Репутация: 1013
Ваше звание: просто мимо прохожу
Откуда: Ё-бург

Re: суммарная площадь пересечения

Сообщение trir » 25 ноя 2017, 09:27


VorSer
Интересующийся
Сообщения: 31
Зарегистрирован: 18 июл 2017, 12:26
Репутация: 0

Re: суммарная площадь пересечения

Сообщение VorSer » 25 ноя 2017, 13:15

Спасибо, помогло!)

Код: Выделить всё

SELECT mo_cs1.cad_num AS id,
    mo_cs1.obj_label AS name,
    round(mo_cs1.map_area / 1000) AS region_area,
    round(sum(st_area(st_intersection(mo_cs1.geom, sh2017_cs1.geom))) / 10000000) AS res
   FROM atd.mo_cs1,
    ohota.sh2017_cs1
  GROUP BY mo_cs1.cad_num, mo_cs1.obj_label, mo_cs1.map_area
  ORDER BY mo_cs1.obj_label;
Табличка получилась как надо и считает сравнительно быстро - около 9 сек.
Спойлер
id name region_area res
1 Андреапольский 306 289
3 Бельский 214 214
7 Жарковский 163 163
8 Западнодвинский 282 282
22 Нелидовский 264 227
23 Оленинский 267 267
24 Осташковский 312 312
25 Пеновский 242 242
29 Селижаровский 312 294
34 Торопецкий 338 338
Попробовал вычесть из sh2017_cs1 населенные пункты заменив sh2017_cs1.geom на ST_difference(sh2017_cs1.geom, np_cs1.geom):

Код: Выделить всё

round(sum(st_area(st_intersection(mo_cs1.geom, (ST_difference(sh2017_cs1.geom, np_cs1.geom))))) / 10000000) AS res
Результата за полчаса не дождался... подскажите как мне провернуть это действие в разумное время

trir
Гуру
Сообщения: 5271
Зарегистрирован: 09 апр 2010, 19:30
Репутация: 1013
Ваше звание: просто мимо прохожу
Откуда: Ё-бург

Re: суммарная площадь пересечения

Сообщение trir » 25 ноя 2017, 18:46


VorSer
Интересующийся
Сообщения: 31
Зарегистрирован: 18 июл 2017, 12:26
Репутация: 0

Re: суммарная площадь пересечения

Сообщение VorSer » 25 ноя 2017, 23:33

Читал, пробовал понять - не вышло. Оставил на потом. Кажется задачку попроще не могу решить:
Есть три запроса, каждый из которых делает то что нужно. Но через UNION объединяется построчно, а мне надо чтобы результат каждого из запросов выводился в отдельный столбец.
Спойлер

Код: Выделить всё

(SELECT
	mo_cs1.cad_num::int AS id,
	mo_cs1.obj_label AS name,
	round(sum(st_area(st_intersection(mo_cs1.geom, sh2017_cs1.geom))) / 10000000) as res
	FROM atd.mo_cs1, ohota.sh2017_cs1
	GROUP BY mo_cs1.cad_num, mo_cs1.obj_label, mo_cs1.map_area
	)
UNION
	(SELECT
	mo_cs1.cad_num::int AS id,
	mo_cs1.obj_label AS name,
	round(sum(st_area(st_intersection(mo_cs1.geom, sh2017_cs1.geom))) / 10000000) as res_ohs
	FROM atd.mo_cs1, ohota.sh2017_cs1
    WHERE sh2017_cs1.name NOT LIKE 'ОДОУ%'
	GROUP BY mo_cs1.cad_num, mo_cs1.obj_label, mo_cs1.map_area
	)
UNION
    (SELECT
	mo_cs1.cad_num::int AS id,
	mo_cs1.obj_label AS name,
	round(sum(st_area(st_intersection(mo_cs1.geom, sh2017_cs1.geom))) / 10000000) as res_ohs
	FROM atd.mo_cs1, ohota.sh2017_cs1
    WHERE sh2017_cs1.name LIKE 'ОДОУ%'
	GROUP BY mo_cs1.cad_num, mo_cs1.obj_label, mo_cs1.map_area
	)
ORDER BY id;
Вот такой вывод получаю:
Спойлер
1,'Андреапольский','289'
1,'Андреапольский','82'
1,'Андреапольский','207'
3,'Бельский','214'
3,'Бельский','32'
3,'Бельский','182'
7,'Жарковский','35'
7,'Жарковский','163'
7,'Жарковский','128'
А надо такой:
Спойлер
1,'Андреапольский','289','82','207'
3,'Бельский','214','32','182'
7,'Жарковский','35','163','128'
Попробовал SELECT * FROM (SELECT - не дает, потому что подзапрос возвращает более одного значения, JOIN вообще не понял как воткнуть... Чувствую, что должно быть как-то просто: три запроса возвращают таблицы из трёх столбцов, два из которых идентичны...

freeExec
Гуру
Сообщения: 1195
Зарегистрирован: 23 апр 2011, 10:32
Репутация: 205
Откуда: Ульяновск

Re: суммарная площадь пересечения

Сообщение freeExec » 26 ноя 2017, 10:04

Возможно вам подайдёт array_agg

trir
Гуру
Сообщения: 5271
Зарегистрирован: 09 апр 2010, 19:30
Репутация: 1013
Ваше звание: просто мимо прохожу
Откуда: Ё-бург

Re: суммарная площадь пересечения

Сообщение trir » 26 ноя 2017, 10:24

тут нужен join, а не union

VorSer
Интересующийся
Сообщения: 31
Зарегистрирован: 18 июл 2017, 12:26
Репутация: 0

Re: суммарная площадь пересечения

Сообщение VorSer » 26 ноя 2017, 13:38

trir писал(а):
26 ноя 2017, 10:24
тут нужен join, а не union
Так и есть, только я его готовить не умею:
Сделал три вьюхи report_res,report_ohs, report_odou, пишу запрос

Код: Выделить всё

SELECT * FROM ohota.report_res r
  	JOIN ohota.report_ohs as o ON r.id=o.id
        JOIN ohota.report_odou d ON r.id=d.id;
А он мне из трёх таблиц все столбцы склеивает
Спойлер
1,'Андреапольский','289',1,'Андреапольский','207',1,'Андреапольский','82'
3,'Бельский','214',3,'Бельский','182',3,'Бельский','32'
7,'Жарковский','163',7,'Жарковский','128',7,'Жарковский','35'

trir
Гуру
Сообщения: 5271
Зарегистрирован: 09 апр 2010, 19:30
Репутация: 1013
Ваше звание: просто мимо прохожу
Откуда: Ё-бург

Re: суммарная площадь пересечения

Сообщение trir » 26 ноя 2017, 14:05

Код: Выделить всё

SELECT r.obj_label, r.res, o.res, d.res FROM ohota.report_res r
  	JOIN ohota.report_ohs as o ON r.id=o.id
        JOIN ohota.report_odou d ON r.id=d.id;

VorSer
Интересующийся
Сообщения: 31
Зарегистрирован: 18 июл 2017, 12:26
Репутация: 0

Re: суммарная площадь пересечения

Сообщение VorSer » 26 ноя 2017, 15:47

Спасибо!
Чуть раньше дочитал мануал до USING вроде получилось

Код: Выделить всё

SELECT *
	FROM ohota.report_res
  	JOIN ohota.report_ohs USING (id, name)
        JOIN ohota.report_odou USING (id, name);

VorSer
Интересующийся
Сообщения: 31
Зарегистрирован: 18 июл 2017, 12:26
Репутация: 0

Re: суммарная площадь пересечения

Сообщение VorSer » 26 ноя 2017, 18:52

Итоговый запрос про объединяющий вьюхи получился такой:

Код: Выделить всё

CREATE OR REPLACE VIEW ohota.report AS
	SELECT r.id, r.name,
	round(r.region_area::numeric/1000,1) as region_area,
	round(r.res::numeric/1000,1) as res,
	round(r.res_rel::numeric,2) as res_rel, 
	round(o.res_ohs::numeric/1000,1) as res_ohs,
	round(o.res_ohs::numeric/r.res::numeric,2) as res_ohs_rel,
	round(d.res_odou::numeric/1000,1) as res_odou,
	round(d.res_odou::numeric/r.res::numeric,2) as res_odou_rel   
FROM ohota.report_res r
JOIN ohota.report_ohs o USING (id, name)
JOIN ohota.report_odou d USING (id, name);
Задача почти выполнена, не хватает только строчки Итого:
Как сделать внизу суммы всех столбцов с площадями, и расчетом процентов?

trir
Гуру
Сообщения: 5271
Зарегистрирован: 09 апр 2010, 19:30
Репутация: 1013
Ваше звание: просто мимо прохожу
Откуда: Ё-бург

Re: суммарная площадь пересечения

Сообщение trir » 26 ноя 2017, 19:20

не думаю, что это нужно делать на SQL'е, но можно сделать отдельным запросом и объединить через union

Ответить

Вернуться в «PostGIS/PostgreSQL»

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и 5 гостей