Member Avatar for Designer_101

Hi,

I have a list of postcodes in the UK and their co-ordinates... I've also written a nice function that measures the distance between two postcodes.

When my user logs in their postcode will be stored in a cookie, when they do a search for events I want to show them the events closest to them which in my head tells me I'd have to do the following.

-Collect ALL events and store them in an array
-Start a foreach loop
-Take the postcode of the event & from the cookie and parse them into a calculate function.
-Calculate function calculates the distance from the user and stores it in a new array
-Once all events have had their distance calculated they are sorted in ascending order by the "distancefrom" field and the top 10 are displayed to the user.

My questions are... what happens when there are 1000's of events?? Surley having that many events put into an array will take long and be really inefficient. Memory problems aswell?

Is my thinking right? Is this the best way to do what I want to do?

Any idea?

Suggestions?

Questions?

Many thanks :)

  • 5 Contributors
  • 8 Replies
  • 126 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by mschroeder
Member Avatar for aquilax

Can't you do it in the database?
Search for events in the following range:

x >= posx-spread
x <= posx+spread
y >= posy-spread
y <= posy+spread

Then you can also use your distance function on the returned subset.

Member Avatar for Designer_101

Sorry for the extremely late reply!
The question still bugs me and I know Im gunna have a fun time when I reach having to code it!

Could you perhaps show me an example sql statement...

Thanks

Member Avatar for aquilax

If the coordinates are 150x150 and the spread is 50 here is the example:

SELECT * FROM events 
WHERE posx > 100 
AND posx < 200
AND posy > 100
AND posy < 200
Member Avatar for Designer_101

Ah I see what you mean! However I need all the rows in the database to be searched and the closest 5 results from a certain postcode (the users postcode that's stored in their account details) to be pulled into an array and displayed..

Any ideas there?

Member Avatar for Nyight

To directly add onto aquilax's code:

AND postcode = $postcode
LIMIT 5
Edited by Nyight because: n/a
Member Avatar for aquilax

The idea here is just to limit the numbers of rows, you'll be checking.

Then you can find the closest points using the distance formula.

Member Avatar for somedude3488

By coordinates, are you meaning latitude and longitude.

If so, then the best way to do this is with mysql only.

Ex.

$lat = ''; //latitude of user
$lng = ''; //longitude of user
$lat_col = ''; //latitude column in database table
$lng_col = ''; //longitude column in database table
$sql = "SELECT fields,( 3959 * acos( cos( radians('{$lat}') ) * cos( radians( {$lat_col} ) ) * cos( radians( {$lng_col} ) - radians('{$lng}') ) + sin( radians('{$lat}') ) * sin( radians( {$lat_col} ) ) ) ) AS `distance` FROM `table` WHERE where clause here ORDER BY `distance` DESC";

That query isn't tested, but you get the idea.

commented: Nice +1
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.