У меня есть база данных Postgres с установленным расширением postgis, и она заполняется данными открытой карты улиц.
Со следующим оператором SQL:
SELECT
l.osm_id,
sum(
st_area(st_intersection(ST_Buffer(l.way, 30), p.way))
/
st_area(ST_Buffer(l.way, 30))
) as green_fraction
FROM planet_osm_line AS l
INNER JOIN planet_osm_polygon AS p ON ST_Intersects(l.way, ST_Buffer(p.way,30))
WHERE p.natural in ('water') or p.landuse in ('forest') GROUP BY l.osm_id;
Я рассчитываю «зеленый» балл.
Моя цель — создать «зеленую» оценку для каждого osm_id.
Что значит; какая часть дороги проходит рядом с водой, лесом или чем-то подобным.
Для этого:
Я создаю 30-метровый буфер вокруг каждого пути и вычисляю пересечение между этим буфером и любыми зелеными объектами поблизости.
Я использую «зеленые объекты» для обозначения полигонов в базе данных OpenStreetMap, таких как парк.
Можно ли ускорить этот расчет?
Одна вещь, которую я хотел создать, это создать 2 индекса в надежде ускорить расчет:
CREATE INDEX way_index_2 on planet_osm_polygon USING gist(way) WHERE "natural" IN ('water','wood','forest','hill','valley');
CREATE INDEX way_index_3 on planet_osm_polygon USING gist(way) WHERE "landuse" IN ('forest');
Вот «объяснение» к этому утверждению:
EXPLAIN (ANALYZE, BUFFERS) SELECT
l.osm_id,
sum(
st_area(st_intersection(ST_Buffer(l.way, 30), p.way))
/
st_area(ST_Buffer(l.way, 30))
) as green_fraction
FROM planet_osm_line AS l
INNER JOIN planet_osm_polygon AS p ON ST_Intersects(l.way, ST_Buffer(p.way,30))
WHERE p.natural in ('water') or p.landuse in ('forest') GROUP BY l.osm_id limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=17816.83..133789235.22 rows=1 width=16) (actual time=1575643.737..1575651.862 rows=1 loops=1)
Buffers: shared hit=816315 read=221878, temp read=313661 written=73938
-> GroupAggregate (cost=17816.83..1435958589062981.00 rows=10734420 width=16) (actual time=1575643.723..1575651.847 rows=1 loops=1)
Group Key: l.osm_id
Buffers: shared hit=816315 read=221878, temp read=313661 written=73938
-> Nested Loop (cost=17816.83..1433802261939271.50 rows=28652652777 width=448) (actual time=978502.788..1575648.857 rows=8 loops=1)
Join Filter: st_intersects(l.way, st_buffer(p.way, '30'::double precision, ''::text))
Rows Removed by Join Filter: 6528525
Buffers: shared hit=816315 read=221878, temp read=313661 written=73938
-> Index Scan using osm_id_idx on planet_osm_line l (cost=0.44..1242021.57 rows=22671610 width=247) (actual time=5.963..6.181 rows=6 loops=1)
Buffers: shared hit=5 read=3
-> Materialize (cost=17816.39..1445364.98 rows=1263812 width=201) (actual time=85.181..4605.942 rows=1088089 loops=6)
Buffers: shared hit=380236 read=220348, temp read=313661 written=73938
-> Gather (cost=17816.39..1403253.92 rows=1263812 width=201) (actual time=510.609..1066.182 rows=1250378 loops=1)
Workers Planned: 4
Workers Launched: 4
Buffers: shared hit=380236 read=220348
-> Parallel Bitmap Heap Scan on planet_osm_polygon p (cost=16816.39..1275872.72 rows=315953 width=201) (actual time=447.168..9410.838 rows=250076 loops=5)
Recheck Cond: (("natural" = ANY ('{water,wood,forest,hill,valley}'::text[])) OR (landuse = 'forest'::text))
Rows Removed by Index Recheck: 2554266
Filter: (("natural" = 'water'::text) OR (landuse = 'forest'::text))
Rows Removed by Filter: 53217
Heap Blocks: lossy=1
Buffers: shared hit=380236 read=220348
-> BitmapOr (cost=16816.39..16816.39 rows=1554297 width=0) (actual time=491.891..491.893 rows=0 loops=1)
Buffers: shared hit=7797
-> Bitmap Index Scan on way_index_2 (cost=0.00..7822.79 rows=750359 width=0) (actual time=413.690..413.690 rows=737741 loops=1)
Buffers: shared hit=3758
-> Bitmap Index Scan on way_index_3 (cost=0.00..8361.69 rows=803938 width=0) (actual time=78.198..78.198 rows=783702 loops=1)
Buffers: shared hit=4039
Planning Time: 0.315 ms
Execution Time: 1575673.609 ms
(32 rows)
ОБНОВЛЯТЬ:
Вот краткая схема обеих таблиц;
Table
"public.planet_osm_line"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------------+---------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
osm_id | bigint | | | | plain | | |
access | text | | | | extended | | |
addr:housename | text | | | | extended | | |
addr:housenumber | text | | | | extended | | |
addr:interpolation | text | | | | extended | | |
admin_level | text | | | | extended | | |
....
natural | text | | | | extended | | |
Indexes:
"highway_idx" btree (highway)
"motorway_idx" gist (way) WHERE highway = 'motorway'::text
"motorway_trunk_primary_secondary_tertiary_unclassified_idx" gist (way) WHERE highway = ANY (ARRAY['motorway'::text, 'trunk'::text, 'primary'::text, 'secondary'::text, 'tertiary'::text, 'unclassified'::text])
"name_idx" btree (name)
"osm_id_idx" btree (osm_id)
"planet_osm_line_osm_id_idx" btree (osm_id)
"planet_osm_line_way_idx" gist (way)
"primary_idx" gist (way) WHERE highway = 'primary'::text
"primary_secondary_idx" gist (way) WHERE highway = ANY (ARRAY['primary'::text, 'secondary'::text])
"primary_secondary_tertiary_idx" gist (way) WHERE highway = ANY (ARRAY['primary'::text, 'secondary'::text, 'tertiary'::text])
"primary_secondary_tertiary_unclassified_idx" gist (way) WHERE highway = ANY (ARRAY['primary'::text, 'secondary'::text, 'tertiary'::text, 'unclassified'::text])
"secondary_idx" gist (way) WHERE highway = 'secondary'::text
"secondary_tertiary_idx" gist (way) WHERE highway = ANY (ARRAY['secondary'::text, 'tertiary'::text])
"tertiary_idx" gist (way) WHERE highway = 'tertiary'::text
"tertiary_secondary_idx" gist (way) WHERE highway = ANY (ARRAY['tertiary'::text, 'unclassified'::text])
"trunk_idx" gist (way) WHERE highway = 'trunk'::text
"trunk_primary_secondary_tertiary_unclassified_idx" gist (way) WHERE highway = ANY (ARRAY['trunk'::text, 'primary'::text, 'secondary'::text, 'tertiary'::text, 'unclassified'::text])
"unclassified_idx" gist (way) WHERE highway = 'unclassified'::text
"way_idx" gist (way)
"way_index_1" gist (way)
"way_index_4" gist (way) WHERE "natural" = ANY (ARRAY['water'::text, 'wood'::text, 'forest'::text, 'hill'::text, 'valley'::text])
"way_index_5" gist (way) WHERE landuse = 'forest'::text
Triggers:
planet_osm_line_osm2pgsql_valid BEFORE INSERT OR UPDATE ON planet_osm_line FOR EACH ROW EXECUTE FUNCTION planet_osm_line_osm2pgsql_valid()
Access method: heap
И:
Table "public.planet_osm_polygon"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------------+-------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
osm_id | bigint | | | | plain | | |
access | text | | | | extended | | |
addr:housename | text | | | | extended | | |
addr:housenumber | text | | | | extended | | |
addr:interpolation | text | | | | extended | | |
admin_level | text | | | | extended | | |
aerialway | text | | | | extended | | |
aeroway | text | | | | extended | | |
amenity | text | | | | extended | | |
area | text | | | | extended | | |
barrier | text | | | | extended | | |
landuse | text | | | | extended | | |
Indexes:
"fuel_toilet_parking_restaurant_idex" gist (way) WHERE amenity = ANY (ARRAY['fuel'::text, 'toilets'::text, 'parking'::text, 'restaurant'::text, 'cafe'::text, 'pub'::text, 'ice_cream'::text, 'biergarten'::text])
"planet_osm_polygon_osm_id_idx" btree (osm_id)
"planet_osm_polygon_way_idx" gist (way)
"viewpoint_attraction_guest_house_idex" gist (way) WHERE tourism = ANY (ARRAY['viewpoint '::text, 'attraction'::text, 'guest_house'::text])
"way_index_2" gist (way) WHERE "natural" = ANY (ARRAY['water'::text, 'wood'::text, 'forest'::text, 'hill'::text, 'valley'::text])
"way_index_3" gist (way) WHERE landuse = 'forest'::text
Triggers:
planet_osm_polygon_osm2pgsql_valid BEFORE INSERT OR UPDATE ON planet_osm_polygon FOR EACH ROW EXECUTE FUNCTION planet_osm_polygon_osm2pgsql_valid()
Access method: heap
ОБНОВЛЯТЬ:
Planet_osm_polygon
wood | text | | | | extended | | |
z_order | integer | | | | plain | | |
way_area | real | | | | plain | | |
way | geometry(Geometry,3857) | | | | main | | |
way_buffer_30 | geometry(Polygon) | | | | external | | |
way_buffer_30_area | numeric | | | generated always as (st_area(way_buffer_30)) stored | main | | |
И
planet_osm_line
way_area | real | | | | plain | | |
way | geometry(LineString,3857) | | | | main | | |
Привет, Адриан, я обновил вопрос выводом EXPLAIN (ANALYZE, BUFFERS).
Попробуйте обычный индекс btree на planet_osm_polygon(natural, landuse)
для предложения where
и пространственный индекс на planet_osm_line using gist(way)
для соединения. Индекс planet_osm_line(osm_id)
может помочь группе. Вы также можете попробовать dba.SE и gis.SE.
Пространственный индекс на planet_osm_line уже на месте. СОЗДАЙТЕ ИНДЕКС way_idx ON planet_osm_line, ИСПОЛЬЗУЯ gist(way); Также указатель на planet_osm_line(osm_id). Насколько я понимаю, они не используются или игнорируются.
Вы уже запускали АНАЛИЗ на столах?
Ага. Мне было интересно, могу ли я ограничить область bbox чем-нибудь с помощью planet_osm_line.way && ST_Transform(ST_MakeEnvelope(9.07805053255787, 47.4781579714091, 12.0860304569466, 49.478071667293,4326),3857);"?
@Andreas Можете ли вы показать полную схему каждой таблицы? Это скажет нам, что такое индексы и типы.
@Schwern Конечно: я обновил вопрос, включив в него большую часть схемы таблиц.
@Andreas Андреас Не могли бы вы отредактировать DDL, чтобы показать определения столбцов planet_osm_polygon.way
и planet_osm_line.way
? Кроме того, похоже, что вы создаете довольно много индексов - вы можете удалить те, которые не помогают каким-либо часто выполняемым запросам. Если вы часто выполняете сложные запросы, которые никогда не повторяете, вы можете создавать собственные клоны этих таблиц с пользовательскими индексами. Нередко при работе с огромными наборами геометрий быстрее построить и настроить клон, чем повторно использовать исходную таблицу.
Упс, извините, упустил важный момент :) Я обновил вопрос, включив в него определения столбцов planet_osm_polygon.way и planet_osm_line.way.
Может быть, я чего-то не понимаю в основных указателях, но многие из этих указателей кажутся избыточными.
Всегда помните о ST_Subdivide при работе с большими геометриями: stackoverflow.com/a/68622815/2275388 ура!
На самом деле это не столько ответ, сколько куча комментариев, слишком длинных для формата комментариев:
alter system set default_toast_compression=lz4;
alter table planet_osm_polygon
add column way_buffer_30 geometry,
alter column way_buffer_30 set storage external,
add column way_buffer_30_area numeric
generated always as (st_area(way_buffer_30)) stored;
update planet_osm_polygon
set way_buffer_30=st_buffer(way,30,'quad_segs=1');
create index osmp_way_buffer_30_gix on planet_osm_polygon
using gist(way_buffer_30) with (fillfactor=100);
cluster verbose planet_osm_polygon using osmp_way_buffer_30_gix;
alter table planet_osm_line
add column way_buffer_30 geometry,
alter column way_buffer_30 set storage external,
add column way_buffer_30_area numeric
generated always as (st_area(way_buffer_30)) stored;
update planet_osm_line
set way_buffer_30=st_buffer(way,30,'quad_segs=1');
create index osml_way_buffer_30_gix on planet_osm_line
using gist(way_buffer_30) with (fillfactor=100);
cluster verbose planet_osm_polygon using osml_way_buffer_30_gix;
SELECT
l.osm_id,
sum(st_area(st_intersection(l.way_buffer_30, p.way))
/ l.way_buffer_30_area
) as green_fraction
FROM planet_osm_line AS l
INNER JOIN planet_osm_polygon AS p ON ST_Intersects(l.way, p.way_buffer_30)
WHERE p.natural in ('water') or p.landuse in ('forest') GROUP BY l.osm_id;
default_toast_compression=lz4
Вы работаете с полигонами, которые, скорее всего, будут сжаты и обработаны TOAST. По умолчанию default_toast_compression=pglz
обычно медленнее, чем lz4
. Обратите внимание, что вам необходимо принудительно перезаписать эти таблицы/столбцы после изменения параметра, иначе он будет применяться по умолчанию только с этого момента, не затрагивая ничего задним числом.quad_segs=8
может привести к тому, что ваш буфер будет иметь в 8 раз больше вершин, чем вход.fillfactor=90
предназначен для учета новых строк, вставленных в таблицу).ST_Buffer()
. То же самое касается ST_Transform()
— примените его к столбцу перед индексацией, а не к запросу.INNER JOIN planet_osm_polygon AS p ON ST_DWithin(l.way,p.way,30)
create index way_buffer_30_gix on planet_osm_polygon
using gist(way_buffer_30) with (fillfactor=100)
where natural='water' or landuse='forest';
where
.Спасибо, я попробую ваши предложения и сообщу. Да, это разовая операция. Я использую SELECT PostGIS_Version(); 3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 (1 строка)
У меня возникнет ошибка, если я выполню шаг 3. (ОШИБКА: тип геометрии (MultiPolygon) не соответствует типу столбца (Polygon))
Вы можете обойти это, удалив (POLYGON)
из определения столбца, сделав его общим geometry
. Это также хорошее время, чтобы перепроверить, должен ли ваш ввод быть MULTILINESTRING для начала, и если вы обычно обрабатываете его как таковой.
Вы должны запустить
EXPLAIN (ANALYZE, BUFFERS) SELECT ...
и добавить этот вывод в свой вопрос.