MySQL Postgresql/PostGIS

У меня есть координаты lat/lon в 400-миллионной таблице разделенных разделов mysql. Таблица увеличивается на 2000 записей в минуту, а старые данные размываются каждые несколько недель. Я изучаю способы пространственного анализа этих данных по мере их появления.

Для большей части анализа требуется найти, находится ли точка в конкретном полигоне lat/lon или в каких полигонах содержится эта точка.

Я вижу следующие способы решения проблемы в многоугольнике (PIP):

Создайте функцию mysql, которая принимает точку и геометрию и возвращает логическое значение. Простой, но не уверенный способ использования геометрии для выполнения операций с координатами lat/lon, поскольку Geometry предполагает плоские поверхности, а не сферы.
Создайте функцию mysql, которая принимает точку и идентификатор настраиваемой структуры данных и возвращает логическое значение. Вершины многоугольников могут быть сохранены в таблице, а функция может вычислять PIP с использованием сферической математики. Большое количество точек полигона может привести к огромной таблице и медленным запросам.
Оставьте данные точки в mysql и сохраните данные полигона в PostGIS и используйте сервер приложений для запуска запроса PIP в PostGIS с помощью пробной точки в качестве параметра.
Портируйте приложение из MySQL в Postgresql/PostGIS. Это потребует больших усилий для переписывания запросов и процедур. Я все еще могу это сделать, но насколько хорош Postgresql при обработке 400 миллионов строк. Быстрый поиск в google для "mysql 1 billion rows" возвращает много результатов. тот же запрос для Postgres не возвращает никаких релевантных результатов.

Хотелось бы услышать некоторые мысли и предложения.

Ответ 1

Несколько мыслей.

First PostgreSQL и MySQL - совершенно разные звери, когда дело доходит до настройки производительности. Поэтому, если вы перейдете по маршруту переноса, будьте готовы пересмотреть свои стратегии индексирования. Не только PostgreSQL имеет гораздо более гибкую индексацию, чем MySQL, но подходы к таблице также очень разные, что означает, что соответствующие стратегии индексирования так же различны, как и тактика. К сожалению, это означает, что вы можете немного бояться. Если бы я мог дать совет, я бы предложил сначала сбросить все неключевые индексы, а затем добавить их обратно при необходимости.

Второй момент заключается в том, что здесь никто не может дать вам много практических советов, потому что мы не знаем внутренних компонентов вашей программы. В PostgreSQL вам лучше всего индексировать только то, что вам нужно, но вы можете индексировать выходы функций (что действительно полезно в таких случаях), и вы можете индексировать только часть таблицы.

Я скорее парень PostgreSQL, чем парень MySQL, поэтому, конечно, я думаю, вам стоит пойти с PostgreSQL. Однако, вместо того, чтобы говорить вам, почему и т.д., И у вас есть борьба в таком масштабе, я расскажу вам несколько вещей, которые я хотел бы использовать, если бы я пытался это сделать.

Функциональные индексы
Напишите собственные функции для индексов для соответствующего анализа.
PostGIS довольно изумительный и очень гибкий

В конце концов, переключение db на этом томе будет кривой обучения, и вам нужно быть готовым к этому. Тем не менее, PostgreSQL может обрабатывать тома просто отлично.

Ответ 2

Количество строк здесь совершенно не имеет значения. Вопрос в том, какая часть работы полигона может быть сделана индексом.

Ответ на это зависит от того, насколько велики полигоны.

PostGIS очень быстро находит все точки в ограничивающей рамке многоугольника. Затем требуется больше усилий, чтобы выяснить, действительно ли точка находится внутри многоугольника.

Если ваши многоугольники маленькие (небольшие ограничивающие прямоугольники), запрос будет эффективным. Если ваши многоугольники большие или имеют форму, которая уменьшает ширину рамки, тогда она будет менее эффективной.

Если ваши многоугольники более или менее статичны, есть работа вокруг. Вы можете разделить свои многоугольники на более мелкие полигоны и воссоздать idnex. Тогда индекс будет более эффективным.

Если ваши многоугольники на самом деле являются многополигонами, первым шагом является разделение многополигонов на многоугольники с помощью ST_Dump и воссоздание и построение индекса результата.

НТН

Никлас