PostGIS Intersection weird behavior

Question

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:

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.

Maybe because the bounding box is a big square instead of a thin rectangle ? — thibautg, Jun 06 '16 at 18:40
I don't understand your comment. The rotated bounding box is of course a bit bigger than the original one. — andehhh, Jun 06 '16 at 19:03
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, Jun 06 '16 at 19:11
ah okay, now I get the idea. that might indeed be a reason. gonna try that tomorrow and let you know! thank you! — andehhh, Jun 06 '16 at 21:00
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, Jun 07 '16 at 03:18
I tried it now with shortened lines and the processing time went from 5h to 1h. So the bbox seems to be the problem here. I was able to do the computation over night now for every possible combination I needed. Thank you for your help!!! — andehhh, Jun 07 '16 at 09:24

thibautg · Accepted Answer · 2016-06-07T12:44:56.473

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).

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.

PostGIS Intersection weird behavior

1 Answers1