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.