-1
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ Did you profile using queryplans? \$\endgroup\$ – πάντα ῥεῖ Apr 19 '17 at 19:39
  • \$\begingroup\$ I looked at at the explain tab afterwards and it said the insert statement was using the most cost. \$\endgroup\$ – Matt Apr 19 '17 at 20:09
2
\$\begingroup\$

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;
\$\endgroup\$

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.