Query examples¶

Swap in coordinates, IDs, and times. End-column filters keep currently live rows.

Spawn in a circle¶

SELECT s.spawn_id,
       p.pokemon_name,
       f.form_suffix,
       s.percent_iv,
       s.cp,
       s.expires
FROM pogo_live.sub_v2_spawns AS s
JOIN pogo_gm.pokemon AS p USING (pokemon_id)
JOIN pogo_gm.forms AS f USING (form_id)
WHERE s.expires > now()
  AND ST_DWithin(
        s.location,
        ST_MakePoint(-97.7431, 30.2672)::geography,
        2000
      )
ORDER BY s.percent_iv DESC
LIMIT 100;

Hundo spawn¶

SELECT spawn_id, pokemon_id, form_id, expires
FROM pogo_live.sub_v2_spawns
WHERE expires > now()
  AND attack_iv = 15
  AND defense_iv = 15
  AND stamina_iv = 15;

Great League rank 1–10¶

Cap 2 is Great League at level 50. Use solo_stat_pool_hash for the spawn form only.

SELECT s.spawn_id,
       p.pokemon_name,
       r.rank,
       r.level AS pvp_level,
       r.cp AS pvp_cp
FROM pogo_live.sub_v2_spawns AS s
JOIN pogo_gm.pokemon AS p ON p.pokemon_id = s.pokemon_id
JOIN pogo_pvp.stat_ranking AS r
  ON r.stat_pool_hash = s.stat_pool_hash
 AND r.iv_key = s.iv_key
 AND s.level <= r.level
WHERE s.expires > now()
  AND r.cap_id = 2
  AND r.rank BETWEEN 1 AND 10
  AND s.stat_pool_hash IS NOT NULL;

Active raid with gym name¶

SELECT r.raid_id,
       g.name AS gym_name,
       r.raid_level,
       p.pokemon_name,
       r.raid_end,
       g.lat,
       g.lon
FROM pogo_live.sub_v2_raids AS r
JOIN pogo_live.sub_v2_gyms AS g USING (gym_id)
LEFT JOIN pogo_gm.pokemon AS p ON p.pokemon_id = r.raid_pokemon_id
WHERE r.raid_end > now()
  AND ST_DWithin(
        g.location,
        ST_MakePoint(-97.7431, 30.2672)::geography,
        5000
      );

Spawns in a named place (typo-tolerant)¶

Pick a wof_id from geo.wof_place_search, then keep rows whose settlement or county is that place or a descendant. See Places (WOF).

WITH place AS (
  SELECT wof_id
  FROM geo.wof_place_search('Housten', 5, ARRAY['eng'], ARRAY['locality'])
  LIMIT 1
)
SELECT s.spawn_id,
       p.pokemon_name,
       s.geo_settlement_wof_id,
       s.geo_settlement_distance_m
FROM pogo_live.sub_v2_spawns AS s
JOIN pogo_gm.pokemon AS p USING (pokemon_id)
JOIN place ON true
WHERE s.expires > now()
  AND (
    s.geo_settlement_wof_id IN (SELECT wof_id FROM geo.wof_in_place(place.wof_id))
    OR s.geo_county_wof_id IN (SELECT wof_id FROM geo.wof_in_place(place.wof_id))
  )
LIMIT 100;

Quest encounter reward¶

SELECT q.quest_id,
       st.name AS pokestop_name,
       p.pokemon_name,
       qr.form_id,
       qr.encounter_shiny
FROM pogo_live.sub_v2_quests AS q
JOIN pogo_live.sub_v2_quest_rewards AS qr USING (quest_id)
JOIN pogo_live.sub_v2_pokestops AS st USING (pokestop_id)
LEFT JOIN pogo_gm.pokemon AS p ON p.pokemon_id = qr.pokemon_id
WHERE q.expires > now()
  AND qr.pokemon_id IS NOT NULL;

Invasion lineup slot¶

SELECT i.id,
       st.name AS pokestop_name,
       i.character,
       l.slot,
       p.pokemon_name,
       i.expiration
FROM pogo_live.sub_v2_invasions AS i
JOIN pogo_live.sub_v2_invasions_lineup AS l ON l.invasion_id = i.id
JOIN pogo_live.sub_v2_pokestops AS st USING (pokestop_id)
LEFT JOIN pogo_gm.pokemon AS p ON p.pokemon_id = l.pokemon_id
WHERE i.expiration > now();

Max battle with spot¶

SELECT b.bread_battle_seed,
       b.battle_level,
       p.pokemon_name,
       b.battle_end,
       st.name AS station_name,
       st.lat,
       st.lon
FROM pogo_live.sub_v2_max_battles AS b
JOIN pogo_live.sub_v2_stations AS st USING (station_id)
LEFT JOIN pogo_gm.pokemon AS p ON p.pokemon_id = b.battle_pokemon_id
WHERE b.battle_end > now();

Encounter id (Golbat uint64 → stored)¶

SELECT spawn_id, encounter_id, int8_to_u64(encounter_id) AS encounter_u64
FROM pogo_live.sub_v2_spawns
WHERE encounter_id = u64_to_int8('17408421884750151680')
  AND expires > now();

Recent spawns by snowflake time¶

SELECT spawn_id, snowflake_to_timestamp(spawn_id) AS minted, expires
FROM pogo_live.sub_v2_spawns
WHERE spawn_id >= timestamp_to_snowflake(now() - interval '15 minutes')
  AND expires > now();

Local expiry in the matched timezone¶

SELECT s.spawn_id,
       tz.tz_name,
       s.expires,
       s.expires AT TIME ZONE tz.tz_name AS expires_local
FROM pogo_live.sub_v2_spawns AS s
JOIN geo.timezone AS tz USING (timezone_id)
WHERE s.expires > now()
LIMIT 20;

Weather name¶

SELECT w.s2_cell_id,
       gm.weather_name,
       w.lat,
       w.lon
FROM pogo_live.sub_v2_weather AS w
JOIN pogo_gm.weather AS gm
  ON gm.weather_id = w.gameplay_condition;

Move names on a spawn¶

SELECT s.spawn_id,
       fq.move_name AS fast_move,
       cq.move_name AS charge_move
FROM pogo_live.sub_v2_spawns AS s
LEFT JOIN pogo_gm.moves AS fq ON fq.move_id = s.fast_move_id
LEFT JOIN pogo_gm.moves AS cq ON cq.move_id = s.charge_move_id
WHERE s.expires > now()
LIMIT 20;