February 2024 Summaries

Snowflake has partnered with industry leader CARTO to bring native support for H3 Spatial Indexing, accelerating geospatial analysis and visualization. H3 is a Discrete Geographical Grid that indexes the world into a grid at multiple resolutions, making spatial data smaller and faster to process. The integration allows users to leverage 19 H3-specific SQL functions in Snowflake, enabling advanced features such as manipulating H3 data, generating areas of influence, and enriching grids with external data. Additionally, CARTO's low-code tool Workflows offers pre-built components that utilize the H3 functions, while the mapping platform CARTO Builder allows for native visualization of H3 data. A new H3 Acceleration Programme is also available to help users become proficient in using H3 and accelerate adoption across their organization.

This summary highlights the use of spatial priority scores to improve network access for senior citizens, leveraging location intelligence and CARTO Workflows. The approach involves three phases: creating an eligibility model through regression analysis, generating a priority score using spatial composite methods, and creating AI-generated descriptive labels. By combining datasets such as telco data, building height, and human mobility data, the analysis aims to identify areas with higher senior populations, lower network speeds, and increased urbanity, requiring enhanced network connectivity services. The resulting priority score maps showcase regions warranting immediate attention, and the methodologies employed demonstrate CARTO's spatial analysis capabilities, including regression models, composite score generation, and AI-driven insights.

Balancing territories for equity and efficiency is crucial in sales management, as maintaining optimal balance between profitability and minimizing costs can lead to unhappy employees or lost revenue. The use of spatial analytics and data science can help optimize territories based on company priorities, such as reducing travel time and increasing productivity. A baseline solution using nearest neighbor analysis assigns accounts to the rep who lives closest to each medical center, but this may not meet the goal of evenly valued territories. Two solutions were explored: Minimum Cost Flow algorithm and Genetic Algorithms. The Minimum Cost Flow algorithm reduces standard deviation of sales value by 17%, while the Genetic Algorithm reduces it by 50% with only a 3% increase in total distance travelled, creating more equitable and efficient territories. By using data-driven territory management, sales managers can reduce inefficiencies, costs, and achieve their sales goals.

Geospatial data processing can be challenging, especially when dealing with large datasets. While PostgreSQL and PostGIS are popular choices for geospatial analysis, they may not perform well on huge amounts of data. In contrast, databases like CitusDB, Greenplum, Amazon Redshift, MapD, ClickHouse, Vertica, and Druiddb offer better performance but often with limited support for geospatial functionality. The author tested ClickHouse with the NYC Taxi dataset and found impressive results, achieving a 10x improvement in query performance by creating a new table with a quadkey column as an index. Quadkey is a way to encode lat-lon pairs into integers, allowing for efficient querying of large datasets.

Retailers are struggling to make informed decisions about store locations due to outdated "four-wall economics" approach, but spatial data models can help. By analyzing location data and consumer behavior insights, retailers can identify underperforming markets and optimize their store network. A spatial data model was built using publicly available data on Target's store locations, CBSA areas, census block group boundaries, and socio-demographic measures. The model identified Nashville as a potential market for expansion, but with low addressable population and limited suitable sites. In contrast, Houston had a higher addressable population and more dispersed High-High locations, making it the most suitable choice for Target's expansion. Further analysis could involve integrating additional data sources to gain a more granular view of performance in each market area.

The text discusses the integration of new data streams and spatial analysis to improve road safety in New York City, focusing on the use of GPS and mobile event data from applications like TomTom and Waze. By examining NYC Open Data, specifically NYPD motor vehicle collision records, analysts can identify patterns in collision occurrences, such as time and location, and contributing factors like driver distraction. Spatial data science methodologies, including spatial autocorrelation and decision trees, help to pinpoint collision hotspots and common features of crash-prone streets, such as the number of parking and travel lanes. These analyses reveal that collisions are more frequent during early morning hours on weekends and that motorists are more often injured in crashes outside of traffic jams, while pedestrians are more vulnerable during jams. The findings emphasize the importance of leveraging both traditional and new data sources to inform city planners and transportation agencies on how to enhance road safety and manage traffic more effectively.

The US faces significant health inequity issues, particularly in pharmacy accessibility. A 2017 study found that while 90% of Americans filled prescription orders at community pharmacies within 2 miles, there is substantial variation in accessibility across the country. Retail chains dominate the market, contributing to the decline of independent pharmacies, which often serve as the sole pharmacy for local communities. Pharmacy deserts, where access to pharmacies is limited, can exacerbate health issues like the opioid epidemic. Spatial analysis and data visualizations can help identify areas of influence, such as travel impedance, and inform patient behaviors and decision-making processes. An Area of Influence (AoI) analysis in New York City identified independent pharmacies as a critical component of addressing pharmacy deserts, providing accommodations like home delivery services and meeting the needs of local communities. Ensuring pharmacy accessibility is crucial to confronting the opioid epidemic, particularly in areas with high rates of overdose-related deaths, such as Chicago's South and West sides. By leveraging location data and spatial analysis, healthcare professionals can develop targeted strategies to address these issues and promote health equity.

In 2018, data visualization is expected to continue growing in importance as organizations seek to derive greater insights from their location data. To achieve this, tools and resources geared towards making data visualization more accessible to everyone will become increasingly popular, including online platforms like DataVizProject.com and Google's Data Studio. The use of open and private data sources will also increase, with new marketplaces and data exchanges emerging to provide more opportunities for data scientists and visualizers. Artificial intelligence and machine learning will play a key role in enhancing data visualization, while interactive maps will become a standard medium for data visualizations. Additionally, there is a growing focus on "data stories" that tell a complete story using multiple types of data and visualization methods, as well as the use of new color schemes and palettes designed to be accessible to people with visual impairments. Finally, data visualizations around current events will continue to dominate social conversations, while journalists are striking back with data visualizations to counter the label of "fake news".

A growing number of online-native retailers aim to expand into physical stores, seeking a balance between online and offline engagement. To achieve this, they must analyze their sales data using geospatial analytics to identify the most profitable locations for brick-and-mortar expansion. By clustering data around retail zones in cities like New York and San Francisco, retailers can pinpoint areas with high demand for their products. The analysis also involves assigning predicted values to each cluster based on online sales data and creating models to estimate future sales in new markets. This Location Intelligent approach enables retailers to make informed decisions about site planning, mitigate risks, and increase the chances of success in their brick-and-mortar expansion efforts.

A spatial interaction model is used to map and model the interactivity between various factors in distinct locations, making it useful for understanding data with multiple location components. This technique can be applied to various fields such as retail, commercial real estate, public transit, city planning, and conservation efforts. By using a spatial interaction model, businesses and organizations can gain insights into how attractive a particular new location is, predict the flow between new locations, and understand the impact of their decisions on their customers or target audience. The models can also be used to assess the expansion of public transit, evaluate the effectiveness of conservation efforts, and inform decision-making in various sectors.

Introducing Workflows Templates: pre-built spatial analysis tools are gaining momentum to empower users to automate processes without specialized coding skills, democratizing spatial analysis and making it accessible to a broader audience compared to high-code alternatives. No-code tools like CARTO Workflows allow users to utilize a visual, drag-and-drop interface to construct analytical algorithms intuitively. The gallery of Workflows templates provides pre-loaded analytical processes that can be used with any connected cloud data warehouse, allowing users to load and customize them without needing to build from scratch. These templates cover a wide range of scenarios, from simple building blocks for geospatial analysis to more complex industry-specific workflows, designed to encourage users of all levels to create their own workflows and experiment with different components. Users can now access Workflow templates directly inside their CARTO Workspace and share their own templates by exporting them as SQL files, making it easy to collaborate without code.

The partnership between CARTO and Markerr enables professionals in various fields, including real estate investment and urban planning, to access comprehensive property data for enhanced market analysis. This data allows users to turn raw data into structured market and property data, providing valuable insights for strategic decision-making. With the integration of Markerr's datasets via CARTO's Data Observatory, users can leverage high-quality datasets such as RealRent Single-Family, Property attributes, and Property transactions to inform their business decisions and explore housing affordability across Metropolitan Statistical Areas.

The CARTO team has launched Airship, a new front-end library for Location Intelligence apps, aiming to empower developers with tools to build custom applications and solutions. With minimal coding requirements, designers and developers can generate styles, interactive elements, typography, and more design elements optimized specifically for location applications. The library consists of independent and reusable components built with Atomic Design in mind, including the Donut Chart, Gauge Chart, Histogram, Stacked Bar Chart, Flags, Legends, Popups, Buttons, Switches, and Selectors. Airship's goal is to make it easier for developers to integrate beautifully designed components into their Location Intelligence applications, improving end-users' experiences.

Mapnik and PostGIS are both used for rendering map tiles, but they differ in how they generate these tiles. Mapnik uses a combination of Postgres and PostGIS to render tiles, whereas PostGIS's ST_AsMVT function allows users to create full tiles from SQL queries directly in the database. A benchmark was conducted to compare the execution times of both methods, with the results showing that rendering tiles straight from the database using ST_AsMVT is significantly faster than using Mapnik, especially for large datasets. However, the difference in speed comes at the cost of additional complexity and potential overhead due to data transfer and conversion between internal representations. The use of MVTs as a building block in the database offers flexibility and can potentially offload CPU-intensive encoding operations from the tiler.

The CARTO team is optimizing PostGIS performance, focusing on reducing database query execution time to improve map rendering speed. They've identified two key areas for improvement: Mapnik rendering and MVT (Vector Tile) rendering. To optimize Mapnik rendering, they've modified the `ST_AsTWKB()` function to minimize memory allocations for small objects like points, and changed the implementation of `ST_Simplify()` and `ST_RemoveRepeatedPoints()` functions to reduce copying data around. For MVT rendering, they've made changes to the `ST_AsMVTGeom()` function to avoid unnecessary copying and polygon validity checks, which resulted in significant performance gains. These optimizations aim to improve map rendering speed while maintaining performance for other use cases.

CARTOframes is a Python package that allows users to interact with CARTO, a geographic information system (GIS) platform, for data analysis and visualization. The package was created by Andy Eschbacher and Jeff Ferzoco as an internal need on their team, who wanted to use CARTO more efficiently within their data science workflows. The package has gained popularity among customers, including one who used it to process terabytes of daily data, and has been updated with features such as CARTO VL styling language, vector maps, and improved data import and export functionality. Future development plans include full feature parity with CARTO VL, integration with premium data offerings, and a more robust analysis toolkit. Users have praised the package for its ease of use, flexibility, and ability to visualize data in real-time.

CARTO VL is a JavaScript library for vector-based visualization and analytics, enabling faster and smarter decision-making with Location Intelligence applications. It leverages the entire CARTO platform with new high-performance cartographic capabilities, smart aggregations, access to Carto's Data Observatory, and advanced spatial analytics. CARTO VL helps surface more patterns and insights by enabling greater interaction with underlying information, allowing users to quickly create filters, pop-ups, graphs, and widgets on top of their data. The library has powerful integrations and components, is integrated with Mapbox GL for beautiful high-quality basemaps, and supports a smarter backend for analyzing large datasets. It also unleashes the full potential behind WebGL, offering state-of-the-art styling options, including dynamic data-oriented styling, multi-scale thematic mapping, and support for temporal dimensions. CARTO VL is developer-friendly, integrates with various tools, and offers seamless interaction animation and performance.

The authors of the article discuss their design decisions and iterations to improve the performance of a GIS system using Clickhouse as the database. They explain how they used the quadtree keys or quadkey system to divide the map into cache-friendly tiles, which reduced the number of read rows from 387M to just 2M. To further optimize this process, they employed sampling, which allowed them to reduce the reads from disk and improve performance. Additionally, they implemented a sampling-based approach for aggregating data, which enabled them to calculate histograms and other widgets more efficiently. By making these optimizations, they were able to significantly improve the system's performance, reducing response times by up to 80% in some cases.

For retailers, having a clear data-driven picture of store health is crucial for identifying macro-level trends and making informed decisions. Traditional methodologies relying on sales figures and static demographics are no longer effective and can leave analysts behind. Modern location data streams provide a more detailed and up-to-date view of consumer behavior, enabling site monitoring processes to be more accurate and complete. Integrating mobile data into site-planning workflows, tracking patterns with spend insights, and using geospatial analysis can empower store managers, analysts, and executives by providing a clearer picture of site health at all scales, ultimately helping retailers make informed decisions and stay ahead in the competitive retail landscape.

Introducing CARTOframes: A Python Interface for CARTO` CARTOframes is a Python package that enables data scientists to easily integrate CARTO into their workflows, creating interactive or static maps and performing spatial analysis using CARTO's cloud-based PostGIS database. It allows users to read from a CARTO dataset into a pandas DataFrame and send DataFrames back to CARTO as tables, interacting with the Data Observatory for measures of interest. With its focus on data science community standards like Jupyter notebooks, pandas, matplotlib, and SQL/PostGIS, CARTOframes ties in seamlessly with the wider Python ecosystem, making it easy to communicate analyses and reproducible workflows.

The text provides a comprehensive guide on how to use CARTO.js with React to create a map application that displays geospatial data from the CARTO platform. The application demonstrates basic concepts of CARTO.js, including creating a client, layers, and dataviews, as well as integrating it with React components. It showcases how to display a histogram widget that updates in real-time as the user zooms or pans the map, using filters to only show data within the visible area. The application also includes a layer component that can be customized with different styles and colors based on the histogram data. The guide provides detailed explanations of key concepts, including CartoCSS, carto.Source, carto.Style, carto.Client, carto.dataview.Formula, carto.layer.Layer, carto.filter.BoundingBoxLeaflet, and carto.filter.BoundingSphereLeaflet.

PostgreSQL's foreign data wrappers (FDW) allow developers to expose external sources of data as tables inside PostgreSQL, enabling queries to be evaluated against both local and remote data sources. CARTO leverages FDW in two main ways: postgres_fdw for linking data between remote databases and users' Carto databases, and odbc_fdw for exposing operational database data to Carto instances. By utilizing the full-featured postgres_fdw implementation, CARTO achieves faster data access without replicating data from one database to another. To support spatial queries, CARTO extended postgres_fdw to include PostGIS geometry types and functions, enabling remote data access and generalizing their enhancements for use with any PostgreSQL extended type. Additionally, odbc_fdw enables schedule live data syncs between Carto instances and remote databases, supporting connections to various data sources such as MySQL, PostgreSQL, SQL Server, and Apache Hive.

The Postgis team updated their MVT encoder technology, switching from node-mapnik to St_AsMVT in Postgis 2.5, to improve performance, reduce network load, and better support scalability. The transition was made to take advantage of the increased resources available on database servers, which allows for faster tile generation and reduces friction between users sharing resources. Despite some differences in behavior with invalid geometries and simplification, St_AsMVT has shown significant improvements in performance, especially for line and polygon geometries, while Postgis excels at discarding small polygons and handling attribute encoding. The update also highlights the importance of considering geometry size and property associations when generating vector tiles, as well as optimizing requests to reduce unnecessary columns and improving performance through point aggregations.

The shapefile format has limitations such as size restrictions, limited data types, and lack of support for certain spatial types. Esri's File Geodatabase (FGDB) format is considered a replacement, offering improvements like file size flexibility and rich data types, but it lacks open-source support and requires proprietary Esri APIs. The GeoPackage (GPKG) format, developed by the Open Geospatial Consortium (OGC), addresses these issues with an open-source SQLite storage engine, platform independence, and extensibility through documentation and extensions.

Happy PostGIS day! At CARTO we love PostGIS and its fully-fledged geospatial analytics engine, which helps us build tools to understand geospatial data through Location Intelligence. We've added support for CARTO in Franchise, a SQL tool with a notebook interface, allowing users to explore, visualize, and style their geospatial analysis against PostGIS using CARTO. The integration uses CARTO's SQL and Maps APIs to leverage the power of CARTO Engine, providing a fully functional PostGIS SQL console and CartoCSS panel embedded in Franchise. This example showcases how to integrate CARTO.js as a React component, offering a simple way for users to get started with CARTO in their own applications. With this integration, users can now easily explore and analyze geospatial data using a SQL notebook interface, making it easier to gain insights from Location Intelligence.

CARTO Workflows is a no-code tool that enables the integration of analysis workflows into cloud-native applications, allowing users to automate spatial analysis and trigger workflow execution from external applications. The platform provides a user-friendly interface for designing and automating workflows, which can be productionized and integrated as key processes within applications. CARTO Workflows integrates with various data warehouses, including BigQuery, Snowflake, Redshift, and PostgreSQL, and allows users to define global variables that can be used as input values for component settings. The platform also enables the execution of workflows via an API call, which can be triggered from external applications, and provides features such as caching and query status monitoring. CARTO Workflows has been designed to ease the integration of asynchronous analytical processes in front-end applications, making it easier for users to embed analytical procedures into custom applications.

The CARTO Core Team has embarked on a project to improve the performance of their core infrastructure, aiming for a 5x increase in velocity. They have been focusing on optimizing key components such as PostGIS, Mapnik, and PostgreSQL. By monitoring system metrics and running micro-benchmarks, they identified low-hanging fruit opportunities, including parallelism improvements, caching features, and memory management fixes. While they did not achieve the exact 5x goal in end-to-end benchmarks, they made significant progress across various projects, resulting in gratifying performance improvements and introducing new functionality, such as easier spatial test harnesses and support for PostgreSQL version 11. The team's efforts demonstrate that ongoing optimization and innovation can lead to substantial benefits for users.

Mapbox Vector Tiles (MVTs) are being integrated into CARTO Engine, enabling users to leverage these technologies directly from the platform's APIs. This integration provides several advantages over traditional raster tiles, including styling capabilities, efficiency, interactivity, and WebGL rendering. With MVTs, users can create dynamic maps that analyze and visualize data in real-time without pre-rendering, reducing complexity and eliminating the need for additional software or extra work. CARTO takes care of optimizing tile size and performance, ensuring a great user experience regardless of dataset size or complexity. This integration opens up new possibilities for location applications and is just the first step in more vector support across the platform.

Measuring tile rendering processes to improve performance` Our infrastructure's tile generation process consists of two main steps: gathering required data from the database filtered by tile bounding box, and sending this data to the renderer to generate the tile. The process has a wide improvement margin for data gathering due to its time and resource consumption compared to actual tile rendering. To better understand and measure the process, we gathered PostgreSQL logs using a custom parser to extract relevant information such as query execution times, XYZ coordinates of requested tiles, and affected tables. We also processed inconsistencies in log line formatting, merged queries that were split across multiple lines, and aggregated prepared statement execution times. The resulting data allowed us to identify query runtimes and how many times the same tile is generated, providing valuable insights to improve the process.

CARTO VL is a Javascript library for vector-based visualization inside Location Intelligence applications, providing client-side rendering of large datasets, improved performance, and enhanced capabilities for dynamic analysis and user interaction. With CARTO VL, users can interact with and manipulate location data in the browser, enabling faster rendering speeds, responsive data-driven visualizations, and dynamic user interactions. The library offers freedom to render tiles from anywhere, supporting multiple client-side rendering libraries, and is designed to work with existing technology and needs. By leveraging vector technology, CARTO VL aims to revolutionize Location Intelligence applications by providing a more performant, reliable, and controlled experience for users.

The author of the text, a CARTO user, was facing challenges with parallel query support in PostgreSQL 10 and PostGIS 2.4 for spatial queries, which were not optimized to take advantage of parallel processing. To improve performance, the authors had to tweak both packages, including modifying the PostgreSQL planner to generate more aggressive parallel plans with PostGIS functions and adding higher costs to PostGIS functions to capture their high computational complexity. However, this process was not without its challenges, as some spatial functions were modified to be compatible with parallel processing, but others required adjustments to maintain compatibility with existing inlining behavior. The authors ultimately applied patches to their PostgreSQL 10 and PostGIS 2.4 forks to achieve the desired performance improvements.