Google recently released the Open Buildings dataset, including 516 million building footprints covering 64% of the African continent. Each building is described as a polygon with a confidence score indicating the degree of confidence that the building identified is indeed a building. There are many potential use cases for the data, including our recent visualization and blog post assessing the damage from a volcano on La Palma in the Spanish Canary Islands.
To analyze the entire continent, we can leverage BigQuery to store the information in one table and then use standard SQL to ask the data questions. In addition, to explore the entire continent at once, we can use CARTO’s BigQuery Tiler (part of the CARTO Spatial Extension for BigQuery) to visualize all 516 million buildings on one map efficiently.
Open Buildings has hosted all of the building polygons in a series of compressed CSV files in Google Cloud Storage, totaling 46 GB. Instead of downloading the files locally and pushing them back up into BigQuery, we can use the bq command-line tool to create a table in BigQuery directly from the files in cloud storage.
Each of the compressed CSV files are stored in the path:
We can use the following command to take each of the compressed CSV files (the *.csv.gz denotes all files ending in that extension) and create a single table in BigQuery. It will automatically determine a schema to use and create a series of jobs to build a table.
bq load --autodetect YOUR_DATASET_NAME.open_buildings_raw "gs://open-buildings-data/v1/polygons_s2_level_4_gzip/*.csv.gz"
When previewing the table in BigQuery, the schema should look like this:
We now have the data of the buildings in a single BigQuery table. However, there are some refinements we can make to make geospatial analysis faster and easier.
create table `YOUR_PROJECT_NAME.YOUR_DATASET_NAME.open_buildings_clean` cluster by geom as select confidence, ST_GeogFromText(geometry) as geom from `YOUR_PROJECT_NAME.YOUR_DATASET_NAME.open_buildings_raw`
We can create a tileset using CARTO’s Spatial Extension for BigQuery with a single SQL command. The first argument is the source table (the clean table we just created). The second argument is the desired output tileset table name. Last is an optional argument, which could be null, causing the default parameters to be used. However, setting zoom_min to 10 and zoom_max to 20 will reduce the size and improve performance. Since we are viewing individual buildings, we do not need to visualize the data at a zoom level lower than 10.
CALL bqcarto.tiler.CREATE_TILESET( '`YOUR_PROJECT_NAME.YOUR_DATASET_NAME.open_buildings_clean`', '`YOUR_PROJECT_NAME.YOUR_DATASET_NAME.open_buildings_clean_tileset`', STRUCT ( null AS name, null AS description, null AS legend, 10 AS zoom_min, 20 AS zoom_max, null AS geom_column_name, null AS zoom_min_column, null AS zoom_max_column, null AS max_tile_size_kb, null AS tile_feature_order, null AS drop_duplicates, null AS extra_metadata ) );
Using CARTO Builder, we can add the tileset, apply some styling based on confidence score and switch to a Google base map to see how the polygons line up versus Google Maps road and satellite imagery layers.
Click “Add source from…” and select the tileset created earlier.
Click the base map menu option and switch the base map to Google Maps. Optionally we can switch to Satellite if we wish to compare the building polygons to actual imagery.
Go into the “Layer settings”, click the three vertical dots next to Fill Color, select the confidence column in the “Color Based On” field, and switch the steps to 3 by clicking on the color ramp.
At the end of the steps outlined above, we can see a map with all of the building polygons shown like the screenshot above. We can move around to most areas of Africa and see each building allowing for rapid exploration.
A live map can be found here.
The Data Appeal Company’s Hannah Babineau - International Partnerships and Marketing Manager - guest blogs for CARTO!Spatial Data
Read any news website in 2022 and there’s one phrase you can’t escape; the “cost of living crisis.” In the UK, living costs have been rising at their fastest rate in 30 yea...Spatial Data
Please fill out the below form and we'll be in touch real soon.