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 Java.

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=java \
    --test=junit
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/java/example/micronaut/DeliveryDriver.java
package example.micronaut;

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;

@MappedEntity("delivery_driver") (1)
public record DeliveryDriver(
    @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
) {

    public enum Status {
        AVAILABLE,
        BUSY
    }

    public DeliveryDriver(String name, Status status, Point location) {
        this(null, name, status, 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/java/example/micronaut/DeliveryDriverRepository.java
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;

import java.util.List;

@JdbcRepository(dialect = Dialect.MYSQL) (1)
public 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/java/example/micronaut/DispatchService.java
package example.micronaut;

import io.micronaut.data.model.geo.Point;
import jakarta.inject.Singleton;

import java.util.Comparator;
import java.util.List;
import java.util.Optional;

@Singleton
public class DispatchService {

    private static final double MAX_DISTANCE_METERS = 5_000d; (1)
    private static final double WGS84_A = 6378137.0;              // semi-major axis, meters
    private static final double WGS84_F = 1.0 / 298.257223563;   // flattening
    private static final double EPS = 1e-15;

    private final DeliveryDriverRepository deliveryDriverRepository;

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

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

        return candidates.stream()
            .map(driver -> new DriverMatch(
                driver.id(),
                driver.name(),
                distanceMeters(orderLocation, driver.location())
            ))
            .min(Comparator.comparingDouble(DriverMatch::distanceMeters)); (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.0;
        }

        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.0, Math.min(1.0, 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.0 - cosD, 0.0) ? 0.0 : (d + 3.0 * sinD) / (1.0 - cosD);
        double g = near(1.0 + cosD, 0.0) ? 0.0 : (d - 3.0 * sinD) / (1.0 + cosD);

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

        return 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.0 || lon > 180.0) {
            throw new IllegalArgumentException("Longitude must be in (-180, 180]");
        }
        if (lat < -90.0 || lat > 90.0) {
            throw new IllegalArgumentException("Latitude must be in [-90, 90]");
        }
    }

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

    private static double square(double x) {
        return 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/java/example/micronaut/DriverMatch.java
package example.micronaut;

import io.micronaut.serde.annotation.Serdeable;

@Serdeable
public record DriverMatch(Long driverId, String name, double distanceMeters) {
}

8. Controller

Expose the dispatch operation through an HTTP endpoint:

src/main/java/example/micronaut/DeliveryDispatchController.java
package example.micronaut;

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;

import java.util.Optional;

@Controller("/orders") (1)
public class DeliveryDispatchController {

    private final DispatchService dispatchService;

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

    @Get("/nearest-driver") (2)
    public Optional<DriverMatch> nearestDriver(@QueryValue double longitude, @QueryValue double latitude) { (3)
        return 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 an empty Optional 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/java/example/micronaut/DeliveryDispatchControllerTest.java
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.junit5.annotation.MicronautTest;
import jakarta.inject.Inject;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertThrows;

@MicronautTest(transactional = false) (1)
class DeliveryDispatchControllerTest {

    @Inject
    DeliveryDriverRepository deliveryDriverRepository;

    @Inject
    @Client("/") (2)
    HttpClient httpClient;

    @BeforeEach
    void clean() {
        deliveryDriverRepository.deleteAll();
    }

    @Test
    void findsClosestAvailableDriverWithinFiveKilometers() {
        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)

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

        assertEquals(HttpStatus.OK, response.status());
        assertEquals(closest.id(), response.body().driverId()); (6)
    }

    @Test
    void returnsNotFoundWhenNoAvailableDriverIsCloseEnough() {
        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)
        ));

        BlockingHttpClient client = httpClient.toBlocking();
        HttpClientResponseException thrown = assertThrows(HttpClientResponseException.class, () ->
            client.exchange(
                HttpRequest.GET("/orders/nearest-driver?longitude=-73.9857&latitude=40.7484"),
                DriverMatch.class
            )
        ); (7)

        assertEquals(HttpStatus.NOT_FOUND, thrown.getStatus());
    }
}
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).