0

I'm trying to achieve a multiple-table intersect with PostGIS while retaining the geom for each shape.

For any given property shape, I want individual cuts of that shape split apart by different characteristics of the below layers.

So for example:

480128 | 3Sh3W | Otamatea | MOZ | S | 2 | B' | 5 | P 5 | geom
480128 | 3Sh3W | Turangai| MOZ | L/S | 2 | B' | 5 | P 5 | geom
480128 | 3Sh3W | Makahu | ZOT | L/S | 2 | B' | 5 | P 5 | geom

The SQL I've used so far is:

CREATE TABLE sd_processed AS 
SELECT 
    A.title_no, 
    B.erosion, 
    C.series, 
    C.domnzsc nzsc,
    C.ps, 
    D.prd_class as prd, 
    E.slope as slope, 
    F.drain_clas as drain,
    G.veg,
    ST_DUMP(ST_INTERSECTION(
        A.geom, 
        ST_INTERSECTION(A.geom,
            ST_INTERSECTION(B.geom, 
                ST_INTERSECTION(C.geom,
                    ST_INTERSECTION(D.geom, 
                        ST_INTERSECTION(E.geom, 
                            ST_INTERSECTION(F.geom, G.geom)
                        )
                    )
                )
            )
        )
    )) geom
FROM
    sd_title A,
    sd_erosion B,
    sd_particle_size C,
    sd_potential_rooting_depth D,
    sd_slope E,
    sd_soil_drainage F,
    sd_vegetation G
WHERE
    ST_INTERSECTS(A.geom, B.geom) = true
    AND ST_INTERSECTS(A.geom, C.geom) = true
    AND ST_INTERSECTS(A.geom, D.geom) = true
    AND ST_INTERSECTS(A.geom, E.geom) = true
    AND ST_INTERSECTS(A.geom, F.geom) = true
    AND ST_INTERSECTS(A.geom, G.geom) = true
    AND ST_INTERSECTS(B.geom, C.geom) = true
    AND ST_INTERSECTS(B.geom, D.geom) = true
    AND ST_INTERSECTS(B.geom, E.geom) = true
    AND ST_INTERSECTS(B.geom, F.geom) = true
    AND ST_INTERSECTS(B.geom, G.geom) = true
    AND ST_INTERSECTS(C.geom, D.geom) = true
    AND ST_INTERSECTS(C.geom, E.geom) = true
    AND ST_INTERSECTS(C.geom, F.geom) = true
    AND ST_INTERSECTS(C.geom, G.geom) = true
    AND ST_INTERSECTS(D.geom, E.geom) = true
    AND ST_INTERSECTS(D.geom, F.geom) = true
    AND ST_INTERSECTS(D.geom, G.geom) = true
    AND ST_INTERSECTS(E.geom, F.geom) = true
    AND ST_INTERSECTS(E.geom, G.geom) = true
    AND ST_INTERSECTS(F.geom, G.geom) = true
LIMIT 1000;
  • 3
    Please use ANSI SQL's JOIN operator. The massive FROM list has been poor form for several decades now. – Vince Mar 13 at 23:48
  • Could you add a screenshot showing what you want? – BERA Mar 14 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.