pogom Extractor

CREATE TABLE "pokemon_name" ("pokemon_id" INTEGER NOT NULL PRIMARY KEY, "pokemon_name" VARCHAR(255) NOT NULL);
INSERT INTO pokemon_name VALUES (1, "Bulbasaur");
INSERT INTO pokemon_name VALUES (2, "Ivysaur");
INSERT INTO pokemon_name VALUES (3, "Venusaur");
INSERT INTO pokemon_name VALUES (4, "Charmander");
INSERT INTO pokemon_name VALUES (5, "Charmeleon");
INSERT INTO pokemon_name VALUES (6, "Charizard");
INSERT INTO pokemon_name VALUES (7, "Squirtle");
INSERT INTO pokemon_name VALUES (8, "Wartortle");
INSERT INTO pokemon_name VALUES (9, "Blastoise");
INSERT INTO pokemon_name VALUES (10, "Caterpie");
INSERT INTO pokemon_name VALUES (11, "Metapod");
INSERT INTO pokemon_name VALUES (12, "Butterfree");
INSERT INTO pokemon_name VALUES (13, "Weedle");
INSERT INTO pokemon_name VALUES (14, "Kakuna");
INSERT INTO pokemon_name VALUES (15, "Beedrill");
INSERT INTO pokemon_name VALUES (16, "Pidgey");
INSERT INTO pokemon_name VALUES (17, "Pidgeotto");
INSERT INTO pokemon_name VALUES (18, "Pidgeot");
INSERT INTO pokemon_name VALUES (19, "Rattata");
INSERT INTO pokemon_name VALUES (20, "Raticate");
INSERT INTO pokemon_name VALUES (21, "Spearow");
INSERT INTO pokemon_name VALUES (22, "Fearow");
INSERT INTO pokemon_name VALUES (23, "Ekans");
INSERT INTO pokemon_name VALUES (24, "Arbok");
INSERT INTO pokemon_name VALUES (25, "Pikachu");
INSERT INTO pokemon_name VALUES (26, "Raichu");
INSERT INTO pokemon_name VALUES (27, "Sandshrew");
INSERT INTO pokemon_name VALUES (28, "Sandslash");
INSERT INTO pokemon_name VALUES (29, "Nidoran Female");
INSERT INTO pokemon_name VALUES (30, "Nidorina");
INSERT INTO pokemon_name VALUES (31, "Nidoqueen");
INSERT INTO pokemon_name VALUES (32, "Nidoran Male");
INSERT INTO pokemon_name VALUES (33, "Nidorino");
INSERT INTO pokemon_name VALUES (34, "Nidoking");
INSERT INTO pokemon_name VALUES (35, "Clefairy");
INSERT INTO pokemon_name VALUES (36, "Clefable");
INSERT INTO pokemon_name VALUES (37, "Vulpix");
INSERT INTO pokemon_name VALUES (38, "Ninetales");
INSERT INTO pokemon_name VALUES (39, "Jigglypuff");
INSERT INTO pokemon_name VALUES (40, "Wigglytuff");
INSERT INTO pokemon_name VALUES (41, "Zubat");
INSERT INTO pokemon_name VALUES (42, "Golbat");
INSERT INTO pokemon_name VALUES (43, "Oddish");
INSERT INTO pokemon_name VALUES (44, "Gloom");
INSERT INTO pokemon_name VALUES (45, "Vileplume");
INSERT INTO pokemon_name VALUES (46, "Paras");
INSERT INTO pokemon_name VALUES (47, "Parasect");
INSERT INTO pokemon_name VALUES (48, "Venonat");
INSERT INTO pokemon_name VALUES (49, "Venomoth");
INSERT INTO pokemon_name VALUES (50, "Diglett");
INSERT INTO pokemon_name VALUES (51, "Dugtrio");
INSERT INTO pokemon_name VALUES (52, "Meowth");
INSERT INTO pokemon_name VALUES (53, "Persian");
INSERT INTO pokemon_name VALUES (54, "Psyduck");
INSERT INTO pokemon_name VALUES (55, "Golduck");
INSERT INTO pokemon_name VALUES (56, "Mankey");
INSERT INTO pokemon_name VALUES (57, "Primeape");
INSERT INTO pokemon_name VALUES (58, "Growlithe");
INSERT INTO pokemon_name VALUES (59, "Arcanine");
INSERT INTO pokemon_name VALUES (60, "Poliwag");
INSERT INTO pokemon_name VALUES (61, "Poliwhirl");
INSERT INTO pokemon_name VALUES (62, "Poliwrath");
INSERT INTO pokemon_name VALUES (63, "Abra");
INSERT INTO pokemon_name VALUES (64, "Kadabra");
INSERT INTO pokemon_name VALUES (65, "Alakazam");
INSERT INTO pokemon_name VALUES (66, "Machop");
INSERT INTO pokemon_name VALUES (67, "Machoke");
INSERT INTO pokemon_name VALUES (68, "Machamp");
INSERT INTO pokemon_name VALUES (69, "Bellsprout");
INSERT INTO pokemon_name VALUES (70, "Weepinbell");
INSERT INTO pokemon_name VALUES (71, "Victreebel");
INSERT INTO pokemon_name VALUES (72, "Tentacool");
INSERT INTO pokemon_name VALUES (73, "Tentacruel");
INSERT INTO pokemon_name VALUES (74, "Geodude");
INSERT INTO pokemon_name VALUES (75, "Graveler");
INSERT INTO pokemon_name VALUES (76, "Golem");
INSERT INTO pokemon_name VALUES (77, "Ponyta");
INSERT INTO pokemon_name VALUES (78, "Rapidash");
INSERT INTO pokemon_name VALUES (79, "Slowpoke");
INSERT INTO pokemon_name VALUES (80, "Slowbro");
INSERT INTO pokemon_name VALUES (81, "Magnemite");
INSERT INTO pokemon_name VALUES (82, "Magneton");
INSERT INTO pokemon_name VALUES (83, "Farfetch'd");
INSERT INTO pokemon_name VALUES (84, "Doduo");
INSERT INTO pokemon_name VALUES (85, "Dodrio");
INSERT INTO pokemon_name VALUES (86, "Seel");
INSERT INTO pokemon_name VALUES (87, "Dewgong");
INSERT INTO pokemon_name VALUES (88, "Grimer");
INSERT INTO pokemon_name VALUES (89, "Muk");
INSERT INTO pokemon_name VALUES (90, "Shellder");
INSERT INTO pokemon_name VALUES (91, "Cloyster");
INSERT INTO pokemon_name VALUES (92, "Gastly");
INSERT INTO pokemon_name VALUES (93, "Haunter");
INSERT INTO pokemon_name VALUES (94, "Gengar");
INSERT INTO pokemon_name VALUES (95, "Onix");
INSERT INTO pokemon_name VALUES (96, "Drowzee");
INSERT INTO pokemon_name VALUES (97, "Hypno");
INSERT INTO pokemon_name VALUES (98, "Krabby");
INSERT INTO pokemon_name VALUES (99, "Kingler");
INSERT INTO pokemon_name VALUES (100, "Voltorb");
INSERT INTO pokemon_name VALUES (101, "Electrode");
INSERT INTO pokemon_name VALUES (102, "Exeggcute");
INSERT INTO pokemon_name VALUES (103, "Exeggutor");
INSERT INTO pokemon_name VALUES (104, "Cubone");
INSERT INTO pokemon_name VALUES (105, "Marowak");
INSERT INTO pokemon_name VALUES (106, "Hitmonlee");
INSERT INTO pokemon_name VALUES (107, "Hitmonchan");
INSERT INTO pokemon_name VALUES (108, "Lickitung");
INSERT INTO pokemon_name VALUES (109, "Koffing");
INSERT INTO pokemon_name VALUES (110, "Weezing");
INSERT INTO pokemon_name VALUES (111, "Rhyhorn");
INSERT INTO pokemon_name VALUES (112, "Rhydon");
INSERT INTO pokemon_name VALUES (113, "Chansey");
INSERT INTO pokemon_name VALUES (114, "Tangela");
INSERT INTO pokemon_name VALUES (115, "Kangaskhan");
INSERT INTO pokemon_name VALUES (116, "Horsea");
INSERT INTO pokemon_name VALUES (117, "Seadra");
INSERT INTO pokemon_name VALUES (118, "Goldeen");
INSERT INTO pokemon_name VALUES (119, "Seaking");
INSERT INTO pokemon_name VALUES (120, "Staryu");
INSERT INTO pokemon_name VALUES (121, "Starmie");
INSERT INTO pokemon_name VALUES (122, "Mr. Mime");
INSERT INTO pokemon_name VALUES (123, "Scyther");
INSERT INTO pokemon_name VALUES (124, "Jynx");
INSERT INTO pokemon_name VALUES (125, "Electabuzz");
INSERT INTO pokemon_name VALUES (126, "Magmar");
INSERT INTO pokemon_name VALUES (127, "Pinsir");
INSERT INTO pokemon_name VALUES (128, "Tauros");
INSERT INTO pokemon_name VALUES (129, "Magikarp");
INSERT INTO pokemon_name VALUES (130, "Gyarados");
INSERT INTO pokemon_name VALUES (131, "Lapras");
INSERT INTO pokemon_name VALUES (132, "Ditto");
INSERT INTO pokemon_name VALUES (133, "Eevee");
INSERT INTO pokemon_name VALUES (134, "Vaporeon");
INSERT INTO pokemon_name VALUES (135, "Jolteon");
INSERT INTO pokemon_name VALUES (136, "Flareon");
INSERT INTO pokemon_name VALUES (137, "Porygon");
INSERT INTO pokemon_name VALUES (138, "Omanyte");
INSERT INTO pokemon_name VALUES (139, "Omastar");
INSERT INTO pokemon_name VALUES (140, "Kabuto");
INSERT INTO pokemon_name VALUES (141, "Kabutops");
INSERT INTO pokemon_name VALUES (142, "Aerodactyl");
INSERT INTO pokemon_name VALUES (143, "Snorlax");
INSERT INTO pokemon_name VALUES (144, "Articuno");
INSERT INTO pokemon_name VALUES (145, "Zapdos");
INSERT INTO pokemon_name VALUES (146, "Moltres");
INSERT INTO pokemon_name VALUES (147, "Dratini");
INSERT INTO pokemon_name VALUES (148, "Dragonair");
INSERT INTO pokemon_name VALUES (149, "Dragonite");
INSERT INTO pokemon_name VALUES (150, "Mewtwo");
INSERT INTO pokemon_name VALUES (151, "Mew");

SELECT latitude
    , longitude
    , SUBSTR(disappear_time, 15, 5) || ' ' || GROUP_CONCAT(DISTINCT(pn.pokemon_name)) AS 'Label'
    , 'http://pokespy.stts.edu/icons/'|| CASE(COUNT(1)) WHEN 1 THEN p.pokemon_id ELSE MAX(p.pokemon_id) END || '.png' AS 'Icon'
    , '      <Placemark>
        <name>'||SUBSTR(disappear_time, 15, 5) || ' ' || GROUP_CONCAT(DISTINCT(pn.pokemon_name))||'</name>
        <description>'||SUBSTR(disappear_time, 15, 5) || ' ' || GROUP_CONCAT(DISTINCT(pn.pokemon_name))||'</description>
       <styleUrl>#Icon'||CASE(COUNT(1)) WHEN 1 THEN p.pokemon_id ELSE MAX(p.pokemon_id) END||'</styleUrl>
        <Point>
          <coordinates>'||longitude||','||latitude||',0</coordinates>
        </Point>
      </Placemark>' AS KML_PlaceMark
FROM pokemon p
INNER JOIN pokemon_name pn
ON pn.pokemon_id = p.pokemon_id
WHERE p.pokemon_id NOT IN (16,19,21)
GROUP BY p.latitude, p.longitude
ORDER BY Label;