{ "cells": [ { "cell_type": "markdown", "metadata": { "id": "Y0IPjMQvr_2h" }, "source": [ "## Filtering a Data Observatory dataset using \"Who's On First\" in CARTOFrames" ] }, { "cell_type": "markdown", "metadata": { "id": "VOVRcqXNrnUu" }, "source": [ "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](https://carto.com/spatial-data-catalog/) using [CARTOFrames](https://carto.com/cartoframes/) methods.\n", "\n", "The notebook is organized as follows:\n", "0. Setup account\n", "1. Access a dataset from a Data Observatory subscription to be filtered\n", "2. Who's on First for filtering data in cities\n", "\n", "**Documentation**\n", "- CARTO Spatial Data Catalogue - [link](https://carto.com/spatial-data-catalog/browser/)\n", "- CARTOFrames technical documentation - [link](https://carto.com/developers/cartoframes/)\n", "- \"Who's on First\" GeoJSON data product - [link](https://carto.com/spatial-data-catalog/browser/geography/wof_geojson_4e78587c/data)" ] }, { "cell_type": "markdown", "metadata": { "id": "nN8F02lPsOBU" }, "source": [ "### 0. Setup" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "id": "bZcjZ3X3sUMr" }, "outputs": [], "source": [ "import geopandas as gpd\n", "import pandas as pd\n", "\n", "from cartoframes.auth import set_default_credentials\n", "from cartoframes.data.observatory import *\n", "from cartoframes.viz import *\n", "\n", "pd.set_option('display.max_columns', None)" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "id": "n02GAhWlsXbM" }, "outputs": [], "source": [ "set_default_credentials('creds.json')" ] }, { "cell_type": "markdown", "metadata": { "id": "gdNCauTQtarh" }, "source": [ "### 1. Access a dataset from a Data Observatory subscription to be filtered" ] }, { "cell_type": "markdown", "metadata": { "id": "GRlKWu8xAfGX" }, "source": [ "First, we check our data subscriptions from the Data Observatory to select which dataset we want to filter." ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 1000 }, "id": "JI7RCXrnAsNc", "outputId": "9d409e6c-00c3-48cc-afab-f3afc7563fcd" }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
slugnamedescriptioncategory_idcountry_iddata_source_idprovider_idgeography_namegeography_descriptiontemporal_aggregationtime_coverageupdate_frequencyis_public_datalangversioncategory_nameprovider_namegeography_idid
0ags_sociodemogr_a7e14220Sociodemographics - United States of America (...Census and ACS sociodemographic data estimated...demographicsusasociodemographicsagsCensus Block Group - United States of AmericaNoneyearlyNoneNoneFalseeng2020DemographicsApplied Geographic Solutionscarto-do.ags.geography_usa_blockgroup_2015carto-do.ags.demographics_sociodemographics_us...
1ags_retailpoten_aaf25a8cRetail Potential - United States of America (C...The retail potential database consists of aver...demographicsusaretailpotentialagsCensus Block Group - United States of America ...Shoreline clipped TIGER/Line boundaries. More ...yearly[2018-01-01, 2019-01-01)NoneFalseeng2019DemographicsApplied Geographic Solutionscarto-do-public-data.carto.geography_usa_block...carto-do.ags.demographics_retailpotential_usa_...
2pb_consumer_po_62cddc04Points Of Interest - Consumer - United States ...Consumer Point of interest database per catego...points_of_interestusaconsumer_points_of_interestpitney_bowesLatitude/Longitude - United States of AmericaLocation of Points of InterestmonthlyNonemonthlyFalseengv1Points of InterestPitney Bowescarto-do.pitney_bowes.geography_usa_latlon_v1carto-do.pitney_bowes.pointsofinterest_consume...
3ags_sociodemogr_f510a947Sociodemographics - United States of America (...Census and ACS sociodemographic data estimated...demographicsusasociodemographicsagsCensus Block Group - United States of America ...Shoreline clipped TIGER/Line boundaries. More ...yearly[2019-01-01, 2020-01-01)NoneFalseeng2019DemographicsApplied Geographic Solutionscarto-do-public-data.carto.geography_usa_block...carto-do.ags.demographics_sociodemographics_us...
4ags_consumer_sp_dbabddfbConsumer Spending - United States of America (...The Consumer Expenditure database consists of ...demographicsusaconsumer_spendingagsCensus Block Group - United States of AmericaNoneyearlyNoneNoneFalseeng2020DemographicsApplied Geographic Solutionscarto-do.ags.geography_usa_blockgroup_2015carto-do.ags.demographics_consumerspending_usa...
5spa_geosocial_s_d5dc42aeGeosocial Segments - United States of America ...By analysing feeds from Twitter, Instagram, Me...behavioralusageosocial_segmentsspatial_aiCensus Block Group - United States of America ...Shoreline clipped TIGER/Line boundaries. More ...quarterly[2020-01-01, 2020-04-01)quarterlyFalseengv1BehavioralSpatial.aicarto-do-public-data.carto.geography_usa_block...carto-do.spatial_ai.behavioral_geosocialsegmen...
6mc_geographic__7980c5c3Geographic Insights - United States of America...Geographic Insights validate, evaluate and ben...financialusageographic_insightsmastercardCensus Block Group - United States of America ...Shoreline clipped TIGER/Line boundaries. More ...monthly[2019-01-01, 2020-01-01)monthlyFalseengv1FinancialMastercardcarto-do-public-data.carto.geography_usa_block...carto-do.mastercard.financial_geographicinsigh...
7pb_points_of_i_94bda91bPoints Of Interest - Spain (Latitude/Longitude)Point of interest database per categoriespoints_of_interestesppoints_of_interestpitney_bowesLatitude/Longitude - SpainLocation of Points of InterestmonthlyNonemonthlyFalseengv1Points of InterestPitney Bowescarto-do.pitney_bowes.geography_esp_latlon_v1carto-do.pitney_bowes.pointsofinterest_pointso...
8u360_sociodemogr_28e93b81Sociodemographics - Spain (Grid 100m)Curation of socio-demographic and socio-econom...demographicsespsociodemographicsunica360Grid 100m100x100m gridyearlyNoneNoneFalseeng2019DemographicsUnica360carto-do.unica360.geography_esp_grid100x100m_2019carto-do.unica360.demographics_sociodemographi...
\n", "
" ], "text/plain": [ " slug \\\n", "0 ags_sociodemogr_a7e14220 \n", "1 ags_retailpoten_aaf25a8c \n", "2 pb_consumer_po_62cddc04 \n", "3 ags_sociodemogr_f510a947 \n", "4 ags_consumer_sp_dbabddfb \n", "5 spa_geosocial_s_d5dc42ae \n", "6 mc_geographic__7980c5c3 \n", "7 pb_points_of_i_94bda91b \n", "8 u360_sociodemogr_28e93b81 \n", "\n", " name \\\n", "0 Sociodemographics - United States of America (... \n", "1 Retail Potential - United States of America (C... \n", "2 Points Of Interest - Consumer - United States ... \n", "3 Sociodemographics - United States of America (... \n", "4 Consumer Spending - United States of America (... \n", "5 Geosocial Segments - United States of America ... \n", "6 Geographic Insights - United States of America... \n", "7 Points Of Interest - Spain (Latitude/Longitude) \n", "8 Sociodemographics - Spain (Grid 100m) \n", "\n", " description category_id \\\n", "0 Census and ACS sociodemographic data estimated... demographics \n", "1 The retail potential database consists of aver... demographics \n", "2 Consumer Point of interest database per catego... points_of_interest \n", "3 Census and ACS sociodemographic data estimated... demographics \n", "4 The Consumer Expenditure database consists of ... demographics \n", "5 By analysing feeds from Twitter, Instagram, Me... behavioral \n", "6 Geographic Insights validate, evaluate and ben... financial \n", "7 Point of interest database per categories points_of_interest \n", "8 Curation of socio-demographic and socio-econom... demographics \n", "\n", " country_id data_source_id provider_id \\\n", "0 usa sociodemographics ags \n", "1 usa retailpotential ags \n", "2 usa consumer_points_of_interest pitney_bowes \n", "3 usa sociodemographics ags \n", "4 usa consumer_spending ags \n", "5 usa geosocial_segments spatial_ai \n", "6 usa geographic_insights mastercard \n", "7 esp points_of_interest pitney_bowes \n", "8 esp sociodemographics unica360 \n", "\n", " geography_name \\\n", "0 Census Block Group - United States of America \n", "1 Census Block Group - United States of America ... \n", "2 Latitude/Longitude - United States of America \n", "3 Census Block Group - United States of America ... \n", "4 Census Block Group - United States of America \n", "5 Census Block Group - United States of America ... \n", "6 Census Block Group - United States of America ... \n", "7 Latitude/Longitude - Spain \n", "8 Grid 100m \n", "\n", " geography_description temporal_aggregation \\\n", "0 None yearly \n", "1 Shoreline clipped TIGER/Line boundaries. More ... yearly \n", "2 Location of Points of Interest monthly \n", "3 Shoreline clipped TIGER/Line boundaries. More ... yearly \n", "4 None yearly \n", "5 Shoreline clipped TIGER/Line boundaries. More ... quarterly \n", "6 Shoreline clipped TIGER/Line boundaries. More ... monthly \n", "7 Location of Points of Interest monthly \n", "8 100x100m grid yearly \n", "\n", " time_coverage update_frequency is_public_data lang version \\\n", "0 None None False eng 2020 \n", "1 [2018-01-01, 2019-01-01) None False eng 2019 \n", "2 None monthly False eng v1 \n", "3 [2019-01-01, 2020-01-01) None False eng 2019 \n", "4 None None False eng 2020 \n", "5 [2020-01-01, 2020-04-01) quarterly False eng v1 \n", "6 [2019-01-01, 2020-01-01) monthly False eng v1 \n", "7 None monthly False eng v1 \n", "8 None None False eng 2019 \n", "\n", " category_name provider_name \\\n", "0 Demographics Applied Geographic Solutions \n", "1 Demographics Applied Geographic Solutions \n", "2 Points of Interest Pitney Bowes \n", "3 Demographics Applied Geographic Solutions \n", "4 Demographics Applied Geographic Solutions \n", "5 Behavioral Spatial.ai \n", "6 Financial Mastercard \n", "7 Points of Interest Pitney Bowes \n", "8 Demographics Unica360 \n", "\n", " geography_id \\\n", "0 carto-do.ags.geography_usa_blockgroup_2015 \n", "1 carto-do-public-data.carto.geography_usa_block... \n", "2 carto-do.pitney_bowes.geography_usa_latlon_v1 \n", "3 carto-do-public-data.carto.geography_usa_block... \n", "4 carto-do.ags.geography_usa_blockgroup_2015 \n", "5 carto-do-public-data.carto.geography_usa_block... \n", "6 carto-do-public-data.carto.geography_usa_block... \n", "7 carto-do.pitney_bowes.geography_esp_latlon_v1 \n", "8 carto-do.unica360.geography_esp_grid100x100m_2019 \n", "\n", " id \n", "0 carto-do.ags.demographics_sociodemographics_us... \n", "1 carto-do.ags.demographics_retailpotential_usa_... \n", "2 carto-do.pitney_bowes.pointsofinterest_consume... \n", "3 carto-do.ags.demographics_sociodemographics_us... \n", "4 carto-do.ags.demographics_consumerspending_usa... \n", "5 carto-do.spatial_ai.behavioral_geosocialsegmen... \n", "6 carto-do.mastercard.financial_geographicinsigh... \n", "7 carto-do.pitney_bowes.pointsofinterest_pointso... \n", "8 carto-do.unica360.demographics_sociodemographi... " ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Catalog().subscriptions().datasets.to_dataframe()" ] }, { "cell_type": "markdown", "metadata": { "id": "RZX4JottBHQF" }, "source": [ "We identify the slug_id from the dataset we want to use. For example this one:" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "id": "E64jBp4dBNl7" }, "outputs": [], "source": [ "SpatialFeatures_esp_qk15 = Dataset.get('cdb_spatial_fea_d23a5c97')" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "pomGs2ssCxRv", "outputId": "c4254a04-515d-49dc-dceb-79a7b8fe5062" }, "outputs": [ { "data": { "text/plain": [ "{'slug': 'cdb_spatial_fea_d23a5c97',\n", " 'name': 'Spatial Features - Spain (Quadgrid 15)',\n", " '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).',\n", " 'category_id': 'derived',\n", " 'country_id': 'esp',\n", " 'data_source_id': 'spatial_features',\n", " 'provider_id': 'carto',\n", " 'geography_name': 'Quadgrid 15 - Spain',\n", " 'geography_description': 'Global Quadgrid (zoom level 15)',\n", " 'temporal_aggregation': 'yearly',\n", " 'time_coverage': None,\n", " 'update_frequency': None,\n", " 'is_public_data': True,\n", " 'lang': 'eng',\n", " 'version': '2020',\n", " 'category_name': 'Derived',\n", " 'provider_name': 'CARTO',\n", " 'geography_id': 'carto-do-public-data.carto.geography_esp_quadgrid15_v1',\n", " 'id': 'carto-do-public-data.carto.derived_spatialfeatures_esp_quadgrid15_v1_yearly_2020'}" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SpatialFeatures_esp_qk15.to_dict()" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 379 }, "id": "fvAIhMg9C7Ij", "outputId": "ec7ed779-d922-47df-e386-0513ad3fd846" }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
malegeoidfemaleretailleisuretourismeducationfinancialfood_drinkhealthcarepopulationcountry_isomale_1_to_4male_5_to_9male_under_1female_1_to_4female_5_to_9male_10_to_14male_15_to_19male_20_to_24male_25_to_29male_30_to_34male_35_to_39male_40_to_44male_45_to_49male_50_to_54male_55_to_59male_60_to_64male_65_to_69male_70_to_74male_75_to_79country_iso_a3female_under_1transportationfemale_10_to_14female_15_to_19female_20_to_24female_25_to_29female_30_to_34female_35_to_39female_40_to_44female_45_to_49female_50_to_54female_55_to_59female_60_to_64female_65_to_69female_70_to_74female_75_to_79male_80_and_overfemale_80_and_over
03157.4843070313321220220313314.603374202230320217176987.135426Spain120.913153163.55626230.859980112.445169152.122509179.977780169.691305162.016885168.189652180.966714220.835550269.261748263.299398248.028995227.292451196.722677161.299894142.868832105.937552ESP28.69391562169.153640158.909087153.972406164.477591177.747316219.014149267.756015268.073208256.767236237.604333208.339251177.525427167.336132139.422190145.765479255.243802
13100.7097440313332121231133112.7076663977181329227416213.417445Spain116.135321156.32984729.640561107.896359146.732161171.439127162.141215156.255203163.785669177.375590216.347498264.268170258.871424245.878540227.791271199.504164161.553366141.963759104.895240ESP27.533142115163.738535153.326000147.104910155.585855167.001699205.922699251.286604251.263774238.742479218.524946189.368642163.730100155.843235130.658199146.533779238.448327
25563.2349940313333303033315634.26342326753310322303911197.498466Spain207.438940282.45438352.943473196.299369263.708241310.187053294.944600277.136621291.315402318.226120392.201611481.832879471.522448446.028405404.935209345.842258282.652461251.291441187.960022ESP50.091947124293.852755273.574052269.563257284.239360302.392749368.791786447.273132447.816753427.330467399.392763354.964127303.556543285.400862236.542165264.321666429.473098
35752.4876740331212300200135897.91927739291922412394613530.062792Spain214.959833292.49945154.862983205.108661275.753969323.571403298.909093289.252283296.326437318.596845400.574748495.122733488.513948459.693972418.807097358.926419296.731579266.983096198.371388ESP52.33991254304.900184292.604586279.560116302.507961327.124385391.198812471.563116468.010031448.989864418.053752370.225668313.187995291.416965243.300764279.784366442.072536
44920.4011400331103312113215101.838268386122411172403810022.239175Spain185.931533251.82867447.454258175.431490237.010205277.479072262.715527251.839640260.796087279.359516345.971107422.165983413.846829389.538337356.188780306.779508250.842350222.981889164.327410ESP44.76685349263.162347246.132949237.480096254.350030276.120897335.150496409.423620409.001029392.155128363.719101320.472125273.839742257.380644215.620176230.354640390.621340
510488.06725312022223300230210980.1024342065399192402921468.169211Spain399.577618543.086551101.981938374.481191504.032327597.000008565.179574533.200418543.255342585.150797735.436253911.480321896.233391834.498850751.953428646.956267529.950073474.194427357.228428ESP95.560624172561.082684524.800891514.948448560.215039604.717769723.562416869.828768866.350490839.930112790.127512696.650138593.946884554.767677456.639502481.703569848.459962
610691.77621512022223300230311193.36820914464413302404021885.144834Spain407.338614553.634872103.962726381.754702513.822090608.595502576.157012543.556738553.806953596.516153749.720600929.183921913.640858850.707247766.558575659.522022540.243248483.404662364.166840ESP97.416694193571.980562534.994066524.950252571.096036616.463167737.616141886.723386883.177559856.244038805.474094710.181137605.483075565.542897465.508790491.059674864.939521
77046.0027740331110121011307718.364580575128138255014814764.367519Spain273.552370370.48271469.817227258.793396349.655445408.169877384.237815361.315362370.977625400.892505496.398761607.745009596.215580555.359332500.855984426.264436351.705464316.607648235.482475ESP66.039259151388.281731365.377672359.531128387.915307417.419276507.002730617.319966615.971377596.200625559.683816497.777880421.235484391.043486324.241345319.922590594.874658
84880.5151670331112303332205060.481569234560417247159940.996620Spain184.424333249.78728347.069582174.009405235.088947275.229765260.585895249.798178258.682006277.094953343.166571418.743808410.492089386.380638353.301415304.292676248.808967221.174350162.995328ESP44.40396379261.029094244.137743235.555022252.288204273.882590332.433680406.104742405.685562388.976229360.770708317.874315271.619926255.294256213.872313228.487330387.454870
96940.9737330313330333103237317.75789432015211362513314258.731450Spain264.518769357.40669767.511636249.595717338.068831394.396529372.588657354.263577365.453562393.743390489.236870600.947986590.391082546.894724499.339633430.833752355.374173313.106841232.581040ESP63.692182110374.778970351.354713341.896086367.449503396.543658479.801048582.162156580.282089565.227739524.879842461.562910391.095832370.309326307.973901312.384817571.083390
\n", "
" ], "text/plain": [ " male geoid female retail leisure tourism \\\n", "0 3157.484307 031332122022031 3314.603374 202 2 30 \n", "1 3100.709744 031333212123113 3112.707666 397 7 18 \n", "2 5563.234994 031333330303331 5634.263423 267 5 33 \n", "3 5752.487674 033121230020013 5897.919277 392 9 19 \n", "4 4920.401140 033110331211321 5101.838268 386 12 24 \n", "5 10488.067253 120222233002302 10980.102434 206 5 39 \n", "6 10691.776215 120222233002303 11193.368209 144 6 44 \n", "7 7046.002774 033111012101130 7718.364580 575 12 81 \n", "8 4880.515167 033111230333220 5060.481569 234 5 60 \n", "9 6940.973733 031333033310323 7317.757894 320 15 21 \n", "\n", " education financial food_drink healthcare population country_iso \\\n", "0 3 20 217 17 6987.135426 Spain \n", "1 13 29 227 41 6213.417445 Spain \n", "2 10 32 230 39 11197.498466 Spain \n", "3 22 41 239 46 13530.062792 Spain \n", "4 11 17 240 38 10022.239175 Spain \n", "5 9 19 240 29 21468.169211 Spain \n", "6 13 30 240 40 21885.144834 Spain \n", "7 38 25 501 48 14764.367519 Spain \n", "8 4 17 247 15 9940.996620 Spain \n", "9 1 36 251 33 14258.731450 Spain \n", "\n", " male_1_to_4 male_5_to_9 male_under_1 female_1_to_4 female_5_to_9 \\\n", "0 120.913153 163.556262 30.859980 112.445169 152.122509 \n", "1 116.135321 156.329847 29.640561 107.896359 146.732161 \n", "2 207.438940 282.454383 52.943473 196.299369 263.708241 \n", "3 214.959833 292.499451 54.862983 205.108661 275.753969 \n", "4 185.931533 251.828674 47.454258 175.431490 237.010205 \n", "5 399.577618 543.086551 101.981938 374.481191 504.032327 \n", "6 407.338614 553.634872 103.962726 381.754702 513.822090 \n", "7 273.552370 370.482714 69.817227 258.793396 349.655445 \n", "8 184.424333 249.787283 47.069582 174.009405 235.088947 \n", "9 264.518769 357.406697 67.511636 249.595717 338.068831 \n", "\n", " male_10_to_14 male_15_to_19 male_20_to_24 male_25_to_29 male_30_to_34 \\\n", "0 179.977780 169.691305 162.016885 168.189652 180.966714 \n", "1 171.439127 162.141215 156.255203 163.785669 177.375590 \n", "2 310.187053 294.944600 277.136621 291.315402 318.226120 \n", "3 323.571403 298.909093 289.252283 296.326437 318.596845 \n", "4 277.479072 262.715527 251.839640 260.796087 279.359516 \n", "5 597.000008 565.179574 533.200418 543.255342 585.150797 \n", "6 608.595502 576.157012 543.556738 553.806953 596.516153 \n", "7 408.169877 384.237815 361.315362 370.977625 400.892505 \n", "8 275.229765 260.585895 249.798178 258.682006 277.094953 \n", "9 394.396529 372.588657 354.263577 365.453562 393.743390 \n", "\n", " male_35_to_39 male_40_to_44 male_45_to_49 male_50_to_54 male_55_to_59 \\\n", "0 220.835550 269.261748 263.299398 248.028995 227.292451 \n", "1 216.347498 264.268170 258.871424 245.878540 227.791271 \n", "2 392.201611 481.832879 471.522448 446.028405 404.935209 \n", "3 400.574748 495.122733 488.513948 459.693972 418.807097 \n", "4 345.971107 422.165983 413.846829 389.538337 356.188780 \n", "5 735.436253 911.480321 896.233391 834.498850 751.953428 \n", "6 749.720600 929.183921 913.640858 850.707247 766.558575 \n", "7 496.398761 607.745009 596.215580 555.359332 500.855984 \n", "8 343.166571 418.743808 410.492089 386.380638 353.301415 \n", "9 489.236870 600.947986 590.391082 546.894724 499.339633 \n", "\n", " male_60_to_64 male_65_to_69 male_70_to_74 male_75_to_79 country_iso_a3 \\\n", "0 196.722677 161.299894 142.868832 105.937552 ESP \n", "1 199.504164 161.553366 141.963759 104.895240 ESP \n", "2 345.842258 282.652461 251.291441 187.960022 ESP \n", "3 358.926419 296.731579 266.983096 198.371388 ESP \n", "4 306.779508 250.842350 222.981889 164.327410 ESP \n", "5 646.956267 529.950073 474.194427 357.228428 ESP \n", "6 659.522022 540.243248 483.404662 364.166840 ESP \n", "7 426.264436 351.705464 316.607648 235.482475 ESP \n", "8 304.292676 248.808967 221.174350 162.995328 ESP \n", "9 430.833752 355.374173 313.106841 232.581040 ESP \n", "\n", " female_under_1 transportation female_10_to_14 female_15_to_19 \\\n", "0 28.693915 62 169.153640 158.909087 \n", "1 27.533142 115 163.738535 153.326000 \n", "2 50.091947 124 293.852755 273.574052 \n", "3 52.339912 54 304.900184 292.604586 \n", "4 44.766853 49 263.162347 246.132949 \n", "5 95.560624 172 561.082684 524.800891 \n", "6 97.416694 193 571.980562 534.994066 \n", "7 66.039259 151 388.281731 365.377672 \n", "8 44.403963 79 261.029094 244.137743 \n", "9 63.692182 110 374.778970 351.354713 \n", "\n", " female_20_to_24 female_25_to_29 female_30_to_34 female_35_to_39 \\\n", "0 153.972406 164.477591 177.747316 219.014149 \n", "1 147.104910 155.585855 167.001699 205.922699 \n", "2 269.563257 284.239360 302.392749 368.791786 \n", "3 279.560116 302.507961 327.124385 391.198812 \n", "4 237.480096 254.350030 276.120897 335.150496 \n", "5 514.948448 560.215039 604.717769 723.562416 \n", "6 524.950252 571.096036 616.463167 737.616141 \n", "7 359.531128 387.915307 417.419276 507.002730 \n", "8 235.555022 252.288204 273.882590 332.433680 \n", "9 341.896086 367.449503 396.543658 479.801048 \n", "\n", " female_40_to_44 female_45_to_49 female_50_to_54 female_55_to_59 \\\n", "0 267.756015 268.073208 256.767236 237.604333 \n", "1 251.286604 251.263774 238.742479 218.524946 \n", "2 447.273132 447.816753 427.330467 399.392763 \n", "3 471.563116 468.010031 448.989864 418.053752 \n", "4 409.423620 409.001029 392.155128 363.719101 \n", "5 869.828768 866.350490 839.930112 790.127512 \n", "6 886.723386 883.177559 856.244038 805.474094 \n", "7 617.319966 615.971377 596.200625 559.683816 \n", "8 406.104742 405.685562 388.976229 360.770708 \n", "9 582.162156 580.282089 565.227739 524.879842 \n", "\n", " female_60_to_64 female_65_to_69 female_70_to_74 female_75_to_79 \\\n", "0 208.339251 177.525427 167.336132 139.422190 \n", "1 189.368642 163.730100 155.843235 130.658199 \n", "2 354.964127 303.556543 285.400862 236.542165 \n", "3 370.225668 313.187995 291.416965 243.300764 \n", "4 320.472125 273.839742 257.380644 215.620176 \n", "5 696.650138 593.946884 554.767677 456.639502 \n", "6 710.181137 605.483075 565.542897 465.508790 \n", "7 497.777880 421.235484 391.043486 324.241345 \n", "8 317.874315 271.619926 255.294256 213.872313 \n", "9 461.562910 391.095832 370.309326 307.973901 \n", "\n", " male_80_and_over female_80_and_over \n", "0 145.765479 255.243802 \n", "1 146.533779 238.448327 \n", "2 264.321666 429.473098 \n", "3 279.784366 442.072536 \n", "4 230.354640 390.621340 \n", "5 481.703569 848.459962 \n", "6 491.059674 864.939521 \n", "7 319.922590 594.874658 \n", "8 228.487330 387.454870 \n", "9 312.384817 571.083390 " ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SpatialFeatures_esp_qk15.tail()" ] }, { "cell_type": "markdown", "metadata": { "id": "r8K6I-kDsh3I" }, "source": [ "### 2. Who's On First GeoJSON for filtering data in cities" ] }, { "cell_type": "markdown", "metadata": { "id": "OsKLO9C_tU0f" }, "source": [ "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](https://carto.com/spatial-data-catalog/browser/?license=public).\n", "\n", "[\"Who's on First\"](https://whosonfirst.org/) is a gazetteer (o big list) of places, each with a stable identifier and some number of descriptive properties about that location. \n", "\n", "\n", "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." ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 323 }, "id": "WvAK3hmbsk0O", "outputId": "06f9f1e2-c68c-46a1-b24c-864509b9ab60" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "You can find more entities with the Global country filter. To apply that filter run:\n", "\tCatalog().country('glo')\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
slugnamedescriptioncountry_idprovider_idgeom_typegeom_coverageupdate_frequencyis_public_datalangversionprovider_nameid
0wof_ancestors_eaaeac75Ancestors - GlobalA normalized view of the hierarchies in 'geojs...glowhos_on_firstMULTIPLENoneNoneTrueeng20190520Who's On Firstcarto-do-public-data.whos_on_first.geography_g...
1wof_concordance_392f80adConcordances - GlobalRelationship between Who's On First identifier...glowhos_on_firstMULTIPLENoneNoneTrueeng20190520Who's On Firstcarto-do-public-data.whos_on_first.geography_g...
2wof_geojson_4e78587cGeoJSON - GlobalThe main table in Who's On First. Holds all th...glowhos_on_firstMULTIPLENoneNoneTrueeng20190520Who's On Firstcarto-do-public-data.whos_on_first.geography_g...
3wof_names_5a30fa98Names - GlobalWhat things are called in Who's On First. A no...glowhos_on_firstMULTIPLENoneNoneTrueeng20190520Who's On Firstcarto-do-public-data.whos_on_first.geography_g...
4wof_spr_850ad7e9Standard Places Response - GlobalThe \"Standard Places Response\" (or SPR) is an ...glowhos_on_firstMULTIPLENoneNoneTrueeng20190520Who's On Firstcarto-do-public-data.whos_on_first.geography_g...
\n", "
" ], "text/plain": [ " slug name \\\n", "0 wof_ancestors_eaaeac75 Ancestors - Global \n", "1 wof_concordance_392f80ad Concordances - Global \n", "2 wof_geojson_4e78587c GeoJSON - Global \n", "3 wof_names_5a30fa98 Names - Global \n", "4 wof_spr_850ad7e9 Standard Places Response - Global \n", "\n", " description country_id \\\n", "0 A normalized view of the hierarchies in 'geojs... glo \n", "1 Relationship between Who's On First identifier... glo \n", "2 The main table in Who's On First. Holds all th... glo \n", "3 What things are called in Who's On First. A no... glo \n", "4 The \"Standard Places Response\" (or SPR) is an ... glo \n", "\n", " provider_id geom_type geom_coverage update_frequency is_public_data \\\n", "0 whos_on_first MULTIPLE None None True \n", "1 whos_on_first MULTIPLE None None True \n", "2 whos_on_first MULTIPLE None None True \n", "3 whos_on_first MULTIPLE None None True \n", "4 whos_on_first MULTIPLE None None True \n", "\n", " lang version provider_name \\\n", "0 eng 20190520 Who's On First \n", "1 eng 20190520 Who's On First \n", "2 eng 20190520 Who's On First \n", "3 eng 20190520 Who's On First \n", "4 eng 20190520 Who's On First \n", "\n", " id \n", "0 carto-do-public-data.whos_on_first.geography_g... \n", "1 carto-do-public-data.whos_on_first.geography_g... \n", "2 carto-do-public-data.whos_on_first.geography_g... \n", "3 carto-do-public-data.whos_on_first.geography_g... \n", "4 carto-do-public-data.whos_on_first.geography_g... " ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Catalog().provider('whos_on_first').public().geographies.to_dataframe()" ] }, { "cell_type": "markdown", "metadata": { "id": "8griL1w_D_Xj" }, "source": [ "Note that the ID to access the WoF GeoJSON table is 'wof_geojson_4e78587c'." ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "id": "ERTww0ogD8VS" }, "outputs": [], "source": [ "wof_geojson = Geography.get('wof_geojson_4e78587c')" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "ul9SMfO_EBeu", "outputId": "9aac8c58-f448-42f1-a576-6986f60e435f" }, "outputs": [ { "data": { "text/plain": [ "{'slug': 'wof_geojson_4e78587c',\n", " 'name': 'GeoJSON - Global',\n", " 'description': \"The main table in Who's On First. Holds all the relevant information for a place in the 'body' JSON field.\",\n", " 'country_id': 'glo',\n", " 'provider_id': 'whos_on_first',\n", " 'geom_type': 'MULTIPLE',\n", " 'update_frequency': None,\n", " 'is_public_data': True,\n", " 'lang': 'eng',\n", " 'version': '20190520',\n", " 'provider_name': \"Who's On First\",\n", " 'id': 'carto-do-public-data.whos_on_first.geography_glo_geojson_20190520'}" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "wof_geojson.to_dict()" ] }, { "cell_type": "markdown", "metadata": { "id": "ciG_W6ZtED2X" }, "source": [ "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." ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 193 }, "id": "4FUNrrG9EC2i", "outputId": "5f20a2ab-19cf-488f-ecfd-02ebcea2a962" }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
geoididbodynamecountryparent_idis_currentplacetypegeometry_typebboxgeomlastmodifiedlastmodified_timestamp
0404338863404338863{\"id\": 404338863, \"type\": \"Feature\", \"properti...MadridES856827830localadminPolygonPOLYGON((-3.51823494 40.31206394, -3.51823494 ...POLYGON ((-3.88558 40.57446, -3.88560 40.57442...15132675062017-12-14 16:05:06+00:00
1101748283101748283{\"id\": 101748283, \"type\": \"Feature\", \"properti...MadridES856827831localityMultiPolygonPOLYGON((-3.5180508952556 40.312064309035, -3....POLYGON ((-3.77456 40.40031, -3.77456 40.40026...15368811932018-09-13 23:26:33+00:00
28568278385682783{\"id\": 85682783, \"type\": \"Feature\", \"propertie...MadridES4042273871regionMultiPolygonPOLYGON((-3.05298331 39.88471951, -3.05298331 ...MULTIPOLYGON (((-4.31951 40.64764, -4.31873 40...15538148732019-03-28 23:14:33+00:00
\n", "
" ], "text/plain": [ " geoid id body \\\n", "0 404338863 404338863 {\"id\": 404338863, \"type\": \"Feature\", \"properti... \n", "1 101748283 101748283 {\"id\": 101748283, \"type\": \"Feature\", \"properti... \n", "2 85682783 85682783 {\"id\": 85682783, \"type\": \"Feature\", \"propertie... \n", "\n", " name country parent_id is_current placetype geometry_type \\\n", "0 Madrid ES 85682783 0 localadmin Polygon \n", "1 Madrid ES 85682783 1 locality MultiPolygon \n", "2 Madrid ES 404227387 1 region MultiPolygon \n", "\n", " bbox \\\n", "0 POLYGON((-3.51823494 40.31206394, -3.51823494 ... \n", "1 POLYGON((-3.5180508952556 40.312064309035, -3.... \n", "2 POLYGON((-3.05298331 39.88471951, -3.05298331 ... \n", "\n", " geom lastmodified \\\n", "0 POLYGON ((-3.88558 40.57446, -3.88560 40.57442... 1513267506 \n", "1 POLYGON ((-3.77456 40.40031, -3.77456 40.40026... 1536881193 \n", "2 MULTIPOLYGON (((-4.31951 40.64764, -4.31873 40... 1553814873 \n", "\n", " lastmodified_timestamp \n", "0 2017-12-14 16:05:06+00:00 \n", "1 2018-09-13 23:26:33+00:00 \n", "2 2019-03-28 23:14:33+00:00 " ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "city_name = \"'Madrid'\"\n", "country_code = \"'ES'\"\n", "placetype = \"'locality'\"\n", "\n", "sql_query = f\"SELECT * FROM $geography$ WHERE name = {city_name} AND country = {country_code}\"\n", "\n", "wof_geojson_filtered = wof_geojson.to_dataframe(sql_query=sql_query)\n", "\n", "wof_geojson_filtered" ] }, { "cell_type": "markdown", "metadata": { "id": "1UWpXObbEPXf" }, "source": [ "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." ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 692 }, "id": "79ImYc29EJnh", "outputId": "e2e7c9b9-c3aa-4f98-fb15-e1b60be8aed8" }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", " None\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", "\n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", "\n", "\n", " Static map image\n", " \n", " \n", " \n", "\n", "\n", " \n", "
\n", "
\n", "
\n", " \n", " \n", "
\n", "
\n", " \n", "\n", "
\n", " \n", " \n", " \n", " \n", " \n", "
\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
\n", " \n", " \n", "
\n", "
\n", "
\n", "
\n", " \n", "
\n", "
\n", "
\n", "\n", " \n", "\n", "
\n", "
\n", " :\n", "
\n", " \n", " \n", "
\n", "
\n", "\n", "
\n", " StackTrace\n", "
    \n", "
    \n", "
    \n", "\n", "\n", "\n", "\n", "\n", "\">\n", "\n", "" ], "text/plain": [ "" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "wof_geojson_filtered['geoid_str'] = wof_geojson_filtered['geoid'].astype(str) \n", "Map(\n", " Layer(\n", " wof_geojson_filtered, # where the data comes from\n", " color_category_style('geoid_str',palette='Vivid',opacity=0.6,stroke_width=0.2),\n", " widgets=[category_widget('geoid_str','Select geoid to visualize')],\n", " popup_hover=[popup_element('geoid','geoid'),\n", " popup_element('name','name'),\n", " popup_element('placetype','placetype')],\n", " legends=color_category_legend('Geoid'),\n", " geom_col='geom', #the name of the column on the query that has a GEOGRAPHY data\n", " encode_data = False \n", " )\n", ")" ] }, { "cell_type": "markdown", "metadata": { "id": "yjqihG9JEhlh" }, "source": [ "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.\n", "\n", "As in this example we want to filter the data for Madrid, we will use geoid = '\t101748283'." ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "id": "mw5R2vFtERNG" }, "outputs": [], "source": [ "\"\"\"Helper function for downloading only the data within the area (geometry) of interest\n", " Args:\n", " do_dataset: DO Dataset you'd like to download for a specific area of interest\n", " do_geom_dataset: DO Dataset containing the geometry you'd like to use as filter (your area of interest)\n", " target_geoid: geoid of the geometry you'd like to use as filter (your area of interest)\n", "\"\"\"\n", "def filter_data(do_dataset, do_geom_dataset, target_geoid):\n", " do_geom_dataset_id=do_geom_dataset.id\n", " sql_query = f\"\"\"WITH do_geom AS (\n", " SELECT geom\n", " FROM `{do_geom_dataset_id}`\n", " WHERE geoid = '{target_geoid}')\n", "\n", " SELECT do_d.* FROM $dataset$ do_d, do_geom WHERE ST_Intersects(do_d.geom, do_geom.geom)\"\"\"\n", " filtered_data = do_dataset.to_dataframe(sql_query = sql_query)\n", " return filtered_data" ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 564 }, "id": "n3a4jj1mEtET", "outputId": "bd98daba-7eed-4cbc-caaa-a332c09ee303" }, "outputs": [ { "data": { "text/html": [ "
    \n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
    geoiddo_datecountry_isocountry_iso_a3populationfemalemalefemale_under_1female_1_to_4female_5_to_9female_10_to_14female_15_to_19female_20_to_24female_25_to_29female_30_to_34female_35_to_39female_40_to_44female_45_to_49female_50_to_54female_55_to_59female_60_to_64female_65_to_69female_70_to_74female_75_to_79female_80_and_overmale_under_1male_1_to_4male_5_to_9male_10_to_14male_15_to_19male_20_to_24male_25_to_29male_30_to_34male_35_to_39male_40_to_44male_45_to_49male_50_to_54male_55_to_59male_60_to_64male_65_to_69male_70_to_74male_75_to_79male_80_and_overretaileducationfinancialfood_drinkhealthcareleisuretourismtransportationgeom
    0331110121033122020-01-01SpainESP9337.6271174881.4288194456.19803141.766095163.672177221.137283245.566234231.080698227.383101245.334472263.994076320.650538390.419935389.567041377.063165353.968339314.816339266.407605247.312870205.064303376.22454744.155446173.006394234.309353258.144348243.008669228.511522234.622352253.541816313.944129384.364319377.072623351.233350316.763072269.588137222.433800200.236423148.929341202.332936300100010POLYGON ((-3.70239 40.32980, -3.70239 40.33817...
    1331110121120202020-01-01SpainESP12524.9839246547.6827185977.30126056.022764219.540946296.621506329.389162309.959063304.999293329.078293354.107280430.103163523.688038522.544012505.771966474.793871422.277495357.344649331.732017275.062080504.64711859.227708232.061352314.289800346.260753325.958592306.512897314.709633340.087184421.107543515.565376505.784677471.125289424.888722361.610845298.360577268.586231199.765704271.39837646310618073POLYGON ((-3.68042 40.35492, -3.68042 40.36329...
    2331110103233122020-01-01SpainESP12421.0768336493.3630305927.71350555.557999217.719627294.160728326.656544307.387631302.469019326.348264351.169585426.535027519.343525518.208965501.576084470.854971418.774260354.380114328.979961272.780164500.46056058.736354230.136176311.682449343.388167323.254432303.970064312.098805337.265824417.614041511.288227501.588670467.216838421.363837358.610911295.885376266.358035198.108446269.14685227203189436POLYGON ((-3.70239 40.46367, -3.70239 40.47202...
    3331110103302122020-01-01SpainESP2254.6601931178.6681401075.99206010.08482739.52022953.39573359.29433755.79666654.90384659.23837763.74392077.42416894.27066594.06472691.04554385.46907676.01544564.32669059.71608349.51475690.84305110.66175941.77406656.57625662.33144558.67679355.17631356.65183361.22012375.80484392.80848091.04782584.80868676.48550265.09465953.70879148.34901835.96042848.85524100000000POLYGON ((-3.65845 40.53050, -3.65845 40.53885...
    4331110121011102020-01-01SpainESP15010.8147527847.1998837163.61503967.141591263.113193355.491909394.762959371.476565365.532447394.390390424.386867515.465653627.624291626.253203606.152427569.026092506.086817428.266773397.570788329.653602604.80431970.982620278.118529376.666836414.983067390.651518367.346454377.170004407.584217504.684685617.889516606.167654564.629409509.216307433.379674357.576148321.892486239.413157325.2627578373161264397POLYGON ((-3.70239 40.43859, -3.70239 40.44695...
    \n", "
    " ], "text/plain": [ " geoid do_date country_iso country_iso_a3 population \\\n", "0 33111012103312 2020-01-01 Spain ESP 9337.627117 \n", "1 33111012112020 2020-01-01 Spain ESP 12524.983924 \n", "2 33111010323312 2020-01-01 Spain ESP 12421.076833 \n", "3 33111010330212 2020-01-01 Spain ESP 2254.660193 \n", "4 33111012101110 2020-01-01 Spain ESP 15010.814752 \n", "\n", " female male female_under_1 female_1_to_4 female_5_to_9 \\\n", "0 4881.428819 4456.198031 41.766095 163.672177 221.137283 \n", "1 6547.682718 5977.301260 56.022764 219.540946 296.621506 \n", "2 6493.363030 5927.713505 55.557999 217.719627 294.160728 \n", "3 1178.668140 1075.992060 10.084827 39.520229 53.395733 \n", "4 7847.199883 7163.615039 67.141591 263.113193 355.491909 \n", "\n", " female_10_to_14 female_15_to_19 female_20_to_24 female_25_to_29 \\\n", "0 245.566234 231.080698 227.383101 245.334472 \n", "1 329.389162 309.959063 304.999293 329.078293 \n", "2 326.656544 307.387631 302.469019 326.348264 \n", "3 59.294337 55.796666 54.903846 59.238377 \n", "4 394.762959 371.476565 365.532447 394.390390 \n", "\n", " female_30_to_34 female_35_to_39 female_40_to_44 female_45_to_49 \\\n", "0 263.994076 320.650538 390.419935 389.567041 \n", "1 354.107280 430.103163 523.688038 522.544012 \n", "2 351.169585 426.535027 519.343525 518.208965 \n", "3 63.743920 77.424168 94.270665 94.064726 \n", "4 424.386867 515.465653 627.624291 626.253203 \n", "\n", " female_50_to_54 female_55_to_59 female_60_to_64 female_65_to_69 \\\n", "0 377.063165 353.968339 314.816339 266.407605 \n", "1 505.771966 474.793871 422.277495 357.344649 \n", "2 501.576084 470.854971 418.774260 354.380114 \n", "3 91.045543 85.469076 76.015445 64.326690 \n", "4 606.152427 569.026092 506.086817 428.266773 \n", "\n", " female_70_to_74 female_75_to_79 female_80_and_over male_under_1 \\\n", "0 247.312870 205.064303 376.224547 44.155446 \n", "1 331.732017 275.062080 504.647118 59.227708 \n", "2 328.979961 272.780164 500.460560 58.736354 \n", "3 59.716083 49.514756 90.843051 10.661759 \n", "4 397.570788 329.653602 604.804319 70.982620 \n", "\n", " male_1_to_4 male_5_to_9 male_10_to_14 male_15_to_19 male_20_to_24 \\\n", "0 173.006394 234.309353 258.144348 243.008669 228.511522 \n", "1 232.061352 314.289800 346.260753 325.958592 306.512897 \n", "2 230.136176 311.682449 343.388167 323.254432 303.970064 \n", "3 41.774066 56.576256 62.331445 58.676793 55.176313 \n", "4 278.118529 376.666836 414.983067 390.651518 367.346454 \n", "\n", " male_25_to_29 male_30_to_34 male_35_to_39 male_40_to_44 male_45_to_49 \\\n", "0 234.622352 253.541816 313.944129 384.364319 377.072623 \n", "1 314.709633 340.087184 421.107543 515.565376 505.784677 \n", "2 312.098805 337.265824 417.614041 511.288227 501.588670 \n", "3 56.651833 61.220123 75.804843 92.808480 91.047825 \n", "4 377.170004 407.584217 504.684685 617.889516 606.167654 \n", "\n", " male_50_to_54 male_55_to_59 male_60_to_64 male_65_to_69 male_70_to_74 \\\n", "0 351.233350 316.763072 269.588137 222.433800 200.236423 \n", "1 471.125289 424.888722 361.610845 298.360577 268.586231 \n", "2 467.216838 421.363837 358.610911 295.885376 266.358035 \n", "3 84.808686 76.485502 65.094659 53.708791 48.349018 \n", "4 564.629409 509.216307 433.379674 357.576148 321.892486 \n", "\n", " male_75_to_79 male_80_and_over retail education financial food_drink \\\n", "0 148.929341 202.332936 3 0 0 1 \n", "1 199.765704 271.398376 4 6 3 10 \n", "2 198.108446 269.146852 27 2 0 31 \n", "3 35.960428 48.855241 0 0 0 0 \n", "4 239.413157 325.262757 83 7 31 61 \n", "\n", " healthcare leisure tourism transportation \\\n", "0 0 0 0 10 \n", "1 6 18 0 73 \n", "2 8 9 4 36 \n", "3 0 0 0 0 \n", "4 26 4 3 97 \n", "\n", " geom \n", "0 POLYGON ((-3.70239 40.32980, -3.70239 40.33817... \n", "1 POLYGON ((-3.68042 40.35492, -3.68042 40.36329... \n", "2 POLYGON ((-3.70239 40.46367, -3.70239 40.47202... \n", "3 POLYGON ((-3.65845 40.53050, -3.65845 40.53885... \n", "4 POLYGON ((-3.70239 40.43859, -3.70239 40.44695... " ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SpatialFeatures_esp_qk15_madrid = filter_data(SpatialFeatures_esp_qk15,wof_geojson,'101748283')\n", "SpatialFeatures_esp_qk15_madrid.head()" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 675 }, "id": "NfLXHz7yE3-q", "outputId": "fe81497f-914d-4875-a6c3-c75e16a3c2cf" }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", " None\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", "\n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", "\n", "\n", "\n", " Static map image\n", " \n", " \n", " \n", "\n", "\n", " \n", "
    \n", "
    \n", "
    \n", " \n", " \n", "
    \n", "
    \n", " \n", "\n", "
    \n", " \n", " \n", " \n", " \n", " \n", "
    \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
    \n", " \n", " \n", "
    \n", "
    \n", "
    \n", "
    \n", " \n", "
    \n", "
    \n", "
    \n", "\n", " \n", "\n", "
    \n", "
    \n", " :\n", "
    \n", " \n", " \n", "
    \n", "
    \n", "\n", "
    \n", " StackTrace\n", "
      \n", "
      \n", "
      \n", "\n", "\n", "\n", "\n", "\n", "\">\n", "\n", "" ], "text/plain": [ "" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Map(\n", " Layer(\n", " SpatialFeatures_esp_qk15_madrid, # where the data comes from\n", " color_continuous_style('population', palette=palettes.magenta,opacity=0.75,stroke_width=0.5),\n", " widgets=[formula_widget('population','sum','Total Population'),histogram_widget('population','Population by cell')],\n", " popup_hover=[popup_element('population','Population')],\n", " geom_col='geom', #the name of the column on the query that has a GEOGRAPHY data\n", " encode_data = False \n", " )\n", ")" ] } ], "metadata": { "colab": { "collapsed_sections": [], "name": "CARTO | Using \"Who's on First\" geometries to filter data from Data Observatory", "provenance": [] }, "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.5" } }, "nbformat": 4, "nbformat_minor": 4 }