brideau/pokelyzer

A webhook listener and database schema for doing geospatial analysis and advanced analytics on Pokemon Go data.

pokelyzer's Issues

Suggestion: Add support for rarity

It would be wonderful to be able to filter based on rarity.

Thanks, keep up the good work.

Unicode error while logging

Likely the gender symbol for Nidoran

Exception in thread search_thread-0:
Traceback (most recent call last):
File "/usr/lib/python2.7/threading.py", line 551, in __bootstrap_inner
self.run()
File "/usr/lib/python2.7/threading.py", line 504, in run
self.__target(_self.__args, *_self.__kwargs)
File "/vagrant/pokelyzer/PokemonGo-Map/pogom/search.py", line 159, in search_thread
parse_map(response_dict, i, step, step_location)
File "/vagrant/pokelyzer/PokemonGo-Map/pogom/models.py", line 253, in parse_map
logPokemonDb(p)
File "/vagrant/pokelyzer/PokemonGo-Map/pogom/customLog.py", line 57, in logPokemonDb
cursor.execute(query, data)
UnicodeEncodeError: 'latin-1' codec can't encode character u'\u2642' in position 7: ordinal not in range(256)

Error after Jul 28, 2016 ~12PM EDT patch

I did

DROP TABLE public.pokemon_info;

and then restored from the newest backup.

However, I am now getting many instances of this error:

Exception in thread search_thread-2:
Traceback (most recent call last):
  File "/usr/lib/python2.7/threading.py", line 810, in __bootstrap_inner
    self.run()
  File "/usr/lib/python2.7/threading.py", line 763, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/home/ubuntu/PokemonGo-Map/pogom/search.py", line 159, in search_thread
    parse_map(response_dict, i, step, step_location)
  File "/home/ubuntu/PokemonGo-Map/pogom/models.py", line 235, in parse_map
    logPokemonDb(p)
  File "/home/ubuntu/PokemonGo-Map/pogom/customLog.py", line 59, in logPokemonDb
    cursor.execute(query, data)
IntegrityError: duplicate key value violates unique constraint "id_primary_key"
DETAIL:  Key (id)=(113) already exists.


Exception in thread search_thread-1:
Traceback (most recent call last):
  File "/usr/lib/python2.7/threading.py", line 810, in __bootstrap_inner
    self.run()
  File "/usr/lib/python2.7/threading.py", line 763, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/home/ubuntu/PokemonGo-Map/pogom/search.py", line 159, in search_thread
    parse_map(response_dict, i, step, step_location)
  File "/home/ubuntu/PokemonGo-Map/pogom/models.py", line 235, in parse_map
    logPokemonDb(p)
  File "/home/ubuntu/PokemonGo-Map/pogom/customLog.py", line 59, in logPokemonDb
    cursor.execute(query, data)
IntegrityError: duplicate key value violates unique constraint "id_primary_key"
DETAIL:  Key (id)=(116) already exists.

Is this a sign that something went wrong in the patch or an indicator of something else? Things were working fine before.

adding pokemon_info table with existing datas

Hi,

I saw that there is a new table, pokemon_info, can I add this one without any risk to lose my datas collected since yesterday or is it better to delete everything and get a more clear database ?

Wrong Timestamp in Postgres

[2016-08-01T13:09:52.056Z] INFO: MainApp/10351 on **-MacBook-Air-7.local: Running server on port 9876

But system time is 15:09:52. Checked timezone in postgresql.conf, but already set correctly. Any suggestions?

Exception: ReferenceError: Promise is not defined

Looks like I am hitting an exception that is traces back to index.js

pi@raspberrypi:~/rep/pokelyzer $ DB_NAME='pokemon_go' DB_USER='pokemon_go_role' DB_PASS='********' DB_PORT=5432 WS_PORT=9876 ERA=2  node app.js | bunyan -l info

/home/pi/rep/pokelyzer/node_modules/pg/node_modules/pg-pool/index.js:11
  this.Promise = this.options.Promise || Promise
                                         ^
ReferenceError: Promise is not defined
    at module.exports (/home/pi/rep/pokelyzer/node_modules/pg/node_modules/pg-pool/index.js:11:42)
    at new BoundPool (/home/pi/rep/pokelyzer/node_modules/pg/lib/pool-factory.js:12:10)
    at Object.<anonymous> (/home/pi/rep/pokelyzer/app.js:19:12)
    at Module._compile (module.js:456:26)
    at Object.Module._extensions..js (module.js:474:10)
    at Module.load (module.js:356:32)
    at Function.Module._load (module.js:312:12)
    at Function.Module.runMain (module.js:497:10)
    at startup (node.js:119:16)
    at node.js:906:3

No data being parsed to pokelyzer.

After much faffing around i finally have pogomap up and running with the pokelyzer but nothing is being sent to the database at all. Chances are I have missed something so obvious but I'd appreciate any help someone could give me?

To clarify I have the database set up and functioning along with the dev branch map server and WH.

I had this working using the master pogomap branch but got undefined column errors, after starting fresh and getting pogomap updated nada is received in the cmd prompt window.

Provide option for excluding common Pokemon from the database

There's a ton of pidgeys and weedles out there - let's save our hard drives and not bother saving their data.

Can we import data from MySQL? (aka, can we use Database X with Pokelyzer?)

So the current dev version of pogomap now offers incorporation into mySQL. I've been using it and gathering data and it's all saving well and good. I was hoping though if there was a way to take that data and import it to pokelyzer or is it restricted to postgresql? Seems a shame for me to waste all this existing scan data if i have to start over to use pokelyzer.

Add a column for wild/lured pokemon

It should be possible to tell the difference between these two.

Tableau "Invalid table name" Error

Occurs when you try to use the date dimension as it was before commit bd81308.

To patch an existing installation, run the following SQL:

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

Final Schema

Hi,

Ive noticed there are a bunch of patches. It would be great if you can add a basic Schema drawing or dump of the final schema after the patches.

is pokelyze_sql.sql the final schema?

Thanks

Authentication Issue With Postgres

Moving your thread here to keep the other ticket clean. Here's the original screenshot you posted:

Try resetting the password by going to the Login Roles part of the tree menu, right clicking your user and setting it in the definitions tab. This seemed to solve it for other people.

Error conflicting options string(s)

parser.add_argument('-t', '--threads', help='Number of search threads', required=False, type=int, default=5, dest='num_threads')

Adding line above to /pogom/utils.py causes this error:

Traceback (most recent call last):
File "runserver.py", line 13, in
from pogom.app import Pogom
File "/home/theresthatguy/Pokemon/pogoDB/pogom/app.py", line 11, in
from .models import Pokemon, Gym, Pokestop, ScannedLocation
File "/home/theresthatguy/Pokemon/pogoDB/pogom/models.py", line 13, in
from .customLog import printPokemon, logPokemonDb
File "/home/theresthatguy/Pokemon/pogoDB/pogom/customLog.py", line 7, in
args = get_args()
File "/home/theresthatguy/Pokemon/pogoDB/pogom/utils.py", line 61, in get_args
parser.add_argument('-t', '--threads', help='Number of search threads', required=False, type=int, default=5, dest='num_threads')
File "/usr/lib/python2.7/argparse.py", line 1308, in add_argument
return self._add_action(action)
File "/usr/lib/python2.7/argparse.py", line 1682, in _add_action
self._optionals._add_action(action)
File "/usr/lib/python2.7/argparse.py", line 1509, in _add_action
action = super(_ArgumentGroup, self)._add_action(action)
File "/usr/lib/python2.7/argparse.py", line 1322, in _add_action
self._check_conflict(action)
File "/usr/lib/python2.7/argparse.py", line 1460, in _check_conflict
conflict_handler(action, confl_optionals)
File "/usr/lib/python2.7/argparse.py", line 1467, in _handle_conflict_error
raise ArgumentError(action, message % conflict_string)
argparse.ArgumentError: argument -t/--threads: conflicting option string(s): -t, --threads

And if I comment it out, it's all good.

Only 4 tables not 5

When I restore using the pokemon_go_db_backup.tar file it only restores 4 tables and not 5. The "spotted_pokemon" table is missing.

Are you able to provide the SQL for me to run to add the table to the db?

Grunt Build error?

I'm not sure exactly how I got to this point, or how to fix it, but I was able to get the Pokemon Go map running, then tried to set Pokelyzer up and failing, then doing something wrong, deleting the old database to try and fix it, reinstalling exactly how the instructions say, getting up to starting the server up.
Anyone know what this means or how to fix it?

C:\Users***\Desktop\PokemonGo-Map-develop>python runserver.py -u (username) -p (password) -st 25 -k (Google API) -l (location)
2016-07-31 19:54:16,346 [ MainThread][ runserver] [CRITICAL] Please run "grunt build" before starting the server.

Wiki typo/duplicate line

In https://github.com/Brideau/pokelyzer/wiki/Ubuntu-Setup-Instructions the line If you go back into pgAdmin, right click the spotted_pokemon table and view the top 100 rows of data, you should see it being populated! is repeated twice (the last line of the wiki entry and the 2nd-to-last line).

name 'query' is not defined

Traceback (most recent call last): File "runserver.py", line 13, in <module> from pogom.app import Pogom File "/root/Downloads/PokemonGo-Map/pogom/app.py", line 15, in <module> from .models import Pokemon, Gym, Pokestop, ScannedLocation File "/root/Downloads/PokemonGo-Map/pogom/models.py", line 16, in <module> from .customLog import printPokemon, logPokemonDb File "/root/Downloads/PokemonGo-Map/pogom/customLog.py", line 58, in <module> cursor.execute(query, data) NameError: name 'query' is not defined

I copied code from sample_customLog.py

Encounter_id is not actually unique

It turns out encounter_id is not completely unique: https://www.reddit.com/r/pokemongodev/comments/4vazmk/encounter_id_is_not_unique/

This should fix it, and I need to write a patch that does this:

ALTER TABLE public.spotted_pokemon
ADD CONSTRAINT unique_encounter_spawn
UNIQUE(encounter_id, spawnpoint_id);

Pokelyzer - being a noob

Apologies for my ignorance but i'm on the last step which is;

Run using:

DB_NAME='pokemon_go' DB_USER='pokemon_go_role' DB_PASS='[YOUR PASS]' DB_PORT=5432 WS_PORT=9876 ERA=2 node app.js | bunyan -l info

I am failing to understand how to run this.. I open up CMD and run it through there but to no avail

Error fetching client from pool

Hello, I'm cartoonishly inept at anything database-related, so I haven't been able to figure out where my problem is. I'm getting the following error after running Pokelyzer:

{"name":"MainApp","hostname":"DESKTOP-NT6HCKO","pid":1092,"level":50,"msg":"Idle client error read ECONNRESET Error: read ECONNRESET\n at exports._errnoException (util.js:873:11)\n at TCP.onread (net.js:557:26)","time":"2016-07-31T23:22:04.260Z","v":0} {"name":"MainApp","hostname":"DESKTOP-NT6HCKO","pid":1092,"level":50,"msg":"Error fetching client from pool { [error: password authentication failed for user \"pokemon_go_role\"]\n name: 'error',\n length: 104,\n severity: 'FATAL',\n code: '28P01',\n detail: undefined,\n hint: undefined,\n position: undefined,\n internalPosition: undefined,\n internalQuery: undefined,\n where: undefined,\n schema: undefined,\n table: undefined,\n column: undefined,\n dataType: undefined,\n constraint: undefined,\n file: 'auth.c',\n line: '285',\n routine: 'auth_failed' }","time":"2016-07-31T23:22:10.878Z","v":0}

It looked to me like there was just a typo in my password, but I tried with and without quotes and double checked the spelling and the same error popped up every time. I did run the latest patch, but dropping the 'name' column from spotted_pokemon failed because the column didn't exist in the first place. Could that be the issue? A version mismatch between pokemon_go_db_backup and the instructions? I did also try deleting the database and starting with a fresh restore - same problem.

Is it possible to move old spotted_pokemon Data to the new DB?

I have an old DB with 3k spotted Pokemon entries.
Today I updated my Pokelyzer with the new DB.
I saw that there are new Columns in the new spotted_pokemon table.
I tought i can add the columns 'pokemon_go_era,meta_db_version, meta_row_insertion_time' fill them with correct data and remove the name column to have an exact table like the new version so i just copy paste aall the data (Not forgot to change the ID of every spotted Pokemon).
But i saw that the "encounter_id" also changed how it generates the characters

before:

after:

Are the old encounter_id's compatible with the new DB?

undefined error spam

I'm getting spammed these errors as soon as I launch the app.
I've tried running everything 100% fresh from the https://github.com/Brideau/pokelyzer/wiki/Windows-Setup-Instructions
as well as my customized server both throw these errors like crazy.

{"name":"MainApp","hostname":"DESKTOP-JFKAKVD","pid":27372,"level":30,"msg":"Running server on port 9876","time":"2016-08-01T03:10:59.511Z","v":0}
{"name":"MainApp","hostname":"DESKTOP-JFKAKVD","pid":27372,"level":30,"msg":"Pokemon with ID 13 found.","time":"2016-08-01T03:11:24.003Z","v":0}
{"name":"MainApp","hostname":"DESKTOP-JFKAKVD","pid":27372,"level":30,"msg":"Pokemon with ID 58 found.","time":"2016-08-01T03:11:24.034Z","v":0}
{"name":"MainApp","hostname":"DESKTOP-JFKAKVD","pid":27372,"level":30,"msg":"Pokemon with ID 23 found.","time":"2016-08-01T03:11:24.054Z","v":0}
{"name":"MainApp","hostname":"DESKTOP-JFKAKVD","pid":27372,"level":50,"msg":"Error running query { [error: column \"undefined\" does not exist]\n  name: 'error',\n  length: 103,\n  severity: 'ERROR',\n  code: '42703',\n  detail: undefined,\n  hint: undefined,\n  position: '425',\n  internalPosition: undefined,\n  internalQuery: undefined,\n  where: undefined,\n  schema: undefined,\n  table: undefined,\n  column: undefined,\n  dataType: undefined,\n  constraint: undefined,\n  file: 'parse_relation.c',\n  line: '3090',\n  routine: 'errorMissingColumn' }","time":"2016-08-01T03:11:24.060Z","v":0}
{"name":"MainApp","hostname":"DESKTOP-JFKAKVD","pid":27372,"level":50,"msg":"Error running query { [error: column \"undefined\" does not exist]\n  name: 'error',\n  length: 103,\n  severity: 'ERROR',\n  code: '42703',\n  detail: undefined,\n  hint: undefined,\n  position: '425',\n  internalPosition: undefined,\n  internalQuery: undefined,\n  where: undefined,\n  schema: undefined,\n  table: undefined,\n  column: undefined,\n  dataType: undefined,\n  constraint: undefined,\n  file: 'parse_relation.c',\n  line: '3090',\n  routine: 'errorMissingColumn' }","time":"2016-08-01T03:11:24.100Z","v":0}
{"name":"MainApp","hostname":"DESKTOP-JFKAKVD","pid":27372,"level":50,"msg":"Error running query { [error: column \"undefined\" does not exist]\n  name: 'error',\n  length: 103,\n  severity: 'ERROR',\n  code: '42703',\n  detail: undefined,\n  hint: undefined,\n  position: '425',\n  internalPosition: undefined,\n  internalQuery: undefined,\n  where: undefined,\n  schema: undefined,\n  table: undefined,\n  column: undefined,\n  dataType: undefined,\n  constraint: undefined,\n  file: 'parse_relation.c',\n  line: '3090',\n  routine: 'errorMissingColumn' }","time":"2016-08-01T03:11:24.123Z","v":0}
{"name":"MainApp","hostname":"DESKTOP-JFKAKVD","pid":27372,"level":30,"msg":"Pokemon with ID 52 found.","time":"2016-08-01T03:11:24.295Z","v":0}

spotted_pokemon table has no recorded pokemon

Followed the Pokelyzer setup instructions, I have the PokemonGo-Map folder inside another folder called Pokelyzer. I extracted the Pokelyzer-master folder into the Pokelyzer folder. I performed "npm install" and "npm install -g bunyan".

C:\Users***\Desktop\Pokelyzer>npm install
npm ERR! install Couldn't read dependencies
npm ERR! Windows_NT 10.0.10586
npm ERR! argv "C:\Program Files\nodejs\node.exe" "C:\Program Files\nodejs\node_modules\npm\bin\npm-cli.js" "install"
npm ERR! node v4.4.7
npm ERR! npm v2.15.8
npm ERR! path C:\Users***\Desktop\Pokelyzer\package.json
npm ERR! code ENOPACKAGEJSON
npm ERR! errno -4058
npm ERR! syscall open

npm ERR! package.json ENOENT: no such file or directory, open 'C:\Users***\Desktop\Pokelyzer\package.json'
npm ERR! package.json This is most likely not a problem with npm itself.
npm ERR! package.json npm can't find a package.json file in your current directory.

npm ERR! Please include the following file with any support request:
npm ERR! C:\Users***\Desktop\Pokelyzer\npm-debug.log

C:\Users***\Desktop\Pokelyzer>npm install -g bunyan

[email protected] install C:\Users***\AppData\Roaming\npm\node_modules\bunyan\node_modules\dtrace-provider
node scripts/install.js

C:\Users***\AppData\Roaming\npm\bunyan -> C:\Users**_\AppData\Roaming\npm\node_modules\bunyan\bin\bunyan
[email protected] C:\Users_**\AppData\Roaming\npm\node_modules\bunyan
├── [email protected]
├── [email protected] ([email protected], [email protected], [email protected])
├── [email protected] ([email protected])
└── [email protected]

Then, I ran the server command with the wh parameter, and was able to view the map from localhost:5000, but was unable to read data on pgAdmin III or on Tableau.

Column Undefined

Getting the same column undefined error as in #28 and #29.

I've repulled from git develop branch not using easy setup and still having the issue.

There is no unique or exclusion constraint matching the ON CONFLICT specification

After recently updating to allow for spawn "eras" I am getting this error in my terminal.

Traceback (most recent call last):
  File "/usr/lib/python2.7/threading.py", line 801, in __bootstrap_inner
    self.run()
  File "/usr/lib/python2.7/threading.py", line 754, in run
    self.__target(*self.__args, **self.__kwargs)
  File "/home/theresthatguy/Pokemon/mapdb/pogom/search.py", line 159, in search_thread
    parse_map(response_dict, i, step, step_location)
  File "/home/theresthatguy/Pokemon/mapdb/pogom/models.py", line 236, in parse_map
    logPokemonDb(p)
  File "/home/theresthatguy/Pokemon/mapdb/pogom/customLog.py", line 59, in logPokemonDb
    cursor.execute(query, data)
ProgrammingError: there is no unique or exclusion constraint matching the ON CONFLICT specification

npm install not working

Whenever I use npm install to fix the grunt build issue, it doesn't work. I've tried everything I can, but to no avail. Here is the error message I get:

Thanks for any help that you can give me.

python error while running map after installed Pokelyzer

Hi,

After installing Pokelyzer i have this error when I want to launch the map :

Traceback (most recent call last): File "runserver.py", line 16, in <module> from pogom.models import init_database, create_tables, Pokemon, Pokestop, Gym ImportError: cannot import name init_database

And I can't find how to fix it by myself

'bunyan' is not recognized as an internal or external command, operable program or batch file.

Everything else went smoothly but I can't seem to get past this point.

Error running query { [error: column "undefined" does not exist]

Followed the instructions and now this is happening. Any idea?

It seems that the object doesn't have the property m.last_modified_time, neither m.time_until_hidden_ms

[2016-07-31T22:32:58.933Z]  INFO: MainApp/13524 on DESKTOP-12GRBE3: Running server on port 9876
[2016-07-31T22:33:04.463Z]  DEBUG: MainApp/13524 on DESKTOP-12GRBE3:  (disappear_time=1470000951, pokemon_id=96, spawnpoint_id=48670e7db3d, longitude=-6.261960704620025, latitude=53.35864649693987, encounter_id=MTU4OTM4ODE1NTM3MDE5NDA0OTM=, pokemon_go_era=2, hid
den_time_unix_s=1470000951, hidden_time_utc="2016-07-31 22:35:51", latitude_jittered=53.35920856705674, longitude_jittered=-6.262257744304201)
[2016-07-31T22:33:04.535Z]  INFO: MainApp/13524 on DESKTOP-12GRBE3: Pokemon with ID 96 found.
[2016-07-31T22:33:04.535Z]  DEBUG: MainApp/13524 on DESKTOP-12GRBE3: INSERT INTO spotted_pokemon (           encounter_id,           last_modified_time,           time_until_hidden_ms,           hidden_time_unix_s,           hidden_time_utc,           spawnpoint
_id,           longitude,           latitude,           pokemon_id,           longitude_jittered,           latitude_jittered,           pokemon_go_era)         VALUES (           'MTU4OTM4ODE1NTM3MDE5NDA0OTM='::varchar,           undefined::bigint,           u
ndefined::bigint,           1470000951::bigint,           '2016-07-31 22:35:51'::timestamp,           '48670e7db3d'::varchar,           -6.261960704620025::double precision,           53.35864649693987::double precision,           96::smallint,           -6.262
257744304201::double precision,           53.35920856705674::double precision,           2::integer         )         ON CONFLICT (encounter_id)         DO UPDATE           SET last_modified_time = EXCLUDED.last_modified_time,           time_until_hidden_ms = E
XCLUDED.time_until_hidden_ms,           hidden_time_unix_s = EXCLUDED.hidden_time_unix_s,           hidden_time_utc = EXCLUDED.hidden_time_utc;
[2016-07-31T22:33:04.538Z] ERROR: MainApp/13524 on DESKTOP-12GRBE3:
    Error running query { [error: column "undefined" does not exist]
      name: 'error',
      length: 103,
      severity: 'ERROR',
      code: '42703',
      detail: undefined,
      hint: undefined,
      position: '425',
      internalPosition: undefined,
      internalQuery: undefined,
      where: undefined,
      schema: undefined,
      table: undefined,
      column: undefined,
      dataType: undefined,
      constraint: undefined,
      file: 'parse_relation.c',
      line: '3090',
      routine: 'errorMissingColumn' }

new install instructions - pokemongo-map develop branch

Hi there
I've tried to setup using the new install instructions for OSX, but it seems it isn't working with the develop branch at present - I'm getting the following error when I try to run the PokemonGo-Map server:
[ MainThread][ runserver] [CRITICAL] Please run "grunt build" before starting the server.

I realise this is an issue with PokemonGo maps, not pokelyzer, but the develop branch isn't always going to be ready to go - what do you suggest as a workaround?
Thanks!

Suggestion: Save historic data for pokestops with lured status

It would be great if it was possible to cross reference the spotted pokemons against lured pokestops in proximity of the spawn point.

Edit: It's available from the PokemonGo-Map server.

spotted_pokemon table missing name character varying

It seems to be missing for me

Cannot use Webhook with multiple workers

Every time i run pokelyzer with multiple workers using webhook, i get an error saying the database is locked. I am pretty sure this is because the database only accepts one connection at a time. Is there any way to fix it so that i can collect data again?

Error in routine: 'scanner_yyerror'

Here is what I am getting:

[2016-08-02T23:28:32.069Z]  INFO: MainApp/7789 on ip-172-31-7-220: Pokemon with ID 92 found.
[2016-08-02T23:28:32.071Z] ERROR: MainApp/7789 on ip-172-31-7-220:
    Error running query { [error: syntax error at or near ".00012322043210079682"]
      name: 'error',
      length: 104,
      severity: 'ERROR',
      code: '42601',
      detail: undefined,
      hint: undefined,
      position: '737',
      internalPosition: undefined,
      internalQuery: undefined,
      where: undefined,
      schema: undefined,
      table: undefined,
      column: undefined,
      dataType: undefined,
      constraint: undefined,
      file: 'scan.l',
      line: '1081',
      routine: 'scanner_yyerror' }

app.post('/', function(req, res) {
    var body = req.body;
    for (var l in req.body.logs) {
      if (l.type == "pokemon") {

        var m = l.message;
        m.pokemon_go_era = era;
        m.hidden_time_unix_s = m.disappear_time;
        m.hidden_time_utc = moment
          .unix(m.hidden_time_unix_s)
          .format("YYYY-MM-DD HH:mm:ss");
        m.latitude_jittered = m.latitude + dist.ppf(Math.random()) * 0.0005;
        m.longitude_jittered = m.longitude + dist.ppf(Math.random()) * 0.0005;

        if (encounter_encoded == "t") {
          buff = new Buffer(m.encounter_id, 'base64');
          m.encounter_id = buff.toString();
        }

        log.debug(m.encounter_id);

        pool.connect(function(err, client, done) {
          if(err) {
            return log.error('Error fetching client from pool', err);
          }

          query_string = "INSERT INTO spotted_pokemon ( \
            encounter_id, \
            last_modified_time, \
            time_until_hidden_ms, \
            hidden_time_unix_s, \
            hidden_time_utc, \
            spawnpoint_id, \
            longitude, \
            latitude, \
            pokemon_id, \
            longitude_jittered, \
            latitude_jittered, \
            pokemon_go_era) \
          VALUES ( \
            '" + m.encounter_id + "'::varchar, \
            " + m.last_modified_time + "::bigint, \
            " + m.time_until_hidden_ms + "::bigint, \
            " + m.hidden_time_unix_s + "::bigint, \
            '" + m.hidden_time_utc + "'::timestamp, \
            '" + m.spawnpoint_id + "'::varchar, \
            " + m.longitude + "::double precision, \
            " + m.latitude + "::double precision, \
            " + m.pokemon_id + "::smallint, \
            " + m.longitude_jittered + "::double precision, \
            " + m.latitude_jittered + "::double precision, \
            " + m.pokemon_go_era + "::integer \
          ) \
          ON CONFLICT (encounter_id) \
          DO UPDATE \
            SET last_modified_time = EXCLUDED.last_modified_time, \
            time_until_hidden_ms = EXCLUDED.time_until_hidden_ms, \
            hidden_time_unix_s = EXCLUDED.hidden_time_unix_s, \
            hidden_time_utc = EXCLUDED.hidden_time_utc;";

          log.info("Pokemon with ID " + m.pokemon_id + " found.")
          log.trace(query_string);

          client.query(query_string,
          function(err, result) {
            done();

            if(err) {
              return log.error("Error running query", err);
            }
          });
        });
      }
    }

    res.sendStatus(200);
});

Sample Input:

{"logs":[{"type":"pokemon","message":{"longitude":"-79.380389","spawnpoint_id":"882b34d5553","disappear_time":"1470181592","pokemon_id":"120","encounter_id":"9752388792448690861","latitude":"43.639613","last_modified_time":"1470180898876","time_until_hidden_ms":"693552"}}]}

I modified the script slightly to take in an array of dictionaries and insert them.

Is this an issue in the postgreSQL procedure or the app.js code?

PS
I am using Postgres 9.5

Support local timezones

Right now the time is in UTC. This is intentional as it simplifies things dramatically, but I'd like to have a properly timezone solution in the future.

Based on the Kimball approach, the proper solution to this is:

We just need to implement this.

Export data from Pokeminer

Just pulled a request where I added encounter_id and time_till_hidden. It's extremely simple and close to 10 lines of code.

Until the solution for the webhook comes this would be a nice temporary fix.

pokemon_go_db_backup.tar corrupt?

Hi,

trying to do a fresh install, but unable to open/extract the tar either on Windows or Ubuntu.

The only tar that works for me is the one from your original commit.

Thanks
J

Runtime Exception

Any leads or pointers as to what this might be?

pi@raspberrypi:~/rep/pokelyzer $ DB_NAME='pokemon_go' DB_USER='pokemon_go_role' DB_PASS='pogomystic' DB_PORT=5432 WS_PORT=9876 ERA=2  node app.js | bunyan -l debug
[2016-08-02T05:36:30.213Z]  INFO: MainApp/14906 on raspberrypi: Running server on port 9876
[2016-08-02T05:36:40.360Z] DEBUG: MainApp/14906 on raspberrypi:  (time_until_hidden_ms=874787, last_modified_time=1470116199793, disappear_time=1470117074, pokemon_id=32, latitude=37.54317944859647, spawnpoint_id=808fc0be465, encounter_id=ODM5MTAyMzQ1NDA1NjA3MjQ3Nw==, longitude=-121.97374944075166, pokemon_go_era=2, hidden_time_unix_s=1470117074, hidden_time_utc="2016-08-02 05:51:14", latitude_jittered=37.542733607620825, longitude_jittered=-121.97373263788748)
[2016-08-02T05:36:40.424Z]  INFO: MainApp/14906 on raspberrypi: Pokemon with ID 32 found.
[2016-08-02T05:36:40.424Z] DEBUG: MainApp/14906 on raspberrypi: INSERT INTO spotted_pokemon (           encounter_id,           last_modified_time,           time_until_hidden_ms,           hidden_time_unix_s,           hidden_time_utc,           spawnpoint_id,           longitude,           latitude,           pokemon_id,           longitude_jittered,           latitude_jittered,           pokemon_go_era)         VALUES (           'ODM5MTAyMzQ1NDA1NjA3MjQ3Nw=='::varchar,           1470116199793::bigint,           874787::bigint,           1470117074::bigint,           '2016-08-02 05:51:14'::timestamp,           '808fc0be465'::varchar,           -121.97374944075166::double precision,           37.54317944859647::double precision,           32::smallint,           -121.97373263788748::double precision,           37.542733607620825::double precision,           2::integer         )         ON CONFLICT (encounter_id)         DO UPDATE           SET last_modified_time = EXCLUDED.last_modified_time,           time_until_hidden_ms = EXCLUDED.time_until_hidden_ms,           hidden_time_unix_s = EXCLUDED.hidden_time_unix_s,           hidden_time_utc = EXCLUDED.hidden_time_utc;
[2016-08-02T05:36:40.447Z] ERROR: MainApp/14906 on raspberrypi:
    Error running query { error: syntax error at or near "ON"
        at Connection.parseE (/home/pi/rep/pokelyzer/node_modules/pg/lib/connection.js:551:11)
        at Connection.parseMessage (/home/pi/rep/pokelyzer/node_modules/pg/lib/connection.js:378:17)
        at Socket.<anonymous> (/home/pi/rep/pokelyzer/node_modules/pg/lib/connection.js:117:22)
        at emitOne (events.js:96:13)
        at Socket.emit (events.js:188:7)
        at readableAddChunk (_stream_readable.js:172:18)
        at Socket.Readable.push (_stream_readable.js:130:10)
        at TCP.onread (net.js:542:20)
      name: 'error',
      length: 85,
      severity: 'ERROR',
      code: '42601',
      detail: undefined,
      hint: undefined,
      position: '838',
      internalPosition: undefined,
      internalQuery: undefined,
      where: undefined,
      schema: undefined,
      table: undefined,
      column: undefined,
      dataType: undefined,
      constraint: undefined,
      file: 'scan.l',
      line: '1052',
      routine: 'scanner_yyerror' }

Happens everytime it finds a pokemon

error: column "undefined" does not exist

So. I installed latest PokemonGo-Maps, and had it working.. Then I stumbled upon this pokelyzer and was already hooked..

Installed to ubuntu with all the things mentioned on the wikipage.. Got it running.. Fired up PokemonGo-Maps and it started finding pokemons just like it should.. Then checked back to Pokelyzer side and this is what I'm seeing:

[2016-07-31T23:34:07.978Z] INFO: MainApp/876 on localhost: Pokemon with ID 13 found.
[2016-07-31T23:34:07.982Z] ERROR: MainApp/876 on localhost:
Error running query { [error: column "undefined" does not exist]
name: 'error',
length: 103,
severity: 'ERROR',
code: '42703',
detail: undefined,
hint: undefined,
position: '425',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_relation.c',
line: '3090',
routine: 'errorMissingColumn' }

Any idea how to fix this?-p

Error when starting server.

I run Postgress.app on mac and the Map is working perfectly on localhost but when i try to start pokelyzer from terminal i get this. Note that I'm not great with code but any help in the right direction is appreciated!

Error running query { [error: column "undefined" does not exist]
name: 'error',
length: 103,
severity: 'ERROR',
code: '42703',
detail: undefined,
hint: undefined,
position: '425',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_relation.c',
line: '3090',
routine: 'errorMissingColumn' }

Installation Guide for Mac

I've been trying to install on the Mac using the Ubuntu without any success.
Since most all the screenshots around here are from Mac I believe it's possible.

Add Version and Insertion Timestamp Columns

As the database changes, the schema will too. Because of this, we need to make sure that we're recording metadata about each row so that we can specifically filter out data from earlier versions of the software if necessary.

While we're at it, we might as well record a timestamp of when the row was inserted.

Error running query { [error: syntax error at or near "ON"]

Hi,

I'm getting these errors below when running the commands from the ubuntu tutorial. I double-checked, that I am running the latest code of the AHAAAAAAA/PokemonGo-Map develop branch.

The only deviation from the tutorial is: I'm using PostgreSQL 9.4.8, since this is the one shipped with gitlab.

Could this be the cause of the error messages?

[2016-08-01T01:15:42.212Z]  INFO: MainApp/323 on server: Pokemon with ID 96 found.
[2016-08-01T01:15:42.214Z] ERROR: MainApp/323 on server:
    Error running query { [error: syntax error at or near "ON"]
      name: 'error',
      length: 85,
      severity: 'ERROR',
      code: '42601',
      detail: undefined,
      hint: undefined,
      position: '835',
      internalPosition: undefined,
      internalQuery: undefined,
      where: undefined,
      schema: undefined,
      table: undefined,
      column: undefined,
      dataType: undefined,
      constraint: undefined,
      file: 'scan.l',
      line: '1052',
      routine: 'scanner_yyerror' }
[2016-08-01T01:15:43.222Z]  INFO: MainApp/323 on server: Pokemon with ID 13 found.
[2016-08-01T01:15:43.223Z] ERROR: MainApp/323 on server:
    Error running query { [error: syntax error at or near "ON"]
      name: 'error',
      length: 85,
      severity: 'ERROR',
      code: '42601',
      detail: undefined,
      hint: undefined,
      position: '834',
      internalPosition: undefined,
      internalQuery: undefined,
      where: undefined,
      schema: undefined,
      table: undefined,
      column: undefined,
      dataType: undefined,
      constraint: undefined,
      file: 'scan.l',
      line: '1052',
      routine: 'scanner_yyerror' }

IntegrityError: duplicate key value violates unique constraint "encounter_id_unique"

Getting the following error when running latest Pokemongo dev after I have applied the 28st of July patch to Pokelyzer.

Exception in thread search_thread-0:
Traceback (most recent call last):
File "/usr/lib64/python2.7/threading.py", line 811, in __bootstrap_inner
self.run()
File "/usr/lib64/python2.7/threading.py", line 764, in run
self.__target(_self.__args, *_self.__kwargs)
File "/opt/pokemon/pogom/search.py", line 159, in search_thread
parse_map(response_dict, i, step, step_location)
File "/opt/pokemon/pogom/models.py", line 253, in parse_map
logPokemonDb(p)
File "/opt/pokemon/pogom/customLog.py", line 58, in logPokemonDb
cursor.execute(query, data)
IntegrityError: duplicate key value violates unique constraint "encounter_id_unique"
DETAIL: Key (encounter_id)=(4843258148351874141) already exists.

Error conflicting options string(s)

parser.add_argument('-t', '--threads', help='Number of search threads', required=False, type=int, default=5, dest='num_threads')

Adding line line above causes error to /pogom/utils.py causes this error:

Traceback (most recent call last):
File "runserver.py", line 13, in
from pogom.app import Pogom
File "/home/theresthatguy/Pokemon/pogoDB/pogom/app.py", line 11, in
from .models import Pokemon, Gym, Pokestop, ScannedLocation
File "/home/theresthatguy/Pokemon/pogoDB/pogom/models.py", line 13, in
from .customLog import printPokemon, logPokemonDb
File "/home/theresthatguy/Pokemon/pogoDB/pogom/customLog.py", line 7, in
args = get_args()
File "/home/theresthatguy/Pokemon/pogoDB/pogom/utils.py", line 61, in get_args
parser.add_argument('-t', '--threads', help='Number of search threads', required=False, type=int, default=5, dest='num_threads')
File "/usr/lib/python2.7/argparse.py", line 1308, in add_argument
return self._add_action(action)
File "/usr/lib/python2.7/argparse.py", line 1682, in _add_action
self._optionals._add_action(action)
File "/usr/lib/python2.7/argparse.py", line 1509, in _add_action
action = super(_ArgumentGroup, self)._add_action(action)
File "/usr/lib/python2.7/argparse.py", line 1322, in _add_action
self._check_conflict(action)
File "/usr/lib/python2.7/argparse.py", line 1460, in _check_conflict
conflict_handler(action, confl_optionals)
File "/usr/lib/python2.7/argparse.py", line 1467, in _handle_conflict_error
raise ArgumentError(action, message % conflict_string)
argparse.ArgumentError: argument -t/--threads: conflicting option string(s): -t, --threads

And if I comment it out, I get failed logins

2016-07-28 05:54:16,085 [ runserver] [ INFO] Parsed location is: 39.0553/-94.6023/0.0000 (lat/lng/alt)
2016-07-28 05:54:16,086 [ search] [ INFO] Map iteration: 0
2016-07-28 05:54:16,086 [ search] [ INFO] Attempting login to Pokemon Go.
2016-07-28 05:54:16,087 [ auth_ptc] [ INFO] PTC login for: theresthatguy
2016-07-28 05:54:41,195 [ auth_ptc] [ INFO] PTC Login successful
2016-07-28 05:54:42,319 [ pgoapi] [ ERROR] Login failed - unexpected server response!

Incorrect Date Key

So far, I've only collected data on the night of 7/27 and during the day on 7/28. I've noticed a couple entries in my dataset that have a date key of 20160718. Any idea what could be causing this?

I wasn't sure if this was something from the restore or not. I know this project is getting a ton of attention right now so I understand if you're busy. No rush on a response, was just curious.

Thanks :)

SyntaxError: Unexpected token ILLEGAL

Changed branch to version1-branch, applied latest sql patches installed and followed npm related instructions as explained in the readme.

/opt/pokelyzer/app.js:64
query_string = `INSERT INTO spotted_pokemon
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
SyntaxError: Unexpected token ILLEGAL
at Module._compile (module.js:439:25)
at Object.Module._extensions..js (module.js:474:10)
at Module.load (module.js:356:32)
at Function.Module._load (module.js:312:12)
at Function.Module.runMain (module.js:497:10)
at startup (node.js:119:16)
at node.js:935:3

Any ideas?

Minutes label for time

As per thread on reddit, breaking the time label into hrs / minutes, minutes in particular. Helpful for quickly identifying and summarising spawn points that are always 19mins past the hr etc

No module named queue

Trying to get everything all set up and I'm running into this problem. I don't super know what I'm doing, but I've been following the instructions here and got to the point where I run the server, I guess. When I try though it says "No module named queue." I type "pip install queue" and nothing happens when I start the process over. I'm on Windows 10 if it helps.