Easy Guide: Finding Localities Near the Border with GIS and SQL?
Easy Guide: Finding Localities Near the Border with GIS and SQL?
In a question that has come up frequently this past year in Israel:
Which localities evacuated or not based on their distance from the Lebanon or Gaza border? Also, which localities are near the border on the Lebanese side, and how close they are to the border?
How can one obtain such a list of localities based on distance to the border?
Of course, you can search for such lists on Google and rely on various lists on the internet. However, one can also do it easily on their own with the cloud-based GIS solutions, without extensive knowledge of geography or GIS systems, and pull the information directly into a Google spreadsheet.
I was asked to prepare such a list easily. You will only need a standard Google account and some SQL.
Here’s my recipe, bon appétit… 
If you really want to do a tedious job with lots of clicks on Google Maps (or even with a paper map and a ruler…).
But the simplest and most convenient way is to get an organized list in a table directly into Google Sheets, where you can continue analysis and use it even for those who do not understand GIS or SQL. Everyone understands Excel and Google Sheets.
If you are a GIS systems expert, you can do it with desktop GIS software, but even there the process is not easy and will require:
- Obtaining and downloading relevant data
- Knowing and operating several spatial and tabular functions
Creating temporary layers much and more.
So how can you do it easily, in the browser, for free, and with open data, in a few lines of SQL?
Cloud companies (AWS, GCP, Azure) allow working with their Data warehouse products and also maintaining global open data, some of which is geospatial.
[I chose to use Google’s Big Query, but this task is also possible in other clouds as well].
Google’s BigQuery engine includes SQL and SQL Spatial capabilities.
It also includes global geo-spatial open public data (the new overture maps and open street map) as a geo-spatial global database for querying.
The overture maps and open street map data also include information about borders and places/localities.
[This data is not official, but it’s good enough for this kind of task].
Another bonus, Google recently opened a connection between its spreadsheets, Google Sheets, and BigQuery engine.
So, now you can pull results and queries directly into Google Sheets and also share them, Even without knowing SQL. In addition to many other options for exporting and analyzing the data results. One can also upload own Spatial data and combine it.
I’ve expanded on this topic in previous posts.
So, how do you create such a list with distance from the border for each locality and some additional information, automatically into Google Sheets?
- Recipe for a List of Localities by Distance from a Border (Lebanon-Israel)
Ingredients:
Standard Google Account
GCP Project
Bottle of SQL
A Pinch of Spatial SQL spice
Google Sheets Baking Tray
Instructions:
- Open a project in GCP if you don’t have one. (See in a previous post, how to do this).
- Write a query to get the borders from which you want the distance, the types of localities, and information about them (see SQL code below).
- Run the query and Open it in a Google Sheets spreadsheet.
At this point, I got more appetite and decided to spice things up a bit;
To add some extra flavor to the recipe:
I decided to include also the Lebanese localities, with a country iso2 column to allow filtering. The Population size per locality, a Wikipedia link for each locality, if available, and a link to view each locality on a map/satellite (I can’t help it have to add a web map…).
If you want to analyze the results further, you can open them in a dashboard, in Google’s Looker Studio, and combine them with other information you have.
You can also easily change the query for other borders and other countries.
The table I created of all localities on both sides of the Israel-Lebanon border within 3 km distance, is available for download below, for those who just want the list.
Detailed explanation
After opening a project in Google Cloud’s Big query, open a new query through the dashboard or by clicking on the blue + button at the top:
Now, a tab for the new query named “Untitled query” will open.
Copy and paste the SQL code I wrote below at the end. (technical explanation in the comments in the code)
You can change it if needed to other countries or borders or change the distance required.
When ready, press the RUN or ctrl+enter button. And you have a full list with all the extras (link to the map, Wikipedia, and more)
Now, you can take use the results and continue analyzing them, whether on a geospatial map, in Google’s Looker studio
or export it to a CSV, or Excel file (details at the end of my previous post).
I chose the new and really helpful feature: a connection to Google sheets.
Simply select Google Sheets from the Save Results menu. (above the results of the table on the right)
Clicking this option will open the Google Sheet with the results as a table.
From there you can share, continue analyzing, etc.
If you change the query (you can also do it directly through the connected sheet)
you will get updated results!
But this is already a topic for another blog.
Enjoy!
And that we will know quieter days and use it for peace purposes.
--get ready the area of intrest from overture maps (Isreal, Lebanon countries area)
with bound as (
SELECT (geometry) as geom,country from `bigquery-public-data.overture_maps.division_area`
WHERE (country='LB' or country='IL'or names.primary='Golan Subdistrict') and subtype='country'
),
--get ready the places list from overture maps that are inside the countries area above
places as
(
SELECT id,names.primary as name,bound.country as iso2,(SELECT value FROM unnest(names.common.key_value) WHERE key='he') AS name_he,locality_type as type, population as pop, wikidata,st_x(geometry) as lon, st_y(geometry) as lat, geometry as WKT
from `bigquery-public-data.overture_maps.locality` bs, bound
WHERE ST_intersects(bound.geom, bs.geometry) and locality_type in('city' ,'town' ,'village')
),
--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.iso2) as iso2,cast(min(st_distance(places.WKT, LebanonBound.WKT ,true))as int) as distance_border,
min(places.name) as name_en,
max(name_he) AS name_he,max(type) as type, max(places.pop) as 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--,ST_GEOGPOINT(max(lon), max(lat)) as geom
from places, LebanonBound
where cast(st_distance(places.WKT, LebanonBound.WKT
,true)as int) <3000 --here change the distance from border
group by places.id
order by distance_border
