Geo-spatial query help please

Databases
1

I have a made a query which as far as I can tell, returns the data that it should. The issue I have is that it is telling me an incorrect distance.

This is the join part of the query:


 select business_id, 
       sqrt( ( latitude_miles - 54.6665 )^2
           + ( longitude_mpd * (longitude - 5.66341 ))^2 
       ) as distance 
from address
WHERE 1
ORDER BY distance;

I can’t see anything wrong there so, maybe my statement which inserted the values for latitude_miles and longitude_mpd was wrong?


UPDATE 
  address
SET
  latitude_miles = 69.1 * latitude,
  longitude_mpd = 69.1 * COS(latitude * 3.14159265 / 180)

any pointers are welcome.

bazz

2

I guess it depends on the georeferential system you’re using? If the queries aren’t giving any errors, then it surely isn’t a SQL problem.
What are the formulas you wanted to implement?

3

Thanks guido2004,

Not sure what you mean by ‘which georeferential system I am using’. If you are willing to explain, I would find it helpful I think.

I did get it sorted though and it was a mysql problem - well actually, it was mine. :slight_smile: I didn’t get a mysql error but when I made the join differently, I suddenly got the results I needed.

bazz