Count buffered streets that intersect buffered points using PostGIS

Question

PostgreSQL 9.5.1 with PostGIS 2.0 extension

This first section creates an intersection (clip) of the streets from another polygon (MTC15), then it buffers the intersection:

with cte_intersection as (select st_intersection(a.geom,b.geom) as geom from public.streets a, public."MTC15" b 
 where st_intersects(a.geom,b.geom)),
 cte_buffer as (select st_buffer(geom::geography, 15.24)::geometry as geom from cte_intersection)
insert into public."MTC_MockArea"(geom) select geom from cte_buffer;

This query counts the instances where a buffered point feature intersects the buffered, intersected street feature:

select count(distinct b.gid) as ticket_count from public."MTC_MockArea" a, public."points_buffered" b
 where st_intersects(a.geom,b.geom);

This is taking an extraordinary amount of time and I was wondering if anyone with some PostGIS knowledge could help speed up these queries. I've already created indexed/clustered/vacuumed/analyzed the tables FYI.

• πάντα ῥεῖ: Did you profile using queryplans?
• Matt: I looked at at the explain tab afterwards and it said the insert statement was using the most cost.

Answer 1

adding this CTE decreased the time from 1 hr 11 minutes to 2 minutes 62 seconds:

cte_dumppoly as (select (st_dump(geom)).geom as geom, dccode from public."MTC15")

making the final query look like:

with cte_dumppoly as (select (st_dump(geom)).geom as geom, dccode from public."MTC15"),
 cte_intersection as (select st_intersection(a.geom,b.geom) as geom from public.streets a, cte_dumppoly b 
 where st_intersects(a.geom,b.geom)),
 cte_buffer as (select st_buffer(geom::geography, 15.24)::geometry as geom from cte_intersection)
insert into public."MTC_MockArea"(geom) select geom from cte_buffer;