Enhance the correlation

[DUGUEY]

the correlation is calculate according to the edge angle. This angle is calculate with the edge linked two centroids segments (see figure A) in order to enhance the correlation can Albion take in account also the angle betwen the top and the bottom of the edge (see figure B) ? the correlation would be done if one these 3 angles is below a specific angle.

Figure A : Figure B :

[vmora]

@DUGUEY can you try in QGIS DB Manager to replace the possible_edge view by the follwing. :warning: I only made the change for graphs that have no parent.

First remove the possible_edge view and current_possible_edge_section that depends on it.

drop view albion.possible_edge cascade
;

Then replace $SRID in the following view deffinition by your project srid and re-create the possible_edge view

create view albion.possible_edge as
with tan_ang as (
    select tan(correlation_angle*pi()/180) as value, tan(parent_correlation_angle*pi()/180) as parent_value

    from _albion.metadata
),
result as (
select ns.id as start_, ne.id as end_, ns.graph_id as graph_id, (st_makeline(st_3dlineinterpolatepoint(ns.geom, .5), st_3dlineinterpolatepoint(ne.geom, .5)))::geometry('LINESTRINGZ', $SRID) as geom, null as parent
from albion.all_edge as e
join _albion.hole as hs on hs.collar_id=e.start_
join _albion.hole as he on he.collar_id=e.end_
join _albion.node as ns on ns.hole_id=hs.id
join _albion.node as ne on ne.hole_id=he.id, tan_ang
where ns.graph_id = ne.graph_id
and (
    abs(st_z(st_3dlineinterpolatepoint(ns.geom, .5))-st_z(st_3dlineinterpolatepoint(ne.geom, .5)))
    /st_distance(st_3dlineinterpolatepoint(ns.geom, .5), st_3dlineinterpolatepoint(ne.geom, .5)) < tan_ang.value
    or
    abs(st_z(st_3dlineinterpolatepoint(ns.geom, 0))-st_z(st_3dlineinterpolatepoint(ne.geom, 0)))
    /st_distance(st_3dlineinterpolatepoint(ns.geom, 0), st_3dlineinterpolatepoint(ne.geom, 0)) < tan_ang.value
    or
    abs(st_z(st_3dlineinterpolatepoint(ns.geom, 1))-st_z(st_3dlineinterpolatepoint(ne.geom, 1)))
    /st_distance(st_3dlineinterpolatepoint(ns.geom, 1), st_3dlineinterpolatepoint(ne.geom, 1)) < tan_ang.value
    )
and st_distance( ne.geom, ns.geom ) < ( select correlation_distance from albion.metadata )
and ns.parent is null
and ne.parent is null

union all

select ns.id as start_, ne.id as end_, ns.graph_id as graph_id, (st_makeline(st_3dlineinterpolatepoint(ns.geom, .5), st_3dlineinterpolatepoint(ne.geom, .5)))::geometry('LINESTRINGZ', $SRID) as geom, ns.parent as parent
from _albion.edge as pe
join _albion.node as pns on pns.id=pe.start_
join _albion.node as pne on pne.id=pe.end_
join _albion.node as ns on ns.parent=pns.id
join _albion.node as ne on ne.parent=pne.id, tan_ang
where ns.graph_id = ne.graph_id
and abs( -- tan(A-B) = (tan(A)-tan(B))/(1+tan(A)tan(B)
    (
    (st_z(st_3dlineinterpolatepoint(ns.geom, .5))-st_z(st_3dlineinterpolatepoint(ne.geom,.5)))
    /st_distance(st_3dlineinterpolatepoint(ns.geom, .5), st_3dlineinterpolatepoint(ne.geom, .5))
    -
    (st_z(st_3dlineinterpolatepoint(pns.geom, .5))-st_z(st_3dlineinterpolatepoint(pne.geom,.5)))
    /st_distance(st_3dlineinterpolatepoint(pns.geom, .5), st_3dlineinterpolatepoint(pne.geom, .5))
    )
    /
    (
    1.0
    +
    (st_z(st_3dlineinterpolatepoint(ns.geom, .5))-st_z(st_3dlineinterpolatepoint(ne.geom,.5)))
    /st_distance(st_3dlineinterpolatepoint(ns.geom, .5), st_3dlineinterpolatepoint(ne.geom, .5))
    *
    (st_z(st_3dlineinterpolatepoint(pns.geom, .5))-st_z(st_3dlineinterpolatepoint(pne.geom,.5)))
    /st_distance(st_3dlineinterpolatepoint(pns.geom, .5), st_3dlineinterpolatepoint(pne.geom, .5))
    )
    ) < tan_ang.parent_value
)
select row_number() over() as id, * from result
;

Last re-create the section view:

create view albion.current_possible_edge_section as
select row_number() over() as id, e.start_, e.end_, e.graph_id, s.id as section_id,
    (albion.to_section(e.geom, s.anchor, s.scale))::geometry('LINESTRING', $SRID) as geom, e.parent
from albion.possible_edge as e
join _albion.node as ns on ns.id=e.start_
join _albion.node as ne on ne.id=e.end_
join _albion.hole as hs on hs.id=ns.hole_id
join _albion.hole as he on he.id=ne.hole_id
join _albion.collar as cs on cs.id=hs.collar_id
join _albion.collar as ce on ce.id=he.collar_id
join _albion.section as s on st_intersects(s.geom, cs.geom) and st_intersects(s.geom, ce.geom)
;

[vmora]

Solution for both base graphs and child graphs:

create or replace view albion.possible_edge as
with tan_ang as (
    select tan(correlation_angle*pi()/180) as value, tan(parent_correlation_angle*pi()/180) as parent_value
    from _albion.metadata
),
result as (
select ns.id as start_, ne.id as end_, ns.graph_id as graph_id, (st_makeline(st_3dlineinterpolatepoint(ns.geom, .5), st_3dlineinterpolatepoint(ne.geom, .5)))::geometry('LINESTRINGZ', 32632) as geom, null as parent
from albion.all_edge as e
join _albion.hole as hs on hs.collar_id=e.start_
join _albion.hole as he on he.collar_id=e.end_
join _albion.node as ns on ns.hole_id=hs.id
join _albion.node as ne on ne.hole_id=he.id, tan_ang
where ns.graph_id = ne.graph_id
and (
    (
        abs(ns.from_-ns.to_) >= abs(ne.from_-ne.to_)
        and st_z(st_startpoint(ns.geom)) + st_distance(st_startpoint(ns.geom), st_startpoint(ne.geom))*tan_ang.value >= st_z(st_startpoint(ne.geom))
        and st_z(st_endpoint(ns.geom)) - st_distance(st_startpoint(ns.geom), st_startpoint(ne.geom))*tan_ang.value <= st_z(st_endpoint(ne.geom))
    )
    or
    (
        abs(ns.from_-ns.to_) < abs(ne.from_-ne.to_)
        and st_z(st_startpoint(ne.geom)) + st_distance(st_startpoint(ns.geom), st_startpoint(ne.geom))*tan_ang.value >= st_z(st_startpoint(ns.geom))
        and st_z(st_endpoint(ne.geom)) - st_distance(st_startpoint(ns.geom), st_startpoint(ne.geom))*tan_ang.value <= st_z(st_endpoint(ns.geom))
    )
    )

and st_distance( ne.geom, ns.geom ) < ( select correlation_distance from albion.metadata )
and ns.parent is null
and ne.parent is null

union all -- for graphs with parents

select ns.id as start_, ne.id as end_, ns.graph_id as graph_id, (st_makeline(st_3dlineinterpolatepoint(ns.geom, .5), st_3dlineinterpolatepoint(ne.geom, .5)))::geometry('LINESTRINGZ', 32632) as geom, ns.parent as parent
from _albion.edge as pe
join _albion.node as pns on pns.id=pe.start_
join _albion.node as pne on pne.id=pe.end_
join _albion.node as ns on ns.parent=pns.id
join _albion.node as ne on ne.parent=pne.id, tan_ang
where ns.graph_id = ne.graph_id
and
    (
        abs(ns.from_-ns.to_) >= abs(ne.from_-ne.to_)
        and st_z(st_startpoint(ns.geom)) + st_distance(st_startpoint(ns.geom), st_startpoint(ne.geom))*tan_ang.parent_value + (st_z(st_3dlineinterpolatepoint(pne.geom, .5)) - st_z(st_3dlineinterpolatepoint(pns.geom, .5))) >= st_z(st_startpoint(ne.geom))
        and st_z(st_endpoint(ns.geom)) - st_distance(st_startpoint(ns.geom), st_startpoint(ne.geom))*tan_ang.parent_value + (st_z(st_3dlineinterpolatepoint(pne.geom, .5)) - st_z(st_3dlineinterpolatepoint(pns.geom, .5))) <= st_z(st_endpoint(ne.geom))

    )
    or
    (
        abs(ns.from_-ns.to_) < abs(ne.from_-ne.to_)
        and st_z(st_startpoint(ne.geom)) + st_distance(st_startpoint(ns.geom), st_startpoint(ne.geom))*tan_ang.parent_value  + (st_z(st_3dlineinterpolatepoint(pns.geom, .5)) - st_z(st_3dlineinterpolatepoint(pne.geom, .5))) >= st_z(st_startpoint(ns.geom)) 

        and st_z(st_endpoint(ne.geom)) - st_distance(st_startpoint(ns.geom), st_startpoint(ne.geom))*tan_ang.parent_value + (st_z(st_3dlineinterpolatepoint(pns.geom, .5)) - st_z(st_3dlineinterpolatepoint(pne.geom, .5)) ) <= st_z(st_endpoint(ns.geom)) 

    )
)
select row_number() over() as id, * from result
;

[DUGUEY]

Yes it's work !!!! the automatic correlation appears much more smart ! I have also the strong impression that we can again increase the quality if we avoid all the crossing edge.

[vmora]

@DUGUEY I think we could do that by detecting crossings and selecting the "best" edge: the one with the "best" characteristics. The smallest angle with the parent is a candidate for the definition of "best".

[DUGUEY]

yes the smallest angle is the best candidate !

[vmora]

@DUGUEY could you test the new version, I'm not sure everything is implemented, but correlation has indeed been improved.

[vmora]

done in r2.2