2

We have a table named areas with column - id, name, lattitude, longitude and position. The position column is of POINT type in MySQL and has latitude, longitude value.

To get areas within 25 kilometers of given latitude, longitude i.e. (28.638753, 77.073803) and order them based on proximity to this point, we are using the following query as recommended here by Google.

SELECT id, name, ( 6371 * acos( cos( radians(28.638753) ) * cos( radians( Lattitude ) ) * cos( radians( Longitude ) - radians(77.073803) ) + sin( radians(28.638753) ) * sin( radians( Lattitude ) ) ) ) AS distance 
FROM areas 
HAVING distance < 25 
ORDER BY distance asc;

The table is:

CREATE TABLE `areas` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Lattitude` decimal(18,6) DEFAULT NULL,
  `Longitude` decimal(18,6) DEFAULT NULL,
  `position` point NOT NULL,
  PRIMARY KEY (`ID`),
  SPATIAL KEY `sx_areas_position` (`position`)
)

The explain query gives following. Please note that rows = 21750 as it's test data. Actual data on production will be 100k+ rows.

enter image description here

Following is the index information:

enter image description here

This query is really slow when run with > 100k of records (takes up to 500 milliseconds). Is there any way to optimize it i.e.using spatial index?

15
  • Please post text results of SHOW CREATE TABLE cwmasterdb.areas; and SHOW INDEX FROM cwmasterdb.areas; and EXPLAIN SELECT id, name, …..; so we can see what the optimizer chose for indexes to use for your query. Someone will have suggestions for you. Has the table had ANALYZE run on it recently or OPTIMIZE ? My first suggestion would be from HAVING distance < 25 to HAVING distance BETWEEN 0 and 25 could avoid table scan when you get rid of the 'less than' selection. Jul 2, 2018 at 19:05
  • 1
    I have no clue what ANALYZE or OPTIMIZE is. Having distance between 0 and 25 gave no performance boost. I have shared all the info. Please let me know if you need further data.
    – maverick
    Jul 2, 2018 at 19:56
  • did you mean to skip posting SHOW INDEX FROM areas: ? ANALYZE from 5.7 to consider using is documented here dev.mysql.com/doc/refman/5.7/en/analyze-table.html and OPTIMIZE from 5.7 that could be helpful is documented here dev.mysql.com/doc/refman/5.7/en/optimize-table.html Jul 2, 2018 at 20:15
  • Done the edit. I didn't run ANALYZE or OPTIMIZE for this table.
    – maverick
    Jul 2, 2018 at 20:24
  • Your CREATE TABLE is much different than your Google reference. Look up in the link for their 'markers' table creation. Their lattitude and longitude are probably better data types/sizes than what you have for performance and reducing data transfer volume. No indexes were used according to EXPLAIN, I would think use of indexes would be beneficial. Jul 3, 2018 at 10:30

3 Answers 3

2

There are lots of Q&A here. As a first cut, use a "bounding box".

ANALYZE TABLE and OPTIMIZE TABLE are irrelevant.

You mention Lattitude, but you don't have such a column.

Either go with point and SPATIAL, or go with latitude and longitude; it is probably folly to mix them.

2
  • Sorry, I have latitude and longitude in my table. I have point too, it's basically duplicate info. Could you suggest anyway to optimize above query ?
    – maverick
    Jul 3, 2018 at 5:16
  • @rickjames, Please slow down before you indicate there is no column in their create table provided. Jul 3, 2018 at 11:32
0

Why don't you use the spatial capabilities of MySQL?

That would look like that:

SELECT *
FROM areas
WHERE ST_Distance(ST_Transform(position, [SRID]), ST_Transform(ST_GeomFromText('Point(28.638753 77.073803)', 4326)), [SRID]) < 25000

ST_GeomFromText() creates a point in space with your lat/lon coordinates. [SRID] is the EPSG code representing the geographic system used to place that point. 4326 is the conventional unprojected geographic system using the WGS84 datum (GPS works with this system).

Then you check for a certain distance with all the points in your table but you have to check with a [SRID] with meters unit. You would have to use a more or less local projection for that. In Eastern Quebec, I use 32198 or 32188 often. ST_Transform() projects your points into a system using meters as units if you use an appropriate SRID.

Note that it could be slow to process if you have a really large table of points since you have to transform every points in the table.

0

Use GIS

First,

  1. Add a column pt which is ST_SRID(ST_Point(longitude,latidue),4326)
  2. Drop your old longitude and latidue columns.
  3. CREATE SPATIAL INDEX spatidx ON areas (pt);

Then do something like this,

SELECT ST_AsText(pt)
FROM areas
WHERE ST_Within(
  ST_Buffer( ST_SRID(ST_Point(77.073803,28.638753), 4326), 25000 ),
  pt
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.