API tools faq
paste
Advertisement
Guest User

KML Extrator (w/ rarity)

a guest
Aug 9th, 2016
1,344
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 12.83 KB | None | 0 0
raw download clone embed print report
  1. CREATE TABLE "pokemon_info" ("pokemon_id" INTEGER NOT NULL PRIMARY KEY, "pokemon_name_en" VARCHAR(255) NOT NULL, "pokemon_name_fr" VARCHAR(255) NOT NULL, "pokemon_rarity" VARCHAR(255) NOT NULL, "pokemon_spawn_rate" INTEGER NOT NULL);
  2.  
  3. INSERT INTO pokemon_info VALUES (1,"Bulbasaur","Bulbizarre","Uncommon",61);
  4. INSERT INTO pokemon_info VALUES (2,"Ivysaur","Herbizarre","Rare",421);
  5. INSERT INTO pokemon_info VALUES (3,"Venusaur","Florizarre","Very Rare",641);
  6. INSERT INTO pokemon_info VALUES (4,"Charmander","Salamèche","Rare",119);
  7. INSERT INTO pokemon_info VALUES (5,"Charmeleon","Reptincel","Very Rare",1025);
  8. INSERT INTO pokemon_info VALUES (6,"Charizard","Dracaufeu","Very Rare",879);
  9. INSERT INTO pokemon_info VALUES (7,"Squirtle","Carapuce","Uncommon",65);
  10. INSERT INTO pokemon_info VALUES (8,"Wartortle","Carabaffe","Rare",484);
  11. INSERT INTO pokemon_info VALUES (9,"Blastoise","Tortank","Very Rare",843);
  12. INSERT INTO pokemon_info VALUES (10,"Caterpie","Chenipan","Uncommon",51);
  13. INSERT INTO pokemon_info VALUES (11,"Metapod","Chrysacier","Rare",433);
  14. INSERT INTO pokemon_info VALUES (12,"Butterfree","Papilusion","Very Rare",843);
  15. INSERT INTO pokemon_info VALUES (13,"Weedle","Aspicot","Common",21);
  16. INSERT INTO pokemon_info VALUES (14,"Kakuna","Coconfort","Rare",204);
  17. INSERT INTO pokemon_info VALUES (15,"Beedrill","Dardargnan","Rare",377);
  18. INSERT INTO pokemon_info VALUES (16,"Pidgey","Roucool","Common",10);
  19. INSERT INTO pokemon_info VALUES (17,"Pidgeotto","Roucoups","Uncommon",75);
  20. INSERT INTO pokemon_info VALUES (18,"Pidgeot","Roucarnage","Rare",203);
  21. INSERT INTO pokemon_info VALUES (19,"Rattata","Rattata","Common",13);
  22. INSERT INTO pokemon_info VALUES (20,"Raticate","Rattatac","Rare",159);
  23. INSERT INTO pokemon_info VALUES (21,"Spearow","Piafabec","Uncommon",31);
  24. INSERT INTO pokemon_info VALUES (22,"Fearow","Rapasdepic","Rare",221);
  25. INSERT INTO pokemon_info VALUES (23,"Ekans","Abo","Uncommon",75);
  26. INSERT INTO pokemon_info VALUES (24,"Arbok","Arbok","Very Rare",544);
  27. INSERT INTO pokemon_info VALUES (25,"Pikachu","Pikachu","Uncommon",92);
  28. INSERT INTO pokemon_info VALUES (26,"Raichu","Raichu","Ultra Rare",2051);
  29. INSERT INTO pokemon_info VALUES (27,"Sandshrew","Sabelette","Rare",163);
  30. INSERT INTO pokemon_info VALUES (28,"Sandslash","Sablaireau","Ultra Rare",1619);
  31. INSERT INTO pokemon_info VALUES (29,"Nidoran Female","Nidoran Femelle","Uncommon",77);
  32. INSERT INTO pokemon_info VALUES (30,"Nidorina","Nidorina","Very Rare",504);
  33. INSERT INTO pokemon_info VALUES (31,"Nidoqueen","Nidoqueen","Very Rare",1398);
  34. INSERT INTO pokemon_info VALUES (32,"Nidoran Male","Nidoran Male","Uncommon",77);
  35. INSERT INTO pokemon_info VALUES (33,"Nidorino","Nidorino","Very Rare",586);
  36. INSERT INTO pokemon_info VALUES (34,"Nidoking","Nidoking","Very Rare",1025);
  37. INSERT INTO pokemon_info VALUES (35,"Clefairy","Mélofée","Uncommon",85);
  38. INSERT INTO pokemon_info VALUES (36,"Clefable","Mélodelfe","Very Rare",1309);
  39. INSERT INTO pokemon_info VALUES (37,"Vulpix","Goupix","Rare",188);
  40. INSERT INTO pokemon_info VALUES (38,"Ninetales","Feunard","Ultra Rare",1538);
  41. INSERT INTO pokemon_info VALUES (39,"Jigglypuff","Rondoudou","Rare",101);
  42. INSERT INTO pokemon_info VALUES (40,"Wigglytuff","Grodoudou","Ultra Rare",2051);
  43. INSERT INTO pokemon_info VALUES (41,"Zubat","Nosferapti","Common",24);
  44. INSERT INTO pokemon_info VALUES (42,"Golbat","Nosferalto","Rare",209);
  45. INSERT INTO pokemon_info VALUES (43,"Oddish","Mystherbe","Uncommon",91);
  46. INSERT INTO pokemon_info VALUES (44,"Gloom","Ortide","Very Rare",592);
  47. INSERT INTO pokemon_info VALUES (45,"Vileplume","Rafflesia","Ultra Rare",1864);
  48. INSERT INTO pokemon_info VALUES (46,"Paras","Paras","Uncommon",44);
  49. INSERT INTO pokemon_info VALUES (47,"Parasect","Parasect","Rare",397);
  50. INSERT INTO pokemon_info VALUES (48,"Venonat","Mimitoss","Uncommon",38);
  51. INSERT INTO pokemon_info VALUES (49,"Venomoth","Aéromite","Rare",300);
  52. INSERT INTO pokemon_info VALUES (50,"Diglett","Taupiqueur","Rare",211);
  53. INSERT INTO pokemon_info VALUES (51,"Dugtrio","Triopikeur","Ultra Rare",1663);
  54. INSERT INTO pokemon_info VALUES (52,"Meowth","Miaouss","Rare",106);
  55. INSERT INTO pokemon_info VALUES (53,"Persian","Persian","Very Rare",1282);
  56. INSERT INTO pokemon_info VALUES (54,"Psyduck","Psykokwak","Rare",108);
  57. INSERT INTO pokemon_info VALUES (55,"Golduck","Akwakwak","Very Rare",707);
  58. INSERT INTO pokemon_info VALUES (56,"Mankey","Férosinge","Rare",114);
  59. INSERT INTO pokemon_info VALUES (57,"Primeape","Colossinge","Very Rare",1061);
  60. INSERT INTO pokemon_info VALUES (58,"Growlithe","Caninos","Uncommon",77);
  61. INSERT INTO pokemon_info VALUES (59,"Arcanine","Arcanin","Very Rare",932);
  62. INSERT INTO pokemon_info VALUES (60,"Poliwag","Ptitard","Uncommon",95);
  63. INSERT INTO pokemon_info VALUES (61,"Poliwhirl","Têtarte","Rare",481);
  64. INSERT INTO pokemon_info VALUES (62,"Poliwrath","Tartard","Ultra Rare",1709);
  65. INSERT INTO pokemon_info VALUES (63,"Abra","Abra","Rare",104);
  66. INSERT INTO pokemon_info VALUES (64,"Kadabra","Kadabra","Very Rare",779);
  67. INSERT INTO pokemon_info VALUES (65,"Alakazam","Alakazam","Ultra Rare",2461);
  68. INSERT INTO pokemon_info VALUES (66,"Machop","Machoc","Rare",217);
  69. INSERT INTO pokemon_info VALUES (67,"Machoke","Machopeur","Very Rare",1465);
  70. INSERT INTO pokemon_info VALUES (68,"Machamp","Mackogneur","Ultra Rare",3418);
  71. INSERT INTO pokemon_info VALUES (69,"Bellsprout","Chétiflor","Uncommon",86);
  72. INSERT INTO pokemon_info VALUES (70,"Weepinbell","Boustiflor","Rare",496);
  73. INSERT INTO pokemon_info VALUES (71,"Victreebel","Empiflor","Ultra Rare",2051);
  74. INSERT INTO pokemon_info VALUES (72,"Tentacool","Tentacool","Rare",275);
  75. INSERT INTO pokemon_info VALUES (73,"Tentacruel","Tentacruel","Very Rare",615);
  76. INSERT INTO pokemon_info VALUES (74,"Geodude","Racaillou","Rare",121);
  77. INSERT INTO pokemon_info VALUES (75,"Graveler","Gravalanch","Very Rare",707);
  78. INSERT INTO pokemon_info VALUES (76,"Golem","Grolem","Ultra Rare",2797);
  79. INSERT INTO pokemon_info VALUES (77,"Ponyta","Ponyta","Rare",121);
  80. INSERT INTO pokemon_info VALUES (78,"Rapidash","Galopa","Very Rare",992);
  81. INSERT INTO pokemon_info VALUES (79,"Slowpoke","Ramoloss","Rare",175);
  82. INSERT INTO pokemon_info VALUES (80,"Slowbro","Flagadoss","Very Rare",947);
  83. INSERT INTO pokemon_info VALUES (81,"Magnemite","Magnéti","Rare",200);
  84. INSERT INTO pokemon_info VALUES (82,"Magneton","Magnéton","Ultra Rare",2051);
  85. INSERT INTO pokemon_info VALUES (83,"Farfetch'd","Canarticho","Ultra Rare",61527);
  86. INSERT INTO pokemon_info VALUES (84,"Doduo","Doduo","Uncommon",77);
  87. INSERT INTO pokemon_info VALUES (85,"Dodrio","Dodrio","Very Rare",634);
  88. INSERT INTO pokemon_info VALUES (86,"Seel","Otaria","Rare",196);
  89. INSERT INTO pokemon_info VALUES (87,"Dewgong","Lamantine","Ultra Rare",1578);
  90. INSERT INTO pokemon_info VALUES (88,"Grimer","Tadmorv","Very Rare",879);
  91. INSERT INTO pokemon_info VALUES (89,"Muk","Grotadmorv","Ultra Rare",7691);
  92. INSERT INTO pokemon_info VALUES (90,"Shellder","Kokiyas","Rare",177);
  93. INSERT INTO pokemon_info VALUES (91,"Cloyster","Crustabri","Ultra Rare",1578);
  94. INSERT INTO pokemon_info VALUES (92,"Gastly","Fantominus","Uncommon",77);
  95. INSERT INTO pokemon_info VALUES (93,"Haunter","Spectrum","Rare",419);
  96. INSERT INTO pokemon_info VALUES (94,"Gengar","Ectoplasma","Ultra Rare",1758);
  97. INSERT INTO pokemon_info VALUES (95,"Onix","Onix","Rare",269);
  98. INSERT INTO pokemon_info VALUES (96,"Drowzee","Soporifik","Uncommon",39);
  99. INSERT INTO pokemon_info VALUES (97,"Hypno","Hypnomade","Rare",322);
  100. INSERT INTO pokemon_info VALUES (98,"Krabby","Krabby","Uncommon",73);
  101. INSERT INTO pokemon_info VALUES (99,"Kingler","Krabboss","Very Rare",530);
  102. INSERT INTO pokemon_info VALUES (100,"Voltorb","Voltorbe","Rare",276);
  103. INSERT INTO pokemon_info VALUES (101,"Electrode","Electrode","Ultra Rare",1985);
  104. INSERT INTO pokemon_info VALUES (102,"Exeggcute","Noeunoeuf","Rare",123);
  105. INSERT INTO pokemon_info VALUES (103,"Exeggutor","Noadkoko","Very Rare",1231);
  106. INSERT INTO pokemon_info VALUES (104,"Cubone","Osselait","Rare",163);
  107. INSERT INTO pokemon_info VALUES (105,"Marowak","Ossatueur","Very Rare",1431);
  108. INSERT INTO pokemon_info VALUES (106,"Hitmonlee","Kicklee","Very Rare",504);
  109. INSERT INTO pokemon_info VALUES (107,"Hitmonchan","Tygnon","Rare",446);
  110. INSERT INTO pokemon_info VALUES (108,"Lickitung","Excelangue","Rare",443);
  111. INSERT INTO pokemon_info VALUES (109,"Koffing","Smogo","Rare",213);
  112. INSERT INTO pokemon_info VALUES (110,"Weezing","Smogogo","Very Rare",1398);
  113. INSERT INTO pokemon_info VALUES (111,"Rhyhorn","Rhinocorne","Rare",110);
  114. INSERT INTO pokemon_info VALUES (112,"Rhydon","Rhinoféros","Very Rare",867);
  115. INSERT INTO pokemon_info VALUES (113,"Chansey","Leveinard","Very Rare",1256);
  116. INSERT INTO pokemon_info VALUES (114,"Tangela","Saquedeneu","Rare",346);
  117. INSERT INTO pokemon_info VALUES (115,"Kangaskhan","Kanougrex","Ultra Rare",2461);
  118. INSERT INTO pokemon_info VALUES (116,"Horsea","Hypotrempe","Rare",104);
  119. INSERT INTO pokemon_info VALUES (117,"Seadra","Hypocéan","Very Rare",760);
  120. INSERT INTO pokemon_info VALUES (118,"Goldeen","Poissirène","Rare",103);
  121. INSERT INTO pokemon_info VALUES (119,"Seaking","Poissoroy","Very Rare",684);
  122. INSERT INTO pokemon_info VALUES (120,"Staryu","Stari","Uncommon",97);
  123. INSERT INTO pokemon_info VALUES (121,"Starmie","Staross","Very Rare",1206);
  124. INSERT INTO pokemon_info VALUES (122,"Mr. Mime","M.Mime","Very Rare",1431);
  125. INSERT INTO pokemon_info VALUES (123,"Scyther","Insécateur","Rare",155);
  126. INSERT INTO pokemon_info VALUES (124,"Jynx","Lippoutou","Rare",152);
  127. INSERT INTO pokemon_info VALUES (125,"Electabuzz","Elektek","Rare",261);
  128. INSERT INTO pokemon_info VALUES (126,"Magmar","Magmar","Rare",213);
  129. INSERT INTO pokemon_info VALUES (127,"Pinsir","Scarabrute","Uncommon",99);
  130. INSERT INTO pokemon_info VALUES (128,"Tauros","Tauros","Rare",153);
  131. INSERT INTO pokemon_info VALUES (129,"Magikarp","Magicarpe","Uncommon",79);
  132. INSERT INTO pokemon_info VALUES (130,"Gyarados","Leviator","Ultra Rare",2279);
  133. INSERT INTO pokemon_info VALUES (131,"Lapras","Lokhlass","Very Rare",1183);
  134. INSERT INTO pokemon_info VALUES (132,"Ditto","Métamorph","Ultra Rare",30764);
  135. INSERT INTO pokemon_info VALUES (133,"Eevee","Evoli","Common",24);
  136. INSERT INTO pokemon_info VALUES (134,"Vaporeon","Aquali","Very Rare",1465);
  137. INSERT INTO pokemon_info VALUES (135,"Jolteon","Voltali","Very Rare",1309);
  138. INSERT INTO pokemon_info VALUES (136,"Flareon","Pyroli","Ultra Rare",1568);
  139. INSERT INTO pokemon_info VALUES (137,"Porygon","Porygon","Very Rare",867);
  140. INSERT INTO pokemon_info VALUES (138,"Omanyte","Amonita","Rare",346);
  141. INSERT INTO pokemon_info VALUES (139,"Omastar","Amonistar","Ultra Rare",6153);
  142. INSERT INTO pokemon_info VALUES (140,"Kabuto","Kabuto","Rare",356);
  143. INSERT INTO pokemon_info VALUES (141,"Kabutops","Kabutops","Ultra Rare",2930);
  144. INSERT INTO pokemon_info VALUES (142,"Aerodactyl","Ptéra","Very Rare",879);
  145. INSERT INTO pokemon_info VALUES (143,"Snorlax","Ronflex","Rare",284);
  146. INSERT INTO pokemon_info VALUES (144,"Articuno","Artikodin","Ultra Rare",99999);
  147. INSERT INTO pokemon_info VALUES (145,"Zapdos","Electhor","Ultra Rare",99999);
  148. INSERT INTO pokemon_info VALUES (146,"Moltres","Sulfura","Ultra Rare",99999);
  149. INSERT INTO pokemon_info VALUES (147,"Dratini","Minidraco","Uncommon",91);
  150. INSERT INTO pokemon_info VALUES (148,"Dragonair","Draco","Very Rare",699);
  151. INSERT INTO pokemon_info VALUES (149,"Dragonite","Dracolosse","Very Rare",580);
  152. INSERT INTO pokemon_info VALUES (150,"Mewtwo","Mewtwo","Ultra Rare",99999);
  153. INSERT INTO pokemon_info VALUES (151,"Mew","Mew","Ultra Rare",99999);
  154.  
  155. SELECT latitude
  156.     , longitude
  157.     , SUBSTR(disappear_time, 15, 5) || ' (' || COUNT(1) || ') ' || GROUP_CONCAT(DISTINCT(pi.pokemon_name_en)) AS 'Label'
  158.     , 'http://pokespy.stts.edu/icons/'|| CASE(COUNT(1)) WHEN 1 THEN p.pokemon_id ELSE (SELECT pokemon_id FROM pokemon_info WHERE pokemon_id IN (SELECT pokemon_id FROM pokemon WHERE latitude = p.latitude AND longitude = p.longitude) ORDER BY pokemon_spawn_rate DESC LIMIT 1) END || '.png' AS 'Icon'
  159.     , '      <Placemark>
  160.        <name>'||SUBSTR(disappear_time, 15, 5) || ' (' || COUNT(1) || ') ' || GROUP_CONCAT(DISTINCT(pi.pokemon_name_en))||'</name>
  161.        <description>'||SUBSTR(disappear_time, 15, 5) || ' (' || COUNT(1) || ') ' || GROUP_CONCAT(DISTINCT(pi.pokemon_name_en))||'</description>
  162.        <styleUrl>#Icon'||CASE(COUNT(1)) WHEN 1 THEN p.pokemon_id ELSE (SELECT pokemon_id FROM pokemon_info WHERE pokemon_id IN (SELECT pokemon_id FROM pokemon WHERE latitude = p.latitude AND longitude = p.longitude) ORDER BY pokemon_spawn_rate DESC LIMIT 1) END||'</styleUrl>
  163.        <Point>
  164.          <coordinates>'||longitude||','||latitude||',0</coordinates>
  165.        </Point>
  166.       </Placemark>' AS KML_PlaceMark     
  167. FROM pokemon p
  168. INNER JOIN pokemon_info pi
  169. ON pi.pokemon_id = p.pokemon_id
  170. WHERE p.pokemon_id IN (SELECT pokemon_id FROM pokemon_info WHERE pokemon_spawn_rate >= 24)
  171. GROUP BY p.latitude, p.longitude, SUBSTR(disappear_time, 15, 5)
  172. ORDER BY SUBSTR(disappear_time, 15, 5) || ' (' || COUNT(1) || ') ' || GROUP_CONCAT(DISTINCT(pi.pokemon_name_en)) ASC;
  173.  
  174. DROP TABLE "pokemon_info";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!