[FOSSGIS-Talk] Hilfe gesucht bei Postgis spatial join
Sven Geggus
lists at fuchsschwanzdomain.de
Do Sep 27 15:08:26 CEST 2018
Felix Kunde <felix-kunde at gmx.de> wrote:
> Hm, die zusätzliche BBox-Filterung würde ich mit einem WITH-Block
> voranstellen, so dass du nur mit den gefilterten Polygonen in den JOIN mit
> den Punkten gehst.
Genau mit dem Bounding Box Ding habe ich dann wohl noch ein Problem.
Das hier läuft brauchbar schnell:
SELECT
poly.osm_id AS osm_id,
ST_Centroid(poly.way) AS way,
poly.tags AS tags,
bool_or(COALESCE(ST_Intersects(poly.way, pt.way) AND pt.tags->'amenity' =
'toilets', FALSE)) AS toilets,
bool_or(COALESCE(ST_Intersects(poly.way, pt.way) AND pt.tags->'amenity' =
'shower', FALSE)) AS shower,
bool_or(COALESCE(ST_Intersects(poly.way, pt.way) AND pt.tags->'amenity' =
'fast_food', FALSE)) AS fast_food,
bool_or(COALESCE(ST_Intersects(poly.way, pt.way) AND pt.tags->'amenity' =
'restaurant', FALSE)) AS restaurant
FROM
planet_osm_polygon AS poly
LEFT JOIN planet_osm_hstore_point AS pt ON poly.way && pt.way
WHERE ((poly.tags ? 'tourism') AND (poly.tags->'tourism' = 'camp_site')) and poly.way && ST_Transform(ST_SetSRID('BOX3D(-1.27 44.54, -1.18 44.61)'::box3d,4326),900913)
GROUP BY
poly.osm_id, poly.way, poly.tags;
GroupAggregate (cost=64559.58..197859.20 rows=1 width=305) (actual time=182.114..182.597 rows=5 loops=1)
Group Key: planet_osm_hstore_polygon.osm_id, planet_osm_hstore_polygon.way, planet_osm_hstore_polygon.tags
-> Sort (cost=64559.58..64876.96 rows=126952 width=386) (actual time=181.907..181.913 rows=57 loops=1)
Sort Key: planet_osm_hstore_polygon.osm_id, planet_osm_hstore_polygon.way, planet_osm_hstore_polygon.tags
Sort Method: quicksort Memory: 83kB
-> Nested Loop Left Join (cost=5587.68..53797.91 rows=126952 width=386) (actual time=181.306..181.768 rows=57 loops=1)
-> Bitmap Heap Scan on planet_osm_hstore_polygon (cost=5160.87..5168.90 rows=1 width=269) (actual time=181.172..181.233 rows=5 loops=1)
Recheck Cond: ((way && '01030000A031BF0D000100000005000000620FC606FE4101C1E7AA5812542B55410000000000000000620FC606FE4101C11C62EBE00236554100000000000000005B0C3BFEE70800C11C62EBE00236554100000000000000005B0C3BFEE70800C1E7AA5812542B55410000000000000000620FC606FE4101C1E7AA5812542B55410000000000000000'::geometry) AND (tags ? 'tourism'::text))
Filter: ((tags -> 'tourism'::text) = 'camp_site'::text)
Rows Removed by Filter: 1
Heap Blocks: exact=6
-> BitmapAnd (cost=5160.87..5160.87 rows=2 width=0) (actual time=181.137..181.137 rows=0 loops=1)
-> Bitmap Index Scan on planet_osm_hstore_polygon_index (cost=0.00..111.02 rows=2462 width=0) (actual time=1.824..1.824 rows=1393 loops=1)
Index Cond: (way && '01030000A031BF0D000100000005000000620FC606FE4101C1E7AA5812542B55410000000000000000620FC606FE4101C11C62EBE00236554100000000000000005B0C3BFEE70800C11C62EBE00236554100000000000000005B0C3BFEE70800C1E7AA5812542B55410000000000000000620FC606FE4101C1E7AA5812542B55410000000000000000'::geometry)
-> Bitmap Index Scan on planet_osm_hstore_polygon_tags_index (cost=0.00..5049.60 rows=457280 width=0) (actual time=179.281..179.281 rows=481513 loops=1)
Index Cond: (tags ? 'tourism'::text)
-> Bitmap Heap Scan on planet_osm_hstore_point pt (cost=426.81..48502.06 rows=12695 width=117) (actual time=0.072..0.099 rows=11 loops=5)
Recheck Cond: (planet_osm_hstore_polygon.way && way)
Heap Blocks: exact=18
-> Bitmap Index Scan on planet_osm_hstore_point_index (cost=0.00..423.63 rows=12695 width=0) (actual time=0.059..0.059 rows=11 loops=5)
Index Cond: (planet_osm_hstore_polygon.way && way)
Planning time: 2.021 ms
Execution time: 182.778 ms
Als view geht das dann leider nicht mehr:
CREATE OR REPLACE VIEW poi_campsites AS
SELECT
poly.osm_id AS osm_id,
ST_Centroid(poly.way) AS way,
poly.tags AS tags,
bool_or(COALESCE(ST_Intersects(poly.way, pt.way) AND pt.tags->'amenity' =
'toilets', FALSE)) AS toilets,
bool_or(COALESCE(ST_Intersects(poly.way, pt.way) AND pt.tags->'amenity' =
'shower', FALSE)) AS shower,
bool_or(COALESCE(ST_Intersects(poly.way, pt.way) AND pt.tags->'amenity' =
'fast_food', FALSE)) AS fast_food,
bool_or(COALESCE(ST_Intersects(poly.way, pt.way) AND pt.tags->'amenity' =
'restaurant', FALSE)) AS restaurant
FROM
planet_osm_polygon AS poly
LEFT JOIN planet_osm_hstore_point AS pt ON poly.way && pt.way
WHERE ((poly.tags ? 'tourism') AND (poly.tags->'tourism' = 'camp_site'))
GROUP BY
poly.osm_id, poly.way, poly.tags;
explain analyze select * from poi_campsites where way &&
ST_Transform(ST_SetSRID('BOX3D(-1.27 44.54, -1.18 44.61)'::box3d,4326),900913);
Subquery Scan on poi_campsites (cost=41128163.32..102046296.60 rows=457 width=89) (actual time=6411.384..6411.877 rows=5 loops=1)
-> GroupAggregate (cost=41128163.32..102046292.03 rows=457 width=305) (actual time=6411.383..6411.872 rows=5 loops=1)
Group Key: planet_osm_hstore_polygon.osm_id, planet_osm_hstore_polygon.way, planet_osm_hstore_polygon.tags
-> Sort (cost=41128163.32..41273206.47 rows=58017260 width=386) (actual time=6411.219..6411.226 rows=57 loops=1)
Sort Key: planet_osm_hstore_polygon.osm_id, planet_osm_hstore_polygon.way, planet_osm_hstore_polygon.tags
Sort Method: quicksort Memory: 83kB
-> Nested Loop Left Join (cost=5802.59..23335182.28 rows=58017260 width=386) (actual time=6030.722..6411.066 rows=57 loops=1)
-> Bitmap Heap Scan on planet_osm_hstore_polygon (cost=5381.79..1601893.38 rows=457 width=269) (actual time=6030.604..6410.332 rows=5 loops=1)
Recheck Cond: (tags ? 'tourism'::text)
Filter: ((st_centroid(way) && '01030000A031BF0D000100000005000000620FC606FE4101C1E7AA5812542B55410000000000000000620FC606FE4101C11C62EBE00236554100000000000000005B0C3BFEE70800C11C62EBE00236554100000000000000005B0C3BFEE70800C1E7AA5812542B55410000000000000000620FC606FE4101C1E7AA5812542B55410000000000000000'::geometry) AND ((tags -> 'tourism'::text) = 'camp_site'::text))
Rows Removed by Filter: 360002
Heap Blocks: exact=361065
-> Bitmap Index Scan on planet_osm_hstore_polygon_tags_index (cost=0.00..5381.67 rows=457290 width=0) (actual time=179.533..179.533 rows=481517 loops=1)
Index Cond: (tags ? 'tourism'::text)
-> Bitmap Heap Scan on planet_osm_hstore_point pt (cost=420.80..47429.48 rows=12695 width=117) (actual time=0.101..0.129 rows=11 loops=5)
Recheck Cond: (planet_osm_hstore_polygon.way && way)
Heap Blocks: exact=18
-> Bitmap Index Scan on planet_osm_hstore_point_index (cost=0.00..417.63 rows=12695 width=0) (actual time=0.076..0.076 rows=11 loops=5)
Index Cond: (planet_osm_hstore_polygon.way && way)
Planning time: 6.408 ms
Execution time: 6412.063 ms
Lese ich das richtig, dass die Datenbank im zweiten Fall den index auf der
tags spalte verwendet statt dem Geometrieindex? Denn das wäre natürlch
tödlich.
Gruss
Sven
--
"Thinking of using NT for your critical apps?
Isn't there enough suffering in the world?"
(Advertisement of Sun Microsystems in Wall Street Journal)
/me is giggls at ircnet, http://sven.gegg.us/ on the Web
Mehr Informationen über die Mailingliste FOSSGIS-Talk-Liste