Geospatial data with azure cosmos db SQL API using python

Question

I have 2 million records in azure cosmos db,¡.

I am using it with SQL API with python.

I have adapted this records to be geospatial data like this sample:

{
"
"street": "Palmdale Station", 
"year": "2015", 
"incidentid": "D01DA82AE23D128799924FCF82B72908", 
"id": "501", 
"
"latitude": 34.58986, 

"country": "United States", 
"longitude": -89.61238680302355, 
"_attachments": "attachments/", 
"incidenttype": "Train Accident", 
"_etag": "\"0900f6f1-0000-0100-0000-5dd6cf150000\""
}
{
"_self": "dbs/QwR-AA==/colls/QwR-AIt6sYk=/docs/QwR-AIt6sYnYUx0AAAAAAA==/", 
"street": "Palmdale Station", 
"year": 2015, 
"incidentid": "D01DA82AE23D128799924FCF82B72908", 
"id": "501", 
"notifyrule": "Use Perimeter", 
"city": "Palmdale", 
"infoquality": "Media", 
"severity": "Minor", 
"incidentcategory": "Transportation", 
"location": {
"type": "Point", 
"coordinates": [
-89.61238680302355, 
29.568987477308124
]
}, 
"latitude": 29.568987477308124, 
"infosource": "Media", 
"description": "One person was struck by an Antelope Valley Line Train near the Palmdale Station, located in the area of Clock Tower Plaza Dr E and Transportation Dr. AV Line tracks between Palmdale and Lancaster remain closed. This incident is closed.", 
"_ts": 1575425976, 
"activitystatus": "CLOSED", 
"postal": "93550", 
"createddated": "2015-12-30 21:46:00 EST", 
"country": "United States", 
"longitude": -118.1194, 
"_attachments": "attachments/", 
"incidenttype": "Train Accident", 
"_etag": "\"13002b74-0000-0100-0000-5de717b80000\""
}
results_fake[0]['location'] = {'type': 'Point','coordinates':[results_fake[0]['longitude'],results_fake[0]['latitude']] }

As you can see I have created an adicional key called location, where I have wrote type and coordinates: longitude and latitude as it is explained in microsoft azure documentation: link

I am trying now to query some data, but I am not obtaining any result, I want to obtain the points within a polygon like this:

query =""" SELECT a.id FROM " + container_id + " a
WHERE ST_WITHIN(a.location, {"type": "Polygon","coordinates": [
[[-90.0280152, 30.265521],
[-90.0335083,30.26315],
[-90.0032959,29.3532687],
[-89.1930542,29.3460904],
[-89.2067871,30.267892],
[-90.0280152,30.265521]]]})"""

for item in client.client_connection.QueryItems("dbs/" + database_id + "/colls/" + container_id,
                            query,
                            {'enableCrossPartitionQuery': True}):

    print(json.dumps(item, indent=True))

But I am not getting any results, what I am doing wrong?

Answer 1

You could test your sql in the azure cosmos portal first.I tested your code,please refer to my test:

import pydocumentdb.document_client as document_client

config = {
    'ENDPOINT': 'https://***.documents.azure.com:443/',
    'MASTERKEY': '***'
};

# Initialize the Python DocumentDB client
client = document_client.DocumentClient(config['ENDPOINT'], {'masterKey': config['MASTERKEY']})

# use a SQL based query to get a bunch of documents
query = { 'query': "SELECT a.id FROM a WHERE ST_WITHIN(a.location, {'type': 'Polygon','coordinates': [[[-90.0280152, 30.265521],[-90.0335083,30.26315],[-90.0032959,29.3532687],[-89.1930542,29.3460904],[-89.2067871,30.267892],[-90.0280152,30.265521]]]})" }

options = {}
options['enableCrossPartitionQuery'] = True

result_iterable = client.QueryDocuments('dbs/db/colls/coll', query, options)

results = list(result_iterable);

print(results)

My sample document:

Output: