How to retrieve data from Azure Cosmos DB by using Spatial function DISTANCE

     In my last post, I showed you how to insert Spatial Data into Azure Cosmos DB by using the .NET SDK. In this post; We will focus on selecting data with Spatial functions of Cosmos DB.

     Azure Cosmos DB has three spatial functions when it comes to selecting data. These functions must run in database level. In my earlier post, we created a container with  Hurricane Katrina's spatial data. Today, we will create another container named Customers that holds the location of our customers. Here is my Customer object I want to save to my Cosmos DB container.

 class Customer
 {
     public string id { get; set; }
     public string Name { get; set; }
     public string Phone { get; set; }
     public Point Location { get; set; }

     public Customer() { }
     public Customer(string name, string phone, Point location)
     {
         id = Guid.NewGuid().ToString();
         Name = name;
         Phone = phone;
         Location = location;
     }
 }

Here is my sample data.

static List<Customer> GetCustomers()
{
      return new List<Customer> {
          new Customer("George","784-232-3502",new Point(-75,23.19)),
          new Customer("Thomas","354-884-6224",new Point(-75.2,23)),
          new Customer("Lisa","753-452-5342",new Point(-75.1,23.3)),
          new Customer("Elizabeth","733-347-8843",new Point(-75.2,23.4)),
          new Customer("Nancy","346-785-3298",new Point(-74,23.7)),
          new Customer("Jason","854-954-3334",new Point(-76,23.5)),
          new Customer("Tim","236-744-3502",new Point(-75.4,21.9)),
          new Customer("Eric","643-884-9744",new Point(-75.4,23.3)),
          new Customer("Robert","348-832-4722",new Point(-75.34,23.2)),
          new Customer("Chris","965-232-5223",new Point(-75.54,23.6)),
          new Customer("Allison","326-066-6288",new Point(-75,23.53)),
          new Customer("Brian","623-655-5893",new Point(-75,23.58)),
          new Customer("Victoria","784-152-4588",new Point(-75.19,23.34)),
          new Customer("Patricia","799-264-3502",new Point(-75.52,23.39)),
          new Customer("Sergey","267-232-0045",new Point(-75.42,24.1)),
          new Customer("Ashley","623-236-3588",new Point(-75.19,23.59)),
          new Customer("Andrew","543-734-2842",new Point(-75.25,23.89)),
          new Customer("Amanda","243-632-9432",new Point(-75.3,25.1))
      };
 }

Following function pushes this data into container named Customers

static async Task<bool> PushToCosmos(List<Customer> customers)
{
    try
    {
        foreach (var c in customers)
        {
            var temp = await client.GetDatabase("Spatial").GetContainer("Customers").UpsertItemAsync(c);
        }
        return true;
     }
     catch (Exception ex)
     {
       return false;
      }
}

This is how data looks like in my Customer Container after I ran the PushToCosmos function.

     We are ready to use spatial functions to retrieve data. I have the location of Hurricane Katrina. I want to find my customers who might be affected by Katrina. I want to use the Distance function for this. I have the locations of eye of Katrina in my database, first I will pick one of those locations then look for my customers close to that location. How close? You can control the distance with spatial function named distance, In the following example, I use 100 km which is approximately 62 miles.

static List<Customer> FindCustomers(Point hurricanelocation, int distanceinKM)
{
    var customers = client.GetDatabase("Spatial").GetContainer("Customers").GetItemLinqQueryable(true)
       .Where(t => t.Location.Distance(hurricanelocation) < distanceinKM * 1000).ToList();
    return customers;
}

    When I run this function, database finds 13 customers. I can reach these customers and warn them. Or if you are running after the hurricane, you can review your employees to see how they handled the issues raised by these customers.

  We can get the same result by using the following SQL Query. I ran the following query  directly in my Azure Cosmos DB Emulator. It took 2.97 R/U to run this query.

SELECT * FROM c
WHERE ST_DISTANCE(c.Location, {'type': 'Point', 'coordinates':[-75.1, 23.1]}) < 100000