Postgres - detect overlapping lines from 2 tables

Question

QUESTION: I have 2 tables with bike and hike trails of a country (Poland).

Table bicycle_merge

way      | route  | name        |network    |osmc_color| state  
geometry | text   | text        |text       |text      | text
----------------------------------------------------------------
{geo1}   |bicycle |szlak1       |ncn        |blue      |proposed
{geo2}   |bicycle |szlak2       |lcn        |red       |null
{geo3}   |bicycle |szlak3       |ncn        |green     |proposed
{geo4}   |bicycle |szlak4       |rcn        |blue      |proposed
{geo5}   |bicycle |szlak5       |lcn        |blue      |null
{geo6}   |bicycle |szlak6       |ncn        |yellow    |proposed
....and so on

Table hiking_merge

way      | route  | name        |network    |osmc_color| state  
geometry | text   | text        |text       |text      | text
----------------------------------------------------------------
{geo1}   |hiking  |szlak1       |ncn        |blue      |proposed
{geo2}   |hiking  |szlak2       |ncn        |red       |null
{geo3}   |hiking  |szlak3       |ncn        |green     |proposed
{geo4}   |hiking  |szlak4       |ncn        |blue      |proposed
{geo5}   |hiking  |szlak5       |ncn        |blue      |null
{geo6}   |hiking  |szlak6       |ncn        |yellow    |proposed
...and so on

{geo1},{geo2} etc. is a unique geometry of every trail.

Every trail has a unique geometry but sometimes bicycle and hiking routes overlap. The picture below shows example in QGIS: I would like to delete the overlapping parts as I already have them in a different database.

EDIT: I have tried a solution suggested by Julia Leder :

create table intersections as
(select st_intersection(b.way, h.way) as overlapping_section
from bicycle_merge b, hiking_merge h
where st_intersects(g.way, h.way));

Not all overlapping lines were detected. I'm not sure why? Here's a picture to show the issue:

Answer 1

Are you looking for this:

(SELECT way,route,name,color,description,network FROM bicycle_merge
UNION
SELECT way,route,name,color,description,network FROM hiking_merge)
EXCEPT
(SELECT way,route,name,color,description,network FROM bicycle_merge
INTERSECT
SELECT way,route,name,color,description,network FROM hiking_merge);

This is the MS SQL syntax. For Oracle, write MINUS instead of EXCEPT.

Answer 2

select *
from
    (
        select *
        from bicycle_merge bm
        where not exists (
            select 1
            from hiking_merge
            where geometry = bm.geometry
        )
    )
    union
    (
        select *
        from hicking_merge hm
        where not exists (
            select 1
            from bicycle_merge
            where geometry = hm.geometry
        )
    )

Answer 3

This can be relatively easily done with PostGIS functions. You need to find the overlapping section between the two geometries, which can be done with

ST_Intersection(a.geometry, b.geometry)

Then you need to subtract that section from the original geometry from either biking or hiking geometry, which would be done with

ST_Difference(a.geometry, b.geometry)

To put this all together:

create table intersections as
(select st_intersection(st_buffer(b.way,10), st_buffer(h.way,10)) as overlapping_section
from bicycle_merge b, hiking_merge h
where st_intersects(st_buffer(g.way, 10), st_buffer(h.way,10)));


update bicycle_merge b 
set way = st_difference(b.way, overlapping_section)
from intersections where st_intersects(b.way, overlapping_section);

update hiking_merge h 
set way = st_difference(h.way, overlapping_section)
from intersections where st_intersects(h.way, overlapping_section);

This may need to be adjusted somewhat depeding on types of geometries and how you want to store them, but the above should give you the idea.

The 2nd parameter of st_buffer function takes a radius of the buffer. The units of this number depend on the srid of geometry. They can be in feet, meters or degrees depending on the SRID of your geometry, which you can check by

select st_srid(way) from biking_merge limit 0;