Using a distance function as a column selection

The following two Gosu code examples use the Distance database function.

The first code example constructs a query that returns all unique addresses within ten miles of San Mateo along with their respective distances from San Mateo in miles. The code example then prints the results of the query.

In particular, the code example sets up a row query, addressQuery. For all distinct Address entity instances in the database, this row query returns the following columns: Line1, City, State, PostalCode, and Distance. Constructing the last column, Distance, involves calling the Distance method.

The Distance method returns a simple function. This function returns the distance between the location of an entity instance and a common reference location.

As a first parameter, the Distance method takes a row query with a proximity or spatial restriction. Due to a call to the withinDistance method, the addressQuery row query qualifies as having such a restriction.

As a second parameter, the Distance method takes the String name of a location property. The addressQuery row query rows represent Address entity instances. These entity instances all have a SpatialPoint location property. Hence, "Address.SpatialPoint" is the String name of a location property.

The Distance method uses these two parameters to calculate the distance between each location that the SpatialPoint property values represent and the reference location for the restriction, San Mateo. The resulting distances supply the Distance column with values. The first code example is as follows:

uses gw.api.database.DBFunction
uses gw.api.path.Paths
uses gw.api.database.spatial.SpatialPoint
uses gw.api.database.Query
uses gw.api.database.QuerySelectColumns

// Make a query on the Address entity. Remove duplicate entities from the query.
var addressQuery = Query.make(Address).withDistinct(true)

// Define the location of the center point for the distance calculation.
var SAN_MATEO = new SpatialPoint(-122.300, 37.550)

// Specify the spatial point in the address instance and the maximum distance from the center. 
addressQuery.withinDistance(Address.SPATIALPOINT_PROP.get(), SAN_MATEO, 10, UnitOfDistance.TC_MILE)

// Define the columns for the row query.
var result = addressQuery.select({
  QuerySelectColumns.pathWithAlias("Line1", Paths.make(Address#AddressLine1)),
  QuerySelectColumns.pathWithAlias("City", Paths.make(Address#City)),
  QuerySelectColumns.pathWithAlias("State", Paths.make(Address#State)),
  QuerySelectColumns.pathWithAlias("PostalCode", Paths.make(Address#PostalCode)),
  QuerySelectColumns.dbFunctionWithAlias("Distance", DBFunction.Distance(addressQuery, "Address.SpatialPoint"))
})

// Print the results of the query.
for (address in result) {
  print(address.getColumn("Line1") + ", "
    + address.getColumn("City") + ", "
    + address.getColumn("State") + ", "
    + address.getColumn("PostalCode")
    + " is " + address.getColumn("Distance") + " miles away from San Mateo")
}

The second code example constructs a row query, personQuery. This query returns the name, primary address, and distance from San Mateo for all people in the database having a primary address that is within ten miles of San Mateo. The code example then prints the results of the query.

The call to the Distance method takes the row query, personQuery, as a first parameter. As a second parameter, the Distance method takes the String name of the location property that pinpoints the primary address of each identified Person. This String name is "Person.PrimaryAddress.SpatialPoint." The second code example is as follows:

uses gw.api.database.DBFunction
uses gw.api.path.Paths
uses gw.api.database.spatial.SpatialPoint
uses gw.api.database.Query
uses gw.api.database.QuerySelectColumns

// Make a query on the Person entity.
var personQuery = Query.make(Person)

// Explicitly join the Address entity for the primary address to the Person.
var addressTable = personQuery.join(Person#PrimaryAddress)

// Define the location of the center point for the distance calculation.
var SAN_MATEO = new SpatialPoint(-122.300, 37.550)

// Specify the spatial point in the person's primary address and the maximum distance from the center. 
addressTable.withinDistance(Address.SPATIALPOINT_PROP.get(), "Person.PrimaryAddress.SpatialPoint", SAN_MATEO, 10, UnitOfDistance.TC_MILE)

// Define the columns for the row query.
var result = personQuery.select({
  QuerySelectColumns.path(Paths.make(Person#FirstName)),
  QuerySelectColumns.path(Paths.make(Person#LastName)),
  QuerySelectColumns.pathWithAlias("Line1", Paths.make(Person#PrimaryAddress, Address#AddressLine1)),
  QuerySelectColumns.pathWithAlias("City", Paths.make(Person#PrimaryAddress, Address#City)),
  QuerySelectColumns.pathWithAlias("State", Paths.make(Person#PrimaryAddress, Address#State)),
  QuerySelectColumns.pathWithAlias("PostalCode", Paths.make(Person#PrimaryAddress, Address#PostalCode)),
  QuerySelectColumns.dbFunctionWithAlias("Distance", DBFunction.Distance(personQuery, "Person.PrimaryAddress.SpatialPoint"))
})

// Print the results of the query.
for (address in result) {
  print(
    address.getColumn("Person.FirstName") + " "
      + address.getColumn("Person.LastName") + ", "
      + address.getColumn("Line1") + ", "
      + address.getColumn("City") + ", "
      + address.getColumn("State") + ", "
      + address.getColumn("PostalCode")
      + " is " + address.getColumn("Distance") + " miles away from San Mateo")
}