Blog

How to use SQL queries for geopoint searches

by on March 17, 2015

Based on my research of the space we are in, Backendless is the only mBaaS platform that lets you use SQL queries when searching for data. The geolocation data managed by Backendless is not an exception. A geopoint may include metadata, which is an arbitrary collection of key/value pairs. Geopoints may be searched for using SQL-based queries. A query must be the “where” part of a traditional SQL statement. It can reference the metadata properties as if they are table columns.

The example below uses the sample data which can be installed into any Backendless backend. The data is a collection of geopoints representing cities around the world. Each geopoint contains metadata with the name of the city. The sample below runs the following SQL query:

city in ('AUSTIN', 'DALLAS')

The query searches for all database where the metadata property name is city and the value may be either 'AUSTIN' or 'DALLAS':

    BackendlessGeoQuery geoQuery = new BackendlessGeoQuery();
    geoQuery.addCategory("geoservice_sample");
    geoQuery.setIncludeMeta(true);
    geoQuery.setWhereClause("city in ('AUSTIN', 'DALLAS')");
    
    Backendless.Geo.getPoints(geoQuery, new AsyncCallback<List>() {
       @Override
       public void handleResponse(List geoPoints) {
           for (GeoPoint geoPoint : geoPoints)
               Log.i(TAG, "GeoPoint - " + geoPoint);
       }
    
       @Override
       public void handleFault(BackendlessFault fault) {
           Log.e(TAG, fault.getMessage());
       }
    });

    val geoQuery = BackendlessGeoQuery()
    geoQuery.addCategory("geoservice_sample")
    geoQuery.isIncludeMeta = true
    geoQuery.whereClause = "city in ('AUSTIN', 'DALLAS')"
    
    Backendless.Geo.getPoints(geoQuery, object : AsyncCallback<List> {
       override fun handleResponse(geoPoints: List) {
           for (geoPoint in geoPoints)
               Log.i(TAG, "GeoPoint - $geoPoint")
       }
    
       override fun handleFault(fault: BackendlessFault) {
           Log.e(TAG, fault.message)
       }
    })

    BackendlessGeoQuery *geoQuery = [BackendlessGeoQuery new];
    geoQuery.categories = @[@"geoservice_sample"];
    geoQuery.includemetadata = YES;
    geoQuery.whereClause = @"city in ('AUSTIN', 'DALLAS')";
        
    [Backendless.shared.geo getPointsWithGeoQuery:geoQuery responseHandler:^(NSArray *points) {
        for (GeoPoint *point in points) {
            NSLog(@"GeoPoint - %@", [point pointDescription]);
        }
    } errorHandler:^(Fault *fault) {
        NSLog(@"Error: %@", fault.message);
    }];

    let geoQuery = BackendlessGeoQuery()
    geoQuery.categories = ["geoservice_sample"]
    geoQuery.includemetadata = true
    geoQuery.whereClause = "city in ('AUSTIN', 'DALLAS')"
            
    Backendless.shared.geo.getPoints(geoQuery: geoQuery, responseHandler: { points in
        for point in points {
            print("GeoPoint - \(point.pointDescription())")
        }
    }, errorHandler: { fault in
        print("Error: \(fault.message ?? "")")
    })

    const Backendless = require('backendless')
    /*
     Or use `import Backendless from 'backendless'` for client side.
     If you don't use npm or yarn to install modules, you can add the following line
     <script src="//api.backendless.com/sdk/js/latest/backendless.min.js"></script>
     to your index.html file and use the global Backendless variable.
    */
    
    Backendless.initApp('YOUR_APP_ID', 'YOUR_JS_API_KEY')
    
    const searchPoints = () => {
      const query = new Backendless.GeoQuery()
      query.categories = ['geoservice_sample']
      query.includeMetadata = true
      query.condition = 'city in (\'AUSTIN\', \'DALLAS\')'
    
      return Backendless.Geo.find(query)
    }
    
    const onSuccess = points => {
      points.forEach(point => {
        console.log(`GeoPoint - ${JSON.stringify(point)}`)
      })
    }
    
    const onError = error => {
      console.error('Server reported an error: ', error.message)
      console.error('error code: ', error.code)
      console.error('http status: ', error.status)
    }
    
    Promise.resolve()
      .then(searchPoints)
      .then(onSuccess)
      .catch(onError)
    

    BackendlessGeoQuery geoQuery = BackendlessGeoQuery()
     ..categories = ["geoservice_sample"]
     ..includeMeta = true
     ..whereClause = "city in ('AUSTIN', 'DALLAS')";
    
    Backendless.Geo.getPoints(query: geoQuery).then((geoPoints) {
     geoPoints.forEach((geoPoint) => print("GeoPoint - $geoPoint"));
    });


    The program produces the following output:

    GeoPoint - GeoPoint{objectId='55DEE179-A7EE-9491-FF5A-6644F8E10200', latitude=43.66663, longitude=-92.97464, categories=[geoservice_sample], metadata={city=AUSTIN}, distance=null}
    GeoPoint - GeoPoint{objectId='D60EFC78-673D-0DA8-FFD7-53C8D8BF8D00', latitude=32.78306, longitude=-96.80667, categories=[geoservice_sample], metadata={city=DALLAS}, distance=null}
    GeoPoint - GeoPoint{objectId='C594A668-B9A2-4D24-FF5A-D4F700DECB00', latitude=30.26715, longitude=-97.74306, categories=[geoservice_sample], metadata={city=AUSTIN}, distance=null}

    Enjoy!