0

I'm trying to get points around "me" by location and it's pretty simple :

select * from X where ((SQRT(POW(69.1 * (a.lat - 49.201441 ), 2) + 
POW(69.1 * (16.161299 - a.lng) * COS(a.lat / 57.3), 2))) <='1') LIMIT 5

*

my location = 49.201441;16.161299
distance = 1 (mile)

This query returns me 5 points around "me" at a distance of 1 mile. This is rly fast, but just when my table have a ... I don't know, maybe 5000 rows.

I'm using this query on 200 000 rows table, and it's very slow! Maybe .. 2-7 sec and even if the limit is 1 = no difference.

Can someone explain it to me and help me? Thanks a lot guys!

BTW: If my query does not contains this part of query = it takes maybe 0.0008s... so second part of query is correct.

1 Answers1

0

I have used this for finding people near you.

table name: user_location

id|username|latitude|longitude

i am not sure that the distances is for a mile, but what this does is takes the users lat minus each persons from the database. If the users lat and long are within 1 it will be put into the results.

The biggest problem is 1 isn't equal to 1 mile. it is closer to 10miles i believe (it was a while ago and might not be 10 miles i can't remember), but you can always change this. Another problem is that you basically always have to update people's locations. Which isn't that bad, but if you have a slow server with a lot of users it can slow you down.

It is fast and works... Not the best solution, but might work for you. For me it atleast showed a nice surrounding area.

$query = "SELECT * FROM `user_location` WHERE (".$user_lat." - latitude) <= 1 AND (".$user_lon." - longitude) <= 1 LIMIT 100";

I tested this with 10,000 lines and it was just under 3 seconds with 254 people near me. (randomly generated content)

Dillon Burnett
  • 473
  • 4
  • 11
  • Thanks, but i need exact distances. I have 1 000 000+ points in my database and I need calculate it to 0.00x sec. And I can not update each rows every "insert" cause i'm importing XXXX data every 1 minute. But great solution for others :-) –  Jan 01 '17 at 21:10
  • You can just calculate the distance and change the number. But it will only be accurate when their positions are up to date. I used it for an app to find people near you (sort of like whisper). Personally i only updated it each time they logged in and reconnect every 30mins. So it was still some what decent with out all the crazy traffic. I could have this pinpoint people within 1 city block, i just don't know the ration of miles / feet / m to 1 on lat/long scale. – Dillon Burnett Jan 01 '17 at 21:22