Cosmos DB stores geospatial data in GeoJSON format. You can not tell what raw GeoJSON represents because usually all it has is a type and bunch of coordinates. Azure Cosmos DB does not have any UI to help you what GeoJSON data looks like on a map either. Only option you have is a third party tool which might display data on a map or Azure Cosmos DB Jupyter Notebooks.
I want to run a query in Azure Cosmos DB and see the results on a map. I decided to create a simple UI which displays spatial data on a map. I will show you how to do this step by step. I will use LeafLetJs as a map. It is open source and free! Also, I need to create .NET Core 3.1 web application and use Azure Cosmos DB Emulator for data.
I will use the same data from my earlier post. So, I do not need to worry about the Azure Cosmos DB part in this post. We will start with creating a new web application.
You can download LeafLetJs files and reference them locally if you like. I am planning to use CDN version in this post. I will use the following files.
<link rel="stylesheet" href="https://unpkg.com/leaflet@1.6.0/dist/leaflet.css"
integrity="sha512-xwE/Az9zrjBIphAcBb3F6JVqxf46+CDLwfLMHloNu6KEQCAWi6HcDUbeOfBIptF7tcCzusKFjFw2yuvEpDL9wQ=="
crossorigin="" />
<script src="https://unpkg.com/leaflet@1.6.0/dist/leaflet.js"
integrity="sha512-gZwIG9x3wUXg2hdXF6+rVkLF/0Vi9U8D2Ntg4Ga5I5BZpVkVxlJWbSQtXPSiUTtC0TjtGOmxa1AJPuV0CPthew=="
crossorigin=""></script>In my UI, I want to have a map, a text area that I can type my Cosmos DB query. Also, it would be nice to have another box that shows me some information about the data and Request Unit information. Let create these elements first in HTML.
<body>
<div class="CosmosSpatial">
<div id="CosmosMap"></div>
<div class="CosmosDiv">
<div class="CosmosQuery">
<textarea id="CosmosQuery"></textarea>
<div>Database : <input type="text" id="databasename" value="Spatial" /></div>
<div>Container : <input type="text" id="containername" value="Hurricanes" /></div>
<input type="button" value="Run Query" class="RunButton" />
<input type="button" value="Clear Map" class="RemoveButton" />
</div>
<div id="QueryInfo">
<div>
Request Unit : <span id="QueryCost"></span>
</div>
<div>
Item Count : <span id="ItemCount"></span>
</div>
</div>
</div>
</div>
</body>
We need some styling too. Add the following style in the header section of HTML. Front-end is very important for all applications.. Front-End is your salesman. Users do not care how good your coding skills are or how awesome your database is.
- Make your UI user friendly.
- Don't be afraid to go bold with colors.
- Don't put everything in a boring white-gray grid!
- Learn CSS
<style>
body {
margin: 0;
font-family: -apple-system,BlinkMacSystemFont,"Segoe UI",Roboto,"Helvetica Neue",Arial,"Noto Sans",sans-serif,"Apple Color Emoji","Segoe UI Emoji","Segoe UI Symbol","Noto Color Emoji";
font-size: 1rem;
font-weight: 400;
}
.CosmosSpatial {
display: flex;
flex-direction: column;
height: 100vh;
width: 100vw;
}
#CosmosMap {
width: 100%;
height: calc(100vh - 160px);
}
.CosmosDiv {
display: flex;
height: 160px;
border-top: 2px solid black;
background: #304070;
color: white;
}
.CosmosQuery {
border-right: 2px solid white;
width: 70%;
height: 100%;
text-align: center;
display: flex;
flex-wrap: wrap;
justify-content: space-around;
font-variant: small-caps;
}
.CosmosQuery textarea {
width: 99%;
height: 125px;
border: 1px solid dimgray;
}
.RunButton {
background: springgreen;
border: none;
font-variant: small-caps;
padding: 2px 10px;
border-radius: 5px;
margin: 0 0 2px 0;
}
.QueryInfo {
padding: 5px;
display: flex;
flex-direction: column;
}
</style>
I will use the CosmosMap div element for displaying a map. Let's program this first and display an empty map. To do that, I need to tell LeafLetJs which element to use for a map. Here is my code for this.
<script>
var cosmosmap = L.map("CosmosMap").setView([30.35, -90.08], 7);
L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
maxZoom: 10,
attribution: '© <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors'
}).addTo(cosmosmap);</script>
With this code, I told LeafLetJs to use CosmosMap div element to display the map. Also, I wanted map to focus on 30.35, -90.08 with zoom level 7. This is the New Orleans area. You can change this location for your needs. MaxZoom is the highest-level user can zoom in to. When I start .Net Core application and visit this page, I see the following view.
So far, we have a map focusing on New Orleans area. I will use the bottom part of this page to send my queries to Azure Cosmos DB. Query will run in Hurricanes container in Spatial Database. I can override these by typing different database and Container name. Also, right bottom corner will display some information about the query results.
We need to create a function which will accept database name, container name, and a query from front-end. Then it will run the query in Cosmos DB and returns the data back to Front-End as JSON document.
public async Task<JsonResult> RunCosmosQuery(string db, string cont, string query)
{
var queryDefinition = new QueryDefinition(query);
var container = client.GetDatabase(db).GetContainer(cont);
FeedIterator<Hurricane> queryResultSetIterator = container.GetItemQueryIterator<Hurricane>(queryDefinition);
var hurricanes = new List<Hurricane>();
double rq = 0;
while (queryResultSetIterator.HasMoreResults)
{
var currentResultSet = await queryResultSetIterator.ReadNextAsync();
rq += currentResultSet.RequestCharge;
foreach (Hurricane current in currentResultSet)
{
current.GeoJson = current.Location.ConvertToGeoJson();
hurricanes.Add(current);
}
}
var dt = new CosmosData()
{
Data = hurricanes,
ReqUnit = rq,
Count = hurricanes.Count()
};
return new JsonResult(dt);
}
This function runs the given query in selected database and container. Spatial Data is saved as GeoJson format in Azure Cosmos DB. When we retrieve spatial data from Cosmos DB, we lose the GeoJson format. Front-End still needs the data in GeoJSON format. I taught this should not be an issue if I return the data as JsonResult. I was wrong!
For a valid simple GeoJSON document, you must have 2 properties, first one is type which tells what type of spatial data (Point, Polygon, Line, etc...) we are dealing with. Other one is the coordinates. As you can see in the following screenshot Type is defined as enum in SDK. Type must be a string for a valid GeoJSON but enum returns a number.
To fix this problem, I created the following function. It adds a dynamic object which ends up with a valid GeoJSON format so Front-End map can handle the data and display it right.
public static class CosmosExtensionscs
{
public static dynamic ConvertToGeoJson(this Microsoft.Azure.Cosmos.Spatial.Point current)
{
return new
{
type = "Point",
coordinates = current.Position.Coordinates
};
}}
We are ready to make a request from the front-end. Here is the javascript code which runs when user clicks on Run Query button.
function CallCosmos() {
$.ajax({
url: 'RunCosmosQuery',
data: { db: $('#databasename').val(), cont: $('#containername').val(), query: $('#CosmosQuery').val() },
cache: false,
success: function (data) {
var style = {
"color": "#ff7800",
"weight": 5
}
if (data) {
$('#QueryCost').text(data.reqUnit);
$('#ItemCount').text(data.count);
$.each(data.data, function (key, val) {
if (val) {
L.geoJson(val.geoJson, style).addTo(cosmosmap);
}
});
}
}
});
We are ready to test this. In my Hurricanes container, I have the data of Hurricane Katrina. I want to select all data and display it on this map. I type SELECT * from h and click on Run Query.
You can see the data on map now, Also you can see how much Request Unit query used in the right side. This application supports only Point type for now, I will add other GeoSpatial types and make this available in GitHub when I have more time.
No comments:
Post a Comment