Sort results in mySQL or in PHP array?

Question

I am trying to sort a mySQL results by distance of site visitor from places listed in the database rows. Should I do the sorting in mySQL and get the sorted results, or retrieve the unsorted results from mySQL database then sort using PHP? Is there a difference i performance?

This is the SQL code I will use if I sort using mySQL.

SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) 
+ COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon – lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) 
AS `distance` FROM `members` HAVING `distance`<=’10′ ORDER BY `distance` ASC

Check out this question: stackoverflow.com/questions/1883264/… — Brian Fisher
– Brian Fisher, Commented May 17, 2011 at 21:28

23tux · Accepted Answer · 2011-05-17 21:49:31Z

2

When you have to deal with geodata, I can defenetly recommend PostgreSQL with the PostGIS extension. It's way faster in calculating distances between lat and lng. In PostgreSQL with PostGIS your query would look like that (untested):

SELECT *
FROM members
WHERE ST_Distance_Sphere(members.position, ST_GeomFromText("POINT
(47.8012079 13.0395594)", 4326) <= 10000
ORDER BY ST_Distance_Sphere(members.position, ST_GeomFromText("POINT
(47.8012079 13.0395594)", 4326)

The ST_Distance_Sphere(members.position, ST_GeomFromText("POINT (47.8012079 13.0395594)", 4326) function gives you the distance between a given point (POINT (47.8012079 13.0395594)) and the position members.position of your member. You can use this function multiple times within one statement, PostgreSQL will cache that for you (and don't calculate the distance twice).

And don't forget to add a so called "spatial index" to your members.position column. Have a look at http://postgis.refractions.net/ and http://www.postgresql.org/

There are many tutorials around the web, if you have trouble to start with postgresql. Or, just ask here ;)

regards, tux

answered May 17, 2011 at 21:49

23tux

14.8k16 gold badges98 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nyxynyx Over a year ago

BTW, if you look at my posted formula for finding distances, what is the units the answer is in? Metres? My input is in (x.xxxxxx,x.xxxxxx) like the one used in google maps v3 api. I am not sure what is it called?

23tux Over a year ago

Your (x.xxx, y.yyy) is latitude and longitude. It seems to me, that you are using the haversine formula (en.wikipedia.org/wiki/Haversine_formula). I'm not sure, either the unit is metres or degree. Checkout this paper, it helped me a lot in understanding geodata within databases (and why mysql is not the best way): scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

CanSpice · Accepted Answer · 2011-05-17 21:26:15Z

2

Do it both ways and compare the results. This massively depends on your actual setup. If you have a sub-par database server and a kick-ass webserver, then the PHP will probably be quicker. If you have a super database server and a 486 for a webserver, then the database sort could be quicker. It all depends on your setup.

Edit: Also, I suggest storing your longitudes and latitudes in radians so you don't have to do all of those conversions every time you want to do a query.

answered May 17, 2011 at 21:26

CanSpice

36.1k10 gold badges74 silver badges87 bronze badges

2 Comments

Nyxynyx Over a year ago

Both my database server and webserver are the same computer... will there be too much of a difference in this case?

CanSpice Over a year ago

@Nyxynyx: Answer still holds. Do it both ways and find out definitively for your situation.

bjelli · Accepted Answer · 2011-05-18 03:52:35Z

1

in answer to Nyxynyx question in a comment "BTW, if you look at my posted formula for finding distances, what is the units the answer is in? Metres? My input is in (x.xxxxxx,x.xxxxxx) like the one used in google maps v3 api. I am not sure what is it called? "

The input of your computation is latitude (lat) and longitude (lng)

http://en.wikipedia.org/wiki/Geographic_coordinate_system

Find an explanation of you computation at

http://en.wikipedia.org/wiki/Great-circle_distance

answered May 18, 2011 at 3:52

bjelli

10.1k4 gold badges38 silver badges53 bronze badges

Comments

AlunR · Accepted Answer · 2011-05-18 09:46:32Z

I use a line of code to determine a bounding box surrounding the potential results then you are only running your query on a subset of data.

Eg max LNG, max LAT, min LNG, min LAT.

Otherwise your SQL statement will run against EVERY record in your table.

Our app has 28000 locations currently so the bounding box is VERY necessary!

class Geo{
function RadiusCheck($lat, $lng, $miles) {
    $EQUATOR_LAT_MILE = 69.172;
    $maxLat = $lat + $miles / $EQUATOR_LAT_MILE;
    $minLat = $lat - ($maxLat - $lat);
    $maxLng = $lng + $miles / (cos($minLat * M_PI / 180) * $EQUATOR_LAT_MILE);
    $minLng = $lng - ($maxLng - $lng);


    $result['minLat']   = $minLat;
    $result['maxLat']   = $maxLat;
    $result['minLng']   = $minLng;
    $result['maxLng']   = $maxLng;

    return $result;
}
}

Aaria Carter-Weir · Accepted Answer · 2011-05-17 22:18:06Z

0

Wait what? No! If you're returning thousands of results you can bet it'll be faster on an appropriately configured mysql server. Set the appropriate indexes, store your lat & long's as radians as CanSpice has suggested, and return only the results you need... sorted on the mysql server. @CanSpice is right about checking which is faster, but in my opinion, if your DB server is slower, you've configured it wrong. Fix it!

answered May 17, 2011 at 22:18

Aaria Carter-Weir

1,67912 silver badges17 bronze badges

Collectives™ on Stack Overflow

Sort results in mySQL or in PHP array?

5 Answers 5

2 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related