SQL

gistfile1.pls

SELECT path_overlap_id, overlap_geom, ST_Length(overlap_geom::geography) overlap_length, (ST_Length(overlap_geom::geography)/compare_length)*100 as overlap_percent
                    FROM (
SELECT po.path_overlap_id, 
            CASE WHEN po.bus_sub_path_id IS NULL AND po.other_sub_path_id IS NULL THEN
                -- WHEN INTERSECT BUS AND BUS
                ST_AsText(ST_LineMerge(ST_CollectionExtract(
                ST_Intersection(ST_Buffer(
                    CASE po.path_section WHEN 'D' THEN ST_SetSRID(bp_a.geom_diff, 4326)
                                        ELSE ST_SetSRID(bp_a.geom, 4326)
                    END
                        , {$bufferVal}, 'endcap=flat join=bevel'), ST_SetSRID(bp_b.geom, 4326)), 2)))
            WHEN po.bus_sub_path_id IS NULL THEN
                -- WHEN INTERSECT BUS AND BUS SUB PATH
                ST_AsText(ST_LineMerge(ST_CollectionExtract(
                ST_Intersection(ST_Buffer(
                    CASE po.path_section WHEN 'D' THEN ST_SetSRID(bp_a.geom_diff, 4326)
                                        ELSE ST_SetSRID(bp_a.geom, 4326)
                    END
                        , {$bufferVal}, 'endcap=flat join=bevel'), ST_SetSRID(bb.geom, 4326)), 2)))
            WHEN po.other_sub_path_id IS NULL THEN
                -- WHEN INTERSECT SUB PATH AND BUS PATH
                ST_AsText(ST_LineMerge(ST_CollectionExtract(
                ST_Intersection(ST_Buffer(
                    CASE po.path_section WHEN 'D' THEN ST_SetSRID(ba.diff_prev_geom, 4326)
                                        ELSE ST_SetSRID(ba.geom, 4326)
                    END
                        , {$bufferVal}, 'endcap=flat join=bevel'), ST_SetSRID(bp_b.geom, 4326)), 2)))
            ELSE 
                -- WHEN INTERSECT SUBPATH AND SUBPATH
                ST_AsText(ST_LineMerge(ST_CollectionExtract(
                ST_Intersection(ST_Buffer(ST_SetSRID(ba.geom, 4326), {$bufferVal}, 'endcap=flat join=bevel'), ST_SetSRID(bb.geom, 4326)), 2)))
            END as overlap_geom,
                
            CASE WHEN po.other_sub_path_id IS NULL THEN
                ST_Length(bp_b.geom::geography)
            ELSE
                ST_Length(bb.geom::geography)
            END as compare_length
FROM path_overlap po
                JOIN bus bus_a ON (bus_a.bus_id = po.bus_id)
                JOIN bus bus_b ON (bus_b.bus_id = po.other_bus_id)
                JOIN bus_revision br_a ON (br_a.bus_id = bus_a.bus_id AND br_a.bus_revision_id = bus_a.bus_revision_id)
                JOIN bus_revision br_b ON (br_b.bus_id = bus_b.bus_id AND br_b.bus_revision_id = bus_b.bus_revision_id)
                JOIN bus_path bp_a ON (bp_a.bus_path_id = br_a.bus_path_id)
                JOIN bus_path bp_b ON (bp_b.bus_path_id = br_b.bus_path_id)
                LEFT JOIN bus_sub_path ba USING (bus_sub_path_id)
                LEFT JOIN bus_sub_path bb ON po.other_sub_path_id = bb.bus_sub_path_id
WHERE path_overlap_id IN ({$implodePathOverlapIds})) mainTable;