EnumaElis
EnumaElis

Reputation: 1

Perimeter search with a maximum of 1 item of a specific group

I have a table of shops with some chains among them. When I do a proximity search, I want only the closest shop of each chain to be included in the result. However, I want all shops from the non-chains to be found.

My idea is to assign a group to the chain shops and then filter them in the SQL - but how?

shop_id shop shop_lat shop_lon chaingroup
1 SHOP1 55.8 9.4 NULL
2 SHOP2 55.9 9.5 2
3 SHOP3 55.1 9.6 NULL
4 SHOP4 55.2 9.7 3
5 SHOP5 55.3 9.8 NULL
6 SHOP6 55.4 9.9 2
7 SHOP7 55.5 10.0 NULL
8 SHOP8 55.6 10.1 5
9 SHOP9 55.7 10.2 NULL
10 SHOP10 55.8 10.3 5

My SQL:

SELECT shop, ((ACOS(SIN(53.58393 * PI() / 180) * SIN(shop_lat * PI() / 180) + COS(53.58393 * PI() / 180) * COS(shop_lat * PI() / 180) * COS((10.01413 - shop_lon) * PI() / 180)) * 180 / PI()) * 60 * 1.150779448 * 1.609344) as distance, chaingroup
FROM shops
GROUP BY shop_id
HAVING distance <= 10 
ORDER BY distance ASC

Result should be:

shop distance chaingroup
SHOP1 1.0 NULL
SHOP2 2.0 2
SHOP3 3.0 NULL
SHOP4 4.0 3
SHOP5 5.0 NULL
SHOP7 7.0 NULL
SHOP8 8.0 5
SHOP9 9.0 NULL

As you can see, SHOP10 is not listed because SHOP8 from chain no. 5 has already been found.

Any suggestions?

Upvotes: 0

Views: 51

Answers (0)

Related Questions