{ "cells": [ { "cell_type": "markdown", "metadata": { "id": "7LKLhQBzJMOz" }, "source": [ "## Access premium data from CARTO's Data Observatory.\n", "\n", "This notebook shows how to use CARTOframes for discovering and downloading **premium** datasets from CARTO's [Data Observatory](https://carto.com/spatial-data-catalog/).\n", "\n", "In particular, we will download touristic [POI's from Pitney Bowes](https://carto.com/spatial-data-catalog/browser/?category=points_of_interest&provider=pitney_bowes) within a specific bounding box.\n", "\n", "The notebook is organized in the following sections:\n", " - [Check your subscriptions to premium datasets](#section1)\n", " - [Download a small sample of a dataset applying spatial filtering to explore it further](#section2)\n", " - [Download dataset filtering by column value and bounding box](#section3)\n", " - [Upload filtered dataset to your CARTO account](#section4)\n", " \n", " \n", "**Note** for this notebook we are using the premium [dataset of Pitney Bowes POI's in Spain](https://carto.com/spatial-data-catalog/browser/dataset/pb_points_of_i_94bda91b/)." ] }, { "cell_type": "markdown", "metadata": { "id": "V4-0hJjiVsen" }, "source": [ "### Setup" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Import packages" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "id": "6vLRH4fEEkms" }, "outputs": [], "source": [ "import geopandas as gpd\n", "import pandas as pd\n", "pd.set_option('display.max_columns', None)\n", "\n", "from cartoframes import to_carto\n", "from cartoframes.auth import set_default_credentials\n", "from cartoframes.data.observatory import *\n", "from cartoframes.viz import *\n", "from shapely.geometry import box" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Set CARTO default credentials" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In order to be able to use the Data Observatory via CARTOframes, you need to set your CARTO account credentials first.\n", "\n", "Please, visit the [Authentication guide](https://carto.com/developers/cartoframes/guides/Authentication/) for further detail." ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "from cartoframes.auth import set_default_credentials\n", "\n", "set_default_credentials('creds.json')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Note about credentials**\n", "\n", "For security reasons, we recommend storing your credentials in an external file to prevent publishing them by accident when sharing your notebooks. You can get more information in the section _Setting your credentials_ of the [Authentication guide](https://carto.com/developers/cartoframes/guides/Authentication/)." ] }, { "cell_type": "markdown", "metadata": { "id": "zo5IKlj36qFF" }, "source": [ "### Download a premium dataset\n", "\n", "When working with very large datasets, you might need to explore the dataset in detail to decide if you need the whole data or just part of it. In order to speed up your time to identifying the exact data, it might be very helpful to download just a small sample of your data, to later decide what you need. In this section, we will show how to identify toutistic POI's from a dataset we are already subscribed to." ] }, { "cell_type": "markdown", "metadata": { "id": "opOc5utoCGpx" }, "source": [ "\n", "#### Check your subscriptions to premium datasets\n", "\n", "First, we check we're already subscribed to the dataset we want to use for our analysis. In this case, we would like to use [Pitney Bowes POI's in Spain](https://carto.com/spatial-data-catalog/browser/dataset/pb_points_of_i_94bda91b/). The dataset is `pb_points_of_i_94bda91b`.\n", "\n", "You can subscribe to this premium [dataset](https://carto.com/spatial-data-catalog/browser/dataset/pb_points_of_i_94bda91b/) on your [CARTO dashboard](https://carto.com/help/working-with-data/subscribe_datasets_do/) or contacting CARTO." ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 52 }, "id": "XeLjV_0cCFIz", "outputId": "5fd18729-5b09-4626-c548-7b65a0ee6e8f" }, "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", "
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 AmericaNoneyearlyNoneyearlyFalseeng2020DemographicsApplied 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)yearlyFalseeng2019DemographicsApplied 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)yearlyFalseeng2019DemographicsApplied 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 AmericaNoneyearlyNoneyearlyFalseeng2020DemographicsApplied 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...
\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", "\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", "\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", "\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", "\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", "\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", "\n", " time_coverage update_frequency is_public_data lang version \\\n", "0 None yearly False eng 2020 \n", "1 [2018-01-01, 2019-01-01) yearly False eng 2019 \n", "2 None monthly False eng v1 \n", "3 [2019-01-01, 2020-01-01) yearly False eng 2019 \n", "4 None yearly 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", "\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", "\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", "\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... " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Catalog().subscriptions().datasets.to_dataframe()" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "id": "6KiyNnqbr3uc" }, "outputs": [], "source": [ "pois_ds = Dataset.get('pb_points_of_i_94bda91b')" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 1000 }, "id": "RIz7vMRpr-4u", "outputId": "736631ea-8600-441e-8751-a5505061dcc8" }, "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
HTTPISO3NAMESIC1SIC2SIC8CLASSEMAILGROUPPB_IDSTABBgeoidFAXNUMMICODETEL_NUMdo_dateLATITUDEOPEN_24HPOSTCODEdo_labelAREANAME1AREANAME2AREANAME3AREANAME4BRANDNAMEGEORESULTLONGITUDESUB_CLASSAGENT_CODETRADE_NAMEYEAR_STARTSTATUS_CODEBUSINESS_LINECURRENCY_CODEEMPLOYEE_HEREEXCHANGE_NAMETICKER_SYMBOLEMPLOYEE_COUNTFAMILY_MEMBERSFRANCHISE_NAMEHIERARCHY_CODEPARENT_ADDRESSPARENT_COUNTRYTRADE_DIVISIONADDRESSLASTLINECONFIDENCE_CODEMAINADDRESSLINEPARENT_POSTCODEFORMATTEDADDRESSPARENT_AREANAME1PARENT_AREANAME3SIC8_DESCRIPTIONALT_INDUSTRY_CODELEGAL_STATUS_CODESALES_VOLUME_LOCALCOUNTRY_ACCESS_CODEPARENT_BUSINESS_NAMESUBSIDIARY_INDICATORPARENT_STREET_ADDRESSGLOBAL_ULTIMATE_ADDRESSGLOBAL_ULTIMATE_COUNTRYSALES_VOLUME_US_DOLLARSGLOBAL_ULTIMATE_POSTCODEDOMESTIC_ULTIMATE_ADDRESSGLOBAL_ULTIMATE_AREANAME1GLOBAL_ULTIMATE_AREANAME3GLOBAL_ULTIMATE_INDICATORDOMESTIC_ULTIMATE_POSTCODEDOMESTIC_ULTIMATE_AREANAME1DOMESTIC_ULTIMATE_AREANAME3GLOBAL_ULTIMATE_BUSINESS_NAMEGLOBAL_ULTIMATE_STREET_ADDRESSDOMESTIC_ULTIMATE_BUSINESS_NAMEDOMESTIC_ULTIMATE_STREET_ADDRESS
0NoneESPEL SANTONone655250440000DRINKING PLACESNoneSHOPPING1173764019EX2107476034#1.1064478#41.1567659None10010314936 58 74 132020-11-0143.267950NoneAD700BBV PRIVANZA INTERNATIONALMADRIDVIZCAYAVILADECANSNoneNoneT202.164250DEPARTMENT STORESNoneBRICOR1994.0NoneNoneNoneNoneNoneNoneNone00000None00NoneNoneDIVISION I. - SERVICES10869, TORRECILLA DE LOS ANGELES, CÁCERESLOWNoneNoneNoneMALAGANoneFRUIT AND VEGETABLE MARKETSNoneNoneNaNNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneCARRETERA DE CADIZ 206
1NoneESPLA FUENTENone655250440000DRINKING PLACESNoneSHOPPING1432760662MD2227609955#-6.378907#39.470747None10010314935 48 09 272020-11-0143.323898NoneGX11 1AASPRINGFIELD BANK AND TRUSTMADRIDBIZKAIABARCELONANoneNoneS8HPNTSCZA-1.639469DEPARTMENT STORESNoneNone2018.0NoneNoneNoneNoneNoneNoneNone00000None00NoneNoneDIVISION I. - SERVICES28911, LEGANES, MADRIDLOWNoneNoneNoneGUIPUZCOANoneFRUIT AND VEGETABLE MARKETSNoneNoneNaNNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNonePASEO DE MIRAMON 185
2NoneESPSANT FRANCESC XAVIERNone654150440000DRINKING PLACESNoneSHOPPING1170864171CT2226191863#-8.5258399#42.8889816None10010314938 14 89 432020-11-0143.322003NoneAD700I D T FINANCIAL SERVICESBARCELONAVIZCAYASANT PERE DE RIBESNoneNoneT18-3.686817DEPARTMENT STORESNoneEL CORTE INGLÉS2008.0NoneNoneNoneNoneNoneNoneNone00000None00NoneNoneDIVISION I. - SERVICES08301, MATARO, BARCELONAMEDIUMNoneNoneNoneMADRIDNoneFRUIT AND VEGETABLE MARKETSNoneNoneNaNNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNonePASEO CASTELLANA, 120 - IZ BJ
3NoneESPRUFINO BLANCONone655250440000DRINKING PLACESNoneSHOPPING1171185920MC2225636588#0.629133#41.6186896None10010314946 53 35 122020-11-0142.507879NoneGX11 1AAJYSKE BANK MANAGEMENTMADRIDVIZCAYAGETXONoneNoneT19-3.692750DEPARTMENT STORESNoneHIPERCOR2006.0NoneNoneNoneNoneNoneNoneNone00000None00NoneNoneDIVISION I. - SERVICES30009, MURCIA, MURCIAHIGHNoneNoneNoneMALAGANoneFRUIT AND VEGETABLE MARKETSNoneNoneNaNNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneAVENIDA DEL CARMEN (ED EL FARO), BL 3 LOC
4NoneESPCASA CONVALESCÈNCIANoneNone50440000DRINKING PLACESNoneSHOPPING1173842007AR2219538595#-8.4077351#43.3661062None10010314661 78 11 332020-11-0143.318963NoneAD400OFFSEC SERVICESTENERIFEGIPUZKOASAN BARTOLOMÉNoneNoneT20-3.656440DEPARTMENT STORESNoneEL CORTE INGLÉS1988.0NoneNoneNoneNoneNoneNoneNone00000None00NoneNoneDIVISION I. - SERVICES50180, UTEBO, ZARAGOZALOWNoneNoneNoneLERIDANoneFRUIT AND VEGETABLE MARKETSNoneNoneNaNNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneCARRETERA PALAU (KM 1)
5NoneESPROSALÍA DE CASTRONoneNone50440000DRINKING PLACESNonePERSONAL SERVICES1505524737AN2226659530#-5.6530505#43.5359533None10010314922 04 90 672020-11-0143.323258NoneAD500SOIAMLERIDAVIZCAYAPEDREGUERNoneNoneT17-3.806100DEPARTMENT STORESNoneEL CORTE INGLÉS2020.0NoneNoneNoneNoneNoneNoneNone00000None00NoneNoneDIVISION I. - SERVICES21006, HUELVA, HUELVAHIGHNoneNoneNoneSEVILLANoneFRUIT AND VEGETABLE MARKETSNoneNone59118.0NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneCALLE MIGUEL VAZQUEZ DELGADO 71
6NoneESPCENTRO DE FORMACIÓN Y EMPLEONoneNone50440000DRINKING PLACESNonePERSONAL SERVICES1173898606CT2225995739#-0.883421#41.647102None10010314968 70 11 502020-11-0143.265980NoneGX11 1AALABMADRIDBIZKAIACARAVACA DE LA CRUZNoneNoneT19-2.669666DEPARTMENT STORESNoneNoneNaNNoneNoneNoneNoneNoneNoneNone00000None00NoneNoneDIVISION I. - SERVICES08026, BARCELONA, BARCELONAMEDIUMNoneNoneNoneBARCELONANoneFRUIT AND VEGETABLE MARKETSNoneNone60384.0NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneCALLE ANTIC CAMI DE XIMELIS 19
7NoneESPEFA EL SOTONoneNone50440000DRINKING PLACESNonePERSONAL SERVICES1293842742CT2222921452#-2.79984#36.7689None10010314914 04 99 282020-11-0143.253290NoneAD500F.I.T.AMADRIDVIZCAYAMADRIDNoneNoneT162.127009DEPARTMENT STORESNoneNone2016.0NoneNoneNoneNoneNoneNoneNone00000None00NoneNoneDIVISION I. - SERVICES08030, BARCELONA, BARCELONALOWNoneNoneNoneNoneNoneFRUIT AND VEGETABLE MARKETSNoneNone61505.0NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneAVENIDA GENERAL PERON (ED MASTER'S I), 38 - PI...
8NoneESPO CASTIÑEIRONoneNone50440000DRINKING PLACESNonePERSONAL SERVICES1172241073AN2152151101#-8.644829#42.4307343944 46 13 4710010314943 61 95 402020-11-0142.851396NoneAD500P & C APARTAMENTSCASTILLA Y LEÓNANDORRAIRUNNoneNoneT16-3.689160DEPARTMENT STORESNoneEL CORTE INGLÉS2000.0NoneNoneNoneNoneNoneNoneNoneNoneNone00NoneNoneDIVISION I. - SERVICES29014, MALAGA, MÁLAGAHIGHNoneNoneNoneBARCELONANoneFRUIT AND VEGETABLE MARKETSNoneNone67464.0NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneCALLE BRUC DEL MIG 8
9NoneESPCPEB DE CABAÑAQUINTANoneNone50440000DRINKING PLACESNonePERSONAL SERVICES1171203786AN2221998958#-7.3611215#43.6618237None10010314953 58 03 672020-11-0143.247379NoneAD400ME VACANCESPAÍS VASCOBIZKAIAALCALÁ LA REALNoneNoneT202.127009DEPARTMENT STORESNoneNone1999.0NoneNoneNoneNoneNoneNoneNoneNoneNone00NoneNoneDIVISION I. - SERVICES41020, SEVILLA, SEVILLALOWNoneNoneNoneALBACETENoneFRUIT AND VEGETABLE MARKETSNoneNone60587.0NoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneNoneCALLE MAYOR, 32 - 1 A
\n", "
" ], "text/plain": [ " HTTP ISO3 NAME SIC1 SIC2 SIC8 \\\n", "0 None ESP EL SANTO None 6552 50440000 \n", "1 None ESP LA FUENTE None 6552 50440000 \n", "2 None ESP SANT FRANCESC XAVIER None 6541 50440000 \n", "3 None ESP RUFINO BLANCO None 6552 50440000 \n", "4 None ESP CASA CONVALESCÈNCIA None None 50440000 \n", "5 None ESP ROSALÍA DE CASTRO None None 50440000 \n", "6 None ESP CENTRO DE FORMACIÓN Y EMPLEO None None 50440000 \n", "7 None ESP EFA EL SOTO None None 50440000 \n", "8 None ESP O CASTIÑEIRO None None 50440000 \n", "9 None ESP CPEB DE CABAÑAQUINTA None None 50440000 \n", "\n", " CLASS EMAIL GROUP PB_ID STABB \\\n", "0 DRINKING PLACES None SHOPPING 1173764019 EX \n", "1 DRINKING PLACES None SHOPPING 1432760662 MD \n", "2 DRINKING PLACES None SHOPPING 1170864171 CT \n", "3 DRINKING PLACES None SHOPPING 1171185920 MC \n", "4 DRINKING PLACES None SHOPPING 1173842007 AR \n", "5 DRINKING PLACES None PERSONAL SERVICES 1505524737 AN \n", "6 DRINKING PLACES None PERSONAL SERVICES 1173898606 CT \n", "7 DRINKING PLACES None PERSONAL SERVICES 1293842742 CT \n", "8 DRINKING PLACES None PERSONAL SERVICES 1172241073 AN \n", "9 DRINKING PLACES None PERSONAL SERVICES 1171203786 AN \n", "\n", " geoid FAXNUM MICODE TEL_NUM \\\n", "0 2107476034#1.1064478#41.1567659 None 10010314 936 58 74 13 \n", "1 2227609955#-6.378907#39.470747 None 10010314 935 48 09 27 \n", "2 2226191863#-8.5258399#42.8889816 None 10010314 938 14 89 43 \n", "3 2225636588#0.629133#41.6186896 None 10010314 946 53 35 12 \n", "4 2219538595#-8.4077351#43.3661062 None 10010314 661 78 11 33 \n", "5 2226659530#-5.6530505#43.5359533 None 10010314 922 04 90 67 \n", "6 2225995739#-0.883421#41.647102 None 10010314 968 70 11 50 \n", "7 2222921452#-2.79984#36.7689 None 10010314 914 04 99 28 \n", "8 2152151101#-8.644829#42.4307343 944 46 13 47 10010314 943 61 95 40 \n", "9 2221998958#-7.3611215#43.6618237 None 10010314 953 58 03 67 \n", "\n", " do_date LATITUDE OPEN_24H POSTCODE do_label \\\n", "0 2020-11-01 43.267950 None AD700 BBV PRIVANZA INTERNATIONAL \n", "1 2020-11-01 43.323898 None GX11 1AA SPRINGFIELD BANK AND TRUST \n", "2 2020-11-01 43.322003 None AD700 I D T FINANCIAL SERVICES \n", "3 2020-11-01 42.507879 None GX11 1AA JYSKE BANK MANAGEMENT \n", "4 2020-11-01 43.318963 None AD400 OFFSEC SERVICES \n", "5 2020-11-01 43.323258 None AD500 SOIAM \n", "6 2020-11-01 43.265980 None GX11 1AA LAB \n", "7 2020-11-01 43.253290 None AD500 F.I.T.A \n", "8 2020-11-01 42.851396 None AD500 P & C APARTAMENTS \n", "9 2020-11-01 43.247379 None AD400 ME VACANCES \n", "\n", " AREANAME1 AREANAME2 AREANAME3 AREANAME4 BRANDNAME \\\n", "0 MADRID VIZCAYA VILADECANS None None \n", "1 MADRID BIZKAIA BARCELONA None None \n", "2 BARCELONA VIZCAYA SANT PERE DE RIBES None None \n", "3 MADRID VIZCAYA GETXO None None \n", "4 TENERIFE GIPUZKOA SAN BARTOLOMÉ None None \n", "5 LERIDA VIZCAYA PEDREGUER None None \n", "6 MADRID BIZKAIA CARAVACA DE LA CRUZ None None \n", "7 MADRID VIZCAYA MADRID None None \n", "8 CASTILLA Y LEÓN ANDORRA IRUN None None \n", "9 PAÍS VASCO BIZKAIA ALCALÁ LA REAL None None \n", "\n", " GEORESULT LONGITUDE SUB_CLASS AGENT_CODE TRADE_NAME \\\n", "0 T20 2.164250 DEPARTMENT STORES None BRICOR \n", "1 S8HPNTSCZA -1.639469 DEPARTMENT STORES None None \n", "2 T18 -3.686817 DEPARTMENT STORES None EL CORTE INGLÉS \n", "3 T19 -3.692750 DEPARTMENT STORES None HIPERCOR \n", "4 T20 -3.656440 DEPARTMENT STORES None EL CORTE INGLÉS \n", "5 T17 -3.806100 DEPARTMENT STORES None EL CORTE INGLÉS \n", "6 T19 -2.669666 DEPARTMENT STORES None None \n", "7 T16 2.127009 DEPARTMENT STORES None None \n", "8 T16 -3.689160 DEPARTMENT STORES None EL CORTE INGLÉS \n", "9 T20 2.127009 DEPARTMENT STORES None None \n", "\n", " YEAR_START STATUS_CODE BUSINESS_LINE CURRENCY_CODE EMPLOYEE_HERE \\\n", "0 1994.0 None None None None \n", "1 2018.0 None None None None \n", "2 2008.0 None None None None \n", "3 2006.0 None None None None \n", "4 1988.0 None None None None \n", "5 2020.0 None None None None \n", "6 NaN None None None None \n", "7 2016.0 None None None None \n", "8 2000.0 None None None None \n", "9 1999.0 None None None None \n", "\n", " EXCHANGE_NAME TICKER_SYMBOL EMPLOYEE_COUNT FAMILY_MEMBERS FRANCHISE_NAME \\\n", "0 None None None 00000 None \n", "1 None None None 00000 None \n", "2 None None None 00000 None \n", "3 None None None 00000 None \n", "4 None None None 00000 None \n", "5 None None None 00000 None \n", "6 None None None 00000 None \n", "7 None None None 00000 None \n", "8 None None None None None \n", "9 None None None None None \n", "\n", " HIERARCHY_CODE PARENT_ADDRESS PARENT_COUNTRY TRADE_DIVISION \\\n", "0 00 None None DIVISION I. - SERVICES \n", "1 00 None None DIVISION I. - SERVICES \n", "2 00 None None DIVISION I. - SERVICES \n", "3 00 None None DIVISION I. - SERVICES \n", "4 00 None None DIVISION I. - SERVICES \n", "5 00 None None DIVISION I. - SERVICES \n", "6 00 None None DIVISION I. - SERVICES \n", "7 00 None None DIVISION I. - SERVICES \n", "8 00 None None DIVISION I. - SERVICES \n", "9 00 None None DIVISION I. - SERVICES \n", "\n", " ADDRESSLASTLINE CONFIDENCE_CODE MAINADDRESSLINE \\\n", "0 10869, TORRECILLA DE LOS ANGELES, CÁCERES LOW None \n", "1 28911, LEGANES, MADRID LOW None \n", "2 08301, MATARO, BARCELONA MEDIUM None \n", "3 30009, MURCIA, MURCIA HIGH None \n", "4 50180, UTEBO, ZARAGOZA LOW None \n", "5 21006, HUELVA, HUELVA HIGH None \n", "6 08026, BARCELONA, BARCELONA MEDIUM None \n", "7 08030, BARCELONA, BARCELONA LOW None \n", "8 29014, MALAGA, MÁLAGA HIGH None \n", "9 41020, SEVILLA, SEVILLA LOW None \n", "\n", " PARENT_POSTCODE FORMATTEDADDRESS PARENT_AREANAME1 PARENT_AREANAME3 \\\n", "0 None None MALAGA None \n", "1 None None GUIPUZCOA None \n", "2 None None MADRID None \n", "3 None None MALAGA None \n", "4 None None LERIDA None \n", "5 None None SEVILLA None \n", "6 None None BARCELONA None \n", "7 None None None None \n", "8 None None BARCELONA None \n", "9 None None ALBACETE None \n", "\n", " SIC8_DESCRIPTION ALT_INDUSTRY_CODE LEGAL_STATUS_CODE \\\n", "0 FRUIT AND VEGETABLE MARKETS None None \n", "1 FRUIT AND VEGETABLE MARKETS None None \n", "2 FRUIT AND VEGETABLE MARKETS None None \n", "3 FRUIT AND VEGETABLE MARKETS None None \n", "4 FRUIT AND VEGETABLE MARKETS None None \n", "5 FRUIT AND VEGETABLE MARKETS None None \n", "6 FRUIT AND VEGETABLE MARKETS None None \n", "7 FRUIT AND VEGETABLE MARKETS None None \n", "8 FRUIT AND VEGETABLE MARKETS None None \n", "9 FRUIT AND VEGETABLE MARKETS None None \n", "\n", " SALES_VOLUME_LOCAL COUNTRY_ACCESS_CODE PARENT_BUSINESS_NAME \\\n", "0 NaN None None \n", "1 NaN None None \n", "2 NaN None None \n", "3 NaN None None \n", "4 NaN None None \n", "5 59118.0 None None \n", "6 60384.0 None None \n", "7 61505.0 None None \n", "8 67464.0 None None \n", "9 60587.0 None None \n", "\n", " SUBSIDIARY_INDICATOR PARENT_STREET_ADDRESS GLOBAL_ULTIMATE_ADDRESS \\\n", "0 None None None \n", "1 None None None \n", "2 None None None \n", "3 None None None \n", "4 None None None \n", "5 None None None \n", "6 None None None \n", "7 None None None \n", "8 None None None \n", "9 None None None \n", "\n", " GLOBAL_ULTIMATE_COUNTRY SALES_VOLUME_US_DOLLARS GLOBAL_ULTIMATE_POSTCODE \\\n", "0 None None None \n", "1 None None None \n", "2 None None None \n", "3 None None None \n", "4 None None None \n", "5 None None None \n", "6 None None None \n", "7 None None None \n", "8 None None None \n", "9 None None None \n", "\n", " DOMESTIC_ULTIMATE_ADDRESS GLOBAL_ULTIMATE_AREANAME1 \\\n", "0 None None \n", "1 None None \n", "2 None None \n", "3 None None \n", "4 None None \n", "5 None None \n", "6 None None \n", "7 None None \n", "8 None None \n", "9 None None \n", "\n", " GLOBAL_ULTIMATE_AREANAME3 GLOBAL_ULTIMATE_INDICATOR \\\n", "0 None None \n", "1 None None \n", "2 None None \n", "3 None None \n", "4 None None \n", "5 None None \n", "6 None None \n", "7 None None \n", "8 None None \n", "9 None None \n", "\n", " DOMESTIC_ULTIMATE_POSTCODE DOMESTIC_ULTIMATE_AREANAME1 \\\n", "0 None None \n", "1 None None \n", "2 None None \n", "3 None None \n", "4 None None \n", "5 None None \n", "6 None None \n", "7 None None \n", "8 None None \n", "9 None None \n", "\n", " DOMESTIC_ULTIMATE_AREANAME3 GLOBAL_ULTIMATE_BUSINESS_NAME \\\n", "0 None None \n", "1 None None \n", "2 None None \n", "3 None None \n", "4 None None \n", "5 None None \n", "6 None None \n", "7 None None \n", "8 None None \n", "9 None None \n", "\n", " GLOBAL_ULTIMATE_STREET_ADDRESS DOMESTIC_ULTIMATE_BUSINESS_NAME \\\n", "0 None None \n", "1 None None \n", "2 None None \n", "3 None None \n", "4 None None \n", "5 None None \n", "6 None None \n", "7 None None \n", "8 None None \n", "9 None None \n", "\n", " DOMESTIC_ULTIMATE_STREET_ADDRESS \n", "0 CARRETERA DE CADIZ 206 \n", "1 PASEO DE MIRAMON 185 \n", "2 PASEO CASTELLANA, 120 - IZ BJ \n", "3 AVENIDA DEL CARMEN (ED EL FARO), BL 3 LOC \n", "4 CARRETERA PALAU (KM 1) \n", "5 CALLE MIGUEL VAZQUEZ DELGADO 71 \n", "6 CALLE ANTIC CAMI DE XIMELIS 19 \n", "7 AVENIDA GENERAL PERON (ED MASTER'S I), 38 - PI... \n", "8 CALLE BRUC DEL MIG 8 \n", "9 CALLE MAYOR, 32 - 1 A " ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pois_ds.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's make sure the dataset covers our area of interest." ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 674 }, "id": "LtfCUHlIsJFu", "outputId": "72c3c8f8-72cf-40f2-aee3-c2d8cfa51b9d" }, "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", " StackTrace\n", "
    \n", "
    \n", "
    \n", "\n", "\n", "\n", "\n", "\n", "\">\n", "\n", "" ], "text/plain": [ "" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pois_ds.geom_coverage()" ] }, { "cell_type": "markdown", "metadata": { "id": "BkwHqIdcuJXK" }, "source": [ "\n", "#### Download a small sample of a dataset applying spatial filtering to explore it further\n", "\n", "We're only interested in tourism related POI's in Spain. Since we don't know exactly which variable to use in order to filter tourism POI's, we'll first download a small sample of the dataset to explore it. We'll filter by a bounding box covering Madrid downtown to make sure we have a good variety of POI's.\n", "\n", "We can use SQL queries to specify the bounding box or polygon we are interested in.\n", "- If you'd like to filter by bounding box, you need to use the SQL geography function `ST_IntersectsBox`.\n", "- If you'd like to filter by polygon, you need to use the SQL geography function `ST_Intersects`.\n", "\n", "In order to get the bounding box of interest we'll use [bboxfinder](https://bboxfinder.com)." ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 376 }, "id": "hbce2yN8uNXZ", "outputId": "5bf355a4-45e1-4a39-a360-374e35e3ca07" }, "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", "
    NAMEgeoiddo_dateBRANDNAMEPB_IDTRADE_NAMEFRANCHISE_NAMEISO3AREANAME4AREANAME3AREANAME2AREANAME1STABBPOSTCODEFORMATTEDADDRESSMAINADDRESSLINEADDRESSLASTLINELONGITUDELATITUDEGEORESULTCONFIDENCE_CODECOUNTRY_ACCESS_CODETEL_NUMFAXNUMEMAILHTTPOPEN_24HBUSINESS_LINESIC1SIC2SIC8SIC8_DESCRIPTIONALT_INDUSTRY_CODEMICODETRADE_DIVISIONGROUPCLASSSUB_CLASSEMPLOYEE_HEREEMPLOYEE_COUNTYEAR_STARTSALES_VOLUME_LOCALSALES_VOLUME_US_DOLLARSCURRENCY_CODEAGENT_CODELEGAL_STATUS_CODESTATUS_CODESUBSIDIARY_INDICATORPARENT_BUSINESS_NAMEPARENT_ADDRESSPARENT_STREET_ADDRESSPARENT_AREANAME3PARENT_AREANAME1PARENT_COUNTRYPARENT_POSTCODEDOMESTIC_ULTIMATE_BUSINESS_NAMEDOMESTIC_ULTIMATE_ADDRESSDOMESTIC_ULTIMATE_STREET_ADDRESSDOMESTIC_ULTIMATE_AREANAME3DOMESTIC_ULTIMATE_AREANAME1DOMESTIC_ULTIMATE_POSTCODEGLOBAL_ULTIMATE_INDICATORGLOBAL_ULTIMATE_BUSINESS_NAMEGLOBAL_ULTIMATE_ADDRESSGLOBAL_ULTIMATE_STREET_ADDRESSGLOBAL_ULTIMATE_AREANAME3GLOBAL_ULTIMATE_AREANAME1GLOBAL_ULTIMATE_COUNTRYGLOBAL_ULTIMATE_POSTCODEFAMILY_MEMBERSHIERARCHY_CODETICKER_SYMBOLEXCHANGE_NAMEgeom
    0100 MONTADITOS2173220473#-3.70582#40.4162019-12-01NaN2173220473100 MONTADITOSNaNESPNaNMADRIDMADRIDCOMUNIDAD DE MADRIDMD28012.0CALLE POSTAS 12, 28012, MADRID, MADRIDCALLE POSTAS 1228012, MADRID, MADRID-3.70582040.416000S8HPNTSCZAHIGHNaN915 23 11 40913 51 90 03ATTCLIENTE@GRUPORESTALIA.COMSPAIN.100MONTADITOS.COMNaNNaNNaNNaNNaNTAPAS RESTAURANTSNaN10021076DIVISION G. - RETAIL TRADEEATING AND DRINKING PLACESEATING PLACES/RESTAURANTSEATING PLACES/RESTAURANTSNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNPOINT (-3.70582 40.41600)
    11005 DISTRIBUCION SL1277167953#-3.7055463461631111#40.4202951923090132019-12-01NaN1277167953NaNNaNESPNaNMADRIDMADRIDMADRIDMD28013.0CALLE GRAN VIA, 28013, MADRID, MADRIDCALLE GRAN VIA28013, MADRID, MADRID-3.70554640.420295S4-PNTSCZAMEDIUM34.0915 22 16 12NaNNaNNaNNaNMETALS SERVICE CENTERS AND OFFICES5051.0NaN50510000.0METALS SERVICE CENTERS AND OFFICES350.010035051DIVISION F. - WHOLESALE TRADEWHOLESALE TRADE - DURABLE GOODSMETALS AND MINERALS, EXCEPT PETROLEUMMETALS SERVICE CENTERS AND OFFICES1.01.02004.0149127.0170724.05080.0G3.00.00.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNNaNNaNNaNNaNNaNNaNNaN0.00.0NaNNaNPOINT (-3.70555 40.42030)
    215K ANGELS AND INVESTORS SL.1369422585#-3.70587#40.420482019-12-01NaN1369422585NaNNaNESPMADRIDMADRIDMADRIDMADRIDMD28013.0CALLE GRAN VIA 46, 28013, MADRID, MADRIDCALLE GRAN VIA 4628013, MADRID, MADRID-3.70587040.420480S8HPNTSCZAHIGH34.0NaNNaNNaNWWW.15KANGELS.COMNaNSECURITY AND COMMODITY SERVICES, NEC, NSK6289.0NaN62890000.0SECURITY AND COMMODITY SERVICE350.010010324DIVISION H. - FINANCE, INSURANCE, AND REAL ESTATESECURITY AND COMMODITY BROKERS, DEALERS, EXCHA...SERVICES ALLIED WITH THE EXCHANGE OF SECURITIE...SECURITY AND COMMODITY SERVICE2.02.02017.054756.061000.05080.0G3.00.00.0NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNNaNNaNNaNNaNNaNNaNNaN0.00.0NaNNaNPOINT (-3.70587 40.42048)
    31610 PLAZA DEL CARMEN 5 SL.1289731188#-3.7028#40.418922019-12-01NaN1289731188NaNNaNESPMADRIDMADRIDMADRIDMADRIDMD28013.0PLAZA CARMEN 5, 28013, MADRID, MADRIDPLAZA CARMEN 528013, MADRID, MADRID-3.70280040.418920S8HPNTSCZAHIGH34.0NaNNaNNaNNaNNaNEATING PLACES5812.0NaN58120000.0EATING PLACES350.010020100DIVISION G. - RETAIL TRADEEATING AND DRINKING PLACESEATING PLACES/RESTAURANTSEATING PLACES/RESTAURANTS - UNSPECIFIED35.035.02016.02766588.03167262.05080.0G3.00.00.0PUZZLE DE RESTAURANTES SL.CALLE BALLESTA, 32 - LOC DR, 28004, MADRID, MA...CALLE BALLESTA, 32 - LOC DRMADRIDMADRIDSPAIN28004PUZZLE DE RESTAURANTES SL.CALLE BALLESTA, 32 - LOC DR, 28004, MADRID, MA...CALLE BALLESTA, 32 - LOC DRMADRIDMADRID28004.0NPUZZLE DE RESTAURANTES SL.CALLE BALLESTA, 32 - LOC DR, 28004, MADRID, MA...CALLE BALLESTA, 32 - LOC DRMADRIDMADRIDSPAIN280040.00.0NaNNaNPOINT (-3.70280 40.41892)
    41ST WANDA SERVICE SERVICIOS INTEGRADOS DE CONT...1277282874#-3.70641#40.419562019-12-01NaN1277282874NaNNaNESPMADRIDMADRIDMADRIDMADRIDMD28013.0CALLE PRECIADOS, 29 - 5 A, 28013, MADRID, MADRIDCALLE PRECIADOS, 29 - 5 A28013, MADRID, MADRID-3.70641040.419560S8HPNTSCZAHIGH34.0NaNNaNNaNNaNNaNBUSINESS SERVICES, NEC, NSK7389.0NaN73890900.0FINANCIAL SERVICES350.010905900DIVISION I. - SERVICESBUSINESS SERVICESMISCELLANEOUS BUSINESS SERVICESBUSINESS SERVICES, NEC0.01.02003.058097.068508.05080.0G3.01.00.01ST WANDA SERVICE SERVICIOS INTEGRADOS DE CONT...CALLE PRECIADOS, 29 - 5 A, 28013, MADRID, MADR...CALLE PRECIADOS, 29 - 5 AMADRIDMADRIDSPAIN280131ST WANDA SERVICE SERVICIOS INTEGRADOS DE CONT...CALLE PRECIADOS, 29 - 5 A, 28013, MADRID, MADRIDCALLE PRECIADOS, 29 - 5 AMADRIDMADRID28013.0Y1ST WANDA SERVICE SERVICIOS INTEGRADOS DE CONT...CALLE PRECIADOS, 29 - 5 A, 28013, MADRID, MADR...CALLE PRECIADOS, 29 - 5 AMADRIDMADRIDSPAIN280132.01.0NaNNaNPOINT (-3.70641 40.41956)
    \n", "
    " ], "text/plain": [ " NAME \\\n", "0 100 MONTADITOS \n", "1 1005 DISTRIBUCION SL \n", "2 15K ANGELS AND INVESTORS SL. \n", "3 1610 PLAZA DEL CARMEN 5 SL. \n", "4 1ST WANDA SERVICE SERVICIOS INTEGRADOS DE CONT... \n", "\n", " geoid do_date BRANDNAME \\\n", "0 2173220473#-3.70582#40.416 2019-12-01 NaN \n", "1 1277167953#-3.7055463461631111#40.420295192309013 2019-12-01 NaN \n", "2 1369422585#-3.70587#40.42048 2019-12-01 NaN \n", "3 1289731188#-3.7028#40.41892 2019-12-01 NaN \n", "4 1277282874#-3.70641#40.41956 2019-12-01 NaN \n", "\n", " PB_ID TRADE_NAME FRANCHISE_NAME ISO3 AREANAME4 AREANAME3 \\\n", "0 2173220473 100 MONTADITOS NaN ESP NaN MADRID \n", "1 1277167953 NaN NaN ESP NaN MADRID \n", "2 1369422585 NaN NaN ESP MADRID MADRID \n", "3 1289731188 NaN NaN ESP MADRID MADRID \n", "4 1277282874 NaN NaN ESP MADRID MADRID \n", "\n", " AREANAME2 AREANAME1 STABB POSTCODE \\\n", "0 MADRID COMUNIDAD DE MADRID MD 28012.0 \n", "1 MADRID MADRID MD 28013.0 \n", "2 MADRID MADRID MD 28013.0 \n", "3 MADRID MADRID MD 28013.0 \n", "4 MADRID MADRID MD 28013.0 \n", "\n", " FORMATTEDADDRESS \\\n", "0 CALLE POSTAS 12, 28012, MADRID, MADRID \n", "1 CALLE GRAN VIA, 28013, MADRID, MADRID \n", "2 CALLE GRAN VIA 46, 28013, MADRID, MADRID \n", "3 PLAZA CARMEN 5, 28013, MADRID, MADRID \n", "4 CALLE PRECIADOS, 29 - 5 A, 28013, MADRID, MADRID \n", "\n", " MAINADDRESSLINE ADDRESSLASTLINE LONGITUDE LATITUDE \\\n", "0 CALLE POSTAS 12 28012, MADRID, MADRID -3.705820 40.416000 \n", "1 CALLE GRAN VIA 28013, MADRID, MADRID -3.705546 40.420295 \n", "2 CALLE GRAN VIA 46 28013, MADRID, MADRID -3.705870 40.420480 \n", "3 PLAZA CARMEN 5 28013, MADRID, MADRID -3.702800 40.418920 \n", "4 CALLE PRECIADOS, 29 - 5 A 28013, MADRID, MADRID -3.706410 40.419560 \n", "\n", " GEORESULT CONFIDENCE_CODE COUNTRY_ACCESS_CODE TEL_NUM \\\n", "0 S8HPNTSCZA HIGH NaN 915 23 11 40 \n", "1 S4-PNTSCZA MEDIUM 34.0 915 22 16 12 \n", "2 S8HPNTSCZA HIGH 34.0 NaN \n", "3 S8HPNTSCZA HIGH 34.0 NaN \n", "4 S8HPNTSCZA HIGH 34.0 NaN \n", "\n", " FAXNUM EMAIL HTTP \\\n", "0 913 51 90 03 ATTCLIENTE@GRUPORESTALIA.COM SPAIN.100MONTADITOS.COM \n", "1 NaN NaN NaN \n", "2 NaN NaN WWW.15KANGELS.COM \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " OPEN_24H BUSINESS_LINE SIC1 SIC2 \\\n", "0 NaN NaN NaN NaN \n", "1 NaN METALS SERVICE CENTERS AND OFFICES 5051.0 NaN \n", "2 NaN SECURITY AND COMMODITY SERVICES, NEC, NSK 6289.0 NaN \n", "3 NaN EATING PLACES 5812.0 NaN \n", "4 NaN BUSINESS SERVICES, NEC, NSK 7389.0 NaN \n", "\n", " SIC8 SIC8_DESCRIPTION ALT_INDUSTRY_CODE \\\n", "0 NaN TAPAS RESTAURANTS NaN \n", "1 50510000.0 METALS SERVICE CENTERS AND OFFICES 350.0 \n", "2 62890000.0 SECURITY AND COMMODITY SERVICE 350.0 \n", "3 58120000.0 EATING PLACES 350.0 \n", "4 73890900.0 FINANCIAL SERVICES 350.0 \n", "\n", " MICODE TRADE_DIVISION \\\n", "0 10021076 DIVISION G. - RETAIL TRADE \n", "1 10035051 DIVISION F. - WHOLESALE TRADE \n", "2 10010324 DIVISION H. - FINANCE, INSURANCE, AND REAL ESTATE \n", "3 10020100 DIVISION G. - RETAIL TRADE \n", "4 10905900 DIVISION I. - SERVICES \n", "\n", " GROUP \\\n", "0 EATING AND DRINKING PLACES \n", "1 WHOLESALE TRADE - DURABLE GOODS \n", "2 SECURITY AND COMMODITY BROKERS, DEALERS, EXCHA... \n", "3 EATING AND DRINKING PLACES \n", "4 BUSINESS SERVICES \n", "\n", " CLASS \\\n", "0 EATING PLACES/RESTAURANTS \n", "1 METALS AND MINERALS, EXCEPT PETROLEUM \n", "2 SERVICES ALLIED WITH THE EXCHANGE OF SECURITIE... \n", "3 EATING PLACES/RESTAURANTS \n", "4 MISCELLANEOUS BUSINESS SERVICES \n", "\n", " SUB_CLASS EMPLOYEE_HERE EMPLOYEE_COUNT \\\n", "0 EATING PLACES/RESTAURANTS NaN NaN \n", "1 METALS SERVICE CENTERS AND OFFICES 1.0 1.0 \n", "2 SECURITY AND COMMODITY SERVICE 2.0 2.0 \n", "3 EATING PLACES/RESTAURANTS - UNSPECIFIED 35.0 35.0 \n", "4 BUSINESS SERVICES, NEC 0.0 1.0 \n", "\n", " YEAR_START SALES_VOLUME_LOCAL SALES_VOLUME_US_DOLLARS CURRENCY_CODE \\\n", "0 NaN NaN NaN NaN \n", "1 2004.0 149127.0 170724.0 5080.0 \n", "2 2017.0 54756.0 61000.0 5080.0 \n", "3 2016.0 2766588.0 3167262.0 5080.0 \n", "4 2003.0 58097.0 68508.0 5080.0 \n", "\n", " AGENT_CODE LEGAL_STATUS_CODE STATUS_CODE SUBSIDIARY_INDICATOR \\\n", "0 NaN NaN NaN NaN \n", "1 G 3.0 0.0 0.0 \n", "2 G 3.0 0.0 0.0 \n", "3 G 3.0 0.0 0.0 \n", "4 G 3.0 1.0 0.0 \n", "\n", " PARENT_BUSINESS_NAME \\\n", "0 NaN \n", "1 NaN \n", "2 NaN \n", "3 PUZZLE DE RESTAURANTES SL. \n", "4 1ST WANDA SERVICE SERVICIOS INTEGRADOS DE CONT... \n", "\n", " PARENT_ADDRESS \\\n", "0 NaN \n", "1 NaN \n", "2 NaN \n", "3 CALLE BALLESTA, 32 - LOC DR, 28004, MADRID, MA... \n", "4 CALLE PRECIADOS, 29 - 5 A, 28013, MADRID, MADR... \n", "\n", " PARENT_STREET_ADDRESS PARENT_AREANAME3 PARENT_AREANAME1 \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 CALLE BALLESTA, 32 - LOC DR MADRID MADRID \n", "4 CALLE PRECIADOS, 29 - 5 A MADRID MADRID \n", "\n", " PARENT_COUNTRY PARENT_POSTCODE \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 SPAIN 28004 \n", "4 SPAIN 28013 \n", "\n", " DOMESTIC_ULTIMATE_BUSINESS_NAME \\\n", "0 NaN \n", "1 NaN \n", "2 NaN \n", "3 PUZZLE DE RESTAURANTES SL. \n", "4 1ST WANDA SERVICE SERVICIOS INTEGRADOS DE CONT... \n", "\n", " DOMESTIC_ULTIMATE_ADDRESS \\\n", "0 NaN \n", "1 NaN \n", "2 NaN \n", "3 CALLE BALLESTA, 32 - LOC DR, 28004, MADRID, MA... \n", "4 CALLE PRECIADOS, 29 - 5 A, 28013, MADRID, MADRID \n", "\n", " DOMESTIC_ULTIMATE_STREET_ADDRESS DOMESTIC_ULTIMATE_AREANAME3 \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 CALLE BALLESTA, 32 - LOC DR MADRID \n", "4 CALLE PRECIADOS, 29 - 5 A MADRID \n", "\n", " DOMESTIC_ULTIMATE_AREANAME1 DOMESTIC_ULTIMATE_POSTCODE \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 MADRID 28004.0 \n", "4 MADRID 28013.0 \n", "\n", " GLOBAL_ULTIMATE_INDICATOR \\\n", "0 NaN \n", "1 N \n", "2 N \n", "3 N \n", "4 Y \n", "\n", " GLOBAL_ULTIMATE_BUSINESS_NAME \\\n", "0 NaN \n", "1 NaN \n", "2 NaN \n", "3 PUZZLE DE RESTAURANTES SL. \n", "4 1ST WANDA SERVICE SERVICIOS INTEGRADOS DE CONT... \n", "\n", " GLOBAL_ULTIMATE_ADDRESS \\\n", "0 NaN \n", "1 NaN \n", "2 NaN \n", "3 CALLE BALLESTA, 32 - LOC DR, 28004, MADRID, MA... \n", "4 CALLE PRECIADOS, 29 - 5 A, 28013, MADRID, MADR... \n", "\n", " GLOBAL_ULTIMATE_STREET_ADDRESS GLOBAL_ULTIMATE_AREANAME3 \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 CALLE BALLESTA, 32 - LOC DR MADRID \n", "4 CALLE PRECIADOS, 29 - 5 A MADRID \n", "\n", " GLOBAL_ULTIMATE_AREANAME1 GLOBAL_ULTIMATE_COUNTRY GLOBAL_ULTIMATE_POSTCODE \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 MADRID SPAIN 28004 \n", "4 MADRID SPAIN 28013 \n", "\n", " FAMILY_MEMBERS HIERARCHY_CODE TICKER_SYMBOL EXCHANGE_NAME \\\n", "0 NaN NaN NaN NaN \n", "1 0.0 0.0 NaN NaN \n", "2 0.0 0.0 NaN NaN \n", "3 0.0 0.0 NaN NaN \n", "4 2.0 1.0 NaN NaN \n", "\n", " geom \n", "0 POINT (-3.70582 40.41600) \n", "1 POINT (-3.70555 40.42030) \n", "2 POINT (-3.70587 40.42048) \n", "3 POINT (-3.70280 40.41892) \n", "4 POINT (-3.70641 40.41956) " ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql_query = \"SELECT * except(do_label) FROM $dataset$ WHERE ST_IntersectsBox(geom, -3.707628,40.415947,-3.700891,40.421403)\"\n", "sample_df = pois_ds.to_dataframe(sql_query=sql_query)\n", "\n", "#To keep only most updated POI's (based on the do_date)\n", "sample_df = sample_df.sort_values(['NAME', 'do_date']).groupby('NAME').first().reset_index()\n", "\n", "sample_df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "After some exploration of the DataFrame, we find out `TRADE_DIVISION` is our variable. There is a category called `DIVISION L. - TOURISM`." ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 265 }, "id": "ByHc8RilgDrk", "outputId": "0c448b77-a030-47f6-ca2f-8919228aa7ec" }, "outputs": [ { "data": { "text/plain": [ "DIVISION I. - SERVICES 1769\n", "DIVISION G. - RETAIL TRADE 1370\n", "DIVISION E. - TRANSPORTATION AND PUBLIC UTILITIES 724\n", "DIVISION H. - FINANCE, INSURANCE, AND REAL ESTATE 613\n", "DIVISION F. - WHOLESALE TRADE 205\n", "DIVISION D. - MANUFACTURING 198\n", "DIVISION C. - CONSTRUCTION 67\n", "DIVISION J. - PUBLIC ADMINISTRATION 25\n", "DIVISION L. - TOURISM 14\n", "DIVISION A. - AGRICULTURE, FORESTRY, AND FISHING 10\n", "DIVISION M. - SPORTS 8\n", "Name: TRADE_DIVISION, dtype: int64" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sample_df['TRADE_DIVISION'].value_counts()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "##### Visualize the data sample" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 674 }, "id": "D1osAfHTFhJY", "outputId": "debd318b-2994-4901-c9b0-b436f5be72a2" }, "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", " StackTrace\n", "
      \n", "
      \n", "
      \n", "\n", "\n", "\n", "\n", "\n", "\">\n", "\n", "" ], "text/plain": [ "" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Map(Layer(sample_df, \n", " geom_col='geom',\n", " style=color_category_style('TRADE_DIVISION', size=3, stroke_width=0.2), \n", " encode_data=False))" ] }, { "cell_type": "markdown", "metadata": { "id": "6rK2ORv0gVai" }, "source": [ "\n", "#### Download dataset filtering by column value and bounding box\n", "\n", "Once we know we can use column `TRADE_DIVISION` to filter tourism POIs, we'll add it to our query with a larger bounding box covering our area of interest." ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 274 }, "id": "JJGGHvuggNs_", "outputId": "f8be6686-d62a-4bc3-d561-801a1337cd5b" }, "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", "
      NAMEgeoiddo_dateBRANDNAMEPB_IDTRADE_NAMEFRANCHISE_NAMEISO3AREANAME4AREANAME3AREANAME2AREANAME1STABBPOSTCODEFORMATTEDADDRESSMAINADDRESSLINEADDRESSLASTLINELONGITUDELATITUDEGEORESULTCONFIDENCE_CODECOUNTRY_ACCESS_CODETEL_NUMFAXNUMEMAILHTTPOPEN_24HBUSINESS_LINESIC1SIC2SIC8SIC8_DESCRIPTIONALT_INDUSTRY_CODEMICODETRADE_DIVISIONGROUPCLASSSUB_CLASSEMPLOYEE_HEREEMPLOYEE_COUNTYEAR_STARTSALES_VOLUME_LOCALSALES_VOLUME_US_DOLLARSCURRENCY_CODEAGENT_CODELEGAL_STATUS_CODESTATUS_CODESUBSIDIARY_INDICATORPARENT_BUSINESS_NAMEPARENT_ADDRESSPARENT_STREET_ADDRESSPARENT_AREANAME3PARENT_AREANAME1PARENT_COUNTRYPARENT_POSTCODEDOMESTIC_ULTIMATE_BUSINESS_NAMEDOMESTIC_ULTIMATE_ADDRESSDOMESTIC_ULTIMATE_STREET_ADDRESSDOMESTIC_ULTIMATE_AREANAME3DOMESTIC_ULTIMATE_AREANAME1DOMESTIC_ULTIMATE_POSTCODEGLOBAL_ULTIMATE_INDICATORGLOBAL_ULTIMATE_BUSINESS_NAMEGLOBAL_ULTIMATE_ADDRESSGLOBAL_ULTIMATE_STREET_ADDRESSGLOBAL_ULTIMATE_AREANAME3GLOBAL_ULTIMATE_AREANAME1GLOBAL_ULTIMATE_COUNTRYGLOBAL_ULTIMATE_POSTCODEFAMILY_MEMBERSHIERARCHY_CODETICKER_SYMBOLEXCHANGE_NAMEgeom
      0ARCO DE CUCHILLEROS2033946578#-3.708101#40.41479832019-12-01NaN2033946578NaNNaNESPNaNMADRIDMADRIDCOMUNIDAD DE MADRIDMD28012.0CALLE DE LOS CUCHILLEROS, 28012, MADRID, MADRIDCALLE DE LOS CUCHILLEROS28012, MADRID, MADRID-3.70810140.414798T20LOWNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNARCHNaN10110112DIVISION L. - TOURISMTOURISMIMPORTANT TOURIST ATTRACTIONARCHNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNPOINT (-3.70810 40.41480)
      1ATENEO DE MADRID2033838561#-3.6982188#40.41503082019-12-01NaN2033838561NaNNaNESPNaNMADRIDMADRIDCOMUNIDAD DE MADRIDMD28014.0CALLE DEL PRADO, 28014, MADRID, MADRIDCALLE DEL PRADO28014, MADRID, MADRID-3.69821940.415031T20LOWNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNTOURIST BUILDINGNaN10110200DIVISION L. - TOURISMTOURISMIMPORTANT TOURIST ATTRACTIONTOURIST BUILDINGNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNPOINT (-3.69822 40.41503)
      2BANCO DE ESPAÑA2022507776#-3.6939777#40.41897372019-12-01NaN2022507776NaNNaNESPNaNMADRIDMADRIDCOMUNIDAD DE MADRIDMDNaNMADRID, MADRIDNaNMADRID, MADRID-3.69397840.418974T1HIGHNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNTOURIST BUILDINGNaN10110200DIVISION L. - TOURISMTOURISMIMPORTANT TOURIST ATTRACTIONTOURIST BUILDINGNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNPOINT (-3.69398 40.41897)
      3BASÍLICA DE NUESTRO PADRE JESÚS DE MEDINACELI2033893910#-3.6957089#40.41419562019-12-01NaN2033893910NaNNaNESPNaNMADRIDMADRIDCOMUNIDAD DE MADRIDMD28014.0PLAZA DE JESÚS, 28014, MADRID, MADRIDPLAZA DE JESÚS28014, MADRID, MADRID-3.69570940.414196T20LOWNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNTOURIST BUILDINGNaN10110200DIVISION L. - TOURISMTOURISMIMPORTANT TOURIST ATTRACTIONTOURIST BUILDINGNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNPOINT (-3.69571 40.41420)
      4BIBLIOTECA NACIONAL DE ESPAÑA2033804926#-3.6906236#40.42385262019-12-01NaN2033804926NaNNaNESPNaNMADRIDMADRIDCOMUNIDAD DE MADRIDMD28001.0PASEO DE RECOLETOS, 28001, MADRID, MADRIDPASEO DE RECOLETOS28001, MADRID, MADRID-3.69062440.423853T20LOWNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNTOURIST BUILDINGNaN10110200DIVISION L. - TOURISMTOURISMIMPORTANT TOURIST ATTRACTIONTOURIST BUILDINGNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNPOINT (-3.69062 40.42385)
      \n", "
      " ], "text/plain": [ " NAME \\\n", "0 ARCO DE CUCHILLEROS \n", "1 ATENEO DE MADRID \n", "2 BANCO DE ESPAÑA \n", "3 BASÍLICA DE NUESTRO PADRE JESÚS DE MEDINACELI \n", "4 BIBLIOTECA NACIONAL DE ESPAÑA \n", "\n", " geoid do_date BRANDNAME PB_ID \\\n", "0 2033946578#-3.708101#40.4147983 2019-12-01 NaN 2033946578 \n", "1 2033838561#-3.6982188#40.4150308 2019-12-01 NaN 2033838561 \n", "2 2022507776#-3.6939777#40.4189737 2019-12-01 NaN 2022507776 \n", "3 2033893910#-3.6957089#40.4141956 2019-12-01 NaN 2033893910 \n", "4 2033804926#-3.6906236#40.4238526 2019-12-01 NaN 2033804926 \n", "\n", " TRADE_NAME FRANCHISE_NAME ISO3 AREANAME4 AREANAME3 AREANAME2 \\\n", "0 NaN NaN ESP NaN MADRID MADRID \n", "1 NaN NaN ESP NaN MADRID MADRID \n", "2 NaN NaN ESP NaN MADRID MADRID \n", "3 NaN NaN ESP NaN MADRID MADRID \n", "4 NaN NaN ESP NaN MADRID MADRID \n", "\n", " AREANAME1 STABB POSTCODE \\\n", "0 COMUNIDAD DE MADRID MD 28012.0 \n", "1 COMUNIDAD DE MADRID MD 28014.0 \n", "2 COMUNIDAD DE MADRID MD NaN \n", "3 COMUNIDAD DE MADRID MD 28014.0 \n", "4 COMUNIDAD DE MADRID MD 28001.0 \n", "\n", " FORMATTEDADDRESS MAINADDRESSLINE \\\n", "0 CALLE DE LOS CUCHILLEROS, 28012, MADRID, MADRID CALLE DE LOS CUCHILLEROS \n", "1 CALLE DEL PRADO, 28014, MADRID, MADRID CALLE DEL PRADO \n", "2 MADRID, MADRID NaN \n", "3 PLAZA DE JESÚS, 28014, MADRID, MADRID PLAZA DE JESÚS \n", "4 PASEO DE RECOLETOS, 28001, MADRID, MADRID PASEO DE RECOLETOS \n", "\n", " ADDRESSLASTLINE LONGITUDE LATITUDE GEORESULT CONFIDENCE_CODE \\\n", "0 28012, MADRID, MADRID -3.708101 40.414798 T20 LOW \n", "1 28014, MADRID, MADRID -3.698219 40.415031 T20 LOW \n", "2 MADRID, MADRID -3.693978 40.418974 T1 HIGH \n", "3 28014, MADRID, MADRID -3.695709 40.414196 T20 LOW \n", "4 28001, MADRID, MADRID -3.690624 40.423853 T20 LOW \n", "\n", " COUNTRY_ACCESS_CODE TEL_NUM FAXNUM EMAIL HTTP OPEN_24H BUSINESS_LINE \\\n", "0 NaN NaN NaN NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN NaN NaN NaN \n", "\n", " SIC1 SIC2 SIC8 SIC8_DESCRIPTION ALT_INDUSTRY_CODE MICODE \\\n", "0 NaN NaN NaN ARCH NaN 10110112 \n", "1 NaN NaN NaN TOURIST BUILDING NaN 10110200 \n", "2 NaN NaN NaN TOURIST BUILDING NaN 10110200 \n", "3 NaN NaN NaN TOURIST BUILDING NaN 10110200 \n", "4 NaN NaN NaN TOURIST BUILDING NaN 10110200 \n", "\n", " TRADE_DIVISION GROUP CLASS \\\n", "0 DIVISION L. - TOURISM TOURISM IMPORTANT TOURIST ATTRACTION \n", "1 DIVISION L. - TOURISM TOURISM IMPORTANT TOURIST ATTRACTION \n", "2 DIVISION L. - TOURISM TOURISM IMPORTANT TOURIST ATTRACTION \n", "3 DIVISION L. - TOURISM TOURISM IMPORTANT TOURIST ATTRACTION \n", "4 DIVISION L. - TOURISM TOURISM IMPORTANT TOURIST ATTRACTION \n", "\n", " SUB_CLASS EMPLOYEE_HERE EMPLOYEE_COUNT YEAR_START \\\n", "0 ARCH NaN NaN NaN \n", "1 TOURIST BUILDING NaN NaN NaN \n", "2 TOURIST BUILDING NaN NaN NaN \n", "3 TOURIST BUILDING NaN NaN NaN \n", "4 TOURIST BUILDING NaN NaN NaN \n", "\n", " SALES_VOLUME_LOCAL SALES_VOLUME_US_DOLLARS CURRENCY_CODE AGENT_CODE \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " LEGAL_STATUS_CODE STATUS_CODE SUBSIDIARY_INDICATOR PARENT_BUSINESS_NAME \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " PARENT_ADDRESS PARENT_STREET_ADDRESS PARENT_AREANAME3 PARENT_AREANAME1 \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " PARENT_COUNTRY PARENT_POSTCODE DOMESTIC_ULTIMATE_BUSINESS_NAME \\\n", "0 NaN NaN NaN \n", "1 NaN NaN NaN \n", "2 NaN NaN NaN \n", "3 NaN NaN NaN \n", "4 NaN NaN NaN \n", "\n", " DOMESTIC_ULTIMATE_ADDRESS DOMESTIC_ULTIMATE_STREET_ADDRESS \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " DOMESTIC_ULTIMATE_AREANAME3 DOMESTIC_ULTIMATE_AREANAME1 \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " DOMESTIC_ULTIMATE_POSTCODE GLOBAL_ULTIMATE_INDICATOR \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " GLOBAL_ULTIMATE_BUSINESS_NAME GLOBAL_ULTIMATE_ADDRESS \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " GLOBAL_ULTIMATE_STREET_ADDRESS GLOBAL_ULTIMATE_AREANAME3 \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " GLOBAL_ULTIMATE_AREANAME1 GLOBAL_ULTIMATE_COUNTRY \\\n", "0 NaN NaN \n", "1 NaN NaN \n", "2 NaN NaN \n", "3 NaN NaN \n", "4 NaN NaN \n", "\n", " GLOBAL_ULTIMATE_POSTCODE FAMILY_MEMBERS HIERARCHY_CODE TICKER_SYMBOL \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " EXCHANGE_NAME geom \n", "0 NaN POINT (-3.70810 40.41480) \n", "1 NaN POINT (-3.69822 40.41503) \n", "2 NaN POINT (-3.69398 40.41897) \n", "3 NaN POINT (-3.69571 40.41420) \n", "4 NaN POINT (-3.69062 40.42385) " ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "sql_query = \"\"\"\n", " SELECT * except(do_label) FROM $dataset$ \n", " WHERE TRADE_DIVISION = 'DIVISION L. - TOURISM' \n", " AND ST_IntersectsBox(geom, -3.716398,40.407437,-3.690477,40.425277)\n", "\"\"\"\n", "tourism_pois = pois_ds.to_dataframe(sql_query=sql_query)\n", "\n", "#To keep only most updated POIs (based on the do_date)\n", "tourism_pois = tourism_pois.sort_values(['NAME', 'do_date']).groupby('NAME').first().reset_index()\n", "\n", "tourism_pois.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can classify the tourism POI's using the variable `SUB_CLASS`." ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 176 }, "id": "tzJj21dQo9Kv", "outputId": "b67bbcb9-4f85-411b-c5ab-efdd3811f3a6" }, "outputs": [ { "data": { "text/plain": [ "TOURIST BUILDING 66\n", "MONUMENT 20\n", "IMPORTANT TOURIST ATTRACTION -UNSPECIFIED 16\n", "SCENIC, PANORAMIC VIEW 4\n", "STATUE 2\n", "TOWER 2\n", "IMPORTANT TOURIST ATTRACTION 2\n", "MEMORIAL 1\n", "ARCH 1\n", "Name: SUB_CLASS, dtype: int64" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "tourism_pois['SUB_CLASS'].value_counts()" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 674 }, "id": "2IEOMMsZkUzY", "outputId": "3f7b6f27-03e8-46a5-cc51-25613b0ddbbe" }, "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", " StackTrace\n", "
        \n", "
        \n", "
        \n", "\n", "\n", "\n", "\n", "\n", "\">\n", "\n", "" ], "text/plain": [ "" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Map(Layer(tourism_pois, \n", " geom_col='geom',\n", " style=color_category_style('SUB_CLASS', stroke_width=0.2)))" ] }, { "cell_type": "markdown", "metadata": { "id": "gnpItWtBFPke" }, "source": [ "\n", "#### Upload filtered dataset to your CARTO account\n", "\n", "Finally, we will save this filtered dataset on our CARTO account so that we can use it in the future. We can do this using the CARTOframes function `to_carto`." ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "id": "TDHvu80KpcK7" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Success! Data uploaded to table \"demo_tourism_pois_madrid\" correctly\n" ] }, { "data": { "text/plain": [ "'demo_tourism_pois_madrid'" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "to_carto(tourism_pois, 'demo_tourism_pois_madrid', geom_col='geom')" ] } ], "metadata": { "colab": { "collapsed_sections": [], "name": "CARTO | Data Observatory v2.0 - Access to Data.ipynb", "provenance": [], "toc_visible": true }, "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.7.3" } }, "nbformat": 4, "nbformat_minor": 4 }