4

I have a query to calculate how many crimes (of a certain type) are rural and urban:

select st_intersects(c.wkb_geometry,u.wkb_geometry) as ur,count(c.*) 
from crime.street_crime c, meridian.urban as u
where c.crime_type = 'Vehicle crime' 
group by ur;

This takes several seconds to run on a smallish dataset, explain says:

HashAggregate  (cost=147319.21..147319.73 rows=2 width=7043)
  Group Key: ((c.wkb_geometry && u.wkb_geometry) AND _st_intersects(c.wkb_geometry, u.wkb_geometry))
  ->  Nested Loop  (cost=74.84..144643.18 rows=535205 width=7043)
        ->  Bitmap Heap Scan on street_crime c  (cost=74.84..2772.55 rows=2893 width=233)
              Recheck Cond: ((crime_type)::text = 'Vehicle crime'::text)
              ->  Bitmap Index Scan on street_crime_type_idx  (cost=0.00..74.11 rows=2893 width=0)
                    Index Cond: ((crime_type)::text = 'Vehicle crime'::text)
        ->  Materialize  (cost=0.00..41.77 rows=185 width=6810)"
              ->  Seq Scan on urban u  (cost=0.00..40.85 rows=185 width=6810)

so it is ignoring my spatial index, that I expected it to make use of.

If I rewrite the query to:

select st_intersects(c.wkb_geometry,u.wkb_geometry) as ur,count(c.*) 
from crime.street_crime c, 
(select st_union(wkb_geometry) as wkb_geometry from meridian.urban) as u
where c.crime_type = 'Vehicle crime' 
group by ur;

it takes miliseconds, explain says:

HashAggregate  (cost=3587.76..3588.28 rows=2 width=265)
  Group Key: ((c.wkb_geometry && (st_union(urban.wkb_geometry))) AND _st_intersects(c.wkb_geometry, (st_union(urban.wkb_geometry))))
  ->  Nested Loop  (cost=116.15..3573.29 rows=2893 width=265)
        ->  Aggregate  (cost=41.31..41.32 rows=1 width=6810)
              ->  Seq Scan on urban  (cost=0.00..40.85 rows=185 width=6810)
        ->  Bitmap Heap Scan on street_crime c  (cost=74.84..2772.55 rows=2893 width=233)
              Recheck Cond: ((crime_type)::text = 'Vehicle crime'::text)
              ->  Bitmap Index Scan on street_crime_type_idx  (cost=0.00..74.11 rows=2893 width=0)
                    Index Cond: ((crime_type)::text = 'Vehicle crime'::text)

This makes no sense to me, as by unioning the geometry I am effectively defeating any spatial indexing as the majority of my points will fall in the bounding box and need a more expensive point in polygon check but it is much faster.

Why doesn't my spatial index make the naive query work fast?

  • Can you confirm that the two explain outputs are identical? – Stev_k Oct 31 '18 at 15:42
  • no, stupid cut and paste from a remote machine is intermittent - fixed now. – Ian Turton Oct 31 '18 at 16:07
  • does it help putting the ST_Intersects into the WHERE clause as well? I don't think they will be filtered properly otherwise, and you might be doing some sort of cross-join – Stev_k Oct 31 '18 at 16:25
  • but I need both values, I'd only get one if I add it to the WHERE – Ian Turton Oct 31 '18 at 16:28
  • Well then maybe it's quicker to do two seperate CTE queries - one for the total and one for just within urban areas then join them together, minus the urban areas from the total and get the total urban and total rural? That's my hunch. See if it works for just filtering out crimes within the urban areas – Stev_k Oct 31 '18 at 16:32
3

I have done a test on some arbitrary points and polygons that I have.

Query 1

select st_intersects(pnt.geom,poly.geom) as ur,count(pnt.*) 
from poly, pnt
where pnt.desc = 'Dwelling'
group by ur;

I didn't have the patience for this to finish.

Query 2

select st_intersects(pnt.geom,poly.geom) as ur,count(pnt.*) 
from poly, pnt
where pnt.desc = 'Dwelling'
AND st_intersects(pnt.geom,poly.geom)
group by ur;

This takes sub second time, and uses the index

Query 3

select st_intersects(pnt.geom,poly.geom) as ur,count(pnt.*) 
from pnt, (select st_union(geom) as geom from 
poly) as poly
where pnt.desc = 'Dwelling'
group by ur;

This takes around a minute, longer than the indexed query, but much shorter than the first query.

In conclusion, the spatial index is only used when it is a filter in the query. The reason Query 3 is shorter than Query 1 is I think that the union query is cutting down the combinations, so the select st_intersects on its own without it as a filter seems to be doing a cross-join.

If you need to get the figures within and without of the polygon, I would do something like the below:

Query 4

WITH all_points as (
select 1 as id, count(*)
from pnt
where pnt.desc = 'Dwelling'
),
urban_points as (
select 1 as id, count(pnt.*) 
from poly, pnt
where pnt.desc = 'Dwelling'
AND st_intersects(pnt.geom,poly.geom)
)
select all_points.count - urban.count as rural, urban.count as urban
from all_points
JOIN urban_points urban
ON all_points.id = urban.id

The results of Query 3 and Query 4 are the same, but Query 4 is far quicker

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.