I’ll add some figures to make this code make more sense, but it’s been sitting out in draft form for a couple weeks now. It’s time to post.

The short explanation is I wanted some code to snap points to lines. Enter Nicklas Avéns blog and enhancements to PostGIS. We’ve got a series of field-assessed streams with a coordinate for the location of the stream. We’ve also got aerially-interpreted, hand-digitized stream lines for the same network of streams. The line work is quite precise, as it was generated using photogrammetry for the sake of creating an engineering grade digital terrain model. The GPS points are derived from a handheld recreation grade unit, so some correction would be nice. Enter snap point to line:

CREATE TABLE public.streams_phwh_snap AS
SELECT g1.gid AS gid, g1.stream_nam AS stream_nam, ST_Distance(g1.the_geom, g2.the_geom) AS dist, g1.the_geom AS the_geom
FROM
(SELECT b.gid AS gid,
ST_Closestpoint(ST_Collect(a.the_geom), b.the_geom) AS the_geom,
b.stream_nam
FROM public.hydro_polyline_merge1 a INNER JOIN base.streams_phwh b
ON ST_Dwithin(a.the_geom, b.the_geom, 1000)
GROUP BY b.gid, b.the_geom, b.stream_nam
) AS g1,
base.streams_phwh AS g2
WHERE g1.gid = g2.gid;

And now we’ll join our tabular data back in (yes, I know I could have done this in one step, but can you blame me for writing it in two?).

CREATE TABLE public.streams_phwh_snapped_j AS
SELECT
a.gid,b.stream_nam,b.site_numbe,b.reservatio,b.river_basi,b.drainage_a,b.survey_rea,b.lat,b.long,b.river_code,b.river_mile,b.date_,b.scorers,b.stream_cha,b.hhei_score,b.substrate_,b.per_best_t,b.substrate1,b.substrate2,b.substrate3,b.per_boulde,b.per_boul_1,b.per_bedroc,b.per_cobble,b.per_gravel,b.per_sand,b.per_silt,b.per_leaf_w,b.per_fine_d,b.per_clay_h,b.per_muck,b.per_artifi,b.substrate4,b.pool_metri,b.pool_depth,b.bankfull_m,b.average_ba,b.riparian_w,b.riparian_1,b.floodplain,b.floodpla_1,b.flow_regim,b.sinuosity,b.stream_gra,b.qhei_done,b.watershed,b.designated,b.usgs_quad,b.soil_map_p,b.soil_map_o,b.county,b.twp_city,b.base_flow,b.date_last_,b.amt_last_r,b.turbid,b.per_canopy,b.water_samp,b.temp,b.do,b.ph,b.con,b.reach_rep,b.biotic_per,b.fish_seen,b.salamander,b.frogs_tadp,b.aquatic_ma,b.fish_vouch,b.fish_sampl,b.fish_sam_1,b.fish_lengt,b.blacknose_,b.bluegill_s,b.bluntnose_,b.brindled_m,b.brook_stic,b.brook_trou,b.brown_trou,b.central_mu,b.central_st,b.common_shi,b.creek_chub,b.eastern_sa,b.fantail_da,b.fathead_mi,b.golden_shi,b.goldfish,b.grass_pick,b.greenside_,b.green_sunf,b.johnny_dar,b.largemouth,b.longear_su,b.longnose_d,b.mottled_sc,b.northern_h,b.pumpkinsee,b.rainbow_da,b.rainbow_tr,b.redbelly_d,b.redside_da,b.river_chub,b.silverjaw_,b.smallmouth,b.spotfin_sh,b.stonecat_m,b.striped_sh,b.trout_perc,b.warmouth_s,b.white_suck,b.yellow_bul,b.unidentifi,b.salamand_1,b.salamand_2,b.salamand_3,b.salamand_4,b.mountain_d,b.mountain_1,b.mountain_2,b.northern_d,b.northern_1,b.northern_2,b.two_lined_,b.two_lined1,b.two_lined2,b.long_taile,b.long_tai_1,b.long_tai_2,b.northern_r,b.northern_3,b.northern_4,b.mole_larva,b.mole_juven,b.mole_adult,b.four_toed_,b.four_toed1,b.four_toed2,b.redback_ju,b.redback_ad,b.slimy_juve,b.slimy_adul,b.hmfei_scor,b.hmfei_samp,b.sessile_an,b.aquatic_wo,b.sow_bugs,b.scuds,b.water_mite,b.damselfly_,b.alderfly_l,b.other_beet,b.crayfish,b.dragonfly_,b.riffle_bee,b.larvae_of_,b.midges,b.snails,b.clams,b.fishfly_la,b.water_penn,b.cranefly_l,b.total_numb,b.number_may,b.ameletidae,b.arthroplei,b.baetidae,b.baetiscida,b.caenidae,b.ephemerell,b.ephemerida,b.heptagenii,b.isonychiid,b.leptohyphi,b.leptophleb,b.polymitarc,b.potamanthi,b.pseudironi,b.siphlonuri,b.number_sto,b.capniidae,b.chloroperl,b.leuctridae,b.nemouridae,b.peltoperli,b.perlidae,b.perlodidae,b.pteronarcy,b.taeniopter,b.number_cad,b.brachycent,b.dipseuodop,b.glossosoma,b.goeridae,b.helicopsyc,b.hydropsych,b.hydroptili,b.lepidostom,b.leptocerid,b.limnephili,b.molannidae,b.odontoceri,b.philopotam,b.phryganeid,b.polycentro,b.psychomiid,b.rhyacophil,b.uenoidae,b.stress_che,b.list_versi,b.concern,b.hhei_class,b.hmfeiclass, a.dist, a.the_geom
FROM public.streams_phwh_snap a INNER JOIN base.streams_phwh b
ON a.gid = b.gid;

ALTER TABLE public.streams_phwh_snapped_j ADD PRIMARY KEY (gid);