speckytwat Posted December 4, 2017 Author Share Posted December 4, 2017 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted December 4, 2017 Share Posted December 4, 2017 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted December 4, 2017 Share Posted December 4, 2017 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) Quote Link to comment Share on other sites More sharing options...
speckytwat Posted December 4, 2017 Author Share Posted December 4, 2017 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted December 4, 2017 Share Posted December 4, 2017 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. Quote Link to comment Share on other sites More sharing options...
speckytwat Posted December 5, 2017 Author Share Posted December 5, 2017 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted December 5, 2017 Share Posted December 5, 2017 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. Quote Link to comment Share on other sites More sharing options...
BigB Posted December 5, 2017 Share Posted December 5, 2017 (edited) I believe it to kilometres, you could round(48.272494811035, 2); for cleaner numbers to work with. Edited December 5, 2017 by BigB Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.