Skip to content

Instantly share code, notes, and snippets.

@smnorris

smnorris/cabd_id_xref.sql

Created August 17, 2022 19:16
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save smnorris/812c5cbb04a32789fe80cf7454866433 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save smnorris/812c5cbb04a32789fe80cf7454866433 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
cabd_id_xref.sql
drop table if exists bcfishpass.cabd_id_xref;
create table bcfishpass.cabd_id_xref(
bcdams_id integer,
cabd_id varchar,
distance numeric
);
-- find matches within 20m
insert into bcfishpass.cabd_id_xref
(bcdams_id, cabd_id, distance)
select distinct on (bcdams_id)
cwf.bcdams_id::integer,
cabd.cabd_id::varchar,
round(st_distance(cabd.geom, cwf.geom)::numeric, 2) as distance
from bcfishpass.cwf_bcdams cwf
left join cabd.dams cabd
on st_Dwithin(cwf.geom, cabd.geom, 20)
where cabd_id is not null
order by bcdams_id, distance;
-- find matches 20m-100m
insert into bcfishpass.cabd_id_xref
(bcdams_id, cabd_id, distance)
select distinct on (bcdams_id)
cwf.bcdams_id::integer,
cabd.cabd_id::varchar,
round(st_distance(cabd.geom, cwf.geom)::numeric, 2) as distance
from bcfishpass.cwf_bcdams cwf
left join cabd.dams cabd
on st_Dwithin(cwf.geom, cabd.geom, 100)
left join bcfishpass.cabd_id_xref lut
on cwf.bcdams_id = lut.bcdams_id
where cabd.cabd_id is not null
and lut.bcdams_id is null
and st_distance(cabd.geom, cwf.geom) > 20
order by bcdams_id, distance;
-- get a list of all unmatched features
select
cwf.bcdams_id,
null as cabd_id
from bcfishpass.cwf_bcdams cwf
left join bcfishpass.cabd_id_xref lut
on cwf.bcdams_id = lut.bcdams_id
where lut.bcdams_id is null
union all
select
null as bcdams_id,
cabd.cabd_id
from cabd.dams cabd
left join bcfishpass.cabd_id_xref lut
on cabd.cabd_id = lut.cabd_id
where lut.cabd_id is null;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment