drop - mysql alter table add index

Base de datos: ¿la mejor forma de rendimiento para consultar datos de ubicación geográfica?

Question

Tengo una base de datos MySQL. Guardo casas en la base de datos y realizo literalmente solo 1 consulta en la base de datos, pero necesito que esta consulta se realice muy rápido , y eso es para devolver todas las casas dentro de una latitud y longitud geográficas de casilla cuadrada.

SELECT * FROM homes 
WHERE geolat BETWEEN ??? AND ???
AND geolng BETWEEN ??? AND ???

¿Cuál es la mejor manera para que almacene mis datos geográficos para que pueda realizar esta consulta de mostrar todos los hogares dentro del cuadro de geolocalización de la manera más rápida?

Básicamente:

• ¿Estoy usando la mejor declaración SQL para realizar esta consulta más rápido?
• ¿Existe algún otro método, tal vez ni siquiera el uso de una base de datos, para que pueda consultar de la manera más rápida el resultado de las casas dentro de los límites de una geolocalización en caja?

En caso de que ayude, he incluido mi esquema de tabla de base de datos a continuación:

CREATE TABLE IF NOT EXISTS `homes` (
  `home_id` int(10) unsigned NOT NULL auto_increment,
  `address` varchar(128) collate utf8_unicode_ci NOT NULL,
  `city` varchar(64) collate utf8_unicode_ci NOT NULL,
  `state` varchar(2) collate utf8_unicode_ci NOT NULL,
  `zip` mediumint(8) unsigned NOT NULL,
  `price` mediumint(8) unsigned NOT NULL,
  `sqft` smallint(5) unsigned NOT NULL,
  `year_built` smallint(5) unsigned NOT NULL,
  `geolat` decimal(10,6) default NULL,
  `geolng` decimal(10,6) default NULL,
  PRIMARY KEY  (`home_id`),
  KEY `geolat` (`geolat`),
  KEY `geolng` (`geolng`),
) ENGINE=InnoDB  ;

ACTUALIZAR

Entiendo que el factor espacial tendrá en cuenta la curvatura de la Tierra, pero estoy más interesado en devolver los datos geográficos al MÁS RÁPIDO. A menos que estos paquetes de bases de datos espaciales de alguna manera devuelvan datos más rápido, no recomiende extensiones espaciales. Gracias

ACTUALIZACIÓN 2

Tenga en cuenta que nadie a continuación ha respondido realmente la pregunta. Realmente estoy esperando cualquier ayuda que pueda recibir. Gracias por adelantado.

Answer 1

Aquí hay un buen documento sobre el rendimiento de geolocalización de MySQL.

EDITAR Bastante seguro que esto está usando un radio fijo. Además, no estoy 100% seguro de que el algoritmo para calcular la distancia sea el más avanzado (es decir, que se "perforará" a través de la Tierra).

Lo que es significativo es que el algoritmo es barato para darle un límite de parque de pelota en el número de filas para hacer una búsqueda de distancia adecuada.

Answer 2

Casas? Probablemente ni siquiera tengas diez mil de ellos. Solo use un índice en memoria como STRTree .

Answer 3

Desde MySQL 5.7 mysql puede usar geoindex como ST_Distance_Sphere () y ST_Contains () que mejoran el rendimiento.

Answer 4

Este es un truco que he usado con cierto éxito para crear regiones de redondeo. Es decir, si tiene una ubicación en 36.12345, -120.54321, y desea agruparla con otras ubicaciones que están dentro de un cuadro de cuadrícula de media milla (aproximadamente), puede llamar a su región 36.12x-120.54, y todas las demás ubicaciones con la misma región de redondeo caerán en el mismo cuadro.

Obviamente, eso no le da un radio limpio, es decir, si la ubicación que está mirando está más cerca de un borde que de otro. Sin embargo, con este tipo de configuración, es bastante fácil calcular las ocho cajas que rodean la caja de su ubicación principal. Esto es:

[36.13x-120.55][36.13x-120.54][36.13x-120.53]
[36.12x-120.55][36.12x-120.54][36.12x-120.53]
[36.11x-120.55][36.11x-120.54][36.11x-120.53]

Tire todas las ubicaciones con etiquetas de redondeo coincidentes y luego, una vez que las haya sacado de la base de datos, puede hacer sus cálculos de distancia para determinar cuáles usar.

Answer 5

Esto se ve bastante rápido. Mi única preocupación sería que usaría un índice para obtener todos los valores dentro de un radio de 3 millas de la latitud, luego los filtraría para valores dentro de las 3 millas de la longitud. Si entiendo cómo funciona el sistema subyacente, solo puede usar un ÍNDICE por tabla, por lo que no vale la pena el índice lat o largo.

Si tenía una gran cantidad de datos, podría acelerar las cosas para dar a cada cuadrado de 1x1 milla una ID lógica única, y luego establecer una restricción adicional sobre el SELECT que (área = "23234/34234" O área = "23235/34234 "O ...) para todos los cuadrados alrededor de su punto, luego obligue a la base de datos a usar ese índice en lugar de lat y long. Entonces solo estará filtrando mucho menos millas cuadradas de datos.

Answer 6

Los índices que está usando son de hecho índices B-tree y admiten la palabra clave BETWEEN en su consulta. Esto significa que el optimizador puede usar sus índices para encontrar las casas dentro de su "caja". Sin embargo, no significa que siempre usará los índices. Si especifica un rango que contiene demasiados "hits", los índices no se usarán.

Answer 7

Puede considerar crear una tabla separada ''GeoLocations'' que tenga una clave primaria de (''geolat'', ''geolng'') y una columna que contenga el home_id si esa geolocalización particular tiene una casa. Esto debería permitir que el optimizador busque un rango de ubicaciones geográficas que se ordenarán en el disco para obtener una lista de home_ids. A continuación, puede realizar una combinación con su tabla de "hogares" para buscar información sobre esos home_ids.

CREATE TABLE IF NOT EXISTS `GeoLocations` (
`geolat` decimal(10,6) NOT NULL,
`geolng` decimal(10,6) NOT NULL,
`home_id` int(10) NULL
PRIMARY KEY  (`geolat`,`geolng`)
);

SELECT GL.home_id
FROM GeoLocations GL
INNER JOIN Homes H
 ON GL.home_id = H.home_id
WHERE GL.geolat between X and Y
 and GL.geolng between X and Y

Answer 8

Si realmente necesita ir para el rendimiento, puede definir recuadros de delimitación para sus datos y asignar los recuadros de límites previos al cómputo a sus objetos en la inserción y usarlos más adelante para las consultas.

Si los conjuntos de resultados son razonablemente pequeños, aún podría hacer correcciones de precisión en la lógica de la aplicación (más fácil de escalar horizontalmente que una base de datos) mientras permite resultados precisos.

Eche un vistazo a la geobox.py Bret Slatkin que contiene una gran documentación para el enfoque.

Aún así, recomendaría consultar PostgreSQL y PostGIS en comparación con MySQL si tiene la intención de realizar consultas más complejas en el futuro previsible.

Answer 9

Siguiendo con su enfoque actual, hay un cambio que debe hacer. En lugar de indexar geolat y geolong por separado, debe tener un índice compuesto:

KEY `geolat_geolng` (`geolat`, `geolng`),

Actualmente su consulta solo aprovechará uno de los dos índices.

Answer 10

Tuve el mismo problema y escribí un blogpost de 3 partes. Esto fue más rápido que el índice geográfico.

Intro , Benchmark , SQL

Answer 11

Una muy buena alternativa es MongoDB con su indexación geoespacial .