Lindorm:Query spatiotemporal objects

Construct query conditions

Query conditions, also known as filters, are defined in CQL. CQL is similar to the WHERE clause in an SQL statement and is used to filter data.

Spatial queries

Spatial queries support the predicates in the following table. In the table, Expression represents a spatial object, such as a point, line, or polygon, in Well-Known Text (WKT) format.

• Example 1: Retrieve all spatial objects, such as restaurants, ships, or vehicles, within the spatial range of 120E, 30N, 130E, 40N.

  //Get the DataStore
  DataStore ds = DataStoreFinder.getDataStore(params);
  //Set the query condition
  String schema = "schemaName";
  String stFilter = "bbox(geom, 120,30,130,40)";
  Query query = new Query(schema, ECQL.toFilter(stFilter));
  //Get the query result
  SimpleFeatureCollection features=ds.getFeatureSource(schema).getFeatures(query);

• Example 2: Retrieve all vehicles within a 5 km radius.

  • First, define the 5 km radius as a spatial object (a polygon). For example: (46.9 48.9, 47.1 48.9, 47.1 49.1, 46.9 49.1, 46.9 48.9).

  • Then, use the contains predicate to filter all objects.

  //Get the DataStore
  DataStore ds = DataStoreFinder.getDataStore(params);
  //Set the query condition
  String schema = "schemaName";
  String stFilter =  "contains(‘POLYGON ((46.9 48.9, 47.1 48.9, 47.1 49.1, 46.9 49.1, 46.9 48.9))’, geom)";
  Query query = new Query(schema, ECQL.toFilter(stFilter));
  //Get the query result
  SimpleFeatureCollection features=ds.getFeatureSource(schema).getFeatures(query);

Spatiotemporal queries

Many scenarios require spatiotemporal queries in addition to spatial queries. For example, you can query all vehicle trajectories from the last two hours that are within the range of 20E, 30N, 130E, 40N.

Lindorm GanosBase supports the following time query predicates. In the following table, Expression is the name of the time column, and Time is the time represented as a string in Coordinated Universal Time (UTC).

Lindorm GanosBase supports the following time expression formats:

Example

To query for vehicle trajectories that are within the range of 120E, 30N, 130E, 40N and that occurred between 2014-01-01T11:45:00 and 2014-01-01T12:15:00, you can combine a spatial query and a time query as follows:

//Set the query condition
String schema = "schemaName";
String stFilter = "bbox(geom, 120,30,130,40) AND dtg DURING 2014-01-01T11:45:00.000Z/2014-01-01T12:15:00.000Z";
Query query = new Query(schema, ECQL.toFilter(stFilter));
//Get the query result
SimpleFeatureCollection features=ds.getFeatureSource(schema).getFeatures(query);

Property queries

• Comparison operators include the following: =, <>, >, >=, <, and <=. For example, to select cities with a population greater than 15,000,000, use the condition PERSONS > 15000000. In this example, PERSONS is the field for population size. This naming convention applies to the following examples.

• The BETWEEN operator specifies a range. For example: PERSONS BETWEEN 1000000 AND 3000000.

• Comparison operators also support text values. You can specify a text value to the right of the = operator. For example, CITY_NAME = 'Beijing' selects the city of Beijing. You can also use the LIKE operator, which works the same as in SQL. For example, CITY_NAME LIKE 'N%' selects all cities whose names start with 'N'.

• You can compare two properties. For example, MALE > FEMALE selects cities where the male population is larger than the female population.

• You can construct arithmetic expressions using +, -, *, and /. For example, the filter condition UNEMPLOY / (EMPLOYED + UNEMPLOY) > 0.07 selects all cities with an unemployment rate greater than 7%.

• You can use the IN operator to select properties whose values are within a given set. This works the same as in SQL. For example: ID IN ('cities.1', 'cities.12') or CITY_NAME IN ('Beijing','Shanghai', 'Guangzhou').

• You can use any filter function from GeoServer. For example, strToLowerCase(CITY_NAME) like '%m%' selects cities whose names contain 'm', regardless of case.

• For geometric filtering, you can use BBOX. For example, BBOX(the_geom, -90, 40, -60, 45) selects cities within the range of (-90, 40, -60, 45).

  //Set the query condition
  String schema = "schemaName";
  String filter = "name = 'bob'";
  Query query = new Query(schema, ECQL.toFilter(filter));
  //Get the query result
  SimpleFeatureCollection features=ds.getFeatureSource(schema).getFeatures(query);

Specify returned column names

You can configure the Query object parameters to specify which columns to return. For example:

//Specify the field names to return
String[] returnFields={"geom"};
//Set the query condition
String schema = "schemaName";
String stFilter = "bbox(geom, 120,30,130,40)";
Query query = new Query(schema, ECQL.toFilter(stFilter));
query.setPropertyNames(returnFields);
//Get the result set
SimpleFeatureCollection features=ds.getFeatureSource(schema).getFeatures(query);

Specify the sort order

You can construct a SortBy object to specify which columns to return. For example:

String sortField="name"; //Specify the sort field name
FilterFactory2 ff = CommonFactoryFinder.getFilterFactory2();
SortBy[] sort = new SortBy[]{ff.sort(sortField, order)};
query.setSortBy(sort);
SimpleFeatureCollection features=ds.getFeatureSource(schema).getFeatures(query);