How to use one Mysql query instead of query inside array loop?

Question

The query below is currently within a loop(in PHP) that takes latitude, longitude, and id inputs from 3 different arrays. For each position/key in the array the nearest geographical place is selected from the table using the query below.

As the size of the array is typically around 500, so 500 sets of inputs, for which a nearest geographical position needs to be found it is taking some time.

My question: Is there any way to use one query using the input arrays from php at once, and selecting a set of all the nearest geographical positions for each array key?

$sql="SELECT 
id, d, tolon, tolat, du,
       ( 3959 * acos( cos( radians($lat1) ) 
              * cos( radians( travel.tolat ) ) 
              * cos( radians( travel.tolon) - radians($lon1) ) 
              + sin( radians($lat1) ) 
              * sin( radians( travel.tolat ) ) ) ) AS distance 
FROM travel 
WHERE userid = $id AND tolon > $lonlow AND tolon < $lonhigh AND tolat > $latlow AND tolat < $lathigh
having distance < $maxdistance ORDER BY distance
";

worse case solution would be a UNION, but some one will post a better idea im sure. Have you tried ..OR.. — user557846
– user557846, Commented May 6, 2012 at 23:47
I'd try inserting the array into a temp table and then a regular join could be used. At least it's a starting point — ingo
– ingo, Commented May 6, 2012 at 23:54
@ ingo Well it's not easy to join them since the join is defined by the minimum distance between geographical coordinates in the two tables, which are calculated by a formula. — BastiaanWW
– BastiaanWW, Commented May 7, 2012 at 0:08
You don't need a loop if your database supports LATERAL query. Unfortunately, the database that I know that has LATERAL functionality are DB2 and SQL Server. In SQL Server it's called CROSS APPLY or OUTER APPLY. Postgresql has plan to support LATERAL too. See example: stackoverflow.com/questions/2035312/… An OUTER APPLY walkthrough ienablemuch.com/2012/04/outer-apply-walkthrough.html — Michael Buen
– Michael Buen, Commented May 7, 2012 at 0:45

goat · Accepted Answer · 2012-05-07 03:30:43Z

1

I don't think you'll do much better than a bunch of unions, although adding a limit 1 is a good idea if you only need the closest. A bunch of unions will be a very long sql string, and there's limits you may hit(max packet size is the setting, i believe). You would have to maybe chunk 100 or so unions at a time.

You stand to gain a lot if the table had indexes designed for this type of query. mysql has suport for spatial indexes that you should seriously consider looking into because regular indexes dont perform very well for this use case.

answered May 7, 2012 at 3:30

goat

32k7 gold badges76 silver badges98 bronze badges

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

1 Comment

BastiaanWW Over a year ago

In addition to this answer I used unique values from the arrays to limit the number of records required from the database.

Collectives™ on Stack Overflow

How to use one Mysql query instead of query inside array loop?

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related