Add Advanced Geospatial Processing with Hibernate

Open the file IncidentRepository.java from the directory src/main/java/com/sap/hana/hibernate/sample/repositories.

In all queries, replace the part starting with

and i.y between

until the last

(cast(:distance as double) / 111319))

with a geospatial function. The function to be used is dwithin which calculates whether a location is within a given distance of another location.

Before

query = this.em.createQuery(
    "select i from Incident i "
        + "where i.category in :category "
        + "  and i.date between :dateFrom and :dateTo "
        + "  and i.x between (x(:location) - (cast(:distance as double) / 111319)) "
        + "    and (x(:location) + (cast(:distance as double) / 111319)) "
        + "  and i.y between (y(:location) - (cast(:distance as double) / 111319)) "
        + "    and (y(:location) + (cast(:distance as double) / 111319)) "
        + "order by i.date desc",
    Incident.class );

After

query = this.em.createQuery(
    "select i from Incident i "
        + "where i.category in :category "
        + "  and i.date between :dateFrom and :dateTo "
        + "  and dwithin(i.location, :location, :distance) = true "
        + "order by i.date desc",
    Incident.class );

As you can immediately see, the query is now much easier to read, doesn’t contain any magic constants and performs the correct calculations taking into account the earth’s geometry.

Do this adjustment for all 4 queries in the file.

public Page<Incident> findByLocationNear(Point<G2D> location, Distance distance, Date dateFrom, Date dateTo,
    List<String> category, Pageable pageable) {
  TypedQuery<Incident> query;
  if ( category == null || category.isEmpty() ) {
    query = this.em.createQuery(
        "select i from Incident i "
            + "where i.date between :dateFrom and :dateTo "
            + "  and dwithin(i.location, :location, :distance) = true "
            + "order by i.date desc",
        Incident.class );
  }
  else {
    query = this.em.createQuery(
        "select i from Incident i "
            + "where i.category in :category "
            + "  and i.date between :dateFrom and :dateTo "
            + "  and dwithin(i.location, :location, :distance) = true "
            + "order by i.date desc",
        Incident.class );
    query.setParameter( "category", category );
  }

  query.setParameter( "dateFrom", dateFrom );
  query.setParameter( "dateTo", dateTo );
  query.setParameter( "location", location );
  query.setParameter( "distance", distance );

  query.setFirstResult( (int) pageable.getOffset() );
  query.setMaxResults( pageable.getPageSize() );

  Query countQuery;
  if ( category == null || category.isEmpty() ) {
    countQuery = this.em.createQuery(
        "select count(i) from Incident i "
            + "where i.date between :dateFrom and :dateTo "
            + "  and dwithin(i.location, :location, :distance) = true " );

  }
  else {
    countQuery = this.em.createQuery(
        "select count(i) from Incident i "
            + "where i.category in :category "
            + "  and i.date between :dateFrom and :dateTo "
            + "  and dwithin(i.location, :location, :distance) = true " );
    countQuery.setParameter( "category", category );
  }

  countQuery.setParameter( "dateFrom", dateFrom );
  countQuery.setParameter( "dateTo", dateTo );
  countQuery.setParameter( "location", location );
  countQuery.setParameter( "distance", distance );

  long count = ( (Long) countQuery.getSingleResult() ).longValue();

  return new PageImpl<>( query.getResultList(), pageable, count );
}

Save the IncidentRepository.java file.

Open the file IncidentLocationAndCountRepository.java from the directory src/main/java/com/sap/hana/hibernate/sample/repositories.

Like in the previous step, replace the part starting with

and i.y between

until the last

(cast(:distance as double) / 111319))

with a geospatial function in all queries.

In addition, we can now group the result by the geospatial location directly instead of the individual X and Y coordinates and use the location as the input of the com.sap.hana.hibernate.sample.entities.IncidentLocationAndCount data transfer object (DTO).

Before

query = this.em.createQuery(
    "select new com.sap.hana.hibernate.sample.entities.IncidentLocationAndCount(i.x, i.y, count(*)) "
        + "from Incident i "
        + "where i.category in :category "
        + "  and i.date between :dateFrom and :dateTo "
        + "  and i.x between (x(:location) - (cast(:distance as double) / 111319)) "
        + "    and (x(:location) + (cast(:distance as double) / 111319)) "
        + "  and i.y between (y(:location) - (cast(:distance as double) / 111319)) "
        + "    and (y(:location) + (cast(:distance as double) / 111319)) "
        + "group by i.x, i.y",
    IncidentLocationAndCount.class );

After

query = this.em.createQuery(
    "select new com.sap.hana.hibernate.sample.entities.IncidentLocationAndCount(i.location, count(*)) "
        + "from Incident i "
        + "where i.category in :category "
        + "  and i.date between :dateFrom and :dateTo "
        + "  and dwithin(i.location, :location, :distance) = true "
        + "group by i.location",
    IncidentLocationAndCount.class );

Do these adjustments for both queries in the file.

public List<IncidentLocationAndCount> findByLocationAndCategory(Point<G2D> location, Distance distance,
    Date dateFrom, Date dateTo, List<String> category) {
  TypedQuery<IncidentLocationAndCount> query;
  if ( category == null || category.isEmpty() ) {
    query = this.em.createQuery(
        "select new com.sap.hana.hibernate.sample.entities.IncidentLocationAndCount(i.location, count(*)) "
            + "from Incident i "
            + "where i.date between :dateFrom and :dateTo "
            + "  and dwithin(i.location, :location, :distance) = true "
            + "group by i.location",
        IncidentLocationAndCount.class );
  }
  else {
    query = this.em.createQuery(
        "select new com.sap.hana.hibernate.sample.entities.IncidentLocationAndCount(i.location, count(*)) "
            + "from Incident i "
            + "where i.category in :category "
            + "  and i.date between :dateFrom and :dateTo "
            + "  and dwithin(i.location, :location, :distance) = true "
            + "group by i.location",
        IncidentLocationAndCount.class );
    query.setParameter( "category", category );
  }

  query.setParameter( "dateFrom", dateFrom );
  query.setParameter( "dateTo", dateTo );
  query.setParameter( "location", location );
  query.setParameter( "distance", distance );

  return query.getResultList();
}

Save the IncidentLocationAndCountRepository.java file.

With the geospatial changes in place we can now deploy the application again to the cloud.

In a console run the following command from the root directory of the project

mvn clean appengine:update

The application will be deployed to the Google App Engine.

This deployment should be much faster than the initial deployment since the large data files don’t need to be transferred again and the database has already been loaded.

After the deployment has succeeded you can navigate to https://<your project ID>.appspot.com to see the changes.

While the application now processes the incidents correctly, it does so rather slowly. The reason for this is the way that the geospatial data is stored inside the database. In the next steps you’ll implement improvements for dealing with the geospatial data.

For an introduction of what spatial reference systems (SRS) are, check out the tutorial Spatial Reference Systems.

The default spatial reference system used by the application is WGS 84 (SRID 4326). It is a round-earth SRS which means that the calculations are reasonably accurate, but computationally expensive.

Since the data the application uses is restricted to a relatively small area (the city limits of San Francisco), we can use a planar SRS specifically for that region. During deployment the application has already installed the SRS NAD83(2011) / San Francisco CS13 with SRID 7131 into the database. This SRS is a planar SRS for the San Francisco area which should give reasonable accuracy while being computationally much cheaper.

Open the file DatabaseInitializer.java from the directory src/main/java/com/sap/hana/hibernate/sample/app.

Find the method transformIncidentLocations and change

// @EventListener

to

@EventListener

to make the method a Spring event listener.

  /**
   * Trigger the transformation of incident locations if necessary
   *
   * @param event The context event
   */
  @EventListener
  @Transactional
  @Order(10)
  public void transformIncidentLocations(ContextRefreshedEvent event) {
  ...
  }

During application startup the method will now be called and transform the locations from the current SRS 4326 to the SRS 7131.

Save the DatabaseInitializer.java file.

Now that the locations are available in an optimized representation, you’ll need to update the application to make use of the new data.

Open the file IncidentRepository.java from the directory src/main/java/com/sap/hana/hibernate/sample/repositories.

In all queries, replace the column i.location (the location in SRS 4326) with i.mapLocation (the location in SRS 7131).

In addition, the input location which is still represented in SRS 4326 must be converted to SRS 7131 before doing the geospatial computations. For this, Hibernate Spatial provides a transform function which can be used to transform a geometry from one SRS to another. To make use of the function, replace all occurrences of :location with transform(:location, 7131).

Before

query = this.em.createQuery(
    "select i from Incident i "
        + "where i.category in :category "
        + "  and i.date between :dateFrom and :dateTo "
        + "  and dwithin(i.location, :location, :distance) = true "
        + "order by i.date desc",
    Incident.class );

After

query = this.em.createQuery(
    "select i from Incident i "
        + "where i.category in :category "
        + "  and i.date between :dateFrom and :dateTo "
        + "  and dwithin(i.mapLocation, transform(:location, 7131), :distance) = true "
        + "order by i.date desc",
    Incident.class );

Do these adjustments for all 4 queries in the file.

public Page<Incident> findByLocationNear(Point<G2D> location, Distance distance, Date dateFrom, Date dateTo,
    List<String> category, Pageable pageable) {
  TypedQuery<Incident> query;
  if ( category == null || category.isEmpty() ) {
    query = this.em.createQuery(
        "select i from Incident i "
            + "where i.date between :dateFrom and :dateTo "
            + "  and dwithin(i.mapLocation, transform(:location, 7131), :distance) = true "
            + "order by i.date desc",
        Incident.class );
  }
  else {
    query = this.em.createQuery(
        "select i from Incident i "
            + "where i.category in :category "
            + "  and i.date between :dateFrom and :dateTo "
            + "  and dwithin(i.mapLocation, transform(:location, 7131), :distance) = true "
            + "order by i.date desc",
        Incident.class );
    query.setParameter( "category", category );
  }

  query.setParameter( "dateFrom", dateFrom );
  query.setParameter( "dateTo", dateTo );
  query.setParameter( "location", location );
  query.setParameter( "distance", distance );

  query.setFirstResult( (int) pageable.getOffset() );
  query.setMaxResults( pageable.getPageSize() );

  Query countQuery;
  if ( category == null || category.isEmpty() ) {
    countQuery = this.em.createQuery(
        "select count(i) from Incident i "
            + "where i.date between :dateFrom and :dateTo "
            + "  and dwithin(i.mapLocation, transform(:location, 7131), :distance) = true " );

  }
  else {
    countQuery = this.em.createQuery(
        "select count(i) from Incident i "
            + "where i.category in :category "
            + "  and i.date between :dateFrom and :dateTo "
            + "  and dwithin(i.mapLocation, transform(:location, 7131), :distance) = true " );
    countQuery.setParameter( "category", category );
  }

  countQuery.setParameter( "dateFrom", dateFrom );
  countQuery.setParameter( "dateTo", dateTo );
  countQuery.setParameter( "location", location );
  countQuery.setParameter( "distance", distance );

  long count = ( (Long) countQuery.getSingleResult() ).longValue();

  return new PageImpl<>( query.getResultList(), pageable, count );
}

Save the IncidentRepository.java file.

Open the file IncidentLocationAndCountRepository.java from the directory src/main/java/com/sap/hana/hibernate/sample/repositories.

Like in the previous step, replace i.location with i.mapLocation and :location with transform(:location, 7131) in all queries.

In addition, we need to convert the location back to SRS 4326 before storing it in the com.sap.hana.hibernate.sample.entities.IncidentLocationAndCount data transfer object (DTO) so it can be displayed in the UI. To do this, replace

select new com.sap.hana.hibernate.sample.entities.IncidentLocationAndCount(i.mapLocation, count(*))

with

select new com.sap.hana.hibernate.sample.entities.IncidentLocationAndCount(transform(i.mapLocation, 4326), count(*))

Before

query = this.em.createQuery(
    "select new com.sap.hana.hibernate.sample.entities.IncidentLocationAndCount(i.location, count(*)) "
        + "from Incident i "
        + "where i.category in :category "
        + "  and i.date between :dateFrom and :dateTo "
        + "  and dwithin(i.location, :location, :distance) = true "
        + "group by i.location",
    IncidentLocationAndCount.class );

After

query = this.em.createQuery(
    "select new com.sap.hana.hibernate.sample.entities.IncidentLocationAndCount(transform(i.mapLocation, 4326), count(*)) "
        + "from Incident i "
        + "where i.category in :category "
        + "  and i.date between :dateFrom and :dateTo "
        + "  and dwithin(i.mapLocation, transform(:location, 7131), :distance) = true "
        + "group by i.mapLocation",
    IncidentLocationAndCount.class );

Do these adjustments for both queries in the file.

public List<IncidentLocationAndCount> findByLocationAndCategory(Point<G2D> location, Distance distance,
    Date dateFrom, Date dateTo, List<String> category) {
  TypedQuery<IncidentLocationAndCount> query;
  if ( category == null || category.isEmpty() ) {
    query = this.em.createQuery(
        "select new com.sap.hana.hibernate.sample.entities.IncidentLocationAndCount(transform(i.mapLocation, 4326), count(*)) "
            + "from Incident i "
            + "where i.date between :dateFrom and :dateTo "
            + "  and dwithin(i.mapLocation, transform(:location, 7131), :distance) = true "
            + "group by i.mapLocation",
        IncidentLocationAndCount.class );
  }
  else {
    query = this.em.createQuery(
        "select new com.sap.hana.hibernate.sample.entities.IncidentLocationAndCount(transform(i.mapLocation, 4326), count(*)) "
            + "from Incident i "
            + "where i.category in :category "
            + "  and i.date between :dateFrom and :dateTo "
            + "  and dwithin(i.mapLocation, transform(:location, 7131), :distance) = true "
            + "group by i.mapLocation",
        IncidentLocationAndCount.class );
    query.setParameter( "category", category );
  }

  query.setParameter( "dateFrom", dateFrom );
  query.setParameter( "dateTo", dateTo );
  query.setParameter( "location", location );
  query.setParameter( "distance", distance );

  return query.getResultList();
}

Save the IncidentLocationAndCountRepository.java file.

With the changes in place we can now deploy the application again to the cloud.

In a console run the following command from the root directory of the project

mvn clean appengine:update

The application will be deployed to the Google App Engine.

After the deployment has succeeded you can navigate to https://<your project ID>.appspot.com to see the changes.

The retrieval of the incidents should be much faster now.

In the text area below enter the name of the Hibernate Spatial function that is used to compute the set of locations within a given distance of a given location.