Harnessing the Power of Geospatial Analytics in CARTO for Strategic Hotel Site Selection in Madrid
The beauty of CARTO is that we meet the users where they are in their spatial analysis journey. From advanced spatial capabilities to simple mapping tools to visualize, analyze & share in a matter of minutes. To accelerate your spatial app development efforts, you can use CARTO embedded capabilities inside low-code tools like this one created in Slides.com. In this story map, we will harness the power of geospatial analytics to uncover insights for strategic hotel site selection in Madrid.
A presentation made by Gerardo Ezequiel Martín Carreño
11/08/2023
-
Client: NH Hotel Group, based in Madrid, traditionally focused on serving business and corporate travelers seeks to cater to the rising demand for cultural tourism.
-
Target Demographic: The new hotel aims to serve mid-high income tourists aged 40-69 who appreciate Madrid's vibrant culture.
-
Proposed Solution: NH Hotel Group plans to provide luxury accommodations with local cultural experiences in easily accessible locations.
-
Strategic Location: A central location is key to enhancing guest experiences and meeting the rising trend of combining business and leisure travel.
-
Market Demand: Madrid's tourism market primarily demands cultural and business tourism offerings.
-
Sustainable Positioning: NH Hotel Group prioritises sustainability, with the new hotel ideally situated within walking distance from major tourist attractions.
-
Objective: The company aims to use geospatial data analysis to identify an ideal location and enhance guest experiences
NH Hotels Strategic Expansion into Cultural Tourism in Madrid
Methodology
1
Data Collection and Processing
2
Accessibility & Proximity
3
Spatial Index Enrichment
4
Hotel Optimal Location Analysis
Competitors Proximity Analysis
5
6
Optimizing Hotel Site Selection: Choosing the Ideal Buildings
MAP 1 OF 6
Setting the Stage: Harnessing Diverse Data Sources
Datasets
- Pedestrian Traffic Data: Granular data on pedestrian traffic from the Madrid Open Data Portal and CARTO's demo tables.
- Existing Tourist Accommodations: Comprehensive information on accommodations in Madrid from the Madrid Open Data Portal.
- Points of Interest: Key attractions and points of interest in Madrid obtained from OpenStreetMap Nodes via the CARTO Data Observatory.
Processing
- Preprocessing: The data underwent thorough preprocessing in a Python notebook in Colab. This involved cleaning, standardizing, and enriching the data to ensure its suitability for analysis. The Python notebook in Colab provided a reliable and reproducible environment for processing the data, ensuring accuracy and consistency in the results.
Visualisation
- The processed data was visualized on a map to gain insights and explore the variables. The map displays the distribution of pedestrian traffic across Madrid, highlighting areas with higher concentrations of foot traffic, such as districts like Salamanca and Chamberí. Additionally, time series analysis of pedestrian traffic in selected streets provides a deeper understanding of movement patterns. The interactive features of the map allow users to filter and manipulate the data based on specific criteria or preferences, enhancing the exploration and analysis process
By leveraging these datasets and applying thorough preprocessing techniques, we are able to extract meaningful insights and visualise the data on an interactive map. This visualisation provide a comprehensive understanding of pedestrian traffic patterns and their relationship with tourist accommodations and points of interest in Madrid. Such insights enable informed decision-making for hotel site selection and strategic planning in the city.
Photo by Finn on Unsplash
# Workflow - Preprocessing Study area
CREATE TABLE `carto-dw-ac-vs5d76ww.shared.madrid_est_pedestrian_visitors`
CLUSTER BY (geom)
AS
SELECT
geoid,
ANY_VALUE(geom) AS geom,
-- High income, specific age ranges
SUM(CASE WHEN income = 'high' AND age = '18-29' THEN visitors ELSE 0 END) AS visitors_high_18_29,
SUM(CASE WHEN income = 'high' AND age = '30-39' THEN visitors ELSE 0 END) AS visitors_high_30_39,
SUM(CASE WHEN income = 'high' AND age = '40-49' THEN visitors ELSE 0 END) AS visitors_high_40_49,
SUM(CASE WHEN income = 'high' AND age = '50-59' THEN visitors ELSE 0 END) AS visitors_high_50_59,
SUM(CASE WHEN income = 'high' AND age = '60-69' THEN visitors ELSE 0 END) AS visitors_high_60_69,
-- Mid-high income, specific age ranges
SUM(CASE WHEN income = 'mid-high' AND age = '18-29' THEN visitors ELSE 0 END) AS visitors_midhigh_18_29,
SUM(CASE WHEN income = 'mid-high' AND age = '30-39' THEN visitors ELSE 0 END) AS visitors_midhigh_30_39,
SUM(CASE WHEN income = 'mid-high' AND age = '40-49' THEN visitors ELSE 0 END) AS visitors_midhigh_40_49,
SUM(CASE WHEN income = 'mid-high' AND age = '50-59' THEN visitors ELSE 0 END) AS visitors_midhigh_50_59,
SUM(CASE WHEN income = 'mid-high' AND age = '60-69' THEN visitors ELSE 0 END) AS visitors_midhigh_60_69,
-- Mid income, specific age ranges
SUM(CASE WHEN income = 'mid' AND age = '18-29' THEN visitors ELSE 0 END) AS visitors_mid_18_29,
SUM(CASE WHEN income = 'mid' AND age = '30-39' THEN visitors ELSE 0 END) AS visitors_mid_30_39,
SUM(CASE WHEN income = 'mid' AND age = '40-49' THEN visitors ELSE 0 END) AS visitors_mid_40_49,
SUM(CASE WHEN income = 'mid' AND age = '50-59' THEN visitors ELSE 0 END) AS visitors_mid_50_59,
SUM(CASE WHEN income = 'mid' AND age = '60-69' THEN visitors ELSE 0 END) AS visitors_mid_60_69,
-- Low income, specific age ranges
SUM(CASE WHEN income = 'low' AND age = '18-29' THEN visitors ELSE 0 END) AS visitors_low_18_29,
SUM(CASE WHEN income = 'low' AND age = '30-39' THEN visitors ELSE 0 END) AS visitors_low_30_39,
SUM(CASE WHEN income = 'low' AND age = '40-49' THEN visitors ELSE 0 END) AS visitors_low_40_49,
SUM(CASE WHEN income = 'low' AND age = '50-59' THEN visitors ELSE 0 END) AS visitors_low_50_59,
SUM(CASE WHEN income = 'low' AND age = '60-69' THEN visitors ELSE 0 END) AS visitors_low_60_69,
-- High income, combined age ranges
SUM(CASE WHEN income = 'high' AND age IN ('40-49', '50-59', '60-69') THEN visitors ELSE 0 END) AS visitors_high_40_69,
-- Mid-high income, combined age ranges
SUM(CASE WHEN income = 'mid-high' AND age IN ('40-49', '50-59', '60-69') THEN visitors ELSE 0 END) AS visitors_midhigh_40_69,
-- Mid income, combined age ranges
SUM(CASE WHEN income = 'mid' AND age IN ('40-49', '50-59', '60-69') THEN visitors ELSE 0 END) AS visitors_mid_40_69,
-- Low income, combined age ranges
SUM(CASE WHEN income = 'low' AND age IN ('40-49', '50-59', '60-69') THEN visitors ELSE 0 END) AS visitors_low_40_69
FROM
`carto-demo-data.demo_tables.madrid_est_pedestrian_traffic`
WHERE
age IN ('18-29', '30-39', '40-49', '50-59', '60-69')
GROUP BY
geoid;
# Preprocessing sociodemographic variables
CREATE OR REPLACE TABLE `carto-dw-ac-vs5d76ww.shared.madrid_osm_poi_study_area` AS
WITH poi_data AS (
SELECT
ROW_NUMBER() OVER() AS osm_tourism_poi_id,
names.value AS poi_name,
CASE
WHEN tags.value IN ('museum', 'attraction', 'gallery', 'viewpoint', 'monument', 'memorial') THEN 'patrimony_monuments_museums'
WHEN tags.value IN ('theatre', 'cinema', 'arts_centre') THEN 'theatre_arts'
WHEN tags.value IN ('cafe', 'restaurant', 'bar', 'pub') THEN 'gastronomy'
WHEN tags.value IN ('park', 'garden', 'zoo', 'aquarium', 'theme_park', 'stadium', 'sports_centre', 'swimming_pool', 'golf_course') THEN 'leisure'
END AS poi_category,
do_geom.geom AS geom,
do_data.geoid AS geoid
FROM
`carto-data.ac_vs5d76ww.sub_openstreetmap_pointsofinterest_nodes_esp_latlon_v1_quarterly_v1` do_data
INNER JOIN
`carto-data.ac_vs5d76ww.sub_openstreetmap_geography_esp_latlon_v1` do_geom
ON
do_data.geoid = do_geom.geoid
INNER JOIN
`carto-dw-ac-vs5d76ww.shared.madrid_study_area` AS study_area
ON
ST_CONTAINS(study_area.geom, do_geom.geom),
UNNEST(
ARRAY(
SELECT AS STRUCT key, value
FROM UNNEST(do_data.all_tags)
WHERE key IN ('tourism', 'amenity', 'shop', 'leisure')
)
) AS tags,
UNNEST(
ARRAY(
SELECT AS STRUCT value
FROM UNNEST(do_data.all_tags)
WHERE key = 'name'
)
) AS names
)
SELECT
ANY_VALUE(CASE WHEN poi_category = 'patrimony_monuments_museums' THEN poi_name END) AS patrimony_monuments_museums,
ANY_VALUE(CASE WHEN poi_category = 'theatre_arts' THEN poi_name END) AS theatre_arts,
ANY_VALUE(CASE WHEN poi_category = 'gastronomy' THEN poi_name END) AS gastronomy,
ANY_VALUE(CASE WHEN poi_category = 'leisure' THEN poi_name END) AS leisure,
ANY_VALUE(geom) AS geom,
geoid
FROM poi_data
GROUP BY geoid
HAVING
MAX(CASE WHEN poi_category = 'patrimony_monuments_museums' THEN poi_name END) IS NOT NULL
OR MAX(CASE WHEN poi_category = 'theatre_arts' THEN poi_name END) IS NOT NULL
OR MAX(CASE WHEN poi_category = 'gastronomy' THEN poi_name END) IS NOT NULL
OR MAX(CASE WHEN poi_category = 'leisure' THEN poi_name END) IS NOT NULL;
# OSM POIs from Data Observatory
MAP 2 OF 6
Unlocking Proximity: Accessibility & Proximity Analysis
Ensuring convenient access to key attractions is essential for the success of hotels. In this section, we explore NH Hotels Group's in-depth accessibility analysis in Madrid. By examining isochrone intersections and proximity to existing NH Hotels Group locations, NH Hotels Group gained valuable insights for strategic hotel site selection. Let's dive into the findings of this analysis.
Isochrone Intersection Analysis
- Creation of isochrones representing 15-minute walking distances from key attractions
- Identification of areas where multiple attractions overlap
- Overlapping zones indicate highly desirable locations for hotel development
- Offers convenient access to a diverse range of attractions within a short walking distance
- Enhances the guest experience and provides easy exploration of cultural, artistic, culinary, and leisure experiences
Proximity Analysis to Avoid Cannibalization
- Calculation of minimum distance between potential hotel sites and the closest NH Hotel
- Assessment of proximity to avoid excessive competition and guest diversion
- Ensures a balanced distribution of accommodations across the city
- Strategically positions new hotels while maintaining a healthy distance from existing NH Hotels Group locations
- Optimizes hotel offerings and maintains a harmonious distribution throughout Madrid
By leveraging the insights from our accessibility analysis, including the isochrone intersection analysis and proximity analysis, NH Hotels Group can make informed decisions regarding the selection of hotel sites. Our strategic approach ensures that our hotels are conveniently located near key attractions and that we maintain a balanced and competitive presence in Madrid's hospitality industry.
Photo by Alev Takil on Unsplash
# Workflow - Generating and intersecting isochrones
DECLARE madrid_buffer GEOGRAPHY;
SET madrid_buffer = ST_BUFFER(ST_GEOGPOINT(-3.70000775514063, 40.4150453503748), 3500);
CREATE TABLE `carto-dw-ac-vs5d76ww.shared.madrid_NH_aos_enriched_sum_wdist` AS
(
WITH NH_hotels AS (
SELECT `carto-un`.carto.H3_FROMGEOGPOINT(geom, 10) as h3
FROM `carto-dw-ac-vs5d76ww.shared.madrid_hotels_brand_id` d
WHERE ST_CONTAINS(madrid_buffer, geom)
AND brand = 'NH_%'
),
h3_grid AS (
SELECT h3
FROM `carto-dw-ac-vs5d76ww.shared.madrid_h3_study_area_enriched_final`
)
SELECT h3_grid.h3, MIN(`carto-un`.carto.H3_DISTANCE(h3_grid.h3, NH_hotels.h3)) AS dist
FROM NH_hotels
CROSS JOIN h3_grid
GROUP BY h3_grid.h3
);
# Calculating minimum distance between NH Hotels
CREATE TABLE `carto-dw-ac-vs5d76ww.shared.madrid_osm_tourism_poi_isolines_15min_intersection` AS
SELECT
ST_INTERSECTION(
ST_INTERSECTION(
ST_INTERSECTION(
(SELECT ST_UNION_AGG(geom) FROM `carto-dw-ac-vs5d76ww.shared.madrid_osm_tourism_poi_isolines_15min_gastronomy`),
(SELECT ST_UNION_AGG(geom) FROM `carto-dw-ac-vs5d76ww.shared.madrid_osm_tourism_poi_isolines_15min_leisure`)
),
(SELECT ST_UNION_AGG(geom) FROM `carto-dw-ac-vs5d76ww.shared.madrid_osm_tourism_poi_isolines_15min_patrimony_monuments_museums`)
),
(SELECT ST_UNION_AGG(geom) FROM `carto-dw-ac-vs5d76ww.shared.madrid_osm_tourism_poi_isolines_15min_theater_arts`)
) AS geom;
# Isochrones intersection and aggregation
MAP 3 OF 6
Enriching Spatial Insights: Spatial Index Enrichment
Spatial Index Enrichment plays a crucial role in the decision-making process of NH Hotels Group for hotel site selection in Madrid. By leveraging the H3 spatial indexing system and incorporating various variables, NH Hotels Group gains valuable insights into pedestrian activity, accessibility to attractions, and the distribution of accommodations. This allows them to strategically position their hotels, enhance customer satisfaction, and drive business growth.
Key Insights:
-
Spatial index enrichment enables NH Hotels Group to:
- Make informed decisions on hotel site selection.
- Strategically position hotels in areas with high pedestrian activity.
- Ensure optimal accessibility to attractions.
- Maintain a balanced distribution of accommodations.
- Enhance customer satisfaction and drive business growth.
-
The H3 spatial indexing system offers:
- Valuable statistics and speed of computation for efficient analysis.
- Each hexagon at a resolution of 10 covers an area of approximately 0.015 square kilometers.
- Fine-grained understanding of spatial variations and dynamics in Madrid's city center.
The utilization of spatial indexes provides NH Hotels Group with valuable insights for decision-making and hotel site selection in Madrid. The H3 system offers efficient computation and statistical insights, enabling a detailed understanding of spatial dynamics. This information empowers NH Hotels Group to strategically position their hotels, optimize accessibility to attractions, and achieve their business objectives in Madrid's tourism landscape.
Photo by Quique Olivar on Unsplash
CALL `carto-un`.carto.ENRICH_GRID(
-- Index type
'h3',
-- Grid query and name of the index column
'''
SELECT h3 FROM carto-dw-ac-vs5d76ww.shared.madrid_h3_study_area
''',
'h3',
-- Input query and name of the geometry column
'''
SELECT geom, total_visitors, total_visits, geoid FROM carto-dw-ac-vs5d76ww.shared.madrid_est_pedestrian_traffic_enriched_40_99_midhigh_high_income
''',
'geom',
-- Columns to enrich and aggregation function
[('total_visitors', 'sum'), ('total_visits', 'sum')],
-- Output table
['`carto-dw-ac-vs5d76ww.shared.madrid_h3_study_area_enriched`']
);
# Enrich Grid with visitors
CALL `carto-un`.carto.ENRICH_GRID(
-- Index type
'h3',
-- Grid query and name of the index column
'''
SELECT h3 FROM `carto-dw-ac-vs5d76ww.shared.madrid_h3_study_area`
''',
'h3',
-- Input query and name of the geometry column
'''
SELECT
patrimony_monuments_museums,
theatre_arts,
gastronomy,
leisure,
geom
FROM `carto-dw-ac-vs5d76ww.shared.madrid_osm_poi_study_area`
''',
'geom',
-- Columns to enrich and aggregation function
[('patrimony_monuments_museums', 'count'),
('theatre_arts', 'count'),
('gastronomy', 'count'),
('leisure', 'count')],
-- Output table
['`carto-dw-ac-vs5d76ww.shared.madrid_h3_study_area_enriched_tourism`']
);
# Enrich Grid with pois
CALL `carto-un`.carto.ENRICH_GRID_RAW(
-- Grid type
'h3',
-- Input query
'''
SELECT h3
FROM `carto-dw-ac-vs5d76ww.shared.madrid_h3_study_area`
''',
-- Input index column
'h3',
-- Data query
'''
SELECT intersected_geom AS geom, True AS placeholder
FROM `carto-dw-ac-vs5d76ww.shared.madrid_osm_tourism_poi_isolines_15min_intersection`
''',
-- Data geography column
'geom',
-- Variables to enrich
['placeholder'],
-- Output table
['`carto-dw-ac-vs5d76ww.shared.madrid_h3_study_area_enriched_15minwalk_tourism_intersection`']
);
# Enrich Grid with 15 min tourism POIs
MAP 4 OF 6
Finding the Sweet Spot: Geographic Weighted Regression
Geographically Weighted Regression (GWR) is a powerful analytical technique used in our analysis to identify ideal hotel sites in Madrid. By considering spatial heterogeneity and capturing localized impacts, GWR helps us understand the spatial correlations between variables and their influence on the suitability of hotel locations.
- GWR accounts for spatial heterogeneity, recognizing that relationships between variables may vary across different locations within Madrid.
- The study area was divided into cells, and a local regression model was created for each cell, incorporating data from neighboring cells within a specified neighborhood.
- Weights were assigned to neighboring cells using the Gaussian kernel function, giving higher weights to closer cells and lower weights to those further away.
- The GWR analysis provided coefficients for each cell, indicating the relationship between the coefficient and the target variable.
- GWR helped us capture the localized impacts of variables and uncover spatial patterns in the suitability of hotel locations in Madrid.
- Factors such as the count of points of interest per category and the percentage of area covered by them were considered in the GWR analysis.
- NH Hotels Group can prioritize locations based on specific factors that have a stronger influence in certain areas, aligning with their target audience and preferences.
- GWR provided valuable insights into spatial correlations, enabling more informed decision-making in hotel site selection.
Geographically Weighted Regression (GWR) play a critical role in our analysis of ideal hotel sites in Madrid. By considering spatial heterogeneity and capturing localized impacts, GWR provide valuable insights into the relationships between variables and their influence on the suitability of hotel locations. This information empowers NH Hotels Group to make more informed decisions in site selection, aligning with their target audience and preferences.
Photo by Carlos Zurita Buccola on Unsplash
CALL `carto-un`.carto.GWR_GRID(
-- Source Spatial Index grid
'carto-dw-ac-vs5d76ww.shared.madrid_h3_study_area_enriched_final_NH',
-- Correlation variables
['patrimony_monuments_museums_count', 'theatre_arts_count', 'gastronomy_count', 'leisure_count', 'covered_percentage', 'NH_distance'],
-- Target variable
'visitors_midhigh_40_69_sum',
-- Index column, index type, k-ring distance, kernel function
'h3', 'h3', 3, 'gaussian', TRUE,
-- Output table
'carto-dw-ac-vs5d76ww.shared.madrid_h3_GWR'
);
# Geographic Weigthed Regression
MAP 4 OF 6
Finding the Sweet Spot: Composite Scores, and Identifying Commercial Hotspots
Our analysis incorporates the use of Composite Scores and Hotspot Detection techniques to identify ideal hotel sites in Madrid. These methodologies allow us to evaluate the relative importance of different factors and pinpoint areas with a higher potential for success.
Composite Score
- Composite Scores are calculated by combining and weighting relevant factors.
- Weights are derived from the 2019 Tourist Perception Survey, reflecting visitor preferences and diverse reasons for visiting Madrid.
- Scaling using the "STANDARD_SCALER" method ensures comparability across variables.
- The resulting composite scores were normalized to the range [0.0, 1.0].
- Composite Scores provide a comprehensive assessment of each potential hotel site, enabling the identification of areas with high development potential.
Hotspot detection
- Hotspot Detection analysis utilizes the COMMERCIAL_HOTSPOTS procedure, leveraging the Getis Ord Gi* statistic.
- Variables influencing desirability, such as visitor demographics, accessibility, cultural attractions, and leisure amenities, are considered.
- Weights are assigned to capture the relative significance of variables in determining hotel site desirability.
- A p-value threshold of 0.05 was set to identify statistically significant hotspots.
- The kring parameter determined the size of the neighborhood considered within the analysis, allowing the detection of spatial clusters.
- Hotspots represent clusters of high-ranking locations where multiple factors align, indicating areas with a higher potential for success.
- Hotspot analysis empowers strategic decision-making by focusing attention and resources on the most promising areas.
Photo by Giuseppe Buccola on Unsplash
CALL `carto-un`.carto.COMMERCIAL_HOTSPOTS(
-- Input table with project and dataset.
'carto-dw-ac-vs5d76ww.shared.madrid_h3_study_area_enriched_final',
-- Output table where the results are stored.
'carto-dw-ac-vs5d76ww.shared.madrid_h3_study_area_commercial_hotspots',
-- Column with H3 indexes.
'h3',
-- Input cell index type, 'h3' in this case.
'h3',
-- Variables for the combined Gi* statistic.
['visitors_midhigh_40_69_sum', 'covered_percentage', 'NH_distance', 'patrimony_monuments_museums_count', 'gastronomy_count', 'leisure_count', 'theatre_arts_count'],
-- Weights for each variable, will be normalized to sum up to 1.
[0.20, 0.20, 0.20, 0.1564, 0.0836, 0.0636, 0.0964],
-- Size of k-ring defining the cell's neighbouring area.
3,
-- Threshold for significance of Gi* value, less than 0.05 in this case.
0.05
);
# Commercial Hostpots
# Composite Index
CALL `carto-un`.carto.CREATE_SPATIAL_COMPOSITE_UNSUPERVISED(
-- Input table
'carto-dw-ac-vs5d76ww.shared.madrid_h3_study_area_enriched_final_NH',
-- Unique index
'h3',
-- Output table name
'carto-dw-ac-vs5d76ww.shared.madrid_h3_study_area_composite_index_NH',
-- Options
'''
{
"scoring_method": "CUSTOM_WEIGHTS",
"weights": {
"visitors_midhigh_40_69_sum": 0.20,
"covered_percentage": 0.20,
"NH_distance": 0.20,
"patrimony_monuments_museums_count": 0.1564,
"gastronomy_count": 0.0836,
"leisure_count": 0.0636,
"theatre_arts_count": 0.0964
},
"scaling": "STANDARD_SCALER",
"return_range": [0.0,1.0]
}
'''
)
MAP 5 OF 6
Mapping Competitors: Analyzing Proximity for Competitive Insights
Geographic proximity and competition analysis are essential for NH Hotels Group to make informed decisions about site selection and market positioning. Two key methodologies used in this analysis are Local Outlier Factor (LOF) and K-nearest neighbors (KNN). Here are the most relevant points for each:
Local Outlier Factor (LOF)
- LOF algorithm detects outliers by measuring the density deviation of a data point compared to its neighbors.
- High LOF scores (>1) indicate outliers, while low scores (<1) suggest a lack of clustering behavior.
- A threshold of 1.2 is used to identify areas of intense hotel competition and clustering.
K-nearest neighbors (KNN)
- KNN algorithm assesses the spatial relationship between hotels, providing insights into distances between existing hotels and the competitors.
- KNN analysis reveals clusters and patterns of hotel distribution, guiding strategic positioning to avoid cannibalization.
- Factors such as accessibility to attractions and amenities are considered in the analysis.
- A k-value of 10 is used to determine the nearest neighbors for each NH Hotel location.
In conclusion, the Local Outlier Factor (LOF) and K-nearest neighbors (KNN) analyses provide NH Hotels Group with valuable insights into the competitive landscape and spatial distribution of hotels in Madrid. By leveraging these methodologies, NH Hotels Group can strategically allocate resources, identify areas of high competition, and make informed decisions about site selection and market positioning. These analyses contribute to NH Hotels Group's ability to optimize their hotel portfolio, minimize cannibalization, and enhance their long-term success and profitability in the competitive hotel industry.
Photo by Braden Collum on Unsplash
CREATE OR REPLACE TABLE `carto-dw-ac-vs5d76ww.shared.madrid_hotels_competitors_NH_LOF_analysis` AS
WITH lof_output AS (
-- LOF calculation, excludes 'NH_%' brand hotels.
SELECT `carto-un`.carto.LOF(ARRAY_AGG(STRUCT(CAST(id AS STRING), geom)), 5) as lof
FROM `carto-dw-ac-vs5d76ww.shared.madrid_hotels_brand_id`
WHERE brand != 'NH_%'
)
SELECT lof.*
FROM lof_output, UNNEST(lof_output.lof) AS lof
-- Filter hotels with LOF score less than 1.2, indicating intense competition.
WHERE lof.lof < 1.2;
# Local Outlier Factor (LOF)
-- Subquery 'hotels': Selects and assigns unique IDs to each hotel within Madrid's study area
WITH hotels AS (
SELECT
CONCAT(brand, '_', CAST(ROW_NUMBER() OVER(PARTITION BY brand ORDER BY name) AS STRING)) AS id,
geom, stars_rating, brand
FROM `carto-dw-ac-vs5d76ww.shared.madrid_hotels_brand`
WHERE ST_WITHIN(geom, (SELECT geom FROM `carto-dw-ac-vs5d76ww.shared.madrid_study_area`))
),
-- Subquery 'knn': Computes the 10 nearest neighbours of each hotel, focusing on NH brand hotels
knn AS (
SELECT
knn.*
FROM UNNEST((SELECT `carto-un`.carto.KNN(ARRAY_AGG(STRUCT(id, geom)), 10) FROM hotels)) AS knn
WHERE geoid LIKE 'NH_%' AND geoid_knn NOT LIKE 'NH_%'
)
-- Main Query: Joins the results of KNN with hotel data, calculates lines between NH hotels and their competitors
SELECT
h.id AS NH_id,
h.geom AS geom,
h.stars_rating AS NH_stars,
o.id AS other_id,
o.geom AS other_geom,
o.stars_rating AS other_stars,
k.distance,
k.knn,
ST_MAKELINE(h.geom, o.geom) AS line_geom
FROM knn k
JOIN hotels h ON k.geoid = h.id
JOIN hotels o ON k.geoid_knn = o.id
ORDER BY NH_id, knn;
# K-Nearest Neighbors (KNN)
MAP 6 OF 6
Building the Future: Strategic Mapping for Hotel Location Selection
Visualizing potential hotel locations is a crucial step in NH Hotels Group's site selection process. It provides a clear understanding of the spatial distribution and market dynamics in Madrid.
- Mapping potential hotel locations considers factors such as commercial hotspots, accessibility, proximity to key attractions, and competition. Integration of these factors helps identify areas with high demand and optimal market conditions.
- The visual representation offers an overview of areas with the highest potential for success and profitability. It identifies clusters of favorable locations, aiding resource allocation and investment prioritization.
- Overlaying commercial hotspots and relevant data refines the site selection process, enabling well-informed decisions. Relationships between hotspots, attractions, transportation networks, and competition can be analyzed for strategic decision-making.
- Visualization aids NH Hotels Group in strategic expansion planning, effective resource allocation, and market positioning. It helps identify untapped areas and prevents over-saturation in specific locations.
- Presenting findings through visualizations facilitates effective communication with stakeholders and decision-makers. It aligns strategic objectives and garners support for site selection decisions.
Visualizing potential hotel locations empowers NH Hotels Group to enhance their site selection process, pursue opportunities aligned with their objectives, and maximize their competitive advantage in Madrid's dynamic hotel market. It ensures informed decisions, a strong market presence, and long-term success in the competitive hotel industry.
Photo by Josefina Di Battista on Unsplash
Value of the analysis for NH Hotels Group
-
Optimizing Location Selection
-
Enhancing Customer Satisfaction
-
Gaining a Competitive Advantage
-
Ensuring Long-term Success
Prefer something pre-built?
Conclusions
The geospatial analysis conducted offers NH Hotels Group valuable insights for optimal site selection, market positioning, and sustainability practices in Madrid's competitive hotel industry.
- Emphasized accessibility
- Leveraged commercial hotspots
- Avoided cannibalization
- Tailored offerings to customer preferences
- Embraced sustainability practices
- Leveraged CARTO's platform
Thanks for listening
Any questions?
Gerardo Ezequiel Martín Carreño
Urban Sustainability expert
Full-stack Spatial Data Scientist
