4

I can observe a strange behavior with my PostGIS query. I am trying to accomplish the following:

  • I have one polygon layer
  • I have 2 different line layers
    • layer with horizontal lines
    • layer with 45° lines
  • the goal is to cut the lines at the polygons to get the line parts of each line per polygon

Here is an example what that looks like: example

I am using a query which looks like this:

CREATE TABLE split AS
    ( SELECT row_number() over() AS id,
             a.id AS old_id,
             st_intersection(a.geom, b.geom) AS geom
     FROM lines a,
          polygons b
     WHERE st_intersects(a.geom, b.geom) ) ;

The query takes ~3 minutes for horizontal and vertical lines but 5+ hours (cancelled there) for 45° lines.

Can anyone think of a possible cause? All layers have spatial indices, pk and so on. I am using a Postgres 9.5 with PostGIS 2.2.

I am really clueless about that behavior.

6
  • 4
    Maybe because the bounding box is a big square instead of a thin rectangle ?
    – thibautg
    Commented Jun 6, 2016 at 18:40
  • I don't understand your comment. The rotated bounding box is of course a bit bigger than the original one.
    – andehhh
    Commented Jun 6, 2016 at 19:03
  • 2
    I don't know how PostGIS spatial indices work internally but I guess that they always work with a bounding box. For a vertical/horizontal line, the thickness will be really small (zero actually) and it will be very efficient to filter on the basis of the 'small' dimension. This is different for a line at 45 degrees. The bounding box will be a square. If your line is long, it could almost be your whole canvas, rendering the spatial index useless. Try splitting your 45 deg lines into smaller segments. But again I'm just guessing because I don't know how spatial indices work internally.
    – thibautg
    Commented Jun 6, 2016 at 19:11
  • 1
    ah okay, now I get the idea. that might indeed be a reason. gonna try that tomorrow and let you know! thank you!
    – andehhh
    Commented Jun 6, 2016 at 21:00
  • 3
    I would regenerate shorter lines (using something like this), which have a length based on the approximate average diameter of the polygons they intersect. This way the spatial index will actually work as expected. Then intersect them with ST_Union / ST_LineMerge to stitch together the line segments.
    – Mike T
    Commented Jun 7, 2016 at 3:18

1 Answer 1

3

I've reproduced your problem and did some benchmarking. I've created an intersection between a polygon layer and with horizontal lines (every km), then with lines at 45°, and then with the same lines at 45° split every km vertically (which equals to sqrt(2) = 1,414 km).

enter image description here

Here are the results of the intersection query:

  • horizontal : 8.7 sec (no index)
  • horizontal : 8.9 sec (index)
  • 45° : 60 sec (no index)
  • 45° : 61 sec (index)
  • 45° split : 120 sec (no index)
  • 45° split : 32.2 sec (index)

So it seems that the spatial index has not much impact with long lines. It gets more useful with shorter lines.

Your Answer

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

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