1

I have a materialized view in postgis with linear referenced roads and a table with boundaries.

I want to sum all lines per boundary based on 2 categories. For that: I add a column (tw) which defines the categorie (0 or 1).

I made following query:

select
g.naam,
case when t.tw = 1 then sum(st_length(t.geom)) end as traag,
case when t.tw = 0 then sum(st_length(t.geom)) end as snel

from gemeentegrenzen g

join (select geom, case when morf in (113, 114, 125) or (morf = 103 and type = 2) then 1 else 0 end as tw

from wegenregister.mv_wegsegment_wegverharding_lrs) t on st_intersects(st_buffer(g.geom, 0), t.geom)

group by g.naam, t.tw
;

This takes me a few hours.

In QGIS I made a model that does exact the same things based on the same layers. This process takes about 30-40 minutes.

What is wrong with the sql-query?

when running explain:

"GroupAggregate  (cost=243596190.39..245430994.97 rows=22176 width=283)"
"  Group Key: g.naam, (CASE WHEN ((mv_wegsegment_wegverharding_lrs.morf = ANY ('{113,114,125}'::integer[])) OR ((mv_wegsegment_wegverharding_lrs.morf = 103) AND (mv_wegsegment_wegverharding_lrs.type = 2::numeric))) THEN 1 ELSE 0 END)"
"  ->  Sort  (cost=243596190.39..243858174.65 rows=104793704 width=283)"
"        Sort Key: g.naam, (CASE WHEN ((mv_wegsegment_wegverharding_lrs.morf = ANY ('{113,114,125}'::integer[])) OR ((mv_wegsegment_wegverharding_lrs.morf = 103) AND (mv_wegsegment_wegverharding_lrs.type = 2::numeric))) THEN 1 ELSE 0 END)"
"        ->  Nested Loop  (cost=0.00..173759771.26 rows=104793704 width=283)"
"              Join Filter: st_intersects(st_buffer(g.geom, 0::double precision), mv_wegsegment_wegverharding_lrs.geom)"
"              ->  Seq Scan on gemeentegrenzen g  (cost=0.00..62.08 rows=308 width=20402)"
"              ->  Materialize  (cost=0.00..96126.77 rows=1020718 width=274)"
"                    ->  Seq Scan on mv_wegsegment_wegverharding_lrs  (cost=0.00..53144.18 rows=1020718 width=274)"
  • Could you share the EXPLAIN result? I don't think PostGIS is using spatial index with ST_Buffer. – Michal Zimmermann Sep 20 '17 at 7:46
  • @MichalZimmermann I just added the explain – PieterB Sep 20 '17 at 7:55
2
select
    g.naam,
    t.tw,
    sum(st_length(t.geom))
from gemeentegrenzen g
join (
    select 
        geom, 
        case when morf in (113, 114, 125) or (morf = 103 and type = 2) then 1 else 0 end as tw
        from wegenregister.mv_wegsegment_wegverharding_lrs
    ) t on st_intersects(g.geom, t.geom)
group by g.naam, t.tw
;

I'll go with these two tweaks:

  • don't use CASE with SUM just yet
  • don't use ST_Buffer inside ST_Intersects (the index is not being used)

How many rows do the tables have? I'll try adding primary key/index to g.naam and maybe to morf (depending on values in that column).

Could you try with this query (or send the EXPLAIN result at least)?

  • without st_buffer I get following error: Relate Operation called with a LWGEOMCOLLECTION type. This is unsupported. – PieterB Sep 20 '17 at 12:21
  • OK then, some of your geometries are not simple. Use ST_CollectionExtract to extract the proper geometry types. – Michal Zimmermann Sep 20 '17 at 13:14
  • See here: gis.stackexchange.com/questions/189665/… – pLumo Sep 20 '17 at 14:01
  • Instead of extracting the GeometryCollection within the query, you can use st_intersects(ST_CollectionHomogenize(g.geom), t.geom) and have an index using gist(ST_CollectionHomogenize(geom)). – pLumo Sep 20 '17 at 14:03
  • @RoVo when you provide heterogenous collection (Point and Polygon for example), you get the same collection back. I don't think that's what PieterB is looking for. – Michal Zimmermann Sep 20 '17 at 14:08

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.