@Entity
@Table(name = "shop")
data class Shop(
    @Id
    @SequenceGenerator(
        name = "ID_GENERATOR",
        sequenceName = "shop_seq",
        allocationSize = 1
    )
    @GeneratedValue(
        strategy = SEQUENCE,
        generator = "ID_GENERATOR"
    )
    var id: Long? = null,
    val name: String?,
    val address: String?,
    @Column(nullable = false)
    val lat: Double,
    @Column(nullable = false)
    val lon: Double,
    val workingHours: String?,
) {
    @Column(columnDefinition = "geometry(Point,$SRID)", nullable = false)
    val position: Point = geometryFactory.createPoint(Coordinate(lon, lat))
    companion object {
        private const val SRID = 4326
        private val geometryFactory: GeometryFactory = GeometryFactory(PrecisionModel(), SRID)
    }
}

@Service
class ShopService(private val shopRepository: ShopRepository) {
    fun getClosestShop(lat: Double, lon: Double): ShopProjection? {
        return shopRepository.findAllShopsOrderedByDistance(geometryFactory.createPoint(Coordinate(lon, lat))).first()
    }
    fun getAllShopsOrderedByDistance(lat: Double, lon: Double): List<ShopProjection> {
        return shopRepository.findAllShopsOrderedByDistance(geometryFactory.createPoint(Coordinate(lon, lat)))
    }
    companion object {
        private const val SRID = 4326
        private val geometryFactory = GeometryFactory(PrecisionModel(), SRID)
    }
}

@Repository
interface ShopRepository : JpaRepository<Shop, Long> {
    @Query(
        value = "select s.id, " +
                "s.name, " +
                "s.address, " +
                "s.working_hours, " +
                "round(" +
                "cast(st_distancesphere(position, :point) as numeric), 2) as distance, " +
                "s.lat, s.lon from shop s order by distance",
        nativeQuery = true
    )
    fun findAllShopsOrderedByDistance(@Param("point") point: Point): List<ShopProjection>
}

interface ShopProjection {
    fun getId(): Long
    fun getName(): String?
    fun getAddress(): String?
    fun getWorkingHours(): String?
    fun getDistance(): Double?
}

@RestController
@RequestMapping("/api/offices")
class ShopController(
    private val shopService: ShopService
) {
    @GetMapping("/distance/closest")
    fun getClosestShop(
        @RequestParam("lat", required = true) lat: Double,
        @RequestParam("lon", required = true) lon: Double
    ) = shopService.getClosestShop(lat, lon)?.let { ShopDTO(it) }
    @GetMapping("/distance/all")
    fun getShopsOrderedByDistance(
        @RequestParam("lat", required = true) lat: Double,
        @RequestParam("lon", required = true) lon: Double
    ) = shopService.getAllShopsOrderedByDistance(lat, lon).map { ShopDTO(it) }
}

CREATE TABLE shop
(
    id            BIGINT                NOT NULL PRIMARY KEY,
    name          VARCHAR,
    address       VARCHAR,
    lat           FLOAT                 NOT NULL,
    lon           FLOAT                 NOT NULL,
    working_hours VARCHAR,
    position      geometry(POINT, 4326) NOT NULL
);
INSERT INTO shop(id, name, address, lat, lon, working_hours, position)
values (1, 'Липовый мед Илъича', 'г. Москва, Красная площадь', 55.7538337, 37.6211812,
        'Пн - Пт: 9:00 - 21:00 Сб, Вс - выхоные дни', st_setsrid(st_makepoint(37.6211812, 55.7538337), 4326));
INSERT INTO shop(id, name, address, lat, lon, working_hours, position)
values (2, 'Холодный холод', 'г. Санкт - Петербург, Дворцовая площадь', 59.938879, 30.315212,
        'Пн - Пт: 9:00 - 21:00 Сб, Вс - выхоные дни', st_setsrid(st_makepoint(30.315212, 59.938879), 4326));
INSERT INTO shop(id, name, address, lat, lon, working_hours, position)
values (3, 'Байкальский лед', 'Иркутская обл., оз. Байкал', 51.90503, 126.62002,
        'Пн - Пт: 9:00 - 21:00 Сб, Вс - выхоные дни', st_setsrid(st_makepoint(126.62002, 51.90503), 4326));
INSERT INTO shop(id, name, address, lat, lon, working_hours, position)
values (4, 'Сахалинские дали', 'о. Сахвлин', 46.96411, 142.73476,
        'Пн - Пт: 9:00 - 21:00 Сб, Вс - выхоные дни', st_setsrid(st_makepoint(142.73476, 46.96411), 4326));
INSERT INTO shop(id, name, address, lat, lon, working_hours, position)
values (5, 'Вкус Севера', 'р. Колыма', 64.748, 153.80,
        'Без выходных и перерывов', st_setsrid(st_makepoint(153.80, 64.748), 4326));
INSERT INTO shop(id, name, address, lat, lon, working_hours, position)
values (6, 'Мороженное Хинкали', 'г. Тбилиси', 41.6941100, 44.8336800,
        'Заходи когда нада, друк. Пажалуйста, брат.', st_setsrid(st_makepoint(44.8336800, 41.6941100), 4326));

@RunWith(SpringRunner::class)
@DataJpaTest
@ActiveProfiles("test")
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class ShopRepositoryTest {
    @Autowired
    private lateinit var shopRepository: ShopRepository
    @Test
    fun `findAll should return right shops`() {
        // given
        val expectedShopsCount = 6
        // when
        val result = shopRepository.findAll()
        // then
        assertEquals(expectedShopsCount, result.size)
    }
    @Test
    fun `findAllShopsOrderedByDistance returns`() {
        // given
        val pointToOrderFrom = geometryFactory.createPoint(Coordinate(37.6211812, 55.7538337))
        val orderedIds = listOf(1L, 2L, 6L, 3L, 5L, 4L)
        val orderedNames = listOf(
            "Липовый мед Илъича",
            "Холодный холод",
            "Мороженное Хинкали",
            "Байкальский лед",
            "Вкус Гулага",
            "Сахалинские дали"
        )
        // when
        val result = shopRepository.findAllShopsOrderedByDistance(pointToOrderFrom)
        val actualIds = result.map { it.getId() }
        val actualNames = result.map { it.getName() }
        // then
        assertEquals(orderedIds, actualIds)
        assertEquals(orderedNames, actualNames)
    }
    companion object {
        private const val SRID = 4326
        private val geometryFactory: GeometryFactory = GeometryFactory(PrecisionModel(), SRID)
    }
}

CREATE EXTENSION postgis;

org.hibernate.spatial.dialect.postgis.PostgisDialect

datasource:
    url: jdbc:tc:postgis:9.6.8-2.5://localhost/jpa-spatial
    username: portal
    password: uhSBTvdHCnS7pyUgOTDX
    driver-class-name: org.testcontainers.jdbc.ContainerDatabaseDriver
    hikari:
      driver-class-name: org.testcontainers.jdbc.ContainerDatabaseDriver