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

[rayuki]

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.

[Brideau]

I agree. The best way to solve this would be to write a simple script that iterates through all the rows in the database you already have and inserts them into the Pokelyzer database. I don't have the bandwidth at the moment to do that, but I'd love it if somebody else took that on. I could even point them in the right direction.

Currently Pokelyzer only operates with Postgres because it's the only open source database with widespread compatibility with both BI tools like Tableau/PowerBI and GIS tools like ArcGIS/QGIS. If we "supported" other databases, we'd be limiting the usefulness of the tool and reducing the range of analytics that could be done with it.

The schema that I've designed also differs from the the default ones used in the data source applications. It's designed specifically for analytics and includes tables that allow you to slice the data by date, time, pokemon properties, eras, and automatically generates spatial data for use with GIS tools. There's nothing stopping you from plugging in Tableau or PowerBI into your database of choice, but I can almost guarantee that the database schema won't support any of this in its current state.

[snicker]

didn't have time to spool up PostGRE on my server and I wanted to start collecting data so I went with pokeminer. never done a lot of geospatial data munging before, but I would love to take this on. I just need a bit of background on what the schema differences are and what the key pieces of data are. is there anywhere i can easily view the schema for Pokelyzer to compare to what pokeminer/pokemongo-map keep track of?

[Brideau]

Sure, the full schema is now in the main readme.

It looks beefy because it's designed to be very flexible. When you insert data into the spotted_pokemon table in the middle, it automatically generates geospatial objects that can be directly used by GIS tools or directly via PostGIS queries.

The date and time dimension tables give you the ability to slice, filter and compare the data however you would like from a time perspective.

Same goes for the pokemon_info table, which could be extended further to add details about the relative rarity of each. This lets you filter the data to only show ones that are of a certain rarity, say.

Finally, the _meta table just helps in diagnosing issues, and keeping track of which version of the schema we're on since it is still changing.

[snicker]

it looks like the best way to migrate data would be to iterate the source and post it to the webhook. However, it looks like my dataset does not store the encounter_id. Given that it should be a unique varchar, and doesn't necessarily need to correspond to anything in the game world for correlation, could I generate something unique for each row?

[Brideau]

That could definitely work, though you run the risk of sending in duplicates if the source didn't de-dupe based on encounter_id, which is a really common problem with scanners that have multiple simultaneous scans.