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:

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. Sep 20, 2017 at 7:46
  • @MichalZimmermann I just added the explain
    – PieterB
    Sep 20, 2017 at 7:55

1 Answer 1

from gemeentegrenzen g
join (
        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, 2017 at 12:21
  • OK then, some of your geometries are not simple. Use ST_CollectionExtract to extract the proper geometry types. Sep 20, 2017 at 13:14
  • See here: gis.stackexchange.com/questions/189665/…
    – pLumo
    Sep 20, 2017 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, 2017 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. Sep 20, 2017 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.