Map Streets and Main

street_main.sql

drop table if exists public.main_street_map;
select ogc_fid, street_geom, name, type, globalid, water_geom, distance into public.main_street_map
from
(
select ogc_fid, street_geom, name, type, globalid, water_geom,distance,
row_number() over(partition by ogc_fid order by distance) rank from
(
select st_distance(s.wkb_geometry, m.wkb_geometry) distance, 
	s.ogc_fid, 
	s.wkb_geometry street_geom, 
	s.name, 
	s.type,
	m.globalid,
	m.wkb_geometry water_geom
	from water_system.onondaga_county_streets s 
	join water_system.mains m on st_dwithin(s.wkb_geometry, m.wkb_geometry, 5000)
	where m.lat_system = 'Main' and owner = 'SWD'
) as temp
) as closest 
where rank = 1;