Improve snapping

[merretbuurman]

We found out that the snapping query is slow because it checks too many tables. Instead of asking it to match the reg_id (), we have to hard-code the reg_id. That makes it, like, ten times faster.

(1) when updating the temp table with the subc_id:

UPDATE {tablename} SET subc_id = sub.subc_id, basin_id = sub.basin_id FROM sub_catchments sub WHERE st_intersects({tablename}.geom_user, sub.geom) AND sub.reg_id IN ({reg_ids}) ;

OLD: {tablename}.reg_id = sub.reg_id
NEW: sub.reg_id IN (58, 74)

And we can get it by using RETURNING in the previous query, which updates the temp table with the reg_id:
UPDATE {tablename} SET reg_id = reg.reg_id FROM regional_units reg WHERE st_intersects({tablename}.geom_user, reg.geom) RETURNING {tablename}.reg_id;
or rather
WITH updater AS (UPDATE {tablename} SET reg_id = reg.reg_id FROM regional_units reg WHERE st_intersects({tablename}.geom_user, reg.geom) RETURNING {tablename}.reg_id) SELECT DISTINCT reg_id FROM updater;

(2) when querying the temp table for the snapped points:

OLD: seg.reg_id = poi.reg_id
NEW: seg.reg_id IN (58, 74)

SELECT
    poi.lon,
    poi.lat,
    poi.subc_id,
    poi.basin_id,
    poi.reg_id,
    seg.strahler,
    ST_AsText(ST_LineInterpolatePoint(seg.geom, ST_LineLocatePoint(seg.geom, poi.geom_user)))
    FROM hydro.stream_segments seg, {tablename} poi
    WHERE seg.subc_id = poi.subc_id AND seg.reg_id IN ({reg_ids})