Document DB Location Query

Question

My data looks something like this:

    {
  "id": "a06b42cf-d130-459a-8c89-dab77966747c",
  "propertyBag": {    
    "Fixed": {
      "address": {
        "locationName": "",
        "addressLine1": "1 Microsoft Way",
        "addressLine2": null,
        "city": "Redmond",
        "postalCode": "98052",
        "subDivision": null,
        "state": "WA",
        "country": "USA",
        "location": {
          "type": "Point",
          "coordinates": [
            47.640049,
            -122.129797
          ]
        }
      },
     }
  }
}

Now when I try to query something like this

SELECT * FROM V v 
WHERE ST_DISTANCE(v.propertyBag.Fixed.address.location, { 
    "type": "Point", 
    "coordinates": [47.36, -122.19] 
    }) < 100 * 1000

The results are always empty. I was wondering if someone can please let me know what maybe wrong?

Larry Maccherone · Accepted Answer · 2016-02-07 14:39:06Z

I suspect that you just have the logitude and latitude transposed. Because if I change the document to:

"location": {
  "type": "Point",
  "coordinates": [-122.129797, 47.640049]
}

And I run this query:

SELECT 
  ST_DISTANCE(v.propertyBag.Fixed.address.location, { 
    "type": "Point", 
    "coordinates": [-122.19, 47.36] 
  })
FROM v

I get a result, but if I run it the way you show, I get no results.

In GeoJSON, points are specified with [longitude, latitude] to make it match with our normal expectations of x being east-west, and y being north-south. Unfortunately, this is the opposite of the traditional way of showing GEO coordinates.

-122 is not a valid value for latitude. The range for latitude is -90 to +90. Longitude is specified -180 to +180.

If your database is already populated and you don't feel like migrating it, then you could use a user defined function (UDF) to fix it during the query but I would strongly recommend doing the migration over this approach because geo-spacial indexes won't work as you have it now and your queries will be much slower as a result.

Again, I don't recommend this unless a GEO index is not important, but here is a swapXY UDF that will do the swap:

function(point) { 
    return {
        type: "Point",
        coordinates: [point.coordinates[1], point.coordinates[0]]
    };
}

You use it in a query like this:

SELECT * FROM v 
WHERE 
    ST_DISTANCE(
        udf.swapXY(v.propertyBag.Fixed.address.location), 
        udf.swapXY({
          "type": "Point",
          "coordinates": [47.36, -122.19] 
        })
    ) < 100 * 1000

Collectives™ on Stack Overflow

Document DB Location Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related