Tenemos un conjunto de tablas que se muestran a continuación, usamos para nuestras otras tablas en referencia para los datos de ubicación. Algunos ejemplos son:

  • Encuentra todas las empresas dentro de las x millas de X City
  • Crear la ubicación de un perfil de la empresa como X City

Table Schema

Resolvemos el problema de múltiples ciudades con nombres similares al igualar el estado también, pero ahora nos encontramos con un conjunto diferente de problemas. Utilizamos el lugar de Google Autocompletar para la geocodificación y coincidir con una consulta de los usuarios con nuestras ciudades. Esto funciona bastante bien hasta que el formato de Google se desvíe de los nuestros.

Ejemplo: St. Louis !== Saint Louis y Ameca del Torro !== Ameca Torro

¿Hay alguna manera de combatir las ciudades múltiples en nuestras consultas?

Nuestra consulta para que coincida con las ciudades ahora se ve como:

SELECT c.id
FROM city c
INNER JOIN state s
ON s.id = c.state_id
WHERE c.name = 'Los Angeles' AND s.short_name = 'CA'

También he considerado la ciudad de desnormalización y simplemente almacenando coordenadas para lograr la búsqueda de radio. Tenemos alrededor de 2 millones de filas en nuestra tabla company por lo que se realizará una búsqueda de radio en la tabla en lugar de city con un JOIN en company. Esto también significaría que no podríamos crear regiones personalizadas (simplemente de todos modos) para las ciudades, y agregar otros atributos a las ciudades en el futuro.

Encontré esta respuesta pero básicamente está afirmando nuestra La forma de normalizar la entrada es un buen método, pero no cómo coincidemos con nuestra tabla local (a menos que Google ofrezca una exportación de nombres de la ciudad que no conozco).

2
Matt Weber 27 jun. 2019 a las 21:01

1 respuesta

La mejor respuesta

La respuesta corta es que puede usar la funcionalidad de búsqueda de texto completo de Postgres, con una configuración de búsqueda personalizada.

Desde su negociación con los nombres de los lugares, es probable que desee evitar el stemming, por lo que puede usar la configuración simple como punto de partida. También puede agregar palabras de parada que tengan sentido para los nombres de lugares (con los ejemplos anteriores, probablemente puede considerar "St.", "Saint", y "Del" como palabras de parada).

Un contorno bastante básico de la configuración de su personalizado está a continuación:

  1. Cree un archivo de StopWords y colóquelo en su directorio $SHAREDIR/tsearch_data Postgres. Consulte https://www.postgresql.org/docs /9.1/static/TextSearch-Dictionaries.html#TextSearch-StopWords.
  2. Cree un diccionario que use esta lista de puntos de parada (probablemente puede usar el pg_catalog.simple como su diccionario de plantilla). Consulte https://www.postgresql.org /docs/9.1/static/textSearch-Dictionaries.html#TextSearch-Simple-Dictionary.
  3. Cree una configuración de búsqueda para los nombres de lugares. Consulte https://www.postgresql.org/docs/9.1/STATIC /TextSearch-Configuration.html.
  4. Altere su configuración de búsqueda para usar el diccionario que creó en el Paso 2 (cf. El enlace de arriba).

Otra consideración es cómo considerar la internacionalización. Parece que el problema para su segundo ejemplo (Ameca del Torro vs. Ameca Torro) podría ser una representación española contra inglesa del nombre. Si ese es el caso, también podría considerar almacenar una versión "localizada" y "Universal" (e.g. English) del nombre de la ciudad.

Al final, su consulta (usando la búsqueda de texto completo) puede parecerse así (donde los 'lugares' es el nombre de su configuración de búsqueda):

SELECT cities."id"
FROM cities
    INNER JOIN "state" ON "state".id = cities.state_id
WHERE
    "state".short_name = 'CA'
    AND TO_TSVECTOR('places', cities.name) @@ TO_TSQUERY('places', 'Los & Angeles')
1
Zack 27 jun. 2019 a las 20:24