1

I am working with the GeoNames database which has around 10 million rows of locations including longitude and latitude.

I have tested around 6 various nearby location queries including spatial indexes. I found the following query to be the fastest as well as accurate (using indexes on longitude/latitude)

SELECT
    *,
    ROUND(SQRT(POW(((69.1/1.61) * (? - latitude)), 2) +
    POW(((53/1.61) * (? - longitude)), 2)), 1) AS distance
FROM
    geoName FORCE INDEX (longitude)
WHERE
    latitude > ? - 100 / (69.1/1.61)
    AND latitude < ? + 100 / (69.1/1.61)
    AND longitude > ? - 100 / (53/1.61)
    AND longitude < ? + 100 / (53/1.61)
HAVING
    distance < 1500
ORDER BY
    distance ASC
LIMIT 1

However there is still an issue with speed. This query takes an average of 3.14 seconds to run on my DigitalOcean Managed DB (2 dedicated CPU's 8gb ram). This is far too long and I would hopefully like to get it to around 500ms.

I can only think of 4 ways to do this

  1. Find a more efficient query than the one that I posted above
  2. Migrate from MySQL to PostgreSQL (do you think this will make much of a difference?)
  3. Upgrade the server specs
  4. Split the data into multiple tables by country and keep a database of neighboring countries so that we query multiple countries where the longitude/latitude point of interest is on the border

I suspect #4 will have the biggest impact (with quite a bit of work involved). A query from USA including neighboring countries would result in a total of 1584153 rows (which could be further reduced if we take into account state).

What do you think would be best?

Improve this question
4
  • 2
    Definitely go with PostgreSQL and PostGIS and create a spatial index link. Nr.4 is actually some kind of spatial index approach. Good explanation: link Also have a look here why having a spatial index on points is also important: link
    – Bernd Loigge
    Nov 9, 2021 at 8:37
  • 2
    The main benefit of a spatial index, across most implementations, is its n-dimensional nature: at a slightly higher cost of traversal, in 2 dimensions it filters down in both longitudinal (X) and latitudinal (Y) direction, with an exponentially larger impact on reducing hit counts. Low-level core implementations of n-dim indexes are highly optimized in terms of compression and maintenance, and benefit from different distribution patterns; however, with the former in mind and in absence of the latter, a multi-column index on latitude, longitude may actually serve you quite well.
    – geozelot
    Nov 9, 2021 at 9:01
  • 2
    Table partitioning is an option to maintain (already comparably high) access times for data at scale, and an important metric for 'scale' here is the actual index size: table partitioning usually has no (or even a negative) impact on query speed (e.g. higher planning cost, loading and access of multiple indexes and tables on single threaded transactions, etc.) until such a point when a respective index cannot fit into memory anymore due to its size. Until then I'd advise against this setup in favor of better index utilization.
    – geozelot
    Nov 9, 2021 at 9:11
  • 1
    Thankyou both very much for your insight its really helpful for me! Looks like I will be making the transition to Postgres after 10+ years of only using Mysql. I wish I had set this up from the beginning but better late then never!
    – Ryan NZ
    Nov 9, 2021 at 17:10

0

Reset to default

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.