Spatial Data Retrieval API

Spatial values retrieved from the database are formatted per the GeoJSON specification.

When the data is retrieved from the database using the Object Retrieval API, columns of the spatial types are formatted as GeoJSON objects. Notice the structure of the location property in the response body below:

cURL  Request:

curl -X GET 'https://xxxx.backendless.app/api/data/Person/first'

Response body:

{  
  "created": 1573915664979,  
  "updated": 1575645151891,  
  "objectId": "5573DC95-79C7-1CDF-FF19-198DC95C4F00",  
  "ownerId": null,  
  "___class": "Person",  
  "location": {  
    "type": "Point",  
    "coordinates": [  
      -96.77016753,  
      32.82433675  
    ],  
    "srsId": 4326,  
    "___class": "com.backendless.persistence.Point"  
  }  
}

Where Clause Operators

A where clause is a condition used in the data retrieval API. The condition identifies the objects which should be retrieved from the database. There are several specialized where clause operators which work specifically with the columns of the spatial data types.

Within Operator

This operator retrieves all data objects whose values for the specified spatial type column are within the boundary of the specified geometry.

Syntax:

Within( spatialColumnName, 'Geometry as WKT' ) = Condition

where:

Argument	Description
spatialColumnName	Name of a spatial type column in the data table where the data objects are retrieved from; Returned objects have values in the spatialColumnName column which are within the geometry identified by 'Geometry as WKT'.
'Geometry as WKT'	A WKT string (must be enclosed into single quotes) which designates the boundary of a shape where Backendless will search for values from the spatialColumnName column;
Condition	True or False

Examples:

Retrieve all data objects where the geometries in the location column are within the specified polygon:

Within(location, 'POLYGON((-154.53617745476788 7.820063820687473, -154.53617745476788 51.13813853838118, -14.87797432976788 51.13813853838118, -14.87797432976788 7.820063820687473, -154.53617745476788 7.820063820687473))') = True

Assuming the location column contains POINT values, retrieve all data objects where the points in location are the same as the specified WKT point:

Within(location, 'POINT (-96.77016753 32.82433675)') = True

Codeless Reference

Consider the following records and locations in the Towns data table:

The example below retrieves all data objects where the geometries in the location column are within the specified polygon:

Within(location, 'POLYGON((-88.981058 41.713514, -88.850866 41.679702, -88.873037 41.647986, -89.015021 41.686395, -88.981058 41.713514))') = True

For more information about properties of this Codeless block, refer to the Basic Object Retrieval topic.

The result of this operation will look as shown below after the Codeless logic runs. As you can see, the operation has returned two objects whose Points(coordinates) are within the geometry of the specified polygon.

Suppose you need to retrieve all data objects where the points in location are the same as the specified WKT point:

Within(location, 'POINT (-88.886726 41.673084)') = True

The result of this operation will look as shown below after the Codeless logic runs. The object returned in the response matches the where clause condition.

Contains Operator

This operator retrieves all data objects whose values for the specified spatial type column complete contain the specified geometry. This operator performs the retrieval in the opposite relationship as Within (see above).

Syntax:

Contains( spatialColumnName, 'Geometry as WKT' ) = Condition

where:

Argument	Description
spatialColumnName	Name of a spatial type column in the data table where the data objects are retrieved from; Returned objects have values in the spatialColumnName column which contain the geometry identified by 'Geometry as WKT'.
'Geometry as WKT'	A WKT string (must be enclosed into single quotes) which designates the boundary of a shape which must be contained within the values from the spatialColumnName column;
Condition	True or False

Examples:

Retrieve all data objects where the geometries in the visitedPlaces column contain the specified point:

Contains( visitedPlaces, 'POINT(-116.396023 36.643763)') = True

Codeless Reference

Consider the following locations(visitedPlaces column) in the userGeolocation data table:

The example below retrieves all data objects where the geometries in the visitedPlaces column contain the specified point:

Contains( visitedPlaces, 'POINT (-87.616849 41.830735)') = True

For more information about properties of this Codeless block, refer to the Basic Object Retrieval topic.

The result of this operation will look as shown below after the Codeless logic runs. The object returned in the response matches the where clause condition.

distanceOnSphere Operator

This operator returns the minimal spherical distance between the POINT values in the column (first argument) and the POINT value in the second argument. This operator can be used to effectively perform a search in radius. The operator returns the value in meters. Backendless identifies all objects whose values for the specified column match the condition.

Syntax:

// search in radius - calculates the distance between the value in the column (first argument) and the point in the second argument   
distanceOnSphere( spatialColumnName, 'POINT as WKT' )  

// calculate the distance between the point in spatialColumnNameFrom and the point in spatialColumnNameTo  
distanceOnSphere( spatialColumnNameFrom, spatialColumnNameTo )

where:

Argument	Description
spatialColumnName	Name of a column of type POINT. The return value is the distance in meters between the value in the spatialColumnName column and the point specified in the second argument
'POINT as WKT'	A WKT string (must be enclosed into single quotes) which designates a POINT for which Backendless will calculate the distance on sphere. The distance is calculated between this point and the POINT values stored in spatialColumnName.
spatialColumnNameFrom	Name of a column of type POINT. The return value is the distance in meters between the value in this column and the value in the same object/record from the spatialColumnNameTo column.
spatialColumnNameTo	Name of a column of type POINT. The return value is the distance in meters between the value in this column and the value in the same object/record from the spatialColumnNameFrom column.

Examples:

Retrieve all data objects whose values in the location column are located within 1000 meters from a point with coordinates -96.823563 33.077570:

distanceOnSphere(location, 'POINT(-96.823563 33.077570)') <= 1000

Retrieve all data objects where the distance between the values in the pickupLocation and the dropoffLocation columns is less than 1000 meters:

distanceOnSphere(pickupLocation, dropoffLocation) <= 1000

Codeless Reference

Consider the following records and locations in the Towns data table:

The example below retrieves all data objects whose values in the location column are located within 9000 meters from a point with coordinates -88.839715 41.581650:

distanceOnSphere(location, 'POINT(-88.839715 41.581650)') <= 9000

For more information about properties of this Codeless block, refer to the Basic Object Retrieval topic.

The result of this operation will look as shown below after the Codeless logic runs. The operation has returned objects representing two towns located within 9000 meters from the specified coordinates point.

GetX

This operator returns the X coordinate (the longitude) for the POINT values stored in the specified column.

Syntax:

GetX( spatialColumnName ) op Value

where:

Argument	Description
spatialColumnName	Name of a column of type POINT. Returned objects have X/longitude values in the spatialColumnName column which satisfy the condition expressed via op Value.
op	one of the following comparison operators: > greater than < less than >= greater than or equals <= less than or equals = equals != not equals
Value	a numeric value to compare the return value of the operator with.

Examples:

Retrieve all data objects whose longitude values in the location column are in the range between 41.92 and 44.66 (the continental United States):

GetX(location) > 41.92 && GetX(location) < 44.66

Codeless Reference

Consider the following locations in the Cities data table:

The example below retrieves all data objects whose longitude values in the location column are in the range between -80.27 and -88.10 (the continental United States):

GetX(location) > -80.27 && GetX(location) < -88.10

After the Codeless logic runs, the operation returns four objects representing cities whose longitude match the where clause condition.

GetY

This operator returns the y coordinate (the latitude) for the POINT values stored in the specified column.

Syntax:

GetY( spatialColumnName ) op Value

where:

Argument	Description
spatialColumnName	Name of a column of type POINT. Returned objects have Y/latitude values in the spatialColumnName column which satisfy the condition expressed via op Value.
op	one of the following comparison operators: > greater than < less than >= greater than or equals <= less than or equals = equals != not equals
Value	a numeric value to compare the return value of the operator with.

Examples:

Retrieve all data objects whose latitude values in the location column are in the range between -124.27 and -117.12 (the continental United States):

GetY(location) > -127.27 && GetY(location) < -117.12

Codeless Reference

Consider the following locations in the Cities data table:

The example below retrieves all data objects whose latitude values in the location column are in the range between 32.20 and 38.15 (the continental United States):

GetY(location) > 32.20 && GetY(location) < 38.15

After the Codeless logic runs, the operation returns three objects representing cities whose latitude match the where clause condition.

AsWKT

This operator returns the WKT representation for the values stored in the specified column.

Syntax:

AsWKT(spatialColumnName) op Value

where:

Argument	Description
spatialColumnName	Name of a column values of which will be used to obtain the WKT representation from.
op	one of the following comparison operators: = equals != not equals
Value	a string value to compare the return value of the operator with.

Examples:

Retrieve all data objects which match the specified WKT representation

AsWKT(location) = 'POINT(-124.27 41.92)' 

Codeless Reference

Consider the following locations in the Cities data table:

The example below retrieves the data object whose longitude and latitude values in the location column are -84.401959 33.726826 (the continental United States):

AsWKT(location) = 'POINT(-84.401959 33.726826)' 

After the Codeless logic runs, the operation returns one object whose longitude and latitude match the where clause condition.

AsGeoJSON

This operator returns the GeoJSON representation for the values stored in the specified column.

Syntax:

AsGeoJSON(spatialColumnName) op Value

where:

Argument	Description
spatialColumnName	Name of a column values of which will be used to obtain the GeoJSON representation from.
op	one of the following comparison operators: = equals != not equals
Value	a string value to compare the return value of the operator with.

Examples:

Retrieve all data objects which match the specified GeoJSON representation

AsGeoJSON(location) = 'TBD'

GeoHash

For a given location on earth, the GeoHash function converts its latitude and longitude into a string. This string is the Geohash and will determine which of the predefined regions the point belongs to. When generating a Geohash you can specify the desired length which controls the precision. Generally, points within close geographical proximity will have the same Geohash.

Geohashes are a way to represent locations on the Earth using a simple code made up of numbers (0 to 9) and letters (A to Z, but without A, I, L, and O). Think of the Earth as covered by a big grid that is divided into 32 squares. Each square is one part of this grid. The first character of a geohash points to one of these squares, showing a broad area.

Inside each of these big squares, there are 32 smaller squares, and each of those smaller squares has another 32 even smaller squares inside it. This pattern keeps going, getting more detailed the more characters you add to the geohash. By adding more characters to a geohash, you're like zooming in on a map, going from looking at a large region to focusing on a specific spot.

Syntax:

GeoHash(pointColumnName, maxLength)

where:

Argument	Description
pointColumnName	Name of a column to generate a geohash value for. The column must be of the POINT data type.
maxLength	Optional parameter. A positive integer value, must not exceed 100.

Return value:

The function returns a geohash string based on the location data from the specified column. This geohash string will be no longer than the maximum length you set, up to a limit of 100 characters. However, the string could end up being shorter than this maximum length. This happens because the function keeps adding details to the geohash until it accurately represents the location or until it reaches the maximum length, whichever occurs first.

There are scenarios where your application might require geohash values to be generated automatically for location points stored in the database. For these situations, you can set up a generated column that automatically creates these geohash values for you. The process for creating this column is demonstrated in the screenshot below. This example shows how to create a generated column that will hold the geohash values corresponding to the location points in the Location column.

Notice the expression for the generated column uses a variation of the GeoHash function that applies only to the generated column expressions:

ST_GeoHash( pointColumnName, maxLength )

PointFromGeoHash

The function takes a geohash value and converts it into a point, using Backendless's internal format for representing locations. To make this point usable in your applications, you can convert it into a more familiar format. Use the AsWKT function to get the point in the Well-Known Text (WKT) format, which is a text markup language for representing geometric shapes. Alternatively, use the AsGeoJSON function to convert the point into its JSON format, making it easy to work with in web applications or services that use JSON.

Syntax:

PointFromGeoHash(geoHashValue or columnName)

where:

Argument	Description
geoHashValue	A value created with the GeoHash function. This can be either a literal value or name of a column containing the geohash value.

Examples:

Convert the value in the geoHash column to WKT:

AsWKT(PointFromGeoHash(geoHashValue))

Convert the value in the geoHash column to GeoJSON:

AsGeoJSON(PointFromGeoHash(geoHashValue))