Add Fishbone Analysis into QA/QC Schema

[KSnyderCode]

Can this be added in as part of the QA/QC Schema?

https://www.northrivergeographic.com/archives/fishbones-for-the-tn-ng911-address-server

[northrivergeo]

Found it - so here is the code. Next comment after this will be "making it better".

create table dev.potential_points as (
  select
      a.oirid,
      ST_LineInterpolatePoint(ST_GeometryN(c.geom,1), (ST_LineLocatePoint(st_GeometryN(c.geom,1), a.geom))) as geom
from
    hc911.addresspoints as a
    join hc911.henry_centerline as c on concat_ws(' ', a.street, a.esn) = concat_ws(' ', c.street, c.esn_l)
    where a.stnum::int between c.l_f_add::int and c.l_t_add::int
    and seg_side = 'L'
union all
select
    a.oirid,
    ST_LineInterpolatePoint(ST_GeometryN(c.geom,1), (ST_LineLocatePoint(st_GeometryN(c.geom,1), a.geom))) as geom
from
    hc911.addresspoints as a
    join hc911.henry_centerline as c on concat_ws(' ', a.street, a.esn) = concat_ws(' ', c.street, c.esn_r)
    where a.stnum::int between c.r_f_add::int and c.r_t_add::int
    and seg_side = 'R');

create table dev.fishbones as (
        select
             a.oirid,
             st_makeline(a.geom, t.geom) as geom
        from
             hc911.addresspoints as a
             join dev.potential_points as t on a.oirid = t.oirid);

[northrivergeo]

When I had originally wrote this I was struggling a bit to make it work. What it should do now:

*Case Statement (I think) to decide if address is left or right.

*Get the street address and then use st_linelocatepoint to place the point. An address is "Distance" divided by 5.28 feet. So an address 528 feet up a road should have an stnum 100.

*draw the line from the house to the point along the road

I'm not 100% convinced I need to create a point - in this case I did. I think just drawing the line is good enough. Anyway - I'll see if I can work on this some tonight.