Index and query GeoJSON location data in Azure Cosmos DB for NoSQL

Article
08/01/2023

APPLIES TO: NoSQL

Geospatial data in Azure Cosmos DB for NoSQL allows you to store location information and perform common queries, including but not limited to:

Finding if a location is within a defined area
Measuring the distance between two locations
Determining if a path intersects with a location or area

This guide walks through the process of creating geospatial data, indexing the data, and then querying the data in a container.

Prerequisites

An existing Azure Cosmos DB for NoSQL account.
- If you don't have an Azure subscription, Try Azure Cosmos DB for NoSQL free.
- If you have an existing Azure subscription, create a new Azure Cosmos DB for NoSQL account.
Latest version of .NET.
Latest version of Azure CLI.
- If you're using a local installation, sign in to the Azure CLI by using the az login command.

Create container and indexing policy

All containers include a default indexing policy that will successfully index geospatial data. To create a customized indexing policy, create an account and specify a JSON file with the policy's configuration. In this section, a custom spatial index is used for a newly created container.

Open a terminal.

Create a shell variable for the name of your Azure Cosmos DB for NoSQL account and resource group.

# Variable for resource group name
resourceGroupName="<name-of-your-resource-group>"

# Variable for account name
accountName="<name-of-your-account>"

Create a new database named cosmicworks using az cosmosdb sql database create.

az cosmosdb sql database create \
    --resource-group $resourceGroupName \
    --account-name $accountName \
    --name "cosmicworks" \
    --throughput 400

Create a new JSON file named index-policy.json and add the following JSON object to the file.

{
  "indexingMode": "consistent",
  "automatic": true,
  "includedPaths": [
    {
      "path": "/*"
    }
  ],
  "excludedPaths": [
    {
      "path": "/\"_etag\"/?"
    }
  ],
  "spatialIndexes": [
    {
      "path": "/location/*",
      "types": [
        "Point",
        "Polygon"
      ]
    }
  ]
}

Use az cosmosdb sql container create to create a new container named locations with a partition key path of /region.

az cosmosdb sql container create \
    --resource-group $resourceGroupName \
    --account-name $accountName \
    --database-name "cosmicworks" \
    --name "locations" \
    --partition-key-path "/category" \
    --idx @index-policy.json

Retrieve the primary connection string for the account using az cosmosdb keys list.
```
az cosmosdb keys list \
    --resource-group $resourceGroupName \
    --name $accountName \
    --type "connection-strings" \
    --query "connectionStrings[?keyKind == \`Primary\`].connectionString" \
    --output tsv
```
Tip

To see all the possible connection strings for an account, use az cosmosdb keys list --resource-group $resourceGroupName --name $accountName --type "connection-strings".
Record the connection string. You use this credential later in this guide.

Create .NET SDK console application

The .NET SDK for Azure Cosmos DB for NoSQL provides classes for common GeoJSON objects. Use this SDK to streamline the process of adding geographic objects to your container.

Open a terminal in an empty directory.
Create a new .NET application by using the dotnet new command with the console template.
```
dotnet new console
```
Import the Microsoft.Azure.Cosmos NuGet package using the dotnet add package command.
```
dotnet add package Microsoft.Azure.Cosmos --version 3.*
```
Warning

Entity Framework does not currently spatial data in Azure Cosmos DB for NoSQL. Use one of the Azure Cosmos DB for NoSQL SDKs for strongly-typed GeoJSON support.
Build the project with the dotnet build command.
```
dotnet build
```
Open the integrated developer environment (IDE) of your choice in the same directory as your .NET console application.
Open the newly created Program.cs file and delete any existing code. Add using directives for the Microsoft.Azure.Cosmos, Microsoft.Azure.Cosmos.Linq, andMicrosoft.Azure.Cosmos.Spatial namespaces.
```
using Microsoft.Azure.Cosmos;
using Microsoft.Azure.Cosmos.Linq;
using Microsoft.Azure.Cosmos.Spatial;
```
Add a string variable named *connectionString with the connection string you recorded earlier in this guide.
```
string connectionString = "<your-account-connection-string>"
```
Create a new instance of the CosmosClient class passing in connectionString and wrapping it in a using statement.
```
using CosmosClient client = new (connectionString);
```
Retrieve a reference to the previously created container (cosmicworks/locations) in the Azure Cosmos DB for NoSQL account by using CosmosClient.GetDatabase and then Database.GetContainer. Store the result in a variable named container.
```
var container = client.GetDatabase("cosmicworks").GetContainer("locations");
```
Save the Program.cs file.

Add geospatial data

The .NET SDK includes multiple types in the Microsoft.Azure.Cosmos.Spatial namespace to represent common GeoJSON objects. These types streamline the process of adding new location information to items in a container.

Create a new file named Office.cs. In the file, add a using directive to Microsoft.Azure.Cosmos.Spatial and then create a Office record type with these properties:

	Type	Description	Default value
id	`string`	Unique identifier
name	`string`	Name of the office
location	`Point`	GeoJSON geographical point
category	`string`	Partition key value	`business-office`

using Microsoft.Azure.Cosmos.Spatial;

public record Office(
    string id,
    string name,
    Point location,
    string category = "business-office"
);

Note

This record includes a Point property representing a specific position in GeoJSON. For more information, see GeoJSON Point.

Create another new file named Region.cs. Add another record type named Region with these properties:

	Type	Description	Default value
id	`string`	Unique identifier
name	`string`	Name of the office
location	`Polygon`	GeoJSON geographical shape
category	`string`	Partition key value	`business-region`

using Microsoft.Azure.Cosmos.Spatial;

public record Region(
    string id,
    string name,
    Polygon location,
    string category = "business-region"
);

Note

This record includes a Polygon property representing a shape composed of lines drawn between multiple locations in GeoJSON. For more information, see GeoJSON Polygon.

Create another new file named Result.cs. Add a record type named Result with these two properties:

Type Description

name string Name of the matched result

distanceKilometers decimal Distance in kilometers
```
public record Result(
    string name,
    decimal distanceKilometers
);
```
Save the Office.cs, Region.cs, and Result.cs files.
Open the Program.cs file again.

	Type	Description
name	`string`	Name of the matched result
distanceKilometers	`decimal`	Distance in kilometers

Create a new Polygon in a variable named mainCampusPolygon.

Polygon mainCampusPolygon = new (
    new []
    {
        new LinearRing(new [] {
            new Position(-122.13237, 47.64606),
            new Position(-122.13222, 47.63376),
            new Position(-122.11841, 47.64175),
            new Position(-122.12061, 47.64589),
            new Position(-122.13237, 47.64606),
        })
    }
);

Create a new Region variable named mainCampusRegion using the polygon, the unique identifier 1000, and the name Main Campus.
```
Region mainCampusRegion = new ("1000", "Main Campus", mainCampusPolygon);
```
Use Container.UpsertItemAsync to add the region to the container. Write the region's information to the console.
```
await container.UpsertItemAsync<Region>(mainCampusRegion);
Console.WriteLine($"[UPSERT ITEM]\t{mainCampusRegion}");
```
Tip

This guide uses upsert instead of insert so you can run the script multiple times without causing a conflict between unique identifiers. For more information on upsert operations, see creating items.
Create a new Point variable named headquartersPoint. Use that variable to create a new Office variable named headquartersOffice using the point, the unique identifier 0001, and the name Headquarters.
```
Point headquartersPoint = new (-122.12827, 47.63980);
Office headquartersOffice = new ("0001", "Headquarters", headquartersPoint);
```
Create another Point variable named researchPoint. Use that variable to create another Office variable named researchOffice using the corresponding point, the unique identifier 0002, and the name Research and Development.
```
Point researchPoint = new (-96.84369, 46.81298);
Office researchOffice = new ("0002", "Research and Development", researchPoint);
```

Create a TransactionalBatch to upsert both Office variables as a single transaction. Then, write both office's information to the console.

TransactionalBatch officeBatch = container.CreateTransactionalBatch(new PartitionKey("business-office"));
officeBatch.UpsertItem<Office>(headquartersOffice);
officeBatch.UpsertItem<Office>(researchOffice);
await officeBatch.ExecuteAsync();

Console.WriteLine($"[UPSERT ITEM]\t{headquartersOffice}");
Console.WriteLine($"[UPSERT ITEM]\t{researchOffice}");

Note

For more information on transactions, see transactional batch operations.

Save the Program.cs file.

Run the application in a terminal using dotnet run. Observe that the output of the application run includes information about the three newly created items.

dotnet run

[UPSERT ITEM]   Region { id = 1000, name = Main Campus, location = Microsoft.Azure.Cosmos.Spatial.Polygon, category = business-region }
[UPSERT ITEM]   Office { id = 0001, name = Headquarters, location = Microsoft.Azure.Cosmos.Spatial.Point, category = business-office }
[UPSERT ITEM]   Office { id = 0002, name = Research and Development, location = Microsoft.Azure.Cosmos.Spatial.Point, category = business-office }

Query geospatial data using NoSQL query

The types in the Microsoft.Azure.Cosmos.Spatial namespace can be used as inputs to a NoSQL parameterized query to use built-in functions like ST_DISTANCE.

Open the Program.cs file.

Create a new string variable named nosql with the query is used in this section to measure the distance between points.

string nosqlString = @"
    SELECT
        o.name,
        NumberBin(distanceMeters / 1000, 0.01) AS distanceKilometers
    FROM
        offices o
    JOIN
        (SELECT VALUE ROUND(ST_DISTANCE(o.location, @compareLocation))) AS distanceMeters
    WHERE
        o.category = @partitionKey AND
        distanceMeters > @maxDistance
";

Tip

This query places the geospatial function within a subquery to simplify the process of reusing the already calculated value multiple times in the SELECT and WHERE clauses.

Create a new QueryDefinition variable named query using the nosqlString variable as a parameter. Then use the QueryDefinition.WithParameter fluent method multiple times to add these parameters to the query:

Value

@maxDistance 2000

@partitionKey "business-office"

@compareLocation new Point(-122.11758, 47.66901)
```
var query = new QueryDefinition(nosqlString)
    .WithParameter("@maxDistance", 2000)
    .WithParameter("@partitionKey", "business-office")
    .WithParameter("@compareLocation", new Point(-122.11758, 47.66901));
```
Create a new iterator using Container.GetItemQueryIterator<>, the Result generic type, and the query variable. Then, use a combination of a while and foreach loop to iterate over all results in each page of results. Output each result to the console.
```
var distanceIterator = container.GetItemQueryIterator<Result>(query);
while (distanceIterator.HasMoreResults)
{
    var response = await distanceIterator.ReadNextAsync();
    foreach (var result in response)
    {
        Console.WriteLine($"[DISTANCE KM]\t{result}");
    }
}
```
Note

For more information on enumerating query results, see query items.
Save the Program.cs file.

	Value
@maxDistance	`2000`
@partitionKey	`"business-office"`
@compareLocation	`new Point(-122.11758, 47.66901)`

Run the application again in a terminal using dotnet run. Observe that the output now includes the results of the query.

dotnet run

[DISTANCE KM]   Result { name = Headquarters, distanceKilometers = 3.34 }
[DISTANCE KM]   Result { name = Research and Development, distanceKilometers = 1907.43 }

Query geospatial data using LINQ

The LINQ to NoSQL functionality in the .NET SDK supports including geospatial types in the query expressions. Even further, the SDK includes extension methods that map to equivalent built-in functions:

Extension method	Built-in function
`Distance()`	`ST_DISTANCE`
`Intersects()`	`ST_INTERSECTS`
`IsValid()`	`ST_ISVALID`
`IsValidDetailed()`	`ST_ISVALIDDETAILED`
`Within()`	`ST_WITHIN`

Open the Program.cs file.
Retrieve the Region item from the container with a unique identifier of 1000 and store it in a variable named region.
```
Region region = await container.ReadItemAsync<Region>("1000", new PartitionKey("business-region"));
```
Use the Container.GetItemLinqQueryable<> method to get a LINQ queryable, and the build the LINQ query fluently by performing these three actions:
1. Use the Queryable.Where<> extension method to filter to only items with a category equivalent to "business-office".
2. Use Queryable.Where<> again to filter to only locations within the region variable's location property using Geometry.Within().
3. Translate the LINQ expression to a feed iterator using CosmosLinqExtensions.ToFeedIterator<>.
```
var regionIterator = container.GetItemLinqQueryable<Office>()
    .Where(o => o.category == "business-office")
    .Where(o => o.location.Within(region.location))
    .ToFeedIterator<Office>();
```
Important

In this example, the office's location property has a point, and the region's location property has a polygon. ST_WITHIN is determining if the point of the office is within the polygon of the region.

Use a combination of a while and foreach loop to iterate over all results in each page of results. Output each result to the console.

while (regionIterator.HasMoreResults)
{
    var response = await regionIterator.ReadNextAsync();
    foreach (var office in response)
    {
        Console.WriteLine($"[IN REGION]\t{office}");
    }
}

Save the Program.cs file.

Run the application one last time in a terminal using dotnet run. Observe that the output now includes the results of the second LINQ-based query.

dotnet run

[IN REGION]     Office { id = 0001, name = Headquarters, location = Microsoft.Azure.Cosmos.Spatial.Point, category = business-office }

Clean up resources

Remove your database after you complete this guide.

Open a terminal and create a shell variable for the name of your account and resource group.

# Variable for resource group name
resourceGroupName="<name-of-your-resource-group>"

# Variable for account name
accountName="<name-of-your-account>"

Use az cosmosdb sql database delete to remove the database.

az cosmosdb sql database delete \
    --resource-group $resourceGroupName \
    --account-name $accountName \
    --name "cosmicworks"

Next steps

Geospatial and GeoJSON location data

Index and query GeoJSON location data in Azure Cosmos DB for NoSQL

Prerequisites

Create container and indexing policy

Create .NET SDK console application

Add geospatial data

Query geospatial data using NoSQL query

Query geospatial data using LINQ

Clean up resources

Next steps

Feedback

Additional resources