Добрый день.

Я хочу сформировать что-то на подобии квадродерева для кластеризации точек геоданных. То есть я разбиваю карту сначало 30х30, а дальше каждый квадрат на 4 и так еще 18 уровней. При записи новой точки (кол-во запросов чтения, конечно больше чем добавления и изменения, но я считаю что примерно одинаково) ей присваиваются все уровни. Таким образом операция выборки проходит довольно легко.

Я реализовал хранение каждого уровня в отдельном поле (small integer). Таким образом у меня 19 полей: L1x, L1y, L2, L3, ...
Я полагаю, что таким образом будет обеспечено лучшее индексирование, чем если бы я хранил одно поле тип : 18190101111...
Я думаю, что одно поле будет индексироваться не совсем правильно, к тому же мне надо делать выборки по определенному уровню, а если у меня будет одно поле, то запрос LIKE будет тормозить всю операцию..

У меня есть два вопроса:

1. Правильно ли я поступил? Либо есть способы лучше, возможно есть более подходящий тип данных для меня.
2. Есть опреация CLUSTER. Но эта операция работает только на один идекс для таблицы. Есть ли способ кластеризовать сразу несколько индексов одной таблицы? Либо есть другие способы?
3. Возможно можно подключить SP-GIST индексы для данной сетуации, но я не очень понимаю как дальше работать с ними..

Для L1x и L1y значения идет в пределах от 0 до 29
А для остальных полей L значения могут быть только такие: 0, 1, 10, 11

Я построил индексы для всех полей. Возможно это я зря сделал для полей кроме L1x и L1y

Misha Tyurin:

С этим сложнее. Работаю через ORM Django.

Запрос на получение маркеров (точек) и уникальных квадратов кластеров такой:

# Получаем маркеры, которые входят в запрашиваемый бокс.
# Исключаем все маркеры, владельцы которых заблокированы, не активны или помечены флагом удален
markers = Marker.objects.filter(point__contained=q_box).filter(owner__is_active=True).filter(owner__is_blocked=False).filter(owner__is_deleted=False)
# Исключаем все маркеры, владельцы которых добавили запрашевающего юзера в черный список
markers = markers.exclude(owner__blocked_users=user)
# Фильтрация
# Возраст запрашивающего юзера
users_age = calculate_age(user.date_of_birth)
markers = markers.filter(Q(max_age__gte=users_age) | Q(max_age=None)).filter(Q(min_age__lte=users_age) | Q(min_age=None))
markers = markers.filter(Q(allowed_gender=user.gender) | Q(allowed_gender=2))
# Прекция из БД по уровню
# Получаем список уровней, которые есть в кверисете маркеров
if level =='markers_only':
pass
elif level == 3:
unic_zones = markers.distinct('L1y','L1x')
else:
# Надо прощитывать все уровни. Иначе два маркера где например
# L11=11 но при этом другие уровни не равны, проекция будет взята по одному
i = 4
q = ['L1y', 'L1x']
while i <= level:
q.append( 'L' + str(i) )
i = i + 1
unic_zones = markers.distinct(*q)

А можно ли отловить запросы которые поступают в БД? Какие есть вредства?

Ivan Durakа у вас там сейчас 19 индексов??????
Это жесть.
Как вы ищите? Есть ли поиск по нижним уровням БЕЗ верхних?

Тоже думаю что жесть.. Там один составной индекс и остальные обычные. Вот и думаю может объединить все уровни в одно поле.. Но как тогда потом производить поиск, не будет ли он долгим?

Алгоритм такой:

1. Получаю все маркеры в заданной области клиента
1а. Вычисляю уровень зуммирования при данной областим запроса.
2. Фильтрую по разным фильтрам, которые пришли в запросе от клиента.
3. Делаю distinct по всем полям уровня до рассчитанного уровня зуммирования.
4. (а вот тут хренова) Делаю цикл по всем полученным уникальным уровням после distinct'a.
По каждому уровню делаю запрос на получение колличества всех маркеров (из отфильтрованных) по данному уровню.

Таким образом получается список кластер - кол-во маркеров в кластере. Ну там еще вычисляется координата центра кластера, но это без участия бд, чисто математически по обозначению уровня.

Ivan DurakЕсть ли поиск по нижним уровням БЕЗ верхних?

Нет. В поиске участвует всегда от верхнего уровня до какого-либо. Отдельно по нижним нет.

Ivan DurakClosiusпропущено...


Нет. В поиске участвует всегда от верхнего уровня до какого-либо. Отдельно по нижним нет.
тогда вообще-то будет работать и одно составное поле, Like '12334%' использует поиск по индексу!!
И в принципе так же будет работать составной индекс с правильным порядком полей, от вернего к нижним!

Составной индекс сделать не получится.. Если только не делать несколько составных индексов типа (L1, L2) (L1,L2,L3) (L1,L2,L3,L4) и тд Но это тоже много индексов..

Я вот думаю стоит ли вообще инексировать нижние уровни, которые могут иметь только 4 значения?

kealon(Ruslan)Closius,
в некоторых базах есть встроенные средства индексирования для поддержки таких запросов
например в SQLite есть определённые флаги компиляции которые позволяют добавить поддерживание двумерных индексов для выборок вида (x1<x<x2)and(y1<y<y2) (вроде через регионы реализованы)

Не очень понял как это мне поможет.

LonepsychoClosius,

SELECT * FROM pg_stat_activity ну и собсно, лог файлы...

Что-то он выводит постоянно только эти строки:

"SELECT setting FROM pg_settings WHERE name IN ('autovacuum', 'track_counts')"
"SELECT c.oid, c.relname , nspname
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhparent
JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = 18577::oid
ORDER BY inhseqno"
"SELECT "django_migrations"."app", "django_migrations"."name" FROM "django_migrations""
"SELECT * FROM pg_stat_activity;"

Может есть средство как в реальном времени смотреть запросы? Ну с обновлением раз в 10 секунд например

Или может у меня логи не ведутся??

ClosiusIvan Durakпропущено...

тогда вообще-то будет работать и одно составное поле, Like '12334%' использует поиск по индексу!!
И в принципе так же будет работать составной индекс с правильным порядком полей, от вернего к нижним!

Составной индекс сделать не получится.. Если только не делать несколько составных индексов типа (L1, L2) (L1,L2,L3) (L1,L2,L3,L4) и тд Но это тоже много индексов..


Немного не так хотел сказать. Да надо делать составные все. Сделал сейчас вместо отдельных индексов все составные. Вроде производительность увеличилась.. Но всеравно в таблице 22 индекса... незнаю на сколько это плохо.

А вот что быстрее: составной индекс по например 5 полям или дублирование данных (от нижнего до текущего индекса прям в этом поле хранить) в каждом поле и делать индексы отдельно по полю?

kealon(Ruslan)Closiusпропущено...


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

И где мне про это почитать?

Вообще мне просто надо хранить квадро дерево. И нужен способ его хранения в БД