Skip to content

Instantly share code, notes, and snippets.

@mlebkowski
Created September 5, 2013 14:32
Show Gist options
  • Save mlebkowski/6450911 to your computer and use it in GitHub Desktop.
Save mlebkowski/6450911 to your computer and use it in GitHub Desktop.
SELECT
zero.name,
(min(lng - offset - if(lng-offset>180, 360, 0)) + 360) % 360 + offset as min,
max(lng - offset - if(lng-offset>=180, 360, 0)) + offset as max
FROM
(SELECT
min(lng) as offset,
name FROM places group by name
) as zero
LEFT JOIN places ON (places.name = zero.name)
GROUP BY places.name
-- http://sqlfiddle.com/#!2/383a6/2
CREATE TABLE `places` (`name` CHAR(50), `lng` DOUBLE(10,7));
INSERT INTO `places` (`name`, `lng`)
VALUES
('Warszawa', 19.1),
('Warszawa', 19.3),
('Warszawa', 20),
('Warszawa', 21.2),
('Warszawa', 21.71),
('Gironde', -1.14),
('Gironde', -1.10),
('Gironde', -1.0133),
('Gironde', -0.98199),
('Gironde', -0.85411),
('Gironde', -0.9371),
('Gironde', -0.7303),
('Gironde', -0.07),
('Gironde', 0.06067),
('Gironde', 0.06093),
('Gironde', 0.03419),
('Gironde', 0.07188),
('Gironde', 0.31034),
('Чукотский АО', 167.98694),
('Чукотский АО', 161.63333),
('Чукотский АО', -179.26667),
('Чукотский АО', 169.90298),
('Чукотский АО', 169.90298),
('Чукотский АО', 176.16238),
('Чукотский АО', 166.33333),
('Чукотский АО', 177.67938),
('Чукотский АО', 177.67938),
('Чукотский АО', -170.28333),
('Чукотский АО', 167),
("c1", -173),("c1", +171), ("c1", +176), ("c1", +179), ("c1", -175),
("a1", -173),("a1", +174), ("a1", +176), ("a1", +179), ("a1", -175),
("a2", -93),("a2", -84), ("a2", -91),("a2", -89),
("a3", -5),("a3", 5), ("a3", -3),("a3", 1),
("a4", 85),("a4", 95), ("a4", 87),("a4", 91),
("b1", -179),("b1", -173), ("b1", -176), ("b1", -174), ("b1", -175),
("b2", 93),("b2", 99), ("b2", 91),("b2", 94),
("b3", -5),("b3", -1), ("b3", -3),("b3", -2),
("b4", 87),("b4", 81), ("b4", 86),("b4", 84),
("z", -87),("z", -88),("z", -89),("z", -90), ("z", -91), ("z", -92), ("z", -93),
("y", 87),("y", 88),("y", 89),("y", 90), ("y", 91), ("y", 92), ("y", 93),
("x", 150),("x", 155),("x", 160),("x", 170), ("x", 175), ("x", -175), ("x", -170)
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment