Data flow - CSV to CosmosDB - decimal or float geospatial data

Andrew Lightfoot 6

Hello

In a dataflow with a CSV source and CosmosDB sink I want to map the Long/Lat fields to a point array in CosmosDB so I can use geospatial queries.

It should look like this:

"location":{
    "type":"Point",
    "coordinates":[ 0.1055987, -4.8 ]
}

When I first did this the mapping retained the values as strings (so ["0.1055987", "-4.8" ]) and the geospatial queries didn't work - which I believe is because the values need to be numbers, not strings.

However when I try and convert the values to either decimals or floats (eg toFloat(LONGITUDE, '##.#######') ) I get this error when the data flow runs: Cannot cast 0.1055987 into a Json value. FloatType has no matching Json

I get the same error for decimals.

The Long/Lat values are decimals eg 0.1055987

Any help would be appreciated!

1 answer

Kiran-MSFT 691 Reputation points Microsoft Employee

2020-08-17T03:20:15.423+00:00

You need to create an array to represent [ 0.1055987, -4.8 ]. Say you have two columns in your CSV called longitude and latitude you can use structure and array creation.

A expression like

@(
type = 'Point'
coordinates = [latitude, longitude]
)

assign this to a column called "location" in a derived tx.
Please sign in to rate this answer.
Andrew Lightfoot 6 Reputation points

2020-08-27T13:58:51.517+00:00

Thanks for this response. It doesn't work (!) but only because you missed the array function - so it should be coordinates=array(latitude, longitude)

Looking at this again I've tried something else to work this out. I changed the sink to a JSON file in blob storage, and this worked fine. Here's a snippet of the JSON:

"ADDRESS_LINE_1": "TESCO EXPRESS, TESCO EXPRESS ESSO PETROL FILLING STATION, RIPPLE ROAD", "LOCATION": { "type": "Point", "coordinates": [ 0.1055987, 51.5334514 ] }, "IN_BOROUGH": "Yes"

[I had to continue this below as a single comment wasn't submitting even though it was less than 1000 chrs]

Andrew Lightfoot 6 Reputation points

2020-08-27T14:00:03.023+00:00

.... continued

So the JSON is created fine by the flow.

And, I can manually import the JSON into the CosmosDB.

If I change the sink back to the same CosmosDB in DF I get the error as before. So to me that suggests the problem is with the dataset for CosmosDB.

The odd thing though is the error refers to the data type of the column ({"message":"Cannot cast 0.1055987 into a Json value. DecimalType(9,7) has no matching Json value.. Details:Cannot cast 0.1055987 into a Json value.)

Kiran-MSFT 691 Reputation points Microsoft Employee

2020-08-28T04:54:18.23+00:00

Decimal datatype in dataflow has no mapping in CosmosDB. You should try to use double. The array syntax should work [latitude, longitude]. It is probably a UI validation issue preventing it.
@Richard Zhang-MSFT

Andrew Lightfoot 6 Reputation points

2020-08-28T08:45:13.427+00:00

Great. Double worked, thanks !

I think I'd tried every other number format, but not double....
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Data flow - CSV to CosmosDB - decimal or float geospatial data

1 answer

Your answer