Enhancing Market Analysis with Ray-Casting: Pinpointing Customer Locations Inside Geographical Regions

In a business context, we often need to work with geographical coordinates (latitude and longitude) and define different regions, distances, and other related information.

The Problem

In my personal experience, I have found myself needing a way to determine in which of my predetermined regions a new point would be located.

This problem could be broken down to finding whether a point is inside or outside a polygon, defined by the edges that connect all of those points to make a closed area.

Pinpointing a location inside a region (Source: StackOverflow)

Problem definition (Source: GeeksForGeeks)

Solution

I did some research and found out about an algorithm called “Ray-Casting”. The idea of the algorithm is pretty simple: Draw a virtual ray from anywhere outside the polygon to your point and count how often it hits a side of the polygon. If the number of hits is even, it’s outside of the polygon, if it’s odd, it’s inside.

Ray-Casting algorithm demonstration (Source: StackOverflow)

I have found many implementations of this algorithm in different programming languages, but since most of our team and also our clients were not experienced programmers, but rather heavy users of spreadsheets, I had to find a way to more usable for them.

Here is where I introduce a feature in Google Sheets called Apps Script. Google Apps Script is a powerful scripting language that allows you to automate tasks and extend the functionality of Google Sheets. It helps you to automate repetitive tasks, manipulate data, integrate with other Google Services, and, in our case, create custom functions that do complex calculations.

You can access Apps Script by going to Extensions -> Apps Script. It is based on JavaScript, so it uses JavaScript syntax and conventions. It includes standard JavaScript features like variables, loops, functions, and objects. So, the implementation of the Ray-Casting algorithm can be made by creating a function.

function isPointInPolygon(point, polygon) {
  var x = point.lat, y = point.lng;

  var inside = false;
  for (var i = 0, j = polygon.length - 1; i < polygon.length; j = i++) {
    var xi = polygon[i].lat, yi = polygon[i].lng;
    var xj = polygon[j].lat, yj = polygon[j].lng;

    var intersect = ((yi > y) != (yj > y)) &&
                    (x < (xj - xi) * (y - yi) / (yj - yi) + xi);
    if (intersect) inside = !inside;
  }

  return inside;

This function implements the Ray-Casting algorithm by receiving two objects — point and polygon — whose properties are lat and lng, and then it returns TRUE or FALSE.

Then, we need to define these two objects. We would like our function to be flexible enough for someone to choose the cells in which the polygon and point information are, so we create another function that receives these matrices as arguments and call our isPointInPolygon function.

function CONTAINSLATLONG(polygonCoordsMatrix, pointCoords) {
// Get the active spreadsheet and sheet
  var polygonCoords = [];
  for (var i = 0; i < polygonCoordsMatrix.length; i++) {
    if (polygonCoordsMatrix[i][0] !== "" && polygonCoordsMatrix[i][1] !== "") {
      polygonCoords.push({
        lat: parseFloat(polygonCoordsMatrix[i][0]),
        lng: parseFloat(polygonCoordsMatrix[i][1])
      });
    }
  }

  var point = {lat: pointCoords[0][0], lng: pointCoords[0][1]};

  return isPointInPolygon(point, polygonCoords);
}

The for loop is used to get the values in each selected cell for the first matrix and attribute properties lat and lng that define the vertices of the polygon. After that, we do the same to the cells of the point matrix. Finally, we call our Ray-Casting algorithm function.

Now you can use the function CONTAINSLATLONG in your spreadsheet and select the coordinates that form the polygon vertices and the points you want to test. The results can be seen in the images below.

Spreadsheet demonstration (Source: Author)

Demonstration for Polygon and inside point (Source: ScribbleMaps)

Demonstration for Polygon and outside point (Source: ScribbleMaps)

Conclusion

Applying the Ray-Casting algorithm within Google Sheets as a custom function offers a powerful solution for businesses in location-based assessments, particularly when determining whether customer locations fall within specific regions.

This not only enhances efficiency but also promotes collaboration among team members, facilitating data-driven insights and informed decision-making.

Furthermore, the versatility of Google Sheets allows for seamless customization and scalability, enabling businesses to tailor the algorithm to suit their specific needs and adapt it as their requirements evolves. Whether assessing sales territories, service coverage areas, or delivery zones, the Ray-Casting algorithm provides a reliable and efficient method for spatial analysis.