Привет. Сегодня я расскажу о работе с геоданными в PostgreSQL и немного о том, как это сделать в своем Java/Kotlin-приложении.

Допустим, есть прикладная задача: написать приложение, которое находит ближайший к текущему местоположению магазин с мороженным. Для человека, который прежде не сталкивался с решением подобных задач, это покажется чем-то нетривиальным. Наверное, так и есть. Ну, что ж, появилась необходимость — значит, надо реализовать задуманное, а заодно и расширить свой кругозор и опыт работы с технологиями.

Начать стоит с анализа предметной области. Что нам необходимо, чтобы определить положение заданной точки в пространстве? Из школьной географии вспоминаются широта и долгота. Отлично, с этим разобрались. Теперь стоит вспомнить, а с чем мы обычно ассоциируем географию? С картой и глобусом. Глобус — это трехмерный макет, а карта — это плоская проекция его поверхности. Тут-то и начинается самое интересное: Земля не шар, а геоид — тело вращения, похожее на приплюснутый шар. Существует несколько вариантов проецирования поверхности геоида на плоскости и спецификаций, описывающих сам геоид. Но обо всем по порядку.

Мы определились с набором данных, необходимых для хранения геосоставляющей нашего проекта, и теперь осталось выбрать подходящий набор технологий. Для хранения информации будем использовать реляционную базу данных — PostgreSQL. Как хранить геосоставляющую нашего проекта? Есть подходящее расширение PostGIS, которое предназначено для работы с геоданными и предлагает из коробки все 33 удовольствия в виде удобных функций, типов и т. д. Садимся писать код. В качестве языка программирования будет Kotlin, основным фреймворком будет Spring (Spring Web, Spring Data), а в качестве ORM выступит Hibernate. 

Создадим простейшую сущность Shop, описывающую магазин с мороженным.

@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)
    }
}

Вот тут начинается самое интересное: наши широта и долгота определяют некоторую точку в пространстве, но необходимо понимать, в какой системе координат, в какой проекции или на какой поверхности она находится. Об этом поговорим ниже. В PostGIS для хранения точек есть отдельный тип Point, а также два базовых типа Geometry и Geography.  В Hibernate есть отдельное расширение Hibernate Spatial для работы с объектами в пространстве. Казалось бы, всё очевидно и стоит использовать Geography, но внезапно нас настигает разочарование: Hibernate с ним не работает. 

Для человека не погруженного в тему геометрия и география на первый взгляд не совсем связаны. Но это не так. Самое время разобраться в некоторых тонкостях хранения данных. Чтобы создать запись в поле типа Point необходимо указать SRID , который по-умолчанию будет равен 4326. А что такое SRID и какие они бывают?

SRID — это Spatial Reference System Identifier, или идентификатор системы пространственной привязки. То есть это набор правил, описывающих, как определять местоположение объекта в пространстве и как преобразовывать координаты между разными системами и единицами измерений. Кроме того, система пространственной привязки зависит от конкретной проекции. У каждой такой системы есть идентификатор, в большинстве случаев совпадающий с кодом EPSG.

SRID 4326 — это идентификатор проекции Меркатора для геоида описанного в WGS 84.

Код EPSG — это код системы в общепринятом реестре EPSG, содержащем наборы геодезических данных, системы представления, спецификации геоэллипсоидов, системы координат, единицы измерения и правила преобразования.

Вроде понятно, но есть нюанс. Необходимо знать еще одну аббревиатуру: WGS. 

WGS — это World Geodetic System. Стандарт для использования в картографии, геодезии и навигации, включающий описание фундаментальных и производных констант.

WGS 84 — это единая всемирная система геодезических координат, определяющая координаты относительно центра масс Земли (планета накренена вытянутой частью в сторону солнца). 

Отлично, почти со всей теорией разобрались. Теперь выясним, зачем нужно много этих SRID? — Они описывают разные системы. Логично, об этом написано выше. А для чего их несколько? — Для того, чтобы работать с разными проекциями.

Картографическая или геодезическая проекция — это плоское представление геоида Земли. Проекции бывают разные, имеют разные назначения, погрешности и единицы измерения (градусы, радианы, метры, футы и т.д.) и прочие особенности. Это важно при представлении данных, например, на карте. Если взять две разных системы, работающих с разными проекциями, и отметить на них точки с одинаковыми координатами, а затем наложить друг на друга, то точки не совпадут.

Две наиболее популярных системы пространственной привязки, помимо описываемой SRID 4326, — это системы с SRID 3395 и 3857. 

SRID 3395 описывает проекцию Меркатора на сфероиде и используется, например, Яндексом в его картах для реализации тайлового деления. А SRID 3857 используется в картах Google и описывает проекцию Меркатора на сфере. К точности и особенностям представлений SRID 3857, кстати, у мирового сообщества была и есть масса вопросов и претензий, но тем не менее эта система стала полноценным и массово используемым стандартом.

Гораздо подробнее про Google Web Mercator, разницу между проекциями и прочее описано здесь.

Проекция Меркатора — это сохраняющая углы прямоугольная проекция с переменным масштабом, который увеличивается к полюсам, а по вертикали и горизонтали остаётся неизменным. Меридианы на этой проекции представлены в виде параллельных равноудалённых вертикальных линий, а параллели — в виде параллельных горизонтальных, расстояние между которыми увеличивается по мере удаления от экватора. Эта проекция не сохраняет расстояния и площади и используется в морской навигации и авиации.

Так, что-то много матчасти. Вернемся к нашим баранам практике и допишем код.

Создадим сервис, работающий с нашими магазинами:

@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?
}

Если использовать Geometry, то при вычислении расстояния не получится использовать функцию ST_Distance, так как расстояние будет рассчитано не в метрах, а в градусах. Для решения этой проблемы в PostGIS есть функции ST_Distancesphere и ST_Distancespheroid. Они позволяют вычислять расстояние, работая с точками на плоскости, как если бы они располагались на поверхности геоида, и получать результат вычислений в метрах. Об особенностях работы с типом Geometry я расскажу ниже.

Простенький контроллер и DTO-класс:

@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) }
}

А теперь опишем SQL-скрипт, создающий таблицу и заполняющий её начальным набором значений:

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)
    }
}

Я писал и тестировал код для статьи с помощью Docker и Testcontainers. В этом варианте все нужные расширения уже имеются, но в реальной жизни для работы с PostGIS придется создать их руками.

CREATE EXTENSION postgis;

Это основное расширение для работы с PostGIS. Оно включает в себя только Geography и Geometry. По необходимости можно также работать с postgis_raster, postgis_topology, postgis_sfcgal, fuzzystrmatch, address_standardizer, address_standardizer_data_us и postgis_tiger_geocoder, но для решения поставленной задачи их возможности не обязательны и расказывать о них я не буду.

Также в настройках своего приложения необходимо выбрать соответствующий диалект:

org.hibernate.spatial.dialect.postgis.PostgisDialect

Настройки для работы с Testcontainers я сделал такие:

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

Документация, которой я пользовался.

Задача выполнена, и теперь можно получить при помощи нашего приложения ближайший магазин с мороженным или набор всех магазинов с мороженным, отсортированный по удалению от переданных широты и долготы. Но это еще не всё, стоит доразобраться с PostGIS и Hibernate.

Hibernate Spatial изначально разрабатывался как расширение для Hibernate, позволяющее работать с географическими данными. Начиная с Hibernate 5.0 Spatial является частью основного проекта ORM. Hibernate Spatial предоставляет стандартизированный интерфейс между базами данных для хранения географических данных и функций запросов. Он поддерживает большинство функций, описанных в спецификации OGC Simple Feature Specification. Java не представляет в своей стандартной библиотеке инструментария для работы с пространственными типами. Тут на помощь приходят внешние геометрические модели. Hibernate умеет работать с двумя из них: JTS и Geolatte-geom. В примерах, которые я разбирал, решая реальную рабочую задачу на проекте,  почти всегда используется JTS. В документация Hibernate эта библиотека названа стандартом де-факто.

Выбор типа

По Hibernate коротенько пробежались, теперь на очереди PostGIS. Об особенностях работы с ним я кратко сказал в начале статьи, но для понимания стоит копнуть чуть глубже. Для начала рассмотрим диаграмму иерархии типов, представленную в документации:

По названиям типов должно быть понятно, за что они отвечают.

В PostGIS, как уже сказано выше, есть тип Geography. Он похож на Geometry, но есть кардинальные различия. В основе типа Geometry в PostGIS лежит плоскость. Кратчайший путь между двумя точками на плоскости — прямая. Это означает, что расчеты геометрии (площади, расстояния, длины, пересечения и т. д.) могут быть рассчитаны с использованием декартовой математики и векторов прямых линий.

Основа Geography в PostGIS — сфера. Geography обеспечивает встроенную поддержку пространственных объектов, представленных с координатами вида «широта/долгота» или «долгота/широта». Географические координаты — это сферические координаты, выраженные в угловых единицах (градусах). Кратчайший путь между двумя точками на сфере — дуга. Это означает, что расчеты по географическому положению (площади, расстояния, длины, пересечения и т. д.) должны производиться на сфере с использованием более сложной математики. Для более точных измерений при расчетах необходимо учитывать фактическую сфероидальную форму мира. Поскольку лежащая в основе математика намного сложнее, для типа Geography определено меньше функций, чем для типа Geometry. При работе с Geography нет возможности использовать GEOS-функции. До PostGIS 2.2 этот тип поддерживал только длинную широту WGS 84 (SRID: 4326). Для PostGIS 2.2 и выше можно использовать любую систему пространственной привязки на основе долготы и широты, определенную в таблице Space_ref_sys. Можно добавить свою собственную сфероидальную систему пространственной привязки. Независимо от того, какую систему пространственной привязки вы используете, единицы измерения, возвращаемые функциями вроде ST_Distance, ST_Length, ST_Perimeter, ST_Area и т. д. выражаются в метрах. Geography поддерживает меньшее количество функций, чем Geometry, а также не умеет работать с кривыми и еще некоторыми формами, с которыми умеет работать Geometry.

Если отбросить тот факт, что Hibernate Spatial не умеет работать с Geography, то возникает вопрос, когда и какой из этих типов использовать? Ссылаясь на официальную документацию, можно выделить следующие моменты:

• Если ваши данные находятся на небольшой площади, то выбор подходящей проекции и использование Geometry — лучшее решение с точки зрения производительности и доступных функциональных возможностей.

• Если ваши данные разбросаны по всей поверхности земного шара или охватывают континентальный регион, то Geography позволит вам построить систему, не беспокоясь о подробностях проекции.

Индексы

Теперь поговорим об индексах в PostGIS. Они ускоряют выборку данных и позволяют работать с большими объёмами информации. Чаще всего при загрузке данных PostGIS самостоятельно создает индексы. При работе с пространственными данными можно пользоваться не всеми индексами PostgreSQL, а только тремя: GIST, BRIN, SP-GIST. О них и поговорим. При работе с Point используется только GIST. Тема индексов достаточно обширна, а шаблонных описаний их работы с пространственными данными достаточно и без меня, так что ограничимся выкладками о самом необходимом про каждый тип, с которым умеет работать PostGIS, и ссылками на статьи, с которыми я знакомился, когда изучал эту тему.

GIST — это Generalized Search Tree, обобщенное дерево поиска. На самом деле тут речь про сбалансированное по высоте дерево, и устроен GIST похожим на B-Tree образом, с разницей в том, что он не так жестко завязан на операторы сравнения и умеет работать с типами данных, для которых не определены эти операторы, то есть с нерегулярными структурами данных. Почитать про деревья, GIST и его работу с точками можно здесь.

BRIN — это Block Range Index, индекс с потерями. В основном его используют ради компромисса между производительностью при чтении и записи. Индекс предназначен для работы с очень большими таблицами, некоторые столбцы которых имеют естественную корреляцию с их физическим расположением в таблице. BRIN используется не только при работе с пространственными данными, но и для ускорения поиска по различным типам регулярных или нерегулярных структур данных (целых чисел, массивов и т. д.). Идея индекса BRIN состоит в том, чтобы охватывать всю геометрию, содержащуюся во всех строках в наборе блоков таблицы, называемом диапазоном. Очевидно, что этот метод индексации будет эффективен только в том случае, если данные физически упорядочены таким образом, что результирующие блоки объединения для диапазонов блоков будут взаимоисключающими. Результирующий индекс будет действительно небольшим, но во многих случаях менее эффективным, чем индекс GIST. Довольно часто индекс BRIN создается более чем в десять раз быстрее, чем в случае с GIST. Поскольку в индексе BRIN хранится только один блок расширения для одного или нескольких блоков таблицы, то для таких индексов используется намного меньше дискового пространства. Про BRIN-индексы более комплексно можно и следует почитать здесь.

На этом всё. Надеюсь, что смог раскрыть тему и облегчить вам «вход» в предметную область. Комментарии и уточнения приветствую.

Скачать и позапускать тесты, изучить, обсудить или поругать мой код, написанный для данной статьи, можно здесь.

P. S. Прикладная задача, решенная мной, и связанная с ней функциональность были успешно выкачены в прод и выдерживают около 120 тысяч запросов в сутки. Огромное спасибо моим боевым товарищам и сокомандникам, которые поддерживали и подогревали мой интерес к теме при работе над проектом и во время написания статьи. Вы — лучшие.