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

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=kotlin \
    --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/kotlin/example/micronaut/DeliveryDriver.kt
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)
data class DeliveryDriver(
    @field:NotBlank
    var name: String,

    @field:NotNull
    var status: Status, (2)

    @field:NotNull
    @field:Srid(value = 4326, type = Srid.CrsType.GEOGRAPHIC) (3)
    @field:Index(columns = ["location"]) (4)
    var location: Point,

    @field:Id
    @field:GeneratedValue
    var id: Long? = null
) {

    enum class Status {
        AVAILABLE,
        BUSY
    }
}

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/kotlin/example/micronaut/DeliveryDriverRepository.kt
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 : CrudRepository<DeliveryDriver, Long> {

    fun findByStatusAndLocationNear(status: DeliveryDriver.Status, orderLocation: Point, maxDistanceMeters: Double): List<DeliveryDriver> (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/kotlin/example/micronaut/DispatchService.kt
package example.micronaut

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

@Singleton
class DispatchService(private val deliveryDriverRepository: DeliveryDriverRepository) {

    private companion object {
        private const val MAX_DISTANCE_METERS = 5_000.0 (1)
        private const val WGS84_A = 6378137.0
        private const val WGS84_F = 1.0 / 298.257223563
        private const val EPS = 1e-15
    }

    fun findClosestAvailableDriver(orderLocation: Point): DriverMatch? {
        val candidates = deliveryDriverRepository.findByStatusAndLocationNear(
            DeliveryDriver.Status.AVAILABLE,
            orderLocation,
            MAX_DISTANCE_METERS
        ) (2)

        return candidates
            .map { driver ->
                DriverMatch(
                    driver.id,
                    driver.name,
                    distanceMeters(orderLocation, driver.location)
                )
            }
            .minByOrNull { it.distanceMeters } (3)
    }

    private fun distanceMeters(left: Point, right: Point): Double { (4)
        val lon1Deg = left.x()
        val lat1Deg = left.y()

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

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

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

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

        val dLon = lon2 - lon1

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

        var cosD = sinLat1 * sinLat2 + cosLat1 * cosLat2 * Math.cos(dLon)
        cosD = cosD.coerceIn(-1.0, 1.0)

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

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

        val h = if (near(1.0 - cosD, 0.0)) 0.0 else (d + 3.0 * sinD) / (1.0 - cosD)
        val g = if (near(1.0 + cosD, 0.0)) 0.0 else (d - 3.0 * sinD) / (1.0 + cosD)

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

        return WGS84_A * (d + correction)
    }

    private fun validate(lon: Double, lat: Double) {
        require(java.lang.Double.isFinite(lon) && java.lang.Double.isFinite(lat)) {
            "Coordinates must be finite numbers"
        }
        require(lon > -180.0 && lon <= 180.0) {
            "Longitude must be in (-180, 180]"
        }
        require(lat >= -90.0 && lat <= 90.0) {
            "Latitude must be in [-90, 90]"
        }
    }

    private fun near(a: Double, b: Double): Boolean {
        return Math.abs(a - b) <= EPS
    }

    private fun square(x: Double): Double {
        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/kotlin/example/micronaut/DriverMatch.kt
package example.micronaut

import io.micronaut.serde.annotation.Serdeable

@Serdeable
data class DriverMatch(
    val driverId: Long?,
    val name: String,
    val distanceMeters: Double
)

8. Controller

Expose the dispatch operation through an HTTP endpoint:

src/main/kotlin/example/micronaut/DeliveryDispatchController.kt
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

@Controller("/orders") (1)
class DeliveryDispatchController(private val dispatchService: DispatchService) {

    @Get("/nearest-driver") (2)
    fun nearestDriver(@QueryValue longitude: Double, @QueryValue latitude: Double): DriverMatch? { (3)
        return dispatchService.findClosestAvailableDriver(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/kotlin/example/micronaut/DeliveryDispatchControllerTest.kt
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.HttpClient
import io.micronaut.http.client.annotation.Client
import io.micronaut.http.client.exceptions.HttpClientResponseException
import io.micronaut.test.extensions.junit5.annotation.MicronautTest
import org.junit.jupiter.api.Assertions.assertEquals
import org.junit.jupiter.api.BeforeEach
import org.junit.jupiter.api.Test
import org.junit.jupiter.api.assertThrows

@MicronautTest(transactional = false) (1)
class DeliveryDispatchControllerTest(
    private val deliveryDriverRepository: DeliveryDriverRepository,
    @Client("/") private val httpClient: HttpClient (2)
) {

    @BeforeEach
    fun clean() {
        deliveryDriverRepository.deleteAll()
    }

    @Test
    fun findsClosestAvailableDriverWithinFiveKilometers() {
        deliveryDriverRepository.save(
            DeliveryDriver(
                "Nearby Driver",
                DeliveryDriver.Status.AVAILABLE,
                Point(-73.9757, 40.7554)
            )
        )
        val closest = deliveryDriverRepository.save(
            DeliveryDriver(
                "Closest Driver",
                DeliveryDriver.Status.AVAILABLE,
                Point(-73.9827, 40.7504)
            )
        )
        deliveryDriverRepository.save(
            DeliveryDriver(
                "Busy Driver",
                DeliveryDriver.Status.BUSY,
                Point(-73.9850, 40.7488)
            )
        ) (3)
        deliveryDriverRepository.save(
            DeliveryDriver(
                "Far Driver",
                DeliveryDriver.Status.AVAILABLE,
                Point(-73.9000, 40.8000)
            )
        ) (4)

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

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

    @Test
    fun returnsNotFoundWhenNoAvailableDriverIsCloseEnough() {
        deliveryDriverRepository.save(
            DeliveryDriver(
                "Busy Driver",
                DeliveryDriver.Status.BUSY,
                Point(-73.9850, 40.7488)
            )
        )
        deliveryDriverRepository.save(
            DeliveryDriver(
                "Far Driver",
                DeliveryDriver.Status.AVAILABLE,
                Point(-73.9000, 40.8000)
            )
        )

        val client = httpClient.toBlocking()
        val thrown = assertThrows<HttpClientResponseException> {
            client.exchange(
                HttpRequest.GET<Any>("/orders/nearest-driver?longitude=-73.9857&latitude=40.7484"),
                DriverMatch::class.java
            )
        } (7)

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