Analysing pipelines is challenging at the best of times, you obviously want to have the best data to influence your decision when it comes to spending a lot of time and money in investing in something like becoming a Virtual Network Operator in India.

To get up to speed on the licensing reasons behind why this is important read my previous post about India Telecoms Regulations. If you're happy to pretend you understand, then crack on!

The Problem

Having incomplete or inaccurate data is commonplace when dealing with sales pipeline, it only improves as it gets closer to winning BUT the more you know earlier on, the better decisions you can make. The eternal struggle.

Information on offices or site locations isn't generally readily available on the web, sure, Contact Us pages are the first stop, but they won't include EVERY office or site the company operates.

Nor is there information on employee count broken down in any useful way when looking at forecasting per-user SaaS products.

But that shouldn't mean we do nothing.

Even if it's just a heatmap of known locations across India for target market customers, that's the sort of thing that can be invaluable in influencing the decisions.

To do that we need to figure out "which Service Area does an address fall within".

Service Areas

The most recent Unified Licence and UL VNO of March 2024, both contain a table of the Telecoms Circles and Metro Areas that apply to the Service Area for the "Access Service" category.

Unfortunately it's simply not accurate enough. There is no mention of Telangana State for a start. So additional source such as the Department of Telecommunications website, and Wikipedia helped paint a fuller picture.

Despite the inaccuracy above, some of those are actually easy since the State or Union Territories (or combination of them) IS the Service Area, but others are a little more challenging since they either combine districts, or town, or worse, say 'areas covered by telephone exchanges' which is much harder to find authoritative mapping data on.

Service Areas for "Access Service Category B"

This more recent Access Service category, breaks down into smaller geographic chunks (i.e., Administrative Districts) are not listed in the license agreements. But Districts are well recognised administrative regions that come under the remit of the Indian Government's Ministry of Home Affairs.

Being a political administrative area, the information is more widely available. However, thinking that Google Maps API will do what I need, I soon found out that 'boundary coverage' only provides the following levels in India:

• Country
• Administrative Level 1 - States and Union Territories
• Administrative Level 2 - Divisions
• Locality - Cities, Towns, and Villages
• Post Code

The top 3 are way too broad, then the last 2 are way too specific... the hunt continues... right up until I stumble upon OpenStreetMap - a fantastic project that has a seemingly endless amounts of geographical data and an API with it's own selection of multiple Query Languages!

There is a free front-end for OpenStreetMap data available called Overpass Turbo which also has some nice helper functions, and the ability to export in various different formats too, including GeoJSON.

A quick walk through of the Overpass QL used above:

This essentially starts looking at the 'area' defined by being 'admin_level=4', which equates to the State or Union Territory in India, with the name "Punjab". Then relates (rel) anything within that area that is 'admin_level=5', which equates to the Districts in India, and outputs the geometry (including latitude, longitude etc).

Exporting that to a GeoJSON format, gives you a set of named 'features' with various types of geometry that include polygons, lines, and points that Google Maps API and other platforms support.

Some minor manual clean up was needed to remove the 'point' markers for the centre of the Districts, and other stuff that crept in, I didn't need those for what I was doing.

Starting with Google Maps API

Get an API Key, give them your credit card details, then cross your fingers.

I setup a very simple HTML page and include the Google Maps API script with my precious API Key (no, you can't have it).

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Google Maps with TypeScript</title>
    <script src="https://maps.googleapis.com/maps/api/js?key=GOOGLE_API_KEY&libraries=places"></script>
    <style>#status > div > div {display:inline;}</style>
</head>
<body>
    <div><input name="area" id="area"/></div>
    <div id="map" style="width: 100%; height: 600px;"></div>
    <div id="status"></div>
    <script type="module" src="./dist/index.js"></script>
</body>
</html>

And I have a bland map.

Boundaries

The next step is to see if I can get the outlines on my map. TypeScript seems to be the preference when working with Google Maps APIs, so that's what I used. After setting up a new google.maps.Map object for the #map element on the page. it's as simple as map.data.addGeoJson(file) and it magically appears!

const mapOptions: google.maps.MapOptions = {
  center: { lat: 19.0760, lng: 72.8777 }, // Mumbai coordinates
  zoom: 10,
};

const map = new google.maps.Map(document.getElementById("map") as HTMLElement, mapOptions);

map.data.addGeoJson(await (await fetch('/geojson/Punjab.geojson')).json());

map.data.setStyle({
  fillColor: 'red',
  strokeColor: 'red',
  strokeWeight: 2,
  fillOpacity: 0.1
});

First hurdle well and truly behind me, I was feeling good about this...

Locations

Picking some McDonalds (other fast food restaurants are available) I came up with the following addresses for this example:

const addresses = [
    "Ground Floor, Mehtab Cmplx, Jandiala - Phagwara Rd, near New Model Public High School, Jalandhar, Samrai, Punjab 144001, India",
    "Ground Floor, Grand Trunk Rd, near BPCL Petrol Pump, Kaddon, Doraha, Punjab 141421, India",
    "Ground Flr, Pathankot - Jalandhar Rd, near HP Charging Station, Swastik Vihar, Dasuya, Hoshiarpur, Punjab 144205, India",
    "Ground Flr, Dhillon Plaza, Ambala - Chandigarh Expy, near Decathlon, Zirakpur, Punjab 140603, India"
];

I created a new google.maps.Geocoder() object and then looping through the addresses I found earlier, passed them to the geocode() function, and if I got a 'OK' result, then I added a google.maps.Marker() to the map at the specific latitude and longitude I got from the address. (Yes, I know that Marker is being deprecated, but it's still supported currently and does what I need).

var geocoder = new google.maps.Geocoder();

addresses.forEach((address) =>{        
  geocoder.geocode( { 'address': address}, function(results, status) {
    if (status == google.maps.GeocoderStatus.OK && results != null && results[0] != null)
    {
      new google.maps.Marker({
        position: results[0].geometry.location,
        map,
        title: results[0].address_components.map(addr => addr.long_name).join(","),
        icon: {
          path: google.maps.SymbolPath.CIRCLE,
          scale: 8,
          fillColor: "#0000FF",
          fillOpacity: 0.5,
          strokeWeight: 1
          }
        });
      }else{
        console.log("ERROR finding address " + address);
      }
  });
});

This is starting to feel really achievable, but boy was I in for a surprise.

Find Point in Area

None of this is really useful yet, and I was wondering if I found myself digging down a 'cool tech' rabbit hole just because I could. The final piece of the puzzle was to figure out what District the markers were located within. Without resorting to doing it visually 👁️👁️.

Luckily Google Maps API had me covered once again!

There is a built in function within the geometry class:

google.maps.geometry.poly.containsLocation(loation, polygon);

But... I don't have the 'polygon' data to hand, I just have the original GeoJSON file and I the beautiful map on my screen. So, to be able to use this function I had to pull the raw polygon data out of my 'map.data' object that I just imported it to.

I'll put the simple version below, but this is the outline:

• Loop through the data layer to get each feature
• Figure out if the feature's geometry is a Polygon (array of coordinates), or a MultiPolygon (array of Polygons).
  • if it's a MultiPolygon, loop through each of those items as its own Polygon
• Get the array of coordinates from geometry, and re-construct it back into a Polygon object and pass it to google.maps.geometry.poly.containsLocation() along with it the location, which is a google.maps.LatLng type, and the newly created Polygon we're testing against.
• If true, then I keep a track of the count based on the name of the feature. This means I have a count breakdown per District later on.

data.forEach((feature) => {
  const geometry: google.maps.Data.Geometry | null = feature.getGeometry();
  if (geometry?.getType() === "Polygon") {
    const paths = geometry.getArray().map((ring) =>
    ring.getArray().map((latlng) => ({ lat: latlng.lat(), lng: latlng.lng() }))
    );
    
    const polygon = new google.maps.Polygon({ paths });
    if (google.maps.geometry.poly.containsLocation(location, polygon)) {
      return {true, feature.Fg.name}; // do stuff to track counts per feature name
    }else{
      return {false, null}; // not found
    }
  }

  /* if(geometry?.getType() === "MultiPolygon")...
        Do the same as above for each Polygons in the array instead of one */
});

Using Turf.js

Google Maps API is awesome, and allowed me to rapidly develop this prototype in an evening. But I didn't like the prospect of paying for all these API calls I was stacking up.

I saw that there's another lifesaver of a project called "Turf.js", which is a modular 'spatial analysis' library that also contains helper functions for working with GeoJSON that can run client-side as well as on a server with Node.JS.

Mapping all this stuff in a browser to look at is cool, but all really I need to do is update some data, potentially in bulk. As well as ad-hoc situations such as when a sales person adds a new address in India into Dynamics 365.

So I switched tack slightly, keeping the work I've done so far with Google Maps API around to help troubleshoot and visualise what was going on at any point.

Bulk Geocoding Script

Now that I've proved the theory, I need turn it into something more practical, so I setup a CSV file of addresses to use as the input to the new script, and finished exporting all of the States, Districts, and Metro Areas from Overpass-Turbo and cut out some of the extra metadata I didn't need.

I decided to optimise the lookup, so rather than looping through each and every Polygon for the whole of India that comes it at around 350Mb. It made sense to use a quadtree approach (also called spatial quantising), which is basically starting with larger areas to check (States and Union Territories) which is around 70Mb, and then when a match is found in one of those, let's say in the State of Punjab, just load up the GeoJSON file that contains only those Districts ~6Mb.

Here's the outline:

• Loop through CSV to get each address
• Geocode the Address to get just the latitude and longitude using the same Google Maps API calls as before
• Load in the high level 'india-states.geojson' file
• Use Turf's booleanPointInPolygon() function and pass it a point([lat],[lng]) and the GeoJSON feature to check against.
• If it matches, get the feature name, and look for a matching GeoJSON file
  • i.e., match found in feature.name="Punjab", load "Punjab.geojson" and repeat the search process but now in a targeted area.
• Finally update the CSV with the latitude and longitude, along with the Google Maps API returned 'Formatted Address' and the name of the State or Union Territory from the first match, and the District from the second match.

However, that's not the end, we still need to figure out the specific Telecom Circle or Metro Area that address is in, as the borders and boundaries of Districts and States don't always line up nicely.

Mapping to Circles/Metros

Ok, the big reveal.... no, it's not AI.... It's a big ol' if statement. Well, technically I used switch / case but it's the same really.

This also had to be done in multiple passes to get to the correct answer. Beginning with trying to match the one or more states with a specific Service Area name.

switch(state)
{
  ...
  ...
  // Punjab Service Area
  //  - Entire area falling within the State of Punjab and Union territory of
  //  - Chandigarh and Panchkula town of Haryana.
  case "Punjab":
  case "Chandigarh":
    circle = "Punjab Circle";
    break;
  ...
  ...
}

That's easy in the example above, but if the address happened to be within the Hooghly District of the West Bengal State, it's not in the West Bengal Telecom Circle as you might expect, it's actually in the Kolkata Metro Area. So a second pass is needed, this time incorporating the District too.

switch(circle)
{
  ...
  ...
  case "West Bengal Circle":
    switch(district)
    {
      // Kolkata Service Area
      //  - Local Areas served by Calcutta Telephones.
      case "North 24 Parganas District":
      case "South 24 Parganas District":
      case "Hooghly District":
      case "Howrah District":
      case "Nadia District":
        circle = "Kolkata Metro Area";
        break;
    };
    break;
  ...
  ...
}

BUT, that's still not all.. Because that only works when the District (as a whole) is included within the Service Area.

Mumbai is different, Wikipedia shows that Thane District, Raigad District, and Palghar District are only partially in the Mumbai Metropolitan Area.

Back to Overpass-Turbo to get that 'admin_level=7' goodness from OpenStreetMaps to save my hide and bring the opportunity to actually get some sleep slightly closer.

Here you can see the power of using Google Maps API for visual verification and troubleshooting. This is a map of the Districts in the State of Maharashtra , which Mumbai is in. With an overlay of the Mumbai Metropolitan Region - you can clearly see how there is no direct correlation between Districts and the Metro Area.

So the 3rd pass becomes...

• Check the address is within the State of Maharashtra
• Check it's within the Mumbai City or Mumbai Suburban Districts, if so it's definitely within the Mumbai Metropolitan Region
• If the address is within the Thane, Raigad, Palghar Districts
  • Then load the "Mumbai Metropolitan Region.geojson" to check against
    • If inside, then it's in the metro area
    • If not, then it's remains in the Maharashtra Telecom Circle

Running it as a script

Obviously I went to town on this step, adding command line parameters, including optional debug-level logging, and the choice of creating a new file or overwriting the input file.

npx ts-node parse-addresses.ts --input addresses-short.csv --log

And it's pretty quick, a fraction of a second to process each address. I ran it on ~220 addresses and it finished in...

only 49 seconds! I was happy with that.

Azure Function

This particular Pokemon's final form will be an Azure Function, that means it's incredibly easy to call from Power Automate so it can be wired up to Dataverse record changes to automatically map the address to Service Areas in blink of an eye, feeding some fancy Power BI dashboard and reports.

I'm going to write this up as another post, I think you've stuck this out long enough.

Thank you for reading, and be content that I can sleep easy at night knowing that I can find out not only which District and State any Indian address is within, but also which Telecom Circle or Metro Area - all at the press of a button.