What's Up with WiFiDB?

[arizonajon]

Hi Phil and Andrew -

I see that the site appears to be going through a complete db rebuild, or something like that. Any idea when it will be done and ready to allow new uploads and show the newest APs in Google Earth?

Cheers - Jon N7UV

[ACalcutt]

Hi Jon,

At the moment it's just me here, Phil has moved on and I haven't heard from him in over a year, almost 2 now.

I have been working on a rebuild of the wifidb backend. I have changed the backend mysql structure a bit so I can better troubleshoot import issues and to make things a bit more consistent (no more datetimes in 3 different formats, less repeated data in multiple tables). I have also had to do a lot of work on the frontend and apis to get them to work with my new database structure, which is mostly done, but i'm sure there are some things I will find still need fixing.

I have started the rebuild over a few times the last few days because I found some additional things I didn't notice in dev, like the mysql charset we were using wasn't true UTF8, so it wasn't translating certain character correctly. I changed the backend mysql tables to the 'utf8mb4' charset, which is a proper UTF8 (even once fixing that, the import processes wasn't reading UTF8 correctly, which has also been fixed). I also noticed mid import I missed some of the database changes I made in production and had to start over.

So ya, I am doing a rebuild due to the new database structure. Once it completes you should have all your history back and I will turn on imports again. Hopefully the data will be more consistent in the new version, since a lot of improvements have been made to the import process.


Some other changes I have worked on
- Updated to PHP 7.2, the original project was made for PHP 5.3
- Updated to MariaDB v10.3 (a lot of things broke with 10.3 due to our inserts having a blank id field, so suddenly a lot of things started failing silently, because on the backend they were failing to insert)

My version of the WifiDB has been re-homed at https://github.com/acalcutt/WifiDB

-Andrew

[ACalcutt]

I had to restart the rebuild again today. I realized it's been reading Network Type incorrectly for the newer VS1 formats. I've been noticing for a while Infrastructure has been coming in as a number, but today I realized is because it was reading the wrong field on import. unfortunately no way to correct it without a re-import from the source files, so i fixed the issue and restarted yet again... stinks because it had already re-imported 1 million access points.

[arizonajon]

Good evening, Andrew -
Thanks for the update on the server effort. I see it's still in some intermediate state. How long do you suspect it will be before I can upload some more files? I'm assuming that since the db shows only 2.7 M APs right now, and there were about 3.4 M before this, that eventually the db will be back to the old count.
I've got a new Win10 box to replace the mobile Win7 box. So far, I'm not really happy with the Win10 environment, it seems kinda bloated and, for my tiny mobile screen, really hard to navigate. However, it's a quad-core box at least 2 generations newer than the current box, so it certainly should be faster at scanning.
I've also been running Wireshark to see the protocol working as i encounter Cox Wi-Fi APs driving along the street. At least with the Win7 box, it takes so long to go through the handshakes that I'm usually out of range by the time it's done. Makes me long for the "good ol' days" when that experience seemed to be so much faster.
Cheers and 73 - Jon N7UV

[ACalcutt]

We're up to march 2018, so I would suspect I'm almost done with the rebuild...probably by the end of the week. I expect the count will be slightly lower, since one of the issues I fixed was causing duplicate APs (it was reading files fields incorrectly, with one of the fields used in ap hash)

I notice right now it is importing some of your huge files that have basically no new APs, so it is taking forever basically adding huge amounts of signal history. From watching the imports I have the feeling it gets stuck updating the highest GPS when it hits an access point with huge amounts of history...i see the imports pausing on your "cryptic" APs quite often...i think its because it is querying all signal history to figure out its high GPS point every time it sees it, and it just has a ton of history...

I have been working on a lot of front end improvements. I've revived and improved Theme support and am testing a new css version of my outdated vistumbler theme. I may disable this at some point, but right now you can switch between the new theme and the old one in the "Themes" section. I've also added Map, Json, and KMZ icons in a lot of places they didn't exist before (the globe icon now brings you to the map, the red icon is now kmz). Manufacturer has been added pretty much everywhere. Geonames support on the AP page is now working much more simply. I've also been trying to remove a lot of unneeded cruft we're accumulated over the years.

On the backend I updated the import support. The database can now import Vistumbler VS1,VSZ,MDB, WiggleWifi CSV (android), Wardrive DB/DB3 (andorid), Swardriving CSV (android). Wardrive imports are now a direct import and no longer convert to VS1 first... this allows me to keep all the flags that dosen't exist in vistumbler. I wouldn't mind adding support for other applications if you have something that you prefer...at the moment though, vistumbler provides more information than the others. (none of the android exports provide radio type. the import assumes g or n based on frequency)

I don't know if you had a chance to look at any of the new mapbox maps i've added, but I really find the full DB export quite interesting. The amount you've added really is amazing...I never thought we would have any area in that much detail. ( https://live.wifidb.net/wifidb/opt/map. ... =wifidbmap) . I still want to get a few more levels of satellite imagery though, since the road data (Open Street Maps) for phoenix don't seem nearly as good as they are in Massachusetts and at the level you have to zoom to see all the AP detail the satalite imagery is kind of blury.

I've been highly active updating the wifidb ( https://github.com/acalcutt/WifiDB/commits/master ), but i think I have pretty much everything I wanted done for now. at this time i'm just waiting for the rebuild to complete... I think my limitation is the MYSQL server, becasue i've had 8 CPU cores pegged doing the rebuild.

On a hardware note, i had tried a "Vanquisher 8-Inch Industrial Rugged Tablet PC" over the summer. I really wanted to like it but in the end i couldn't keep a stable gps position with the internal gps. i liked it because it was basically an unlocked standard pc in tablet form...but it was a pain to find all the drivers when i reinstalled it. I ended up returning it due to the gps issues... it was to expensive not to have that work.

[arizonajon]

Hi Andrew -

I did see the great work you're doing with Open Street Maps and the ESRI database. For the first time I can really see all the clusters at the national scale, and zoom in as desired to see more detail.

I know my Vistumbler setup is slow, and sometimes gets dozens or more than a hundred APs per GPS location. But when I zoom in, down to neighborhood street detail, I don't see clumps like I would in Google Earth. How were you able to parse individual positions for each AP?

I am seeing something odd - when I first arrived at the wifidb site, I saw neighborhood-street-level details. But now, or maybe as I zoom out then back in to another location, all the street detail is only at national highway levels. Is there something I need to do to enable greater detail?

I wish I could find all my old ns1 files to take advantage of the new features - they have all been lost over time and storage media.

Thanks for the work to improve wifidb!

Cheers and 73 - Jon N7UV

[ACalcutt]

Hi Jon,

Sorry for the late reply... you actually got me working on making a better base map with your last post and I think I finally got something worth posting about it.

I noticed OpenStreetMaps had a lot of great road detail that just didn't seem to be in the free OpenMapTiles basemap I was using. you had mentioned you thought you had seen that detail at one point, but I hadn't seen that it in Arizona (though massachussetts had great detail in that map). Since the paid OpenMapTiles map is over $1000, I set off to make my own map using their open source code. Their code was actually quite good, but it was obfuscated in a bunch of docker images that made it somewhat slow and difficult to read (My initial test with north america said it would take 122days). I felt it would be faster without all the docker virtualization crap, so I went into all the docker images and pulled what they were doing into flat scripts ( https://github.com/acalcutt/wdb-map-gen ). After I de-dockerized openmaptiles and gave it some real hardware I was able to get the full planet map to say it would take a month...then after that I removed a few layers and got that down to 8 days. The 8 days has finally completed and I have this nice new base map ( https://omt.wifidb.net/data/WdbMapTiles/#4/38.97/-94.76 ) that goes down to zoom level 14 now. The road details are great now and I think it will make a great base map for the project. The only thing I would still like to add are labels (like road names), but for now those layers were excluded for two reason (1, they increase the map generation time tremendously, and 2, I haven't make the script to pull in the wikidata it needs to generate those layers.

So far I'm really happy with how the map turned out. Right now it is being used when you select the "Open Street Map" radio button under the map. The roads are also combined with the satellite map I created when you chose "World Imagery + Open Street Map". These are no longer using the free Open Map Tiles basemap, but my own custom made map


For your other question on how was I able to parse individual positions for each AP. the position improves the more times the access point is seen. I am basically taking all the signal history for the AP and ordering it by RSSI, then Date, then Number of GPS satellites and picking the top result. The AP position is updated every time it is seen in a new list. So really the position the WifiDB marks the AP is the place where it had the highest signal strength in all its history. The good thing about this is it makes the positions more accure, but the downside is some APs with huge amount of history are slowing down the imports because it is querying all the signal history.

For you NS1 files, that would be cool, but I don't miss that format at all. There was no distinction between WEP and WPA in that format and I don't think they had radio either. It would just lead to a bunch of hard to match inaccurate APs anyway...lol

[arizonajon]

Hi Andrew -
I am currently zoomed in here in PHX and am seeing alleys, parks, watercourses, ponds, etc. That seems to be working fine. Then, I typed in some random address on Wacker Dr in Chicago, and have the same level of detail. Seems like all the work you did really paid off!
I am trying to run WiFiDB_Uploader and it uploads exactly one file then quits. Am not sure the version, but it's the same that I was using when I was successfully uploading blocks of logs back in November. Is there something new in terms of the uploader?
Cheers and 73 - Jon N7UV

[arizonajon]

However, no labels appear at any scale but really zoomed out. For instance, the city name "Cedar Rapids" shows, but that's it.

[ACalcutt]

I think I fixed Phil's version of the WifiDB Client. It looks like some of the v2 wifidb api code got reverted to an earlier version and it wasn't responding in a way his client expected. I've updated the v2 api and it seems to be working again, let me know if it still seems to be acting strange. My version of the WifiDB Uploader was working since it uses the older v1 API.

For the labels on the maps, that's pretty much expected. I took most of the label layers out for the first version of the map to simplify things and get it created in less time. The OpenMapTiles schema ( https://openmaptiles.org/schema/ ) has several text layers... right now the map I created is missing housenumber, aerodrome_label, park, poi, transportation_name, and water_name

Right now I am trying to generate a new map that includes the transportation_name layer, but Its been a bit of a pain.

[arizonajon]

just tried the uploader again and it seems to be working well. Thanks!

Then, I went over to the AP Map page, unselected all the "year" buttons, and selected "Follow Latest AP". It was really cool, watching it step down the SR51 freeway for a few miles, a new one every few seconds. Then it stopped on "TOYOTA Camry_5997ca2474d7". Will keep watching it to see if it starts up again. Checked the importer, files are getting uploaded, and it says "processing" for the latest file.

[arizonajon]

as soon as I clicked "send", it updated the map page. Nevermind...

[arizonajon]

Howdy Andrew -
Have been watching the AP map on my iPad for the last half hour or 45 minutes now as the machine grains through my upload. Is everything being done on a single machine?
I was wondering how the points get placed on the street map tile. It appears, at least here, that the points are live which means I can click on them and it’s not just a static image. I had been thinking that maybe you sent the tile already with the dots placed on it but that doesn’t appear to be the case. When you send a set of tiles to my computer, do you do you also send the corner coordinates and then when you send the points you tell them what their absolute coordinates are or are they relative coordinates relative to the corners?

[ACalcutt]

Hi Jon, you are correct that the map isn't a static image. I am using Mapbox GL on the frontend and TileServer-GL on the backend

For the daily and latest layers it is actually a dynamic json export that is created live. The json files are linked directly in the map and are displayed with some javascript magic

For the big exports (0 year - 3+ year) there is a nightly process the exports all access point information into 4 big JSON files. then the JSON files are processed by a program called "tippecanoe", which takes the JSON and generates a mbtiles database file for the 4 different layers. tippecanno does grouping at the various zoom levels to trys and keep 200,000 features per tile so that the performance of the map stays acceptable. Once the mbtiles file is generated, it is sent to TileServer-GL, which is what is serving up the maps. This whole process gives me two maps, one with all the legacy 1+ year data ( https://omt.wifidb.net/data/WifiDB/#16/ ... -111.92596 ) and one with the newest data ( https://omt.wifidb.net/data/WifiDB_newe ... -112.07428 ). It is all nicely formatted in layers and has all the AP information in a nice filterable way

The full WifiDB map is actually a combination of about 4 different map files and lots of layers, which are combined by a style on the TileServer-GL server. its a combination of these maps
https://omt.wifidb.net/data/WdbMapTiles/#4/40.07/-99.46
https://omt.wifidb.net/data/NE2/#4/40.07/-99.46
https://omt.wifidb.net/data/WifiDB/#16/ ... -111.92596
https://omt.wifidb.net/data/WifiDB_newe ... -112.07428

The style also does the circle coloring, which i currently using sectype like the old maps. The nice thing about this mbtiles format is I can filter on pretty much any AP information I included, which is everything you see in the popup when you click the AP. I am also using the style to make the circle smaller and darker based on age (indicated by what layer the AP is on)

The whole wifidb and maps setup is all running on one server, though it is separated out into 3 virtual servers. Right now I have a WifiDB frontend virtual server, running apache/php, a WifiDB Backend virtual erver running MariaDB (MySQL), and a map Server running TileServer-GL and generating my wifiDB maps. The last few weeks I have also been using a decommissioned R620 to generate my OpenMapTiles map, since my server running everything else was pretty much at capacity cpu and disk wise.

[ACalcutt]

Also, examples of the JSON can be seen all around the wifidb. The is now "{i}" buttons all over, which show what the json being generated looks like. For example ( https://live.wifidb.net/wifidb/api/geoj ... id=2642156 ). The GPS position is the decimal lat and long, which is the Highest GPS point the wifidb has calculated based on the RSSI, Date, and Sats I mentioned before.

[ACalcutt]

I have to look into a better way to calculate the High GPS point. When it gets uncommon/new APs it flies right though them...but the common ones its just getting stuck trying to find the High GPS point.

[arizonajon]

I have been trying to use more of the features that you're adding to WiFiDB. I appreciate the "show on map" button for individual APs and for whole runs. Much quicker to locate using the inbuilt mapping function rather than the export to kmz then viewing on GE.
I think there's something funky now about my uploader. When I start it up, the usual box appears where I can give a name to the upload set. Then, on clicking OK, it's obviously grabbing new files, uploading them successfully, then deleting them locally. The window stays open. However, after a while (not defined) the whole window just vanishes, like the program has closed abruptly. But, I may find it's still running, only by combing through the Task Manager. Until recently, the abrupt close was never seen by me. It was always the 30 second countdown and then it would really terminate. Now, on the same W7Pro box I've been using for a year or two, when I attempt to shut down when the program is still running, i get the process preventing windows from shutting down warning. Weird.

[arizonajon]

is there a way for me the user to get an idea of how many individual records exist for a given AP? I do a search on "coxwifi" and come up with a list of nearly 23k APs. but there's nothing there that says for AP 22:25:64:FE:6B:46 (for instance) there's 42 positions, or 13,042 positions. I have no idea what good this would do me, but I'm just curious.
Also, since you're able to take advantage of browser processing (not necessarily good for my 7 year old iPad), what if when I request a dump of all APs named "coxwifi", that when i want to sort that list by last date active, or channel number, that the browser could do that locally rather than what appears to be a request back to the server to re-sort. Is that a way to move load off the server? Or does it matter?

[arizonajon]

On the AP Map page, the word "Natural" in the phrase "(Copyright) Natuaral Earth II" is misspelled.

[ACalcutt]

For the WifiDB Client issue, can you post your traces from the uploader log directory. Its possible something else is missing in the v2 api and these may help me figure out what.

there is no count of positions at the moment, but I can look into it. the closet would be the access point details page where it shows each list the ap was part of. the lists on that page can be expanded to see all the points for that AP and their positions. The problem with that is that page probably takes a while to load when there is a large amount of history....

Natural should now be spelled correctly, thanks

[arizonajon]

My WiFiDB upload client ("2.0", from 2016) is no longer even loading correctly. Sounds like you have a version that is behaving well. Where can I get your version?

[ACalcutt]

If I use an auto-uploader I typically use the version I wrote here ( https://github.com/acalcutt/Vistumbler/ ... loader.exe )

I wouldn't mind figuring out whats going on with phils version though, his has a few more nice features. I was having trouble getting the version I downloaded to automatically upload files... I could only seem to get it to work if I manually imported a folder, but it looks like I have v1...not sure where I find v2

[arizonajon]

i dont have something called an uploader log directory. heres a screenshot.

[arizonajon]

Capture.PNG (36.22 KiB) Viewed 202031 times

try again:

[ACalcutt]

Open the uploader and go into "Settings --> Debug and Logging" and enable the 3 trace options

[ACalcutt]

Yours says version says v2 in the title? I can only seem to find source code going to v1.1

[arizonajon]

When I start WiFiDB Uploader, I only get the box that allows me to assign a name to the upload. The main screen (tabular format, showing individual uploads) never shows. However, in the Task Manager, it's running. And, it seems, it is uploading, as I see a bunch of files having been uploaded. Something's really unstable...

[arizonajon]

I can't tell, now. Here's the directories with all the WiFiDB Uploaders I know of:

WiFiDBU19jan19.png (5.68 KiB) Viewed 202025 times

[arizonajon]

here's the contents of the 04122016 directory.

WiFiDBU19jan19_A.png (25.36 KiB) Viewed 202025 times

[ACalcutt]

Jon,

Can you try this version and let me know if it opens for you
https://github.com/acalcutt/WiFiDBClien ... tag/v1.2.1

You will have to set your username and api key in Settings --> WifiDB Settings --> Edit . I would also close and restart after setting that and any of your other settings.

[arizonajon]

thanks Andrew. Did i ever have a API key?

[ACalcutt]

Do you know your password to log into the wifidb site? If you log in you can get your Api key by going to your profile (log in and click your username in the upper right of the page).

I'm almost positive api key has been required for uploads through the api for a while.

Just FYI, I just posted 1.2.1 ( https://github.com/acalcutt/WiFiDBClien ... tag/v1.2.1 )

[arizonajon]

morning Andrew - then i try to log in to wifidb says that i'm locked out...

[arizonajon]

For me, at least, that version of WiFiDB does not run on my mobile PC. "WDB_Uploader has stopped working."

[ACalcutt]

Just some quick updates...

1.) A new GeoLocation button has been added to the maps. This allows you to follow your current location, provided your device has that information and you allow it when your browser asks. I tested this the other day driving on my Note 8 and it worked pretty well. It was nice being able to see live where there were no APs

2.) I have added some point counts on a few pages. The AP details page now has points count for all lists in the details at the top and a count per list, right above where you expand signal history. The file list page also now has a points column, to show points per AP (in that list). I have also added a point count into the map, when you click the AP to get its details.

3.) I fixed a few issues with the KMZ exports. If you are using an old network link with google earth, please update it with the one on the Export KMZ page

4.) I've spent many hours trying to get the WifiDB emails to not be considered spam, but I still seem to be having issues with microsoft (gmail works fine). I've set up SPF, DKIM, DMARK and it all shows valid it my tests and they still don't like it for some reason. If you expect a validation email and don't get it, please check spam/junk folders. Also, if it is there, please mark not spam if its an option.

5.) The main domain has been changed just to wifidb.net . One reason for this is one of my spam tests indicated that 'live' in live.wifidb.net raised my spam score and I wanted to rule that issue out. live.wifidb.net will continue to work, but navigating will bring you to the new domain.

6.) I have created a new basic home page with project information, mainly because the old one was really slow and made browsing to other pages quickly harder. The old homepage still exists, but it has been moved to 'Stats'

[arizonajon]

Things are working pretty well with my setup, and I've made some minor changes to the mobile to make it perhaps be more sensitive.

While we've already talked about the OpenMap layer, and that the label database isn't imported. But I'm zoomed in on midtown Phoenix and I see many labeled roads like "OR99", "QR64", "V49", "VA690", etc. None of those labels jibe with any historical information I can find.

[ACalcutt]

Since we last talked about the road names I have tried to add the 'transportation_name' layer. I haven't quite gotten it to complete properly, but I thought even the incomplete version was nice... so i put it into place for now. I've notice the same thing about certain roads having names I have never seen, and even some that have a blank label. I'm not sure, but I think this may be because the process didn't complete properly. I have tried a few times to get this layer built, but I keep having trouble because it needs a postgres module I haven't quite got to work right. I tried to use an older version of the layer sql that didn't use the postgres plugin, but haven't had any luck with that either.


I have been working a bit on the Wigle Wifi for android import. Their csv file also include cell tower and bluetooth information, so I've tried to add some back-end support for that. So far I am only really displaying it on the map (you should see purple dots for cell towers in massachusetts), but I would like to expand it further once I can get a grasp on whats there.

[Metz]

Hello, is it normal for the wifidb system to be processing recovery files? Or is this due to changes or updates on the back end?

[arizonajon]

Hey Metz -

Go back up this thread to find the link to the WiFiDB Uploader.

Cheers and 73 - Jon N7UV

[Metz]

Doh!! as Homer would say

[arizonajon]

Good morning Andrew -

I seem to be having the slowest upload ever experienced. Metz's stuff is all done, there's no other obvious things running on the server, and I'm running v1.2.1 WiFiDB Uploader. I have over 100 files to upload, but it seems that I can only upload one in the WiFiDB instance running and it never uploads a second or third, and that I have to restart the uploader each time to get one more file uploaded.

As you remember, in the past WiFiDB Uploader batch-uploaded bunches of files, and while it took some time, it was all good and quite reliable. That was with the old uploader version. What is interesting is that I can go to the WiFiDB Import page and import any of the files waiting, and it seems to do it rapidly enough. I just did that again and boom the file was on the server waiting for import.

CaptureWiFiDB.PNG (72.5 KiB) Viewed 199866 times

Here's snap of the Daemon Statuses log taken at 1445Z, after I had restarted WiFiDB Uploader around 1443.

What can I do to get this to start working again?

Cheers and 73 - Jon N7UV

[ACalcutt]

Jon,

I seems to be having issues connecting from work too. I think my server internet connection may be having issues today because my access seems to be very intermittent.


Metz,

Welcome, good to see all your uploads! For your question about the recovery files, I was re-importing a bunch of old CSV files that didn't import properly the first time, about 45 files that origionally failed to import. I've also been working on some backend changes to the import functions to improve, standardize and speed up the imports for the various formats. I found performance issues when I was working with the CSVs which should now be improved (some of the changes I made should also help the other formats)

-Andrew

[Metz]

I found some of my NS1 files from y2k era should I upload?

Also any tips on best settings for vistumbler? With the uploader should I keep auto save to 1k or less ap's?

My concern is keeping loop time as low as possible.

[ACalcutt]

Jon,

I found an issue on the v2 import api that should be fixed. The insert into the database had an extra execute, so it would insert and succeed...then insert and fail because it already existed. This returned a SQL error like "Violation of UNIQUE KEY constraint 'files_tmp$hash'. Cannot insert duplicate key in object 'dbo.files_tmp'. The duplicate key value is (2d1632fa632648f1feef35af43b39ae9)." that the WifiDB Uploader didn't know how to deal with, so it would upload one file and go no further. After removing the extra execute, it stopped giving that error and now the WifiDB uploader seems to be working correctly again.

There have actually been a lot of back end changes recently. I switched the backend from MySQL to Microsoft SQL 2019 for linux. This was something I was testing for a while, but I decided to put it into place about a month ago because we had a big crash. Unfortunately the server experienced a power failure and ups failure while it was importing and corrupted the biggest table (the wifi history table). Since the fixes i found for the corruption were basically to dump the table in recovery mode and reimport it, I decided just to make the cutover to MSSQL and reimport everything again. There were many things I have had to fix in the last month to get this MSSQL integration working properly, but I think i'm nearing the point where everything is working again.

[ACalcutt]

Metz,

I typically set my autosave limit to 1000 because it leaves less of a gap when the saving take place. I wouldn't go more than 2000 if you want vistumbler to perform well.

When you are wardriving I highly recommend being in Minimal GUI mode (View -> Minimal GUI Mode). This removes the listview and treeview, which slow things down quite a bit when you have a large amount of APs in the list.

-Andrew

[ACalcutt]

For the NS1 files, you can import them if you wanted, but the WifiDB doesn't support them directly...you would have to import the ns1 into vistumbler and export a vs1. Personally I wouldn't bother with it, mainly because the NS1 files are missing a lot of information that makes them hard to match up with the other data.

[arizonajon]

Hey Andrew -

Running the Uploader again just now - it appears to be working well and advancing through the waiting files. Server looks to be taking one every 30 seconds or so. Thanks for resolving that!

Cheers and 73 - Jon N7UV