Before you try to do recent tutorials or install new versions of the webhook listener, please install all patches since your installation date.

This patch adds a column that specifies whether a Pokemon spawn was the result of a lure or not. Simply run this SQL and get the latest version of the webhook listener via a git pull or downloading the latest zip file:

ALTER TABLE public.spotted_pokemon ADD COLUMN lured text DEFAULT 'unknown';

This one is the biggest patch yet, and it both fixes bugs and adds support for local time zones. Before you apply it, I recommend backing up your database. A quick way to do that from pgAdmin is to run the following command first to disconnect any active connections from your database:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'pokemon_go'
  AND pid <> pg_backend_pid();

And then run this to create a backup in the form of another database:

CREATE DATABASE pokemon_go_backup WITH TEMPLATE pokemon_go OWNER pokemon_go_role;

If things go awry, you can just delete your current database, and rename this backup to 'pokemon_go' and it will be as if nothing changed. Feel free to create your own tar backup as well from right-clicking the database and selecting Backup if that gives you extra piece of mind.

Once everything is backed up, you can optionally set the timezone of your database to whichever one you like. If your database is in your timezone, you can skip this part. If it isn't, run the following command and search for your long-form name of your time zone (e.g. 'America/Halifax') from the abbreviation if you know it:

SELECT * FROM pg_timezone_names WHERE abbrev = 'PDT';

If you don't have the abbreviation handy, just run SELECT * FROM pg_timezone_names; and search through the output.

Once you have that, edit your postgresql.conf file, and change the timezone parameter to the long-form name of your timezone.

 sudo nano /etc/postgresql/9.5/main/postgresql.conf

Save, and restart your database.

sudo /etc/init.d/postgresql restart

Now connect to your database again with pgAdmin, and this time run the 2016-08-05-timezonePatch.sql patch. This file adds new columns to support your local time zone, and in addition cleans up some old functions, adds to indexes to improve performance, and fixes two earlier bugs where the encounter_id field was not properly decoded, and where the unix time was not properly converted to UTC time.

If you're using Tableau or PowerBI, you can now replace the date_dimension and time_dimension tables with the date_dimension_local and time_dimension_local tables, and use your local timezone.

This patch fixes a bug where two spawns at two different locations may have the same encounter_id, but are not part of the same counter. To fix this, we use a combination of encounter_id and spawn_id to check for uniqueness. After applying the following SQL, do a git pull to get the latest webhook listener:

INSERT INTO _meta (db_version, last_update) VALUES ('v1.2-alpha', '2016-08-03');
ALTER TABLE public.spotted_pokemon DROP CONSTRAINT encounter_id_unique;
ALTER TABLE public.spotted_pokemon
  ADD CONSTRAINT encounter_spawnpoint_id_unique UNIQUE(encounter_id, spawnpoint_id);

I've updated the pokemon_info table so that you can now use the rarity property in your visualizations based on this visualization. (Thanks Niklas and ferazambuja for the help!) To update, just drop the current pokemon_info using this command, or right clicking and Delete/Droping it in pgAdmin:

DROP TABLE public.pokemon_info;

And then use the same Restore feature you used to load the database initially to load the pokemon_into_table_patch_2.tar file available in patches folder above.

This is a patch that makes the Pokelyzer capable of accepting data automatically pushed by PokemonGo-Map via its webhood interface.

First, we'll drop the Name column from the spotted_pokemon table. This is redundant now because of our pokemon_info table. We also update the _meta table with the new schema version.

INSERT INTO _meta (db_version, last_update) VALUES ('v1.0-alpha', '2016-07-31');
ALTER TABLE spotted_pokemon DROP COLUMN name;

This patch adds information that makes it easier to debug issues with the database. Specifically, I have added version tracking so that all records stored record the version of the schema they were stored with, along with a timestamp of when each record was stored.

To apply this patch, get the SQL from the 2016-07-30-metaPatch.sql file in the patches folder above and apply it to your database. There's no need to stop your web server to apply this.

This patch adds an extra column that gives us the ability to assign different Pokemon records to different "eras" - a very useful thing to have when doing historical analysis, especially since the recent changes that switched around all the nests.

It's a bit of a longer one, so see the guide here: http://www.whackdata.com/2016/07/29/the-era-of-eras-pokemon-go-pokelyzer/

Thanks again to @zenthere for supplying the SQL for this as well!

A big thanks to @zenthere for fixing a bug in my jitter calculation, and for creating a beautiful solution to the fact that a lot of rows in the database were duplicates. (Also note, if you don't apply this patch you'll receive a "there is no unique or exclusion constraint" error.) To apply this patch, shut down your map server and execute the following SQL to remove all current duplicates and put a constraint on any new ones:

DELETE FROM spotted_pokemon USING spotted_pokemon sp2
  WHERE spotted_pokemon.encounter_id = sp2.encounter_id AND spotted_pokemon.id > sp2.id;
ALTER TABLE spotted_pokemon ADD CONSTRAINT encounter_id_unique UNIQUE (encounter_id);

Then update your customLog.py file with the one from the Pokelyzer v0.3-alpha release.

Start your server back up!

If you loaded the database backup file before this time (commit bd81308), run the following command to patch your existing database. It fixes an issue with joining tables in Tableau.

ALTER TABLE date_dimension ALTER COLUMN date_key TYPE integer USING (date_key::integer);

Pokelyzer includes language patches for translating Pokemon names into various languages. Following setup, execute the SQL patch of your choice from the language_patches folder.