This notebook illustrates how to use the admin. region geometries from Who's on First (public data) to filter a dataset from CARTO's Data Observatory using CARTOFrames methods.
The notebook is organized as follows: 0. Setup account
Documentation
import geopandas as gpd
import pandas as pd
from cartoframes.auth import set_default_credentials
from cartoframes.data.observatory import *
from cartoframes.viz import *
pd.set_option('display.max_columns', None)
set_default_credentials('creds.json')
First, we check our data subscriptions from the Data Observatory to select which dataset we want to filter.
Catalog().subscriptions().datasets.to_dataframe()
slug | name | description | category_id | country_id | data_source_id | provider_id | geography_name | geography_description | temporal_aggregation | time_coverage | update_frequency | is_public_data | lang | version | category_name | provider_name | geography_id | id | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | lg_parcels_ef7cdae4 | Parcels - United States of America (Parcel) | Nationwide dataset with data from 140 million ... | housing | usa | parcels | landgrid | Parcel - United States of America | Land parcels compiled by Landgrid | monthly | None | monthly | False | eng | v1 | Housing | Landgrid | carto-do.landgrid.geography_usa_parcel_v1 | carto-do.landgrid.housing_parcels_usa_parcel_v... |
1 | sg_social_dist_667d8e8e | Social Distancing Metrics - United States of A... | Due to the COVID-19 pandemic, people are curre... | covid19 | usa | social_distancing | safegraph | Census Block Group - United States of America ... | Shoreline clipped TIGER/Line boundaries. More ... | daily | None | daily | False | eng | v1 | Covid-19 | SafeGraph | carto-do-public-data.carto.geography_usa_block... | carto-do.safegraph.covid19_socialdistancing_us... |
2 | ine_sociodemogr_c8c87afe | Sociodemographics (Spain, Census Sections) | Sociodemographic data from the Instituto Nacio... | demographics | esp | sociodemographics | esp_ine | Census Section (Spain) | 2020 Census Sections, from the Instituto Nacio... | yearly | None | None | True | eng | 2011 | Demographics | Instituto Nacional de Estadística | carto-do-public-data.esp_ine.geography_esp_cen... | carto-do-public-data.esp_ine.demographics_soci... |
3 | expn_sociodemogr_25b78bba | Sociodemographics - Thailand (Grid 250m) | Worldview combines Experian's own datasets wit... | demographics | tha | sociodemographics | experian | Grid 250m - Thailand | Experian 250mx250m grid cells | yearly | [2019-01-01, 2020-01-01) | None | False | eng | 2020 | Demographics | Experian | carto-do.experian.geography_tha_grid_v1 | carto-do.experian.demographics_sociodemographi... |
4 | mc_geographic__7980c5c3 | Geographic Insights - United States of America... | Geographic Insights validate, evaluate and ben... | financial | usa | geographic_insights | mastercard | Census Block Group - United States of America ... | Shoreline clipped TIGER/Line boundaries. More ... | monthly | [2019-01-01, 2020-01-01) | monthly | False | eng | v1 | Financial | Mastercard | carto-do-public-data.carto.geography_usa_block... | carto-do.mastercard.financial_geographicinsigh... |
5 | acs_sociodemogr_b758e778 | Sociodemographics - United States of America (... | The American Community Survey (ACS) is an ongo... | demographics | usa | sociodemographics | usa_acs | Census Block Group - United States of America ... | Shoreline clipped TIGER/Line boundaries. More ... | 5yrs | [2013-01-01, 2018-01-01) | None | True | eng | 20132017 | Demographics | American Community Survey | carto-do-public-data.carto.geography_usa_block... | carto-do-public-data.usa_acs.demographics_soci... |
6 | can_sociodemogr_affc7f83 | Sociodemographics - Canada (Dissemination Area) | Sociodemographic data from Statistics Canada. ... | demographics | can | sociodemographics | can_statistics | Dissemination Area - Canada (2016) | Canada - Dissemination Area | 5yrs | None | None | True | eng | 2016 | Demographics | Statistics Canada | carto-do-public-data.carto.geography_can_disse... | carto-do-public-data.can_statistics.demographi... |
7 | can_employment_e3bbbb6 | Employment And Income - Canada (Dissemination ... | Employment and income data from Statistics Can... | demographics | can | employment | can_statistics | Dissemination Area - Canada (2016) | Canada - Dissemination Area | 5yrs | None | None | True | eng | 2016 | Demographics | Statistics Canada | carto-do-public-data.carto.geography_can_disse... | carto-do-public-data.can_statistics.demographi... |
8 | wp_population_cd347169 | Population Mosaics - Switzerland (Grid 1km, 2020) | Mosaiced 1km resolution global datasets. The m... | demographics | che | population | worldpop | Grid 1km - Switzerland | Global grid at aprox. 1-kilometer resolution (... | yearly | [2020-01-01, 2021-01-01) | None | True | eng | 2020 | Demographics | WorldPop | carto-do-public-data.worldpop.geography_che_gr... | carto-do-public-data.worldpop.demographics_pop... |
9 | wp_population_35d01fd4 | Population Mosaics - Belgium (Grid 100m, 2015) | Mosaiced 100m resolution global datasets. The ... | demographics | bel | population | worldpop | Grid 100m - Belgium | Global grid at aprox. 100-meter resolution (0.... | yearly | [2015-01-01, 2016-01-01) | None | True | eng | 2015 | Demographics | WorldPop | carto-do-public-data.worldpop.geography_bel_gr... | carto-do-public-data.worldpop.demographics_pop... |
10 | wp_population_6bf077c7 | Population Mosaics - Italy (Grid 1km, 2020) | Mosaiced 1km resolution global datasets. The m... | demographics | ita | population | worldpop | Grid 1km - Italy | Global grid at aprox. 1-kilometer resolution (... | yearly | [2020-01-01, 2021-01-01) | None | True | eng | 2020 | Demographics | WorldPop | carto-do-public-data.worldpop.geography_ita_gr... | carto-do-public-data.worldpop.demographics_pop... |
11 | spa_geosocial_s_d5dc42ae | Geosocial Segments - United States of America ... | By analysing feeds from Twitter, Instagram, Me... | behavioral | usa | geosocial_segments | spatial_ai | Census Block Group - United States of America ... | Shoreline clipped TIGER/Line boundaries. More ... | quarterly | [2020-01-01, 2020-04-01) | quarterly | False | eng | v1 | Behavioral | Spatial.ai | carto-do-public-data.carto.geography_usa_block... | carto-do.spatial_ai.behavioral_geosocialsegmen... |
12 | expn_consumer_se_7d6172d | Consumer Segments - Russia (Grid 250m) | WorldView segments has been developed to segme... | demographics | rus | consumer_segments | experian | Grid 250m - Russia | Experian 250mx250m grid cells | yearly | [2019-01-01, 2020-01-01) | None | False | eng | 2020 | Demographics | Experian | carto-do.experian.geography_rus_grid_v2 | carto-do.experian.demographics_consumersegment... |
13 | expn_sociodemogr_81aa1d1e | Sociodemographics - Russia (Grid 250m) | Worldview combines Experian's own datasets wit... | demographics | rus | sociodemographics | experian | Grid 250m - Russia | Experian 250mx250m grid cells | yearly | [2019-01-01, 2020-01-01) | None | False | eng | 2020 | Demographics | Experian | carto-do.experian.geography_rus_grid_v2 | carto-do.experian.demographics_sociodemographi... |
14 | cdb_spatial_fea_d23a5c97 | Spatial Features - Spain (Quadgrid 15) | Spatial Features is a dataset curated by CARTO... | derived | esp | spatial_features | carto | Quadgrid 15 - Spain | Global Quadgrid (zoom level 15) | yearly | None | None | True | eng | 2020 | Derived | CARTO | carto-do-public-data.carto.geography_esp_quadg... | carto-do-public-data.carto.derived_spatialfeat... |
15 | cdb_spatial_fea_802d4d44 | Spatial Features - France (Quadgrid 15) | Spatial Features is a dataset curated by CARTO... | derived | fra | spatial_features | carto | Quadgrid 15 - France | Global Quadgrid (zoom level 15) | yearly | None | None | False | eng | 2020 | Derived | CARTO | carto-do-public-data.carto.geography_fra_quadg... | carto-do.carto.derived_spatialfeatures_fra_qua... |
16 | cdb_spatial_fea_7bd51aec | Spatial Features - Guyana (Quadgrid 15) | Spatial Features is a dataset curated by CARTO... | derived | guy | spatial_features | carto | Quadgrid 15 - Guyana | Global Quadgrid (zoom level 15) | yearly | None | None | False | eng | 2020 | Derived | CARTO | carto-do-public-data.carto.geography_guy_quadg... | carto-do.carto.derived_spatialfeatures_guy_qua... |
17 | ws_climatology_83bcb297 | Climatology - Japan (Grid 22km, hourly) | Global climatology data providing weather stat... | environmental | jpn | climatology | weather_source | Grid 22km - Global | Custom grid at 22 kilometer resolution | hourly | [2005-01-01, 2020-01-01) | yearly | False | eng | v1 | Environmental | Weather Source | carto-do.weather_source.geography_glo_grid22km_v1 | carto-do.weather_source.environmental_climatol... |
18 | uc_activity_ae564b62 | Activity - Philippines (Quadgrid 17) | Leveraging a global panel of location signals ... | human_mobility | phl | activity | unacast | Quadgrid 17 - Philippines | Quad Key Grid - Level 17 | monthly | [2019-01-01, 2020-01-01) | monthly | False | eng | v1 | Human Mobility | Unacast | carto-do-public-data.carto.geography_phl_quadg... | carto-do.unacast.humanmobility_activity_phl_qu... |
19 | uc_home_and_wo_b42b8699 | Home And Work - Philippines (Quadgrid 17) | Paired with the Activity dataset, this data pr... | human_mobility | phl | home_and_work | unacast | Quadgrid | Quadgrid (multiple zoom levels) | monthly | [2019-01-01, 2020-01-01) | monthly | False | eng | v1 | Human Mobility | Unacast | carto-do-public-data.carto.geography_phl_quadg... | carto-do.unacast.humanmobility_homeandwork_phl... |
20 | expn_sociodemogr_8d3aa47a | Sociodemographics - Philippines (Grid 250m) | Worldview combines Experian's own datasets wit... | demographics | phl | sociodemographics | experian | Grid 250m - Philippines | Experian 250mx250m grid cells | yearly | [2019-01-01, 2020-01-01) | None | False | eng | 2020 | Demographics | Experian | carto-do.experian.geography_phl_grid_v1 | carto-do.experian.demographics_sociodemographi... |
21 | ws_forecast_39e1ab6a | Forecast - Spain (Municipality, hourly) | 10 and 15-day weather forecasts | environmental | esp | forecast | weather_source | Municipality (Spain) | 2020 Municipalities, from the Instituto Geográ... | hourly | None | daily | False | eng | v1 | Environmental | Weather Source | carto-do-public-data.esp_ign.geography_esp_mun... | carto-do.weather_source.environmental_forecast... |
22 | ws_historic_43f694cc | Historic - Spain (Municipality, hourly) | Past weather data from the year 2000 to present | environmental | esp | historic | weather_source | Municipality (Spain) | 2020 Municipalities, from the Instituto Geográ... | hourly | None | daily | False | eng | v1 | Environmental | Weather Source | carto-do-public-data.esp_ign.geography_esp_mun... | carto-do.weather_source.environmental_historic... |
We identify the slug_id from the dataset we want to use. For example this one:
SpatialFeatures_esp_qk15 = Dataset.get('cdb_spatial_fea_d23a5c97')
SpatialFeatures_esp_qk15.to_dict()
{'slug': 'cdb_spatial_fea_d23a5c97', 'name': 'Spatial Features - Spain (Quadgrid 15)', 'description': 'Spatial Features is a dataset curated by CARTO providing access to a set of location-based features with global coverage that have been unified in common geographic supports (eg. Quadgrid). This product has been specially designed to facilitate spatial modeling at scale.\nSpatial Features includes core demographic data and POI aggregations by category that have been generated by processing and unifying globally available sources such as Worldpop and OpenStreetMap.\nThe current version of this product is available in two different spatial aggregations: Quadgrid level 15 (with cells of approximately 1x1km) and Quadgrid level 18 (with cells of approximately 100x100m).', 'category_id': 'derived', 'country_id': 'esp', 'data_source_id': 'spatial_features', 'provider_id': 'carto', 'geography_name': 'Quadgrid 15 - Spain', 'geography_description': 'Global Quadgrid (zoom level 15)', 'temporal_aggregation': 'yearly', 'time_coverage': None, 'update_frequency': None, 'is_public_data': True, 'lang': 'eng', 'version': '2020', 'category_name': 'Derived', 'provider_name': 'CARTO', 'geography_id': 'carto-do-public-data.carto.geography_esp_quadgrid15_v1', 'id': 'carto-do-public-data.carto.derived_spatialfeatures_esp_quadgrid15_v1_yearly_2020'}
SpatialFeatures_esp_qk15.tail()
male | geoid | female | retail | leisure | tourism | education | financial | food_drink | healthcare | population | country_iso | male_1_to_4 | male_5_to_9 | male_under_1 | female_1_to_4 | female_5_to_9 | male_10_to_14 | male_15_to_19 | male_20_to_24 | male_25_to_29 | male_30_to_34 | male_35_to_39 | male_40_to_44 | male_45_to_49 | male_50_to_54 | male_55_to_59 | male_60_to_64 | male_65_to_69 | male_70_to_74 | male_75_to_79 | country_iso_a3 | female_under_1 | transportation | female_10_to_14 | female_15_to_19 | female_20_to_24 | female_25_to_29 | female_30_to_34 | female_35_to_39 | female_40_to_44 | female_45_to_49 | female_50_to_54 | female_55_to_59 | female_60_to_64 | female_65_to_69 | female_70_to_74 | female_75_to_79 | male_80_and_over | female_80_and_over | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 3157.484307 | 031332122022031 | 3314.603374 | 202 | 2 | 30 | 3 | 20 | 217 | 17 | 6987.135426 | Spain | 120.913153 | 163.556262 | 30.859980 | 112.445169 | 152.122509 | 179.977780 | 169.691305 | 162.016885 | 168.189652 | 180.966714 | 220.835550 | 269.261748 | 263.299398 | 248.028995 | 227.292451 | 196.722677 | 161.299894 | 142.868832 | 105.937552 | ESP | 28.693915 | 62 | 169.153640 | 158.909087 | 153.972406 | 164.477591 | 177.747316 | 219.014149 | 267.756015 | 268.073208 | 256.767236 | 237.604333 | 208.339251 | 177.525427 | 167.336132 | 139.422190 | 145.765479 | 255.243802 |
1 | 3100.709744 | 031333212123113 | 3112.707666 | 397 | 7 | 18 | 13 | 29 | 227 | 41 | 6213.417445 | Spain | 116.135321 | 156.329847 | 29.640561 | 107.896359 | 146.732161 | 171.439127 | 162.141215 | 156.255203 | 163.785669 | 177.375590 | 216.347498 | 264.268170 | 258.871424 | 245.878540 | 227.791271 | 199.504164 | 161.553366 | 141.963759 | 104.895240 | ESP | 27.533142 | 115 | 163.738535 | 153.326000 | 147.104910 | 155.585855 | 167.001699 | 205.922699 | 251.286604 | 251.263774 | 238.742479 | 218.524946 | 189.368642 | 163.730100 | 155.843235 | 130.658199 | 146.533779 | 238.448327 |
2 | 5563.234994 | 031333330303331 | 5634.263423 | 267 | 5 | 33 | 10 | 32 | 230 | 39 | 11197.498466 | Spain | 207.438940 | 282.454383 | 52.943473 | 196.299369 | 263.708241 | 310.187053 | 294.944600 | 277.136621 | 291.315402 | 318.226120 | 392.201611 | 481.832879 | 471.522448 | 446.028405 | 404.935209 | 345.842258 | 282.652461 | 251.291441 | 187.960022 | ESP | 50.091947 | 124 | 293.852755 | 273.574052 | 269.563257 | 284.239360 | 302.392749 | 368.791786 | 447.273132 | 447.816753 | 427.330467 | 399.392763 | 354.964127 | 303.556543 | 285.400862 | 236.542165 | 264.321666 | 429.473098 |
3 | 5752.487674 | 033121230020013 | 5897.919277 | 392 | 9 | 19 | 22 | 41 | 239 | 46 | 13530.062792 | Spain | 214.959833 | 292.499451 | 54.862983 | 205.108661 | 275.753969 | 323.571403 | 298.909093 | 289.252283 | 296.326437 | 318.596845 | 400.574748 | 495.122733 | 488.513948 | 459.693972 | 418.807097 | 358.926419 | 296.731579 | 266.983096 | 198.371388 | ESP | 52.339912 | 54 | 304.900184 | 292.604586 | 279.560116 | 302.507961 | 327.124385 | 391.198812 | 471.563116 | 468.010031 | 448.989864 | 418.053752 | 370.225668 | 313.187995 | 291.416965 | 243.300764 | 279.784366 | 442.072536 |
4 | 4920.401140 | 033110331211321 | 5101.838268 | 386 | 12 | 24 | 11 | 17 | 240 | 38 | 10022.239175 | Spain | 185.931533 | 251.828674 | 47.454258 | 175.431490 | 237.010205 | 277.479072 | 262.715527 | 251.839640 | 260.796087 | 279.359516 | 345.971107 | 422.165983 | 413.846829 | 389.538337 | 356.188780 | 306.779508 | 250.842350 | 222.981889 | 164.327410 | ESP | 44.766853 | 49 | 263.162347 | 246.132949 | 237.480096 | 254.350030 | 276.120897 | 335.150496 | 409.423620 | 409.001029 | 392.155128 | 363.719101 | 320.472125 | 273.839742 | 257.380644 | 215.620176 | 230.354640 | 390.621340 |
5 | 10488.067253 | 120222233002302 | 10980.102434 | 206 | 5 | 39 | 9 | 19 | 240 | 29 | 21468.169211 | Spain | 399.577618 | 543.086551 | 101.981938 | 374.481191 | 504.032327 | 597.000008 | 565.179574 | 533.200418 | 543.255342 | 585.150797 | 735.436253 | 911.480321 | 896.233391 | 834.498850 | 751.953428 | 646.956267 | 529.950073 | 474.194427 | 357.228428 | ESP | 95.560624 | 172 | 561.082684 | 524.800891 | 514.948448 | 560.215039 | 604.717769 | 723.562416 | 869.828768 | 866.350490 | 839.930112 | 790.127512 | 696.650138 | 593.946884 | 554.767677 | 456.639502 | 481.703569 | 848.459962 |
6 | 10691.776215 | 120222233002303 | 11193.368209 | 144 | 6 | 44 | 13 | 30 | 240 | 40 | 21885.144834 | Spain | 407.338614 | 553.634872 | 103.962726 | 381.754702 | 513.822090 | 608.595502 | 576.157012 | 543.556738 | 553.806953 | 596.516153 | 749.720600 | 929.183921 | 913.640858 | 850.707247 | 766.558575 | 659.522022 | 540.243248 | 483.404662 | 364.166840 | ESP | 97.416694 | 193 | 571.980562 | 534.994066 | 524.950252 | 571.096036 | 616.463167 | 737.616141 | 886.723386 | 883.177559 | 856.244038 | 805.474094 | 710.181137 | 605.483075 | 565.542897 | 465.508790 | 491.059674 | 864.939521 |
7 | 7046.002774 | 033111012101130 | 7718.364580 | 575 | 12 | 81 | 38 | 25 | 501 | 48 | 14764.367519 | Spain | 273.552370 | 370.482714 | 69.817227 | 258.793396 | 349.655445 | 408.169877 | 384.237815 | 361.315362 | 370.977625 | 400.892505 | 496.398761 | 607.745009 | 596.215580 | 555.359332 | 500.855984 | 426.264436 | 351.705464 | 316.607648 | 235.482475 | ESP | 66.039259 | 151 | 388.281731 | 365.377672 | 359.531128 | 387.915307 | 417.419276 | 507.002730 | 617.319966 | 615.971377 | 596.200625 | 559.683816 | 497.777880 | 421.235484 | 391.043486 | 324.241345 | 319.922590 | 594.874658 |
8 | 4880.515167 | 033111230333220 | 5060.481569 | 234 | 5 | 60 | 4 | 17 | 247 | 15 | 9940.996620 | Spain | 184.424333 | 249.787283 | 47.069582 | 174.009405 | 235.088947 | 275.229765 | 260.585895 | 249.798178 | 258.682006 | 277.094953 | 343.166571 | 418.743808 | 410.492089 | 386.380638 | 353.301415 | 304.292676 | 248.808967 | 221.174350 | 162.995328 | ESP | 44.403963 | 79 | 261.029094 | 244.137743 | 235.555022 | 252.288204 | 273.882590 | 332.433680 | 406.104742 | 405.685562 | 388.976229 | 360.770708 | 317.874315 | 271.619926 | 255.294256 | 213.872313 | 228.487330 | 387.454870 |
9 | 6940.973733 | 031333033310323 | 7317.757894 | 320 | 15 | 21 | 1 | 36 | 251 | 33 | 14258.731450 | Spain | 264.518769 | 357.406697 | 67.511636 | 249.595717 | 338.068831 | 394.396529 | 372.588657 | 354.263577 | 365.453562 | 393.743390 | 489.236870 | 600.947986 | 590.391082 | 546.894724 | 499.339633 | 430.833752 | 355.374173 | 313.106841 | 232.581040 | ESP | 63.692182 | 110 | 374.778970 | 351.354713 | 341.896086 | 367.449503 | 396.543658 | 479.801048 | 582.162156 | 580.282089 | 565.227739 | 524.879842 | 461.562910 | 391.095832 | 370.309326 | 307.973901 | 312.384817 | 571.083390 |
CARTO's Data Observatory also provides direct access to a group of public datasets. You can navigate and explore our Spatial Data Catalog from within your Python notebook with the Data Discovery methods in CARTOFrames or using our Spatial Data Catalog.
"Who's on First" is a gazetteer (o big list) of places, each with a stable identifier and some number of descriptive properties about that location.
We can use the WoF GeoJSON to find the city boundaries to use then for filtering the data from other datasets from the Data Observatory.
Catalog().provider('whos_on_first').public().geographies.to_dataframe()
slug | name | description | country_id | provider_id | geom_type | geom_coverage | update_frequency | is_public_data | lang | version | provider_name | id | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | wof_ancestors_eaaeac75 | Ancestors - Global | A normalized view of the hierarchies in 'geojs... | glo | whos_on_first | MULTIPLE | None | None | True | eng | 20190520 | Who's On First | carto-do-public-data.whos_on_first.geography_g... |
1 | wof_concordance_392f80ad | Concordances - Global | Relationship between Who's On First identifier... | glo | whos_on_first | MULTIPLE | None | None | True | eng | 20190520 | Who's On First | carto-do-public-data.whos_on_first.geography_g... |
2 | wof_geojson_4e78587c | GeoJSON - Global | The main table in Who's On First. Holds all th... | glo | whos_on_first | MULTIPLE | None | None | True | eng | 20190520 | Who's On First | carto-do-public-data.whos_on_first.geography_g... |
3 | wof_names_5a30fa98 | Names - Global | What things are called in Who's On First. A no... | glo | whos_on_first | MULTIPLE | None | None | True | eng | 20190520 | Who's On First | carto-do-public-data.whos_on_first.geography_g... |
4 | wof_spr_850ad7e9 | Standard Places Response - Global | The "Standard Places Response" (or SPR) is an ... | glo | whos_on_first | MULTIPLE | None | None | True | eng | 20190520 | Who's On First | carto-do-public-data.whos_on_first.geography_g... |
Note that the ID to access the WoF GeoJSON table is 'wof_geojson_4e78587c'.
wof_geojson = Geography.get('wof_geojson_4e78587c')
wof_geojson.to_dict()
{'slug': 'wof_geojson_4e78587c', 'name': 'GeoJSON - Global', 'description': "The main table in Who's On First. Holds all the relevant information for a place in the 'body' JSON field.", 'country_id': 'glo', 'provider_id': 'whos_on_first', 'geom_type': 'MULTIPLE', 'update_frequency': None, 'is_public_data': True, 'lang': 'eng', 'version': '20190520', 'provider_name': "Who's On First", 'id': 'carto-do-public-data.whos_on_first.geography_glo_geojson_20190520'}
Now we are going to perform a query to the table in order to retrieve the different geometries given a city name and a country ISO Alpha-2 code. As we are looking for city boundaries, we can also limit our search to the placetype = 'locality' if we find that this is the specific type of place for our needs.
city_name = "'Madrid'"
country_code = "'ES'"
placetype = "'locality'"
sql_query = f"SELECT * FROM $geography$ WHERE name = {city_name} AND country = {country_code}"
wof_geojson_filtered = wof_geojson.to_dataframe(sql_query=sql_query)
wof_geojson_filtered
geoid | id | body | name | country | parent_id | is_current | placetype | geometry_type | bbox | geom | lastmodified | lastmodified_timestamp | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 85682783 | 85682783 | {"id": 85682783, "type": "Feature", "propertie... | Madrid | ES | 404227387 | 1 | region | MultiPolygon | POLYGON((-3.05298331 39.88471951, -3.05298331 ... | MULTIPOLYGON(((-4.31950989 40.64764365, -4.318... | 1553814873 | 2019-03-28 23:14:33+00:00 |
1 | 101748283 | 101748283 | {"id": 101748283, "type": "Feature", "properti... | Madrid | ES | 85682783 | 1 | locality | MultiPolygon | POLYGON((-3.5180508952556 40.312064309035, -3.... | POLYGON((-3.77455610356056 40.4003144849762, -... | 1536881193 | 2018-09-13 23:26:33+00:00 |
2 | 404338863 | 404338863 | {"id": 404338863, "type": "Feature", "properti... | Madrid | ES | 85682783 | 0 | localadmin | Polygon | POLYGON((-3.51823494 40.31206394, -3.51823494 ... | POLYGON((-3.88557655 40.57445562, -3.88560442 ... | 1513267506 | 2017-12-14 16:05:06+00:00 |
It may happen that we find that there are more than one locality with the same name. In order to select the right polygon, we can build a map with a category widget that will allow us to decide for the specific geometry that we are looking for.
wof_geojson_filtered['geoid_str'] = wof_geojson_filtered['geoid'].astype(str)
Map(
Layer(
wof_geojson_filtered, # where the data comes from
color_category_style('geoid_str',palette='Vivid',opacity=0.6,stroke_width=0.2),
widgets=[category_widget('geoid_str','Select geoid to visualize')],
popup_hover=[popup_element('geoid','geoid'),
popup_element('name','name'),
popup_element('placetype','placetype')],
legends=color_category_legend('Geoid'),
geom_col='geom', #the name of the column on the query that has a GEOGRAPHY data
encode_data = False
)
)
Once we know which geometry (i.e. polygon of city boundaries) is the right one for our tests, we should copy/note its associated geoid.
As in this example we want to filter the data for Madrid, we will use geoid = ' 101748283'.
"""Helper function for downloading only the data within the area (geometry) of interest
Args:
do_dataset: DO Dataset you'd like to download for a specific area of interest
do_geom_dataset: DO Dataset containing the geometry you'd like to use as filter (your area of interest)
target_geoid: geoid of the geometry you'd like to use as filter (your area of interest)
"""
def filter_data(do_dataset, do_geom_dataset, target_geoid):
do_geom_dataset_id=do_geom_dataset.id
sql_query = f"""WITH do_geom AS (
SELECT geom
FROM `{do_geom_dataset_id}`
WHERE geoid = '{target_geoid}')
SELECT do_d.* FROM $dataset$ do_d, do_geom WHERE ST_Intersects(do_d.geom, do_geom.geom)"""
filtered_data = do_dataset.to_dataframe(sql_query = sql_query)
return filtered_data
SpatialFeatures_esp_qk15_madrid = filter_data(SpatialFeatures_esp_qk15,wof_geojson,'101748283')
SpatialFeatures_esp_qk15_madrid.head()
geoid | do_date | country_iso | country_iso_a3 | population | female | male | female_under_1 | female_1_to_4 | female_5_to_9 | female_10_to_14 | female_15_to_19 | female_20_to_24 | female_25_to_29 | female_30_to_34 | female_35_to_39 | female_40_to_44 | female_45_to_49 | female_50_to_54 | female_55_to_59 | female_60_to_64 | female_65_to_69 | female_70_to_74 | female_75_to_79 | female_80_and_over | male_under_1 | male_1_to_4 | male_5_to_9 | male_10_to_14 | male_15_to_19 | male_20_to_24 | male_25_to_29 | male_30_to_34 | male_35_to_39 | male_40_to_44 | male_45_to_49 | male_50_to_54 | male_55_to_59 | male_60_to_64 | male_65_to_69 | male_70_to_74 | male_75_to_79 | male_80_and_over | retail | education | financial | food_drink | healthcare | leisure | tourism | transportation | geom | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 33111012100231 | 2020-01-01 | Spain | ESP | 2821.407676 | 1474.946532 | 1346.461074 | 12.619821 | 49.454314 | 66.817661 | 74.198982 | 69.822116 | 68.704864 | 74.128956 | 79.767044 | 96.886058 | 117.967211 | 117.709500 | 113.931398 | 106.953191 | 95.123228 | 80.496302 | 74.726742 | 61.961138 | 113.678006 | 13.341774 | 52.274691 | 70.797669 | 77.999520 | 73.426207 | 69.045821 | 70.892240 | 76.608848 | 94.859687 | 116.137476 | 113.934257 | 106.126800 | 95.711441 | 81.457317 | 67.209413 | 60.502372 | 44.999696 | 61.135845 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | POLYGON((-3.8232421875 40.3883968738836, -3.82... |
1 | 33111012112103 | 2020-01-01 | Spain | ESP | 4211.926348 | 2201.867659 | 2010.058675 | 18.839446 | 73.827662 | 99.748466 | 110.767639 | 104.233648 | 102.565767 | 110.663101 | 119.079892 | 144.635941 | 176.106851 | 175.722126 | 170.081996 | 159.664616 | 142.004305 | 120.168563 | 111.555494 | 92.498418 | 169.703728 | 19.917212 | 78.038052 | 105.689998 | 116.441251 | 109.613998 | 103.074766 | 105.831180 | 114.365187 | 141.610877 | 173.375340 | 170.086271 | 158.430937 | 142.882415 | 121.603210 | 100.333285 | 90.320708 | 67.177605 | 91.266383 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | POLYGON((-3.62548828125 40.3632883409158, -3.6... |
2 | 33111010333003 | 2020-01-01 | Spain | ESP | 2940.160426 | 1537.026955 | 1403.133529 | 13.150988 | 51.535844 | 69.630015 | 77.322015 | 72.760923 | 71.596650 | 77.249042 | 83.124433 | 100.963990 | 122.932445 | 122.663887 | 118.726763 | 111.454846 | 99.126961 | 83.884388 | 77.871984 | 64.569077 | 118.462706 | 13.903329 | 54.474931 | 73.777536 | 81.282515 | 76.516709 | 71.951957 | 73.876093 | 79.833307 | 98.852327 | 121.025696 | 118.729742 | 110.593669 | 99.739928 | 84.885847 | 70.038255 | 63.048912 | 46.893730 | 63.709045 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | POLYGON((-3.58154296875 40.4970923726957, -3.5... |
3 | 33111010323201 | 2020-01-01 | Spain | ESP | 1953.053871 | 1020.997463 | 932.056375 | 8.735778 | 34.233599 | 46.252975 | 51.362523 | 48.332735 | 47.559346 | 51.314049 | 55.216883 | 67.067124 | 81.660059 | 81.481666 | 78.866360 | 74.035860 | 65.846845 | 55.721696 | 51.727849 | 42.891157 | 78.690959 | 9.235533 | 36.185940 | 49.008038 | 53.993354 | 50.827584 | 47.795366 | 49.073507 | 53.030693 | 65.664412 | 80.393471 | 78.868340 | 73.463809 | 66.254020 | 56.386934 | 46.524153 | 41.881360 | 31.149994 | 42.319865 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | POLYGON((-3.75732421875 40.4720243969206, -3.7... |
4 | 33111010302231 | 2020-01-01 | Spain | ESP | 26.484580 | 13.845337 | 12.639243 | 0.118462 | 0.464228 | 0.627218 | 0.696507 | 0.655421 | 0.644933 | 0.695849 | 0.748774 | 0.909470 | 1.107359 | 1.104940 | 1.069475 | 1.003971 | 0.892923 | 0.755620 | 0.701461 | 0.581630 | 1.067097 | 0.125239 | 0.490703 | 0.664578 | 0.732182 | 0.689252 | 0.648134 | 0.665466 | 0.719128 | 0.890449 | 1.090184 | 1.069502 | 0.996213 | 0.898444 | 0.764641 | 0.630895 | 0.567936 | 0.422413 | 0.573883 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | POLYGON((-3.8232421875 40.5889281696937, -3.82... |
Map(
Layer(
SpatialFeatures_esp_qk15_madrid, # where the data comes from
color_continuous_style('population', palette=palettes.magenta,opacity=0.75,stroke_width=0.5),
widgets=[formula_widget('population','sum','Total Population'),histogram_widget('population','Population by cell')],
popup_hover=[popup_element('population','Population')],
geom_col='geom', #the name of the column on the query that has a GEOGRAPHY data
encode_data = False
)
)