How Many Buildings Are in the Country? Comparing Israel and Yemen
How to Quickly find the Number of Buildings, Settlements, and Points of Interest in a Specific Country?
For example, comparing Israel to Yemen: How many buildings are in each country? Roads? Restaurants? Buildings of a certain type, above a certain number of floors or height, how many restaurants, pharmacies, and essentially anything we want to analyze.
All using open data, cloud solutions, or open-source software with Spatial SQL.
The first question will be: “How to Obtain Such Information?”
You can search on Google or ask ChatGPT, but there’s no guarantee you’ll get an answer. However, you can also do it easily yourself using cloud GIS solutions, without an extensive geography knowledge, or with Geographic Information Systems (GIS) and pull the data directly into a Google Sheet.
You only need a standard Google account and some SQL knowledge.
In the previous post, I explained how to find the distance of settlements from the border using Spatial SQL. This time, the recipe has a Yemen-Israeli flavor, enjoy…
How to do it easily, in the browser, for free, and with open data in a few lines? After all, we are talking about millions of records with a large volumes for each country.
You can download open data to your computer from global open data bases: Overture Maps, OpenStreetMap, use a desktop GIS tools, or better yet, load it into databases like PostgreSQL-PostGIS or DuckDB.
But this requires finding the data for the required countries from the global data, downloading, loading, and having good computing power which is not always available.
Cloud companies (AWS, GCP, Azure) provide the ability to work with their Datawarehouse products and they also maintaining global open data, some of which is geographic.
[I chose to use Google’s engine, but it is also possible with other clouds]
Google’s BigQuery engine includes SQL and SQL Spatial capabilities + global Geo open data. (Overture Maps, OpenStreetMap) as a database for querying. The open data also includes information on buildings, businesses, settlement boundaries, and more.
[This information is not official nor commercial but good enough for this kind of tasks]
So how do we answer those questions? How can we display them on a map or in a table in a Google Sheet?
Recipe for Finding the Number of Buildings and More in a Country
Ingredients:
– A standard Google account
– A GCP project
– A pinch of SQL
– A dash of Spatial SQL
– A baking sheet of Google Sheets
Preparation:
1. Open a project in GCP, if you don’t have one. (See link in a previous post on how to do this)
2. Write a query to get the desired country’s area and the required information within it (see query below)
3. Activate a map for display (if desired)
4. Open a new Google Sheets and connect to the query (if desired)
For further analysis:
You can open the results in a dashboard in Google’s Looker Studio and combine it with other data you have. You can also easily modify the query for other information and countries.
Detailed Explanation
After you have opened a project in Google Cloud-BigQuery, open a new query via the dashboard or by clicking on the blue “+” button at the top.
:

Now, a new query tab named “Untitled query” will open.
Copy and paste the SQL code I wrote below (technical explanation in the comments) and press the RUN button or ctrl+enter.
You will now have a table with the results answering the question: How many poi’s or buildings are there in Israel and how many in Yemen.
Now you can take the information and continue working with it, on a map, in the studio, or export it to a file (details at the end of a previous post).
In the example, I had created a map from the result and instead of displaying many, many buildings that would overload the map (if the browser can even draw them), I am aggregating the millions of buildings in each country by quantity in a clearer way called Uber’s H3 Hexagons.
The display shows the concentration of the number of buildings in a hexagon grid on the map with color according to quantity.
The result clearly shows where the high concentrations are and where there are almost no buildings or population.
You can also use a new and very useful feature: connection to Google Sheets.
Simply choose Google Sheets from the save results menu above the table results on the right:
Clicking will open the results in Google Sheets, linked to the data.
From there you can share, continue analyzing, and do whatever you want.
If you will change the query (you can also do it directly through the sheet), you will get updated results! but that’s a topic for another blog.
You can also display the result on a Google map:
In the same menu, you can select:
“Open in GeoViz” and then follow the map instructions.
The Bottom Line:
🇮🇱 Israel:
Area: 22,000 sq km
Buildings: 1,357,986
Buildings over 10 floors: 2,833
Roads: 463,118
Restaurants: 3,466
Pharmacies: 526
🇾🇪 Yemen:
Area: 555,000 sq km
Buildings: 4,876,253
Roads: 257,873
Buildings over 10 floors: 2 (yes, that’s not a mistake)
Restaurants: 132
Pharmacies: 57
Enjoy! And may we see quieter days with our neighbors both near and far away and will use real food recipes for Yemeni and Isarely food.
--This exmaple query will result in a table that counts all restaurants and pharmacies in Yemen vs Israel
--get ready the area of intrest shape ofcountries from overture maps (Isreal, Yemen countries area)
with Yemen as
(
SELECT st_simplify(geometry,100) as geom
from `bigquery-public-data.overture_maps.division_area`
WHERE country='YE' and subtype='country'
),
Israel as
(
SELECT st_simplify(ST_UNION_AGG (geometry),100) as geom
from `bigquery-public-data.overture_maps.division_area`
WHERE country='IL' and (subtype='country' or names.primary='Golan Subdistrict')
)
--Now we will count how many pharmacies and restaurants are in each country
SELECT
(select count(*)
FROM `bigquery-public-data.overture_maps.place` as Places, Israel
where ST_intersects(Israel.geom, Places.geometry) and categories.`primary`='pharmacy'
) as count_pharm_IL,
(select count(*)
FROM `bigquery-public-data.overture_maps.place` as Places, Yemen
where ST_intersects(Yemen.geom, Places.geometry) and categories.`primary`='pharmacy'
) as count_Pharm_YE,
(select count(*)
FROM `bigquery-public-data.overture_maps.place` as Places, Israel
where ST_intersects(Israel.geom, Places.geometry) and categories.`primary`='restaurant'
) as count_rest_IL,
(select count(*)
FROM `bigquery-public-data.overture_maps.place` as Places, Yemen
where ST_intersects(Yemen.geom, Places.geometry) and categories.`primary`='restaurant'
) as count_rest_YE
--This is example using the POI (Point of intrests) from overture world data
--You can easiy chang it to count buildings or roads:
--just replace the layer name with: `bigquery-public-data.overture_maps.building` or 'segment' for roads.
--BUT BEWARE!!! of the huge amount of data each query sized, it will easily be above the free tier for BQ which is 1TB per month, and you will be charged with your credit card.