1

I have an app that formerly used PostgreSQL with PostGIS but now the environment changes and I have to use MySql.

In my app i had a database query that searches the nearest point to a point. Note that my table has about 40 Million rows.

SELECT 
    strength, 
    created, 
    updated, 
    point,
    ST_Distance_Sphere(ST_GeomFromText('Point(:lat :long)'), point) AS distance
FROM signalstrengths
ORDER BY distance ASC
LIMIT 1;`;

I read that mysql can't use postgres' <-> operator and the above query is what I came up with through reading docs and SO.

But this query doesn't work and I'm concerned that the speed of the query will suffer from changing to MySql (from what I've read).

Can you help me fixing that query and tell me more about the speed differences?

| improve this question | | | | |
  • Which version of MySQL do you use? – Mesa Sep 4 '18 at 18:33
  • I would have suggested using ROWNUMBER() but it seems that function doesn't work in mysql... – DPSSpatial Sep 4 '18 at 18:59
  • @DPSSparial I.think you meant 'I would have suggested <name_any_useful_function>, but it seems that finction doesn't work in MySQL' ... you could maybe try to create a rectangle around your point to feed the seemingly index driven MBR<function> family, in order to get a small subset of points to calculate the distance on? – geozelot Sep 4 '18 at 19:51
  • There's quite a bit written on the subject of efficiently finding nearest points on a sphere (or even the ellipsoid). Maybe consider using GIS packages in other languages to find a faster solution? MySQL may not be the right tool for this job. – barrycarter Sep 4 '18 at 21:07
  • @Mesa I'm using MariaDB 10.1.22 – Timo Jokinen Sep 5 '18 at 11:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.