Recently I needed to move a large amount of data from one web server to another. I was moving about 50 websites to a new server. I didnt want to download all the data via FTP and then upload it to the new server (especially not from here in Honduras where the Internet isnt as fast as I would have liked). I didnt have SSH access to the old server due to shared hosting security issues. How did I move it all in only a few minutes?
Read the rest of this entry »

For a project I’m currently working on, I needed to be able to find people that a within a certain distance from a particular point.

I have a large database of people that each have a Longitude and Latitude of their location. One of the searches in the app I am building needs to find people that are close to the location of the Job. So if the Job is in Kumeu (NZ), I want to see the people that are close (lets say 25km).

This is made easy with the Haversine formula. Google has a good article that gives a MySQL query that will produce a list of rows from the database where the locations are within a certain number of kilometres/miles.

The MySQL query looks like this:

SELECT *,
(6371 *
acos(cos(radians(174.556107)) * cos(radians(latitude))
* cos(radians(longitude) - radians(-36.775700))
+ sin(radians(174.556107)) * sin(radians(latitude))))
AS distance
FROM Locations
HAVING distance < 25
ORDER BY distance

The location in the example above (-36.775700, 174.556107) is of Kumeu (NZ). The table currently has 4,500 records and the query takes about 0.0063 seconds (actually it is usually 0.0001 seconds).

Note that the above query calculates kilometres. If you are wanting miles, change the figure of 6371 to 3959.

The HAVING distance < 25 means that we are only getting the records who are within 25km of the original location.