איך מוצאים אלו ישובים נמצאים ליד הגבול לפי מרחק?​

				
					--get ready the area of intrest from OSM maps (Isreal, Lebanon countries area)
with bound as (
SELECT (SELECT value FROM unnest(all_tags) WHERE key='name:en') as country,geometry as geom from `bigquery-public-data.geo_openstreetmap.planet_features`
        WHERE feature_type="multipolygons"
          AND (('name:en', 'Israel') in (SELECT (key, value) from unnest(all_tags)) or ('name:en', 'Lebanon') in (SELECT (key, value) from unnest(all_tags) ))
          AND ('boundary', 'administrative') in (SELECT (key, value) from unnest(all_tags))
          AND ('admin_level', '2') in (SELECT (key, value) from unnest(all_tags))
),
--get ready the places list from OSM maps that are inside the countries area above
places as
( 
SELECT osm_id,bound.country,
(SELECT value FROM unnest(all_tags) WHERE key='name:he') AS name,(SELECT value FROM unnest(all_tags) WHERE key='name:en') AS name_en,(SELECT value FROM unnest(all_tags) WHERE key='place')as place,(SELECT value FROM unnest(all_tags) WHERE key='population')as pop,(SELECT value FROM unnest(all_tags) WHERE key='wikidata')as wikidata,
geometry as WKT,st_x(geometry) as lon, st_y(geometry) as lat
 from `bigquery-public-data.geo_openstreetmap.planet_features` bs, bs.all_tags t ,bound
        WHERE feature_type="points"
          AND (t.key ='place') and t.value in('city' ,'town' ,'village')
          and ST_DWithin(bound.geom, bs.geometry,0)
),
--get the line boundary between the countries from OSM
LebanonBound as
(
 SELECT geometry as WKT
 from `bigquery-public-data.geo_openstreetmap.planet_features`
        WHERE feature_type="lines"
          AND ('name:en', 'Lebanon - Israel') in (SELECT (key, value) from unnest(all_tags))
          or ('name:en', 'Israel - Lebanon') in (SELECT (key, value) from unnest(all_tags))
          AND ('boundary', 'administrative') in (SELECT (key, value) from unnest(all_tags))
          AND ('admin_level', '2') in (SELECT (key, value) from unnest(all_tags))
)
--now for the real fun: compile the table we want with the columns we want by distance.
--Adding links to wikidata and creating hyperlinks to googlemaps
select max(places.country) as country,cast(min(st_distance(places.WKT, LebanonBound.WKT ,true))as int) as distance_border, min(places.name) as name_he,min(places.name_en) as name_en,max(places.place) as type, max(places.pop) pop
,'https://www.wikidata.org/wiki/'||max(wikidata) as wikidata,'http://maps.google.com/maps?t=k&q=loc:'||max(lat)||'+'||max(lon) as map_link
from places, LebanonBound
where cast(st_distance(places.WKT, LebanonBound.WKT ,true)as int) <3000 --<<set here the distance limit--->></3000>
group by places.osm_id
order by distance_border