Sorting data by smallest distance first from Distance Matrix service

[speckytwat]

My apologies once again. I certainly wasn't trying to imply superhuman capabilities. The output is as follows (obviously I can't output people's actual names, emails and postcodes)

 

I inputted postcode AB14 0TQ

 

And got:

 

 

Name...
Email...
 
Postcode...
 
Distance from you: 36.359845428702
 
Name...
Email...
 
Postcode...
 
Distance from you: 36.967608439871
 
Name...
Email...
 
Postcode...
 
Distance from you: 36.975387110339
 
Name...
Email...
 
Postcode...
 
Distance from you: 37.157217528884
 
Name...
Email...
 
Postcode...
 
Distance from you: 37.190266800979
 
Name...
Email...
 
Postcode...
 
Distance from you: 37.265837397902
 
Name...
Email...
 
Postcode...
 
Distance from you: 37.318324090488
 
Name...
Email...
 
Postcode...
 
Distance from you: 3265.733830295
 
Name...
Email...
 
Postcode...
 
Distance from you: 3265.733830295
 
Name...
Email...
 
Postcode...
 
Distance from you: 3265.733830295

[Barand]

That isn't the table my code outputs - it doesn't show the lat and long values - which are rather critical to debugging what is going on.

[Barand]

Sorry, I just noticed I wasn't listing those either. Alter the query to pick those up and output them. (or post a sql dump of your members data)

[speckytwat]

I've managed to get better output by altering the query as follows: (changed the WHERE m.postcode)

 

 

$sql = "SELECT FirstName, Surname, Email, Postcode, POW(m.Latitude - p.Latitude, 2) + POW(m.Longitude - p.Longitude, 2) AS distance FROM members as m CROSS JOIN (SELECT latitude, longitude FROM postcodelatlng WHERE postcode = ? LIMIT 1) as p WHERE m.postcode NOT LIKE '' ORDER BY distance ASC";

 

So it now shows all the results, in the right order- there are a few incomplete postcodes for a few of the members which I've disregarded.

 

Only odd thing is that the distance is now showing as very small- nearest result when I searched using a postcode roughly 10 to 20km from most of the members was 0.010025634852, then 0.014432975702997 then 0.016619182127393 and so on.

[Barand]

OK,

so you don't want me to see the output with coordinates

and

you don't want me to see the data.

 

I get it.

 

You're now on your own.

[speckytwat]

That's correct, I'm not prepared to dump members' personal data / information / geolocations on a public Internet forum, and I'm sure you wouldn't either. I'll look for a solution elsewhere.

[Barand]

All that was required was their postcodes and the coordinates you entered for them from your members tables. That data would totally anonyymous. Plus you could have PMed it to me to avoid making anything public.

 

If you have a number of members all with the same wrong distance then it sounds like they all have the same, wrong coordinates - probably 0,0 as the distances are large.

 

Good luck, but you are not going to get a working query if your data is wrong.

[BigB]

I believe it to kilometres, you could round(48.272494811035, 2); for cleaner numbers to work with.