1

This query gives me the 50 closest addresses around a point:

 SELECT      
     company_has_address.companys_id,     
     (3959 * ACOS(COS(RADIANS(33.2003486)) * COS(RADIANS(geocodes_latitude)) * COS(RADIANS(geocodes_longitude) - RADIANS(-      86.7852465)) + SIN(RADIANS(33.2003486)) * SIN(RADIANS(geocodes_latitude)))) AS distance
 FROM
     geocodes
         LEFT JOIN
     company_has_address ON company_has_address.address_id = geocodes.address_id
 WHERE
     company_has_address.companys_id != 5884
 ORDER BY distance ASC
 LIMIT 50

Now, to make this useful, I need to join two tables: company_has_address, which is essentially a tuple of id, company_id, and address_id,

and then join (again) the company table.

This all works. Kind of.

The problem I have is that I want a list of the 10 closest DISTINCT company names even if a company has more than one address.

So, let's say company ABC has two addresses - a billing address and a shipping address. They are next door to one another. They show up TWICE in the first query.

Now, prior to MySQL 5.7, I could use GROUP BY to (randomly) pick one of the addresses and it would just give me one result. That worked great. But, now with 5.7, this no longer works, and produces a row for each of the addresses.

Question: how do I filter the first join (company_has_address) to only take the first company_id listed there so that when I join the company table, I can get unique listings? (I don't care if it's the shipping or PO address. They are - geographically - in the same place so which address it chooses is irrelevant).

3
  • Do you need exactly 50 ids? Would it matter if you, somehow, get only 40 or 35 (because you are skipping repeated ids)?
    – joanolo
    Jan 18, 2017 at 15:56
  • I actually - finally - only need 10. I am grabbing the first 50 here because I am assuming that all results have (at least) two addresses per location. Maybe more in some cases. If I grab 50, then the reduction to 10 should be doable in most circumstances.
    – DrDamnit
    Jan 18, 2017 at 16:01
  • Groupwise max
    – Rick James
    Jan 18, 2017 at 21:11

1 Answer 1

0

The next query is not doing exactly what you ask for in your title, but it conceptually does what you're looking for.

The inner SELECT computes the distances for all geocodes (JOIN company_address). The outer SELECT GROUPs BY company_id, gets the "minimum distance", sorts through it; the LIMIT 50 is respected.

SELECT
    companys_id, min(distance) AS distance
FROM
(
    SELECT      
         company_has_address.companys_id,     
         (3959 * ACOS(COS(RADIANS(33.2003486)) * COS(RADIANS(geocodes_latitude)) * COS(RADIANS(geocodes_longitude) - RADIANS(-86.7852465)) + SIN(RADIANS(33.2003486)) * SIN(RADIANS(geocodes_latitude)))) AS distance
    FROM
         geocodes
         LEFT JOIN company_has_address 
             ON company_has_address.address_id = geocodes.address_id
    WHERE
         company_has_address.companys_id != 5884
) AS s0
GROUP BY 
     companys_id
ORDER BY 
     distance ASC
LIMIT 
     50 ;

This query might not be efficient because it will scan your whole geocodes (x company_has_address) tables, but it does the job.

I would change the LEFT JOIN to a JOIN (does it make sense to have a geocode (address) with no company?).

Check it at http://rextester.com/NCBT28574


Side note: I actually don't know of any simple way to accelerate your search if the table is big; but you could probably "prefilter" your search within a certain (lat+delta_lat, lng+delta_lng)-(lat-delta_lat, lng-delta_lng) "box", and have proper indexes on lat and lng; this may not be perfect, or "prefilter" too much, but may speed up a lot your queries if the number of points is big enough.


Addendum

If you want to retrieve (lat, lng), use:

SELECT
    companys_id, min(distance) AS distance, geocodes_latitude AS lat, geocodes_longitude AS lng
FROM
(
    SELECT      
         company_has_address.companys_id,     
         (3959 * ACOS(COS(RADIANS(33.2003486)) * COS(RADIANS(geocodes_latitude)) * COS(RADIANS(geocodes_longitude) - RADIANS(-86.7852465)) + SIN(RADIANS(33.2003486)) * SIN(RADIANS(geocodes_latitude)))) AS distance,
         geocodes_latitude, geocodes_longitude
    FROM
         geocodes
         LEFT JOIN company_has_address ON company_has_address.address_id = geocodes.address_id
    WHERE
         company_has_address.companys_id != 5884
) AS s0
GROUP BY companys_id
ORDER BY distance ASC
LIMIT 50 ;

This is non-standard SQL (you cannot mix aggregates with values not GROUPed BY, however, mySQL allows for it, and may give you the (lat, lng) corresponding to the min distance. Other ways of getting to the same place involve the use of CTE (not yet on mySQL, already on MariaDB) or TEMP tables.

5
  • This works, but the minute I re-join the company_has_address table to get the company name, I get dupes again... which was why I asked this question in the first place. (That's sounds snarky, please don't take it that way. I can't find another way to say "My skill deficit prompted this question, and my learning curve is making it harder on you than I would prefer") :-)
    – DrDamnit
    Jan 18, 2017 at 16:27
  • I'm an idiot. I should be joining the company table. The other work has already been done.
    – DrDamnit
    Jan 18, 2017 at 16:28
  • So close. I need the latitude and longitude from the query on the very center of the subqueries, but when I start returning that, the group by creates non-distinct records....
    – DrDamnit
    Jan 18, 2017 at 16:36
  • Comments don't allow new code pastes. [Here's the problem I am (apparently) giving myself: ](pastebin.com/HYSpWh3G). I need the lat / long so I can populate a Google Map...
    – DrDamnit
    Jan 18, 2017 at 16:38
  • Check addendum to get (lat, lng)
    – joanolo
    Jan 18, 2017 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.