Link Ingested Features to Basins

[gzt5142]

From the original SQL definitions:

To link a point to its catchments:

<update id="linkPoint" >
update nldi_data.${tempTableName,jdbcType=VARCHAR} upd_table
	set comid = featureid
from nldi_data.${tempTableName,jdbcType=VARCHAR} src_table
	join nhdplus.catchmentsp
	on ST_covers(catchmentsp.the_geom, src_table.location)
	where upd_table.crawler_source_id = src_table.crawler_source_id and
		upd_table.identifier = src_table.identifier
</update>

To link a reach to its catchment:

<update id="linkReachMeasure">
update nldi_data.${tempTableName,jdbcType=VARCHAR} upd_table
	set comid = nhdflowline_np21.nhdplus_comid
from nldi_data.${tempTableName,jdbcType=VARCHAR} src_table
		  join nhdplus.nhdflowline_np21
		  on nhdflowline_np21.reachcode = src_table.reachcode and
		   src_table.measure between nhdflowline_np21.fmeasure and nhdflowline_np21.tmeasure
		  where upd_table.crawler_source_id = src_table.crawler_source_id and
		   upd_table.identifier = src_table.identifier
</update>

Need to implement this using SQL Alchemy in the python port.

It will be reasonably easy to just execute a 'raw' SQL statement such as the above using the SQLAlchemy mechanism -- but this opens up problems with injection, error-trapping, and also debugging. Need to 'translate' this into the appropriate API calls using the connection Engine().