LogoLogo
HomeDocumentationLoginTry for free
  • CARTO Academy
  • Working with geospatial data
    • Geospatial data: the basics
      • What is location data?
      • Types of location data
      • Changing between types of geographical support
    • Optimizing your data for spatial analysis
    • Introduction to Spatial Indexes
      • Spatial Index support in CARTO
      • Create or enrich an index
      • Work with unique Spatial Index properties
      • Scaling common geoprocessing tasks with Spatial Indexes
      • Using Spatial Indexes for analysis
        • Calculating traffic accident rates
        • Which cell phone towers serve the most people?
    • The modern geospatial analysis stack
      • Spatial data management and analytics with CARTO QGIS Plugin
      • Using data from a REST API for real-time updates
  • Building interactive maps
    • Introduction to CARTO Builder
    • Data sources & map layers
    • Widgets & SQL Parameters
    • AI Agents
    • Data visualization
      • Build a dashboard with styled point locations
      • Style qualitative data using hex color codes
      • Create an animated visualization with time series
      • Visualize administrative regions by defined zoom levels
      • Build a dashboard to understand historic weather events
      • Customize your visualization with tailored-made basemaps
      • Visualize static geometries with attributes varying over time
      • Mapping the precipitation impact of Hurricane Milton with raster data
    • Data analysis
      • Filtering multiple data sources simultaneously with SQL Parameters
      • Generate a dynamic index based on user-defined weighted variables
      • Create a dashboard with user-defined analysis using SQL Parameters
      • Analyzing multiple drive-time catchment areas dynamically
      • Extract insights from your maps with AI Agents
    • Sharing and collaborating
      • Dynamically control your maps using URL parameters
      • Embedding maps in BI platforms
    • Solving geospatial use-cases
      • Build a store performance monitoring dashboard for retail stores in the USA
      • Analyzing Airbnb ratings in Los Angeles
      • Assessing the damages of La Palma Volcano
    • CARTO Map Gallery
  • Creating workflows
    • Introduction to CARTO Workflows
    • Step-by-step tutorials
      • Creating a composite score for fire risk
      • Spatial Scoring: Measuring merchant attractiveness and performance
      • Using crime data & spatial analysis to assess home insurance risk
      • Identify the best billboards and stores for a multi-channel product launch campaign
      • Estimate the population covered by LTE cells
      • A no-code approach to optimizing OOH advertising locations
      • Optimizing site selection for EV charging stations
      • How to optimize location planning for wind turbines
      • Calculate population living around top retail locations
      • Identifying customers potentially affected by an active fire in California
      • Finding stores in areas with weather risks
      • How to run scalable routing analysis the easy way
      • Geomarketing techniques for targeting sportswear consumers
      • How to use GenAI to optimize your spatial analysis
      • Analyzing origin and destination patterns
      • Understanding accident hotspots
      • Real-Time Flood Claims Analysis
      • Train a classification model to estimate customer churn
      • Space-time anomaly detection for real-time portfolio management
      • Identify buildings in areas with a deficit of cell network antennas
    • Workflow templates
      • Data Preparation
      • Data Enrichment
      • Spatial Indexes
      • Spatial Analysis
      • Generating new spatial data
      • Statistics
      • Retail and CPG
      • Telco
      • Insurance
      • Out Of Home Advertising
      • BigQuery ML
      • Snowflake ML
  • Advanced spatial analytics
    • Introduction to the Analytics Toolbox
    • Spatial Analytics for BigQuery
      • Step-by-step tutorials
        • How to create a composite score with your spatial data
        • Space-time hotspot analysis: Identifying traffic accident hotspots
        • Spacetime hotspot classification: Understanding collision patterns
        • Time series clustering: Identifying areas with similar traffic accident patterns
        • Detecting space-time anomalous regions to improve real estate portfolio management (quick start)
        • Detecting space-time anomalous regions to improve real estate portfolio management
        • Computing the spatial autocorrelation of POIs locations in Berlin
        • Identifying amenity hotspots in Stockholm
        • Applying GWR to understand Airbnb listings prices
        • Analyzing signal coverage with line-of-sight calculation and path loss estimation
        • Generating trade areas based on drive/walk-time isolines
        • Geocoding your address data
        • Find similar locations based on their trade areas
        • Calculating market penetration in CPG with merchant universe matching
        • Measuring merchant attractiveness and performance in CPG with spatial scores
        • Segmenting CPG merchants using trade areas characteristics
        • Store cannibalization: quantifying the effect of opening new stores on your existing network
        • Find Twin Areas of top-performing stores
        • Opening a new Pizza Hut location in Honolulu
        • An H3 grid of Starbucks locations and simple cannibalization analysis
        • Data enrichment using the Data Observatory
        • New police stations based on Chicago crime location clusters
        • Interpolating elevation along a road using kriging
        • Analyzing weather stations coverage using a Voronoi diagram
        • A NYC subway connection graph using Delaunay triangulation
        • Computing US airport connections and route interpolations
        • Identifying earthquake-prone areas in the state of California
        • Bikeshare stations within a San Francisco buffer
        • Census areas in the UK within tiles of multiple resolutions
        • Creating simple tilesets
        • Creating spatial index tilesets
        • Creating aggregation tilesets
        • Using raster and vector data to calculate total rooftop PV potential in the US
        • Using the routing module
      • About Analytics Toolbox regions
    • Spatial Analytics for Snowflake
      • Step-by-step tutorials
        • How to create a composite score with your spatial data
        • Space-time hotspot analysis: Identifying traffic accident hotspots
        • Computing the spatial autocorrelation of POIs locations in Berlin
        • Identifying amenity hotspots in Stockholm
        • Applying GWR to understand Airbnb listings prices
        • Opening a new Pizza Hut location in Honolulu
        • Generating trade areas based on drive/walk-time isolines
        • Geocoding your address data
        • Creating spatial index tilesets
        • A Quadkey grid of stores locations and simple cannibalization analysis
        • Minkowski distance to perform cannibalization analysis
        • Computing US airport connections and route interpolations
        • New supplier offices based on store locations clusters
        • Analyzing store location coverage using a Voronoi diagram
        • Enrichment of catchment areas for store characterization
        • Data enrichment using the Data Observatory
    • Spatial Analytics for Redshift
      • Step-by-step tutorials
        • Generating trade areas based on drive/walk-time isolines
        • Geocoding your address data
        • Creating spatial index tilesets
Powered by GitBook
On this page
  • You will need...
  • Step 1: Sourcing & loading crime data
  • Step 2: Creating a crime grid
  • Step 3: Contextualizing Crime Risk
  • Next steps...

Was this helpful?

Export as PDF
  1. Creating workflows
  2. Step-by-step tutorials

Using crime data & spatial analysis to assess home insurance risk

Last updated 10 months ago

Was this helpful?

In this tutorial, we'll be using individual crime location data to create a crime risk index. This analysis is really helpful for insurers looking to make more intelligent policy decisions - from customized pricing of premiums to tailored marketing.

You will need...


Step 1: Sourcing & loading crime data

  1. Select Import data, then follow the steps to import the table.

  2. For this dataset we are going to deselect the Let CARTO automatically define the schema option on Schema Preview so we can manually select the correct data types for each field. In this example, you want to be sure that latitude and longitude are defined as the type float64.


Step 2: Creating a crime grid

  1. With the data downloaded, head to the Workflows tab of the CARTO Workspace and select + New Workflow. Use the connection relevant to the location you loaded your data to. Select Create Workflow.

  2. At the top left of the screen, click on the word Untitled to rename your workflow Crime risk.

  3. You should now be seeing a blank Workflows canvas. The first thing we need to do is load our crime data in. To the left of the window, open the Sources tab. Navigate through Connection data to the LA crime locations table you just imported, and drag it onto the canvas.

  4. If you navigate through the Table preview (bottom of the window) you'll notice we don't have a geometry column. Let's change that! Switch from the Sources to Components window, and search for ST GeogPoint; we'll use this to create a point geometry for each crime. Drag this component onto the canvas to the right of the crimes source.

  5. Connect the right-hand node of the crimes table to the input, left-hand node os ST GeogPoint (this may happen automatically if they're placed close together. Set the latitude and longitude columns as lat and lon respectively - and run the workflow!

At the bottom of the window, select ST GeogPoint and open the Table preview again. Scroll right to the end and select Show Column Stats.

Notice anything weird? The minimum latitude and maximum longitude values are both 0 - which means we have a series of features which are incorrectly sitting in "null island" i.e. longitude, latitude = 0,0. These will skew our subsequent analysis, so let's remove them.

  1. Back in Components, find Simple Filter. Drag this onto the canvas, connecting it to the output of ST Geogpoint. Set the filter condition to latitude does not equal 0, and run. Now let's get on with running our analysis.

  2. Now, let's also filter the data to only crimes relevant to home insurance risk. Connect the Simple Filter to a Select Distinct component, looking at the column crm_cd_desc. You can see there are over 130 unique crime codes which we need to filter down.

  3. For this filter, as we will have multiple criteria we will instead need to connect a Where component to the Simple Filter from step 5. In this Where component, copy and paste the following:

crm_cd_desc LIKE '%BURGULARY%' OR 
crm_cd_desc LIKE '%THEFT%' OR 
crm_cd_desc LIKE '%VANDALISM%' OR 
crm_cd_desc LIKE '%STOLEN%' OR 
crm_cd_desc LIKE '%ARSON%'
  1. Connect your Simple Filter to a H3 from GeoPoint component, which we'll use to convert each crime to a hexagonal H3 grid cell. Change the resolution to 9 which is slightly more detailed than the default 8.

  2. In the final step for this section, connect the H3 from GeoPoint component to a Group by component. Set the column as H3 and the aggregation as H3 again, with the type COUNT. This will count all duplicate cells, turning our H3 grid into a frequency grid.


Step 3: Contextualizing Crime Risk

In this section, we will contextualize the crime counts by calculating the number of crimes per 1,000 residents. First, we need to convert our population data into a H3 Index so we can use it in the same calculation as the crime count.

You can follow the steps in the video below to do this (also outlined below).

  1. If you haven't already, head to the Data Observatory and subscribe to Population Mosaics, 2020 - United States of America (Grid 100m).

  2. In your workflow, head to Sources > Data Observatory > WorldPop and drag the gridded population data onto your canvas. You may need to refresh your workflow if you subscribed to the dataset since you started building.

    1. Group by column: H3

    2. Aggregation column: Population, Aggregation type: Sum.

Altogether, this should look something like the below (note how we've used an annotation box to help organize or workflow - you can access these in the Aa button at the top of the window).

Now you should have two inputs ready; crime counts and population. Let's bring them together!

CASE WHEN population_sum_joined = 0 then 0 ELSE h3_count/(population_sum_joined/1000) END

Altogether, your workflow should be looking something like...

  1. Head back to the Builder map you created earlier. Under Sources (bottom left), select Add Source from > Data Explorer and navigate to where you saved your table. Add it to the map!

  2. Rename the layer Crime rate.

  3. Let's style both the Crime count and rate layers in turn by clicking on the layer name in the Layer panel:

    1. Reduce the resolutions of both to 6 (as detailed as possible)

    2. Disable the strokes

    3. Change the fill colors to be determined by H3_Count (average) for the crime count layer, and crime_rate (average) for the crime rate layer. Pro tip - use different color schemes for both layers, so it's obvious to the user that they aren't directly comparable.

  4. At the top of the Builder window in Map views, turn on Switch to a dual map view. Open the legends for each map respectively (at the bottom right of each window) and turn the Crime rates off for the left-hand map and Crime counts off for the right-hand map (or the other way around! You basically only want to see one grid in each map).

Check this out below! How do the two compare?


Next steps...

Want to take this analysis one step further? Here are some ideas for next steps:

Crime location data. We are using data for Los Angeles city (data available ). Most local governments provide this data as open data, so you should be able to easily adapt this tutorial for your area of interest.

Detailed Population data. We are using 100m gridded data, which you can subscribe to via our Spatial Data Catalog.

We’ll be basing our analysis on crime data for Los Angeles city (data available ).

First, let's load this data into your data warehouse. To do this, head to the tab of your CARTO Workspace:

Now the data is loaded into our data warehouse, we’ll be building the below workflow to convert the crime locations into a hexagonal called . This process can be used to convert any point dataset into a H3 Index.

You can now select the Group by component, open the Map preview tab and select Create Map to start exploring your data - here's what ours looks like! Make sure you check out the section of the academy for tutorials on crafting the most impactful maps!

Connect this to an to convert each grid cell to a central point.

Now, we will use a similar approach to when we converted the crime points to a H3 index. Use to convert each point geometry to a H3 index; make sure you set the resolution to 9 (the same as the crime count layer).

Finally, use the component to aggregate the index with the following parameters:

Add a component, with the Group by component from the previous step as the top (main) input, and and crime count Group by table as the bottom input. Use an inner join a type with the columns from both tables as h3.

Finally, we can calculate the crime rate! Add a component to do this and input the below formula.

Use a component to commit it.

Calculate crime rate hotspots and outliers with our tools

Assess property-level home risk by joining your results to property data, such as

Learn more about this process in our blog .

here
WorldPop
here
Data Explorer
Spatial Index
H3
Building Interactive Maps
ST Centroid
H3 from GeoPoint
Group by
Join
Create Column
Save as Table
Statistics
Overture Maps buildings
Using crime data & spatial analysis to assess home insurance risk
Converting population data to a H3 Index
Building-level crime rates
Intermediate difficulty banner
A screenshot of CARTO Workflows
A screenshot of CARTO Workflows
A screenshot of CARTO Workflows