Azure Data Factory Copy Geometry data from Azure SQL to Azure Cosmos DB

Question

Hi Team,

I am trying to copy over data from a table in Azure SQL database containing geometry type columns. When i created a linked service and preview data get the error message

'Type=Newtonsoft.Json.JsonSerializationException,Message=Error getting value from 'Value' on 'System.Data.SqlTypes.SqlDouble'.,Source=Newtonsoft.Json,'

'Type=System.Data.SqlTypes.SqlNullValueException,Message=Data is Null. This method or property cannot be called on Null values.,Source=System.Data,'

To overcome that I tried to convert into .STAsText() and insert to cosmos. However cosmos db does not seem to identify it as proper geometry points or polygons to run the geospatial queries as provided in https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/geospatial?tabs=csharp

Please suggest what is the best way to move this data across from sql to cosmos . To do in C# is difficult as there are 40 million records

Answer 1

Hello @Vijay Rudrasamudram

One possible solution to this issue is to use Azure Data Factory's Mapping Data Flows to transform the data before copying it to Cosmos DB. You can use the Derived Column transformation to convert the geometry data into a format that Cosmos DB can recognize.

Here's an example of how you can use the Derived Column transformation to convert the geometry data into a format that Cosmos DB can recognize:

1. Add a Derived Column transformation to your Mapping Data Flow.
2. In the Derived Column transformation, add a new column and use the following expression to convert the geometry data into a format that Cosmos DB can recognize: toString($geometryColumn.STAsText()) Replace $geometryColumn with the name of your geometry column.
3. Map the new column to the corresponding column in your Cosmos DB sink. This should allow you to copy the geometry data from Azure SQL to Cosmos DB and run geospatial queries on it.

That should help you.

---

I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.