Поиск по двум связанным таблицам

SQL во всех проявлениях - от ANSI-92 до TSQL.

Модераторы: Yurich, Absurd

Ответить
serpanok
Сообщения: 3
Зарегистрирован: 10 окт 2017, 15:26

Поиск по двум связанным таблицам

Сообщение serpanok » 10 окт 2017, 15:28

Добрый день!

У меня есть две таблицы "Улицы" и "Строения". Строения привязаны к "Улицам" Необходимо осуществить поиск нужного строения имея поисковой запрос в виде текста(если поисковой запрос не точен - можно несколько результатов).
У строк есть так же "алиасы", те разные вариации написания(title: Чапаева, sub_titles: Кирова|Пушкина), те улица может иметь синонимы в названии.(формат хранения алиасов может быть любой, как вы скажете так и буду хранить).

Например: "Чапаева 14", "Чапаева", "улица Чапаева 14", "14 Чапаева" итд

Код: Выделить всё

`streets` (
  `street_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `sub_titles` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`street_id`)
);
`locations` (
  `location_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `street_id` smallint(5) unsigned DEFAULT NULL,
  `title` varchar(100) NOT NULL,
  PRIMARY KEY (`location_id`)
);

Нужно получить идентификаторы строений подходящих под поисковой запрос.

Пригодится любая помощь и советы. Даже просто описание запроса на "человеческом" языке, попробую если что сам его составить.

p.s. Я понимаю что это MySQL, но суть таблицы думаю понятна. Если что я разберусь как адаптировать запрос под MySQL. Советы подойдёт на любом языке :)

Аватара пользователя
AiK
Сообщения: 2271
Зарегистрирован: 13 фев 2004, 18:14
Откуда: СПб
Контактная информация:

Re: Поиск по двум связанным таблицам

Сообщение AiK » 10 окт 2017, 18:13

Исходную поисковую строку надо разбить на "слова" по пробелам и обработать каждое слово. Лучше делать это не средствами MySQL, а средствами того языка из которого идёт обращение к БД.

Что делать со словами
1. Попробовать преобразовать в числовое значение. Если получилось - это строение. то надо запомнить в переменную.
2. Если не получилось, то надо проверить не является ли слово сокращением от улицы, проспекта, переулка и т.д.
Вначале надо просто составить список этих сокращений самостоятельно, а потом анализировать все запросы, которые не дали результатов и обновлять список сокращений. Повылезают интересности типа "улю" вместо "ул." и т.д., ну и проспект можно кучей вариаций сократить.
3. Дальше я бы рекомендовал хранить в таблице улиц тип улицы - улица, проспект, проезд, бульвар и т.п. , потому что в городе легко могут быть улица и проспект с одинаковым именем
4. тип улицы и строение из запроса надо удалять и искать только по очищенному наименованию.
5. алиасы названий (типа старые наименования) надо сливать в одну строку
6. делать поиск банальным like с добавлением знака '%' в начало и конец очищенного наименования

Очевидно, что в городе может быть несколько улиц с одинаковым наименованием, поэтому в таблице не хватает района города.

Не описал обработку в исходной поисковой строке букв в строении, типа 13а.

Итого

Код: Выделить всё

select 
* 
from
streets s,
locations l
where 
s.street_id = l.streed_id
and l.title = @building
and s.full_title like CONCAT('%', @street_name, '%');

Даже самый дурацкий замысел можно воплотить мастерски

Absurd
Сообщения: 1213
Зарегистрирован: 26 фев 2004, 13:24
Откуда: Pietari, Venäjä
Контактная информация:

Re: Поиск по двум связанным таблицам

Сообщение Absurd » 10 окт 2017, 18:40

serpanok писал(а):Например: "Чапаева 14", "Чапаева", "улица Чапаева 14", "14 Чапаева" итд
p.s. Я понимаю что это MySQL, но суть таблицы думаю понятна. Если что я разберусь как адаптировать запрос под MySQL. Советы подойдёт на любом языке :)

Не важно насколько сложным будет запрос, работать он будет плохо. Для такиих вещей есть другие инструменты типа elasticsearch.
2B OR NOT(2B) = FF

serpanok
Сообщения: 3
Зарегистрирован: 10 окт 2017, 15:26

Re: Поиск по двум связанным таблицам

Сообщение serpanok » 10 окт 2017, 19:47

Absurd писал(а):Не важно насколько сложным будет запрос, работать он будет плохо. Для такиих вещей есть другие инструменты типа elasticsearch.

На сколько я понял он ставится только вручную на сервер, а у меня простой хостинг :(

serpanok
Сообщения: 3
Зарегистрирован: 10 окт 2017, 15:26

Re: Поиск по двум связанным таблицам

Сообщение serpanok » 10 окт 2017, 19:49

AiK,спасибо большое!

Absurd
Сообщения: 1213
Зарегистрирован: 26 фев 2004, 13:24
Откуда: Pietari, Venäjä
Контактная информация:

Re: Поиск по двум связанным таблицам

Сообщение Absurd » 10 окт 2017, 21:16

serpanok писал(а):На сколько я понял он ставится только вручную на сервер, а у меня простой хостинг :(

Ну тогда нужно искать легковесную альтернативу на php, типа такого

http://www.sphider.eu/download.php

Еще можно попробовать сделать полнотекстовый индекс по всем возможным комбинациям написания каждого адреса.
2B OR NOT(2B) = FF

Ответить