1

PostgreSQL 9.5.1 with PostGIS 2.0 extension

This query is taking > 15 min to run:

with    cte_dumppoly as (select (st_dump(geom)).geom as geom, dccode from public."BJW49"),
    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))

select st_buffer(geom::geography, 15.24)::geometry as geom from cte_intersection

I'm just trying to buffer the intersecting streets by 15.24 meters. Any suggestions to speed this up?

info on data:

select st_area(geom::geography, true) from public."BJW49" = 24,878,964 

sum(st_npoints(geom)) from public."BJW49" = 723,788

select sum(st_npoints(geom)) from public.streets; = 6,287,548

I've already created indexed/clustered/vacuumed/analyzed the tables FYI.

  • are you using spatial indexing? revenant.ca/www/postgis/workshop/indexing.html – julsbreakdown Apr 20 '17 at 12:39
  • 1
    How about editing this part a bit where st_intersects(a.geom,b.geom)) AND a.geom && b.geom? – user30184 Apr 20 '17 at 12:41
  • 1
    @julsbreakdown Yes, I have created indexes, vacuumed, clustered, and analyzed all the tables. – Matt Apr 20 '17 at 12:42
  • @user30184 didn't really make a difference when I added that... – Matt Apr 20 '17 at 13:03
  • 1
    However, as @user30184 has said, you might have an issue with ST_Dump. Possibly better to ST_Dump to a new table and then index that. Also, please show your explain statement. – John Powell Apr 20 '17 at 14:20
1

I ended up getting with a colleague about this and he informed me that public."BJW49" was too large and I needed to break it up. That was why it was taking so long. Here is the query I ended up with:

with    cte_subdpoly as (select st_subdivide(geom) as geom from public."BJW49"),
        cte_intersection as (select st_intersection(a.geom,b.geom) as geom from public.streets a, cte_subdpoly 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."BJW_MockArea"(geom) select geom from cte_buffer;

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.