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 :)

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

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

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

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``````

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?

To directly add onto aquilax's code:

``````AND postcode = \$postcode
LIMIT 5``````

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.

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

I know I've posted this before, but this scribd slideshow covers how to really optimize to the best of your abilities this particular functionality in mysql.

If you have access to the physical server, you could also check out the Spatial extensions for mysql.

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.