none
Table Storage and Sqlgeography

    Question

  • I have a table in Windows Azure which derives from TableServicesEntity and has columns like Latitude and Longitude.

    var yourLocation = SqlGeography.Point(latitude, longitude, 4326);
    
                var query = from place in this.serviceContext.Places
                            let distance = SqlGeography
                                            .Point(place.Latitude, place.Longitude, 4326)
                                            .STDistance(yourLocation)
                                            .Value
                            where distance < 1 * 1609.344
                            orderby distance
                            select place;

    When I run the above query, I get this error

    query {Error translating Linq expression to URI: Constructing or initializing instances of the type <>f__AnonymousType0`2[Places,System.Double] with the expression Point(fac.Latitude, fac.Longitude, 4326).STDistance(POINT (-74.007118 40.71455)).Value is not supported.}

    Any thoughts ?

    Harsimrat



    Explanation of how to use SQLTypes: http://stackoverflow.com/questions/5312987/c-sharp-find-all-latitude-and-longitude-within-a-mile and the above query is also from the same place.
    • Edited by thukralz Sunday, September 09, 2012 6:16 PM
    Sunday, September 09, 2012 6:13 PM

Answers

  • Table storage isn't SQL. It isn't even a relational database. The only types of queries that are supported are equality (==) and inequality (<, >).

    If I understand correctly what that query is supposed to do, it's supposed to find points within a certain radius of a reference point. I would suggest you pull back a logical square instead: (latitude between x-radius and x+radius, longitude between y-radius and y+radius) and then filter client-side. There's no way to get table storage to do this for you server-side.

    • Marked as answer by thukralz Monday, September 10, 2012 12:34 AM
    Sunday, September 09, 2012 9:27 PM