Table of Contents

Find Nearby Delivery Drivers with Micronaut Data JDBC and MySQL Geospatial

Learn how to use Micronaut Data JDBC and MySQL spatial queries to find the closest available delivery driver within 5 km of a food delivery order.

Authors: Milenko Supic

Micronaut Version: 5.0.2

1. Getting Started

In this guide, we will create a Micronaut application written in Groovy.

In this guide, you will build the dispatch part of a food delivery application. When a customer places an order, the application stores driver positions in an SRID-restricted MySQL POINT column and asks Micronaut Data JDBC for available drivers within 5 km of the order location. The dispatch service then chooses the closest returned candidate.

The sample uses WGS 84 coordinates (SRID 4326), the coordinate system commonly used by GPS. Srid.CrsType.GEOGRAPHIC tells Micronaut Data to use the MySQL spherical distance function for the 5 km spatial predicate.

2. What you will need

To complete this guide, you will need the following:

3. Solution

We recommend that you follow the instructions in the next sections and create the application step by step. However, you can go right to the completed example.

4. Writing the Application

Create an application using the Micronaut Command Line Interface or with Micronaut Launch.

mn create-app example.micronaut.micronautguide \
    --features=data-jdbc,liquibase,mysql,serialization-jackson,validation \
    --build=gradle \
    --lang=groovy \
    --test=spock
If you don’t specify the --build argument, Gradle with the Kotlin DSL is used as the build tool.
If you don’t specify the --lang argument, Java is used as the language.
If you don’t specify the --test argument, JUnit is used for Java and Kotlin, and Spock is used for Groovy.

The previous command creates a Micronaut application with the default package example.micronaut in a directory named micronautguide.

If you use Micronaut Launch, select Micronaut Application as application type and add data-jdbc, liquibase, mysql, serialization-jackson, and validation features.

If you have an existing Micronaut application and want to add the functionality described here, you can view the dependency and configuration changes from the specified features, and apply those changes to your application.

Diff

Setup IntelliJ IDEA to develop Micronaut Applications.

4.1. MySQL Driver

Add also the MySQL Driver

build.gradle
runtimeOnly("com.mysql:mysql-connector-j")

Micronaut Data builds repository queries at compilation time. Add the Jakarta Persistence API as a compile-only dependency so the compile-time query model has the Criteria API types available:

build.gradle
compileOnly("jakarta.persistence:jakarta.persistence-api")

4.2. Database Configuration

And the database configuration:

src/main/resources/application.properties
datasources.default.schema-generate=NONE
(1)
datasources.default.driver-class-name=com.mysql.cj.jdbc.Driver
(2)
datasources.default.db-type=mysql
(3)
datasources.default.dialect=MYSQL
1 Use MySQL driver.
2 In order for the database to be properly detected by Micronaut Test Resources.
3 Configure the MySQL dialect.
src/main/resources/application.properties
test-resources.containers.mysql.startup-timeout=600s

The MySQL container image can take a while to download and initialize the first time. The container startup timeout and generated build’s Test Resources client timeout give Micronaut Test Resources enough time to pull the image and start the container.

4.3. Database Migration with Liquibase

We need a way to create the database schema. For that, we use Micronaut integration with Liquibase.

Add the following snippet to include the necessary dependencies:

build.gradle
implementation("io.micronaut.liquibase:micronaut-liquibase")

Configure the database migrations directory for Liquibase in application.properties.

src/main/resources/application.properties
liquibase.datasources.default.change-log=classpath\:db/liquibase-changelog.xml

Create the following files with the database schema creation:

src/main/resources/db/liquibase-changelog.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
  <include file="changelog/01-schema.xml" relativeToChangelogFile="true"/>
</databaseChangeLog>
src/main/resources/db/changelog/01-schema.xml
<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
  <changeSet id="01" author="micronaut-guides">
    <sql>
      CREATE TABLE delivery_driver (
        id BIGINT NOT NULL AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        status VARCHAR(32) NOT NULL,
        location POINT NOT NULL SRID 4326,
        CONSTRAINT pk_delivery_driver PRIMARY KEY (id)
      )
    </sql>

    <sql>
      CREATE SPATIAL INDEX idx_delivery_driver_location ON delivery_driver(location)
    </sql>
  </changeSet>
</databaseChangeLog>

The migration creates the delivery_driver table with a MySQL POINT NOT NULL SRID 4326 column and a spatial index. The SRID attribute restricts the column to WGS 84 coordinates and lets MySQL’s query optimizer use the spatial index. In production, keep the spatial column and index statements in your migration tool instead of relying on test-only schema generation.

5. Domain Model

Create a DeliveryDriver entity:

src/main/groovy/example/micronaut/DeliveryDriver.groovy
package example.micronaut

import groovy.transform.CompileStatic
import io.micronaut.data.annotation.GeneratedValue
import io.micronaut.data.annotation.Id
import io.micronaut.data.annotation.Index
import io.micronaut.data.annotation.MappedEntity
import io.micronaut.data.annotation.Srid
import io.micronaut.data.model.geo.Point

import jakarta.validation.constraints.NotBlank
import jakarta.validation.constraints.NotNull

@CompileStatic
@MappedEntity('delivery_driver') (1)
class DeliveryDriver {

    enum Status {
        AVAILABLE,
        BUSY
    }

    @Id
    @GeneratedValue
    Long id

    @NotBlank
    String name

    @NotNull
    Status status (2)

    @NotNull
    @Srid(value = 4326, type = Srid.CrsType.GEOGRAPHIC) (3)
    @Index(columns = 'location') (4)
    Point location

    DeliveryDriver() {
    }

    DeliveryDriver(String name, Status status, Point location) {
        this.name = name
        this.status = status
        this.location = location
    }
}

Micronaut Data geospatial model types use GeoJSON conversion by default. MySQL stores the value in the POINT NOT NULL SRID 4326 column declared in the Liquibase migration.

1 Map the entity to the delivery_driver table created by Liquibase.
2 Represent whether a driver is available or busy so dispatch queries can filter drivers before applying the spatial predicate.
3 Use SRID 4326 with Srid.CrsType.GEOGRAPHIC for GPS-style longitude and latitude coordinates.
4 Mark the location column as spatially indexed in the Micronaut Data model.

6. Repository

Create a repository that declares the MySQL dialect:

src/main/groovy/example/micronaut/DeliveryDriverRepository.groovy
package example.micronaut

import io.micronaut.data.jdbc.annotation.JdbcRepository
import io.micronaut.data.model.geo.Point
import io.micronaut.data.model.query.builder.sql.Dialect
import io.micronaut.data.repository.CrudRepository

@JdbcRepository(dialect = Dialect.MYSQL) (1)
interface DeliveryDriverRepository extends CrudRepository<DeliveryDriver, Long> {

    List<DeliveryDriver> findByStatusAndLocationNear(DeliveryDriver.Status status, Point orderLocation, double maxDistanceMeters) (2)
}
1 @JdbcRepository tells Micronaut Data to generate a MySQL JDBC repository.
2 The Near predicate is translated to MySQL ST_Distance_Sphere(…​) ⇐ ?.

7. Dispatch Service

The repository returns available drivers within 5 km of the order location. The service then selects the nearest candidate:

src/main/groovy/example/micronaut/DispatchService.groovy
package example.micronaut

import groovy.transform.CompileStatic
import io.micronaut.data.model.geo.Point
import jakarta.inject.Singleton

@CompileStatic
@Singleton
class DispatchService {

    private static final double MAX_DISTANCE_METERS = 5_000d (1)
    private static final double WGS84_A = 6378137.0d
    private static final double WGS84_F = 1.0d / 298.257223563d
    private static final double EPS = 1e-15d

    private final DeliveryDriverRepository deliveryDriverRepository

    DispatchService(DeliveryDriverRepository deliveryDriverRepository) {
        this.deliveryDriverRepository = deliveryDriverRepository
    }

    DriverMatch findClosestAvailableDriver(Point orderLocation) {
        List<DeliveryDriver> candidates = deliveryDriverRepository.findByStatusAndLocationNear(
            DeliveryDriver.Status.AVAILABLE,
            orderLocation,
            MAX_DISTANCE_METERS
        ) (2)

        DriverMatch closest = null
        for (DeliveryDriver driver : candidates) {
            DriverMatch match = new DriverMatch(
                driver.id,
                driver.name,
                distanceMeters(orderLocation, driver.location)
            )
            if (closest == null || match.distanceMeters < closest.distanceMeters) {
                closest = match
            }
        }
        return closest (3)
    }

    private static double distanceMeters(Point left, Point right) { (4)
        double lon1Deg = left.x()
        double lat1Deg = left.y()

        double lon2Deg = right.x()
        double lat2Deg = right.y()

        validate(lon1Deg, lat1Deg)
        validate(lon2Deg, lat2Deg)

        double lon1 = Math.toRadians(lon1Deg)
        double lat1 = Math.toRadians(lat1Deg)
        double lon2 = Math.toRadians(lon2Deg)
        double lat2 = Math.toRadians(lat2Deg)

        if (near(lon1, lon2) && near(lat1, lat2)) {
            return 0.0d
        }

        double dLon = lon2 - lon1

        double sinLat1 = Math.sin(lat1)
        double cosLat1 = Math.cos(lat1)
        double sinLat2 = Math.sin(lat2)
        double cosLat2 = Math.cos(lat2)

        double cosD = sinLat1 * sinLat2 + cosLat1 * cosLat2 * Math.cos(dLon)
        cosD = Math.max(-1.0d, Math.min(1.0d, cosD))

        double d = Math.acos(cosD)
        double sinD = Math.sin(d)

        double k = square(sinLat1 - sinLat2)
        double l = square(sinLat1 + sinLat2)

        double h = near(1.0d - cosD, 0.0d) ? 0.0d : (d + 3.0d * sinD) / (1.0d - cosD)
        double g = near(1.0d + cosD, 0.0d) ? 0.0d : (d - 3.0d * sinD) / (1.0d + cosD)

        double correction = -(WGS84_F / 4.0d) * (h * k + g * l)

        WGS84_A * (d + correction)
    }

    private static void validate(double lon, double lat) {
        if (!Double.isFinite(lon) || !Double.isFinite(lat)) {
            throw new IllegalArgumentException('Coordinates must be finite numbers')
        }
        if (lon <= -180.0d || lon > 180.0d) {
            throw new IllegalArgumentException('Longitude must be in (-180, 180]')
        }
        if (lat < -90.0d || lat > 90.0d) {
            throw new IllegalArgumentException('Latitude must be in [-90, 90]')
        }
    }

    private static boolean near(double a, double b) {
        Math.abs(a - b) <= EPS
    }

    private static double square(double x) {
        x * x
    }
}
1 Query radius is 5,000 meters for the dispatch rule.
2 Ask Micronaut Data for available drivers within 5 km of the order location.
3 Select the closest candidate in application code using a geodetic distance calculation.
4 Calculate an approximate geodetic distance between the order and driver locations using WGS 84 coordinates. Point.x() is longitude and Point.y() is latitude, so the method validates coordinate ranges, converts degrees to radians, computes the central angle between the points, and applies a WGS 84 ellipsoid flattening correction before returning meters.

Create a small response object for the match:

src/main/groovy/example/micronaut/DriverMatch.groovy
package example.micronaut

import groovy.transform.CompileStatic
import io.micronaut.serde.annotation.Serdeable

@CompileStatic
@Serdeable
class DriverMatch {

    final Long driverId
    final String name
    final double distanceMeters

    DriverMatch(Long driverId, String name, double distanceMeters) {
        this.driverId = driverId
        this.name = name
        this.distanceMeters = distanceMeters
    }
}

8. Controller

Expose the dispatch operation through an HTTP endpoint:

src/main/groovy/example/micronaut/DeliveryDispatchController.groovy
package example.micronaut

import groovy.transform.CompileStatic
import io.micronaut.core.annotation.Nullable
import io.micronaut.data.model.geo.Point
import io.micronaut.http.annotation.Controller
import io.micronaut.http.annotation.Get
import io.micronaut.http.annotation.QueryValue

@CompileStatic
@Controller('/orders') (1)
class DeliveryDispatchController {

    private final DispatchService dispatchService

    DeliveryDispatchController(DispatchService dispatchService) {
        this.dispatchService = dispatchService
    }

    @Get('/nearest-driver') (2)
    @Nullable
    DriverMatch nearestDriver(@QueryValue double longitude, @QueryValue double latitude) { (3)
        dispatchService.findClosestAvailableDriver(new Point(longitude, latitude))
    }
}
1 Expose order dispatch operations under /orders.
2 Accept the order location as longitude and latitude query parameters.
3 Returning null from the controller method makes the framework respond with 404 Not Found when no driver is available within 5 km.

9. Test

Add a test that verifies the dispatch endpoint:

src/test/groovy/example/micronaut/DeliveryDispatchControllerSpec.groovy
package example.micronaut

import io.micronaut.data.model.geo.Point
import io.micronaut.http.HttpRequest
import io.micronaut.http.HttpResponse
import io.micronaut.http.HttpStatus
import io.micronaut.http.client.BlockingHttpClient
import io.micronaut.http.client.HttpClient
import io.micronaut.http.client.annotation.Client
import io.micronaut.http.client.exceptions.HttpClientResponseException
import io.micronaut.test.extensions.spock.annotation.MicronautTest
import jakarta.inject.Inject
import spock.lang.Specification

@MicronautTest(transactional = false) (1)
class DeliveryDispatchControllerSpec extends Specification {

    @Inject
    DeliveryDriverRepository deliveryDriverRepository

    @Inject
    @Client('/') (2)
    HttpClient httpClient

    void setup() {
        deliveryDriverRepository.deleteAll()
    }

    void "finds closest available driver within five kilometers"() {
        given:
        deliveryDriverRepository.save(new DeliveryDriver(
            'Nearby Driver',
            DeliveryDriver.Status.AVAILABLE,
            new Point(-73.9757d, 40.7554d)
        ))
        DeliveryDriver closest = deliveryDriverRepository.save(new DeliveryDriver(
            'Closest Driver',
            DeliveryDriver.Status.AVAILABLE,
            new Point(-73.9827d, 40.7504d)
        ))
        deliveryDriverRepository.save(new DeliveryDriver(
            'Busy Driver',
            DeliveryDriver.Status.BUSY,
            new Point(-73.9850d, 40.7488d)
        )) (3)
        deliveryDriverRepository.save(new DeliveryDriver(
            'Far Driver',
            DeliveryDriver.Status.AVAILABLE,
            new Point(-73.9000d, 40.8000d)
        )) (4)

        when:
        BlockingHttpClient client = httpClient.toBlocking()
        HttpResponse<DriverMatch> response = client.exchange(
            HttpRequest.GET('/orders/nearest-driver?longitude=-73.9857&latitude=40.7484'),
            DriverMatch
        ) (5)

        then:
        response.status() == HttpStatus.OK
        response.body().driverId == closest.id (6)
    }

    void "returns not found when no available driver is close enough"() {
        given:
        deliveryDriverRepository.save(new DeliveryDriver(
            'Busy Driver',
            DeliveryDriver.Status.BUSY,
            new Point(-73.9850d, 40.7488d)
        ))
        deliveryDriverRepository.save(new DeliveryDriver(
            'Far Driver',
            DeliveryDriver.Status.AVAILABLE,
            new Point(-73.9000d, 40.8000d)
        ))

        when:
        BlockingHttpClient client = httpClient.toBlocking()
        client.exchange(
            HttpRequest.GET('/orders/nearest-driver?longitude=-73.9857&latitude=40.7484'),
            DriverMatch
        ) (7)

        then:
        HttpClientResponseException thrown = thrown()
        thrown.status == HttpStatus.NOT_FOUND
    }
}
1 Start the Micronaut application with an embedded server and disable the test transaction. The test inserts data directly with the repository, and the HTTP request reads it through a separate connection.
2 Inject an HTTP client bound to the embedded server.
3 A busy driver can be nearby, but should not be returned.
4 An available driver outside the 5 km radius should not be returned.
5 Call the dispatch endpoint with the order longitude and latitude.
6 The response body contains the closest available driver within 5 km.
7 When no available driver is close enough, the endpoint responds with 404 Not Found.

10. Testing the Application

To run the tests:

./gradlew test

Then open build/reports/tests/test/index.html in a browser to see the results.

When you run the test, Micronaut Test Resources starts a MySQL container and configures the JDBC datasource automatically.

11. Next Steps

Read more about Micronaut Data geospatial support.

12. Help with the Micronaut Framework

The Micronaut Foundation sponsored the creation of this Guide. A variety of consulting and support services are available.

13. License

All guides are released with an Apache License 2.0 for the code and a Creative Commons Attribution 4.0 license for the writing and media (images).