Page 1 of 1

Admin access to local WifiDB

Posted: Wed Nov 13, 2019 1:00 am
by mattydiah
I have WifiDB installed and running on an ubuntu box. However, I cannot figure out how to create an administrative account. I turned account activation off, as this wont be a public instance. Then I created myself an account. The only way I could figure to make myself an admin was to run " update user_info set admin = 1 where username = '<my_user>'; in mysql, but that either didnt work, or I dont know where to go to see the admin control panel.

Can you provide some insight?

Thanks

Re: Admin access to local WifiDB

Posted: Wed Nov 13, 2019 8:26 am
by mattydiah
Also, should I be using the "ac-wifidb-sqlsvr" branch, or the master?

Re: Admin access to local WifiDB

Posted: Sat Nov 16, 2019 1:23 am
by ACalcutt
at this time there really isn't an admin control panel. there was a basic admin panel at one time, but it was never recreated when the wifidb got redesigned and changed to smarty templates. The settings are mainly in config files 'wifidb/lib/config.ini.php' and 'tools/daemon.config.inc.php', or in the settings/schedule table

master and ac-wifidb-sqlsvr are basically the same right now (ac-wifidb-sqlsvr is slightly ahead, with added kismet support). However if you are using mysql instead of microsoft sql, you may have some trouble actually importing. most of this release is compatible with mysql and microsoft sql, but there are several spots in the import where I changed to a microsoft sql merge statement because I noticed in the way I was importing in mysql was causing duplicates. In those few cases, I don't think I left the mysql code in place, since they didn't have the merge statement and I'm not using mysql at the moment.

Re: Admin access to local WifiDB

Posted: Sat Nov 16, 2019 10:57 am
by mattydiah
Ok, so I’m better off running this on an MSSQL DB? I think I should be able to just edit the config files and repoint the database it looks for. Hopefully it will work with SQL Express.

One other question, when I go to the map, it shows all of the entries from everyone on wifidb.net. How do I change it so the map only looks at my dataset and not yours?

Thanks for your help!!

Re: Admin access to local WifiDB

Posted: Sat Nov 16, 2019 11:54 am
by ACalcutt
Yes, at the moment MSSQL is probably the best to go with, since that is what I am testing with here. I am currently using SQL 2019 for linux (centos) with a database in the new unicode collation (Latin1_General_100_CI_AS_SC_UTF8). You would set up your tables with the 'blank_db.sqlsrv' file instead of 'blank_db.sql'

The map question is a bit more complicated, basically you first need a tile server to host you map content. I am using TileServer GL hosted at my domain https://omt.wifidb.net/, which is where all the basemaps and wifidb map data is stored. I originally started by using the free OpenMapTiles, which gives you tileserver-gl and a free base map, which worked well but didn't have very detailed maps (unless you pay them over $1000). I eventually set up my own TileServer GL instance, somewhat more more detailed base map ( https://github.com/acalcutt/wdb-map-gen ), which I'd willing to share if interested (it took forever to create)

The current process looks like this
1.) On the wifidb server a cron job (tools/cron/update_geojson) runs '/daemon/geojsond.php' and creates geojson files in '/wifidb/out/geojson'

2.) On the tile server I have a script that downloads those geojson files and the uses a software called tippecanoe to convert them from geojson into the mapbox format needed by Tileserver-GL. My script looks like this

Code: Select all

#!/bin/bash
PATH="/root/.nvm/versions/node/v8.11.3/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin"
echo "---Cleanup Old Files---"
if [ -e "/opt/convert/WifiDB_weekly.json" ]; then rm /opt/convert/WifiDB_weekly.json; fi
if [ -e "/opt/convert/WifiDB_monthly.json" ]; then rm /opt/convert/WifiDB_monthly.json; fi
if [ -e "/opt/convert/WifiDB_0to1year.json" ]; then rm /opt/convert/WifiDB_0to1year.json; fi
if [ -e "/opt/convert/WifiDB_1to2year.json" ]; then rm /opt/convert/WifiDB_1to2year.json; fi
if [ -e "/opt/convert/WifiDB_2to3year.json" ]; then rm /opt/convert/WifiDB_2to3year.json; fi
if [ -e "/opt/convert/WifiDB_Legacy.json" ]; then rm /opt/convert/WifiDB_Legacy.json; fi
if [ -e "/opt/convert/cell_networks.json" ]; then rm /opt/convert/cell_networks.json; fi
if [ -e "/opt/convert/WifiDB.mbtiles" ]; then rm /opt/convert/WifiDB.mbtiles; fi
if [ -e "/opt/convert/WifiDB_newest.mbtiles" ]; then rm /opt/convert/WifiDB_newest.mbtiles; fi
if [ -e "/opt/convert/cell_networks.mbtiles" ]; then rm /opt/convert/cell_networks.mbtiles; fi

echo "---Downloading WifiDB.json---"
scp -r user@serverip:/srv/www/virtual/live.wifidb.net/wifidb/out/geojson/WifiDB_weekly.json /opt/convert/
scp -r user@serverip:/srv/www/virtual/live.wifidb.net/wifidb/out/geojson/WifiDB_monthly.json /opt/convert/
scp -r user@serverip:/srv/www/virtual/live.wifidb.net/wifidb/out/geojson/WifiDB_0to1year.json /opt/convert/
scp -r user@serverip:/srv/www/virtual/live.wifidb.net/wifidb/out/geojson/WifiDB_1to2year.json /opt/convert/
scp -r user@serverip:/srv/www/virtual/live.wifidb.net/wifidb/out/geojson/WifiDB_2to3year.json /opt/convert/
scp -r user@serverip:/srv/www/virtual/live.wifidb.net/wifidb/out/geojson/WifiDB_Legacy.json /opt/convert/
scp -r user@serverip:/srv/www/virtual/live.wifidb.net/wifidb/out/geojson/cell_networks.json /opt/convert/

echo "---Convert Geojson to mbtiles---"
/opt/tippecanoe/tippecanoe -z16 --cluster-densest-as-needed --maximum-tile-features=300000 --maximum-tile-bytes=750000 -r1 -n "WifiDB_newest $(date -d "today" +"%Y-%m-%d %H:%M")" -A "<a href=""https://wifidb.net/"" target=""_blank"">&copy; WifiDB $(date -d "today" +"%Y-%m-%d")</a>" -o /opt/convert/WifiDB_newest.mbtiles /opt/convert/WifiDB_weekly.json /opt/convert/WifiDB_monthly.json /opt/convert/WifiDB_0to1year.json
/opt/tippecanoe/tippecanoe -z16 --cluster-densest-as-needed --maximum-tile-features=300000 --maximum-tile-bytes=750000 -r1 -n "WifiDB $(date -d "today" +"%Y-%m-%d %H:%M")" -A "<a href=""https://wifidb.net/"" target=""_blank"">&copy; WifiDB $(date -d "today" +"%Y-%m-%d")</a>" -o /opt/convert/WifiDB.mbtiles /opt/convert/WifiDB_1to2year.json /opt/convert/WifiDB_2to3year.json /opt/convert/WifiDB_Legacy.json
/opt/tippecanoe/tippecanoe -z16 --cluster-densest-as-needed --maximum-tile-features=300000 --maximum-tile-bytes=750000 -r1 -n "WifiDB_cells_ $(date -d "today" +"%Y-%m-%d %H:%M")" -A "<a href=""https://wifidb.net/"" target=""_blank"">&copy; WifiDB $(date -d "today" +"%Y-%m-%d")</a>" -o /opt/convert/cell_networks.mbtiles /opt/convert/cell_networks.json

echo "---Move file and restart tileserver gl---"
mv /opt/convert/WifiDB_newest.mbtiles /opt/tileserver-gl/data/
mv /opt/convert/WifiDB.mbtiles /opt/tileserver-gl/data/
mv /opt/convert/cell_networks.mbtiles /opt/tileserver-gl/data/
/etc/init.d/tileserver stop
/etc/init.d/tileserver start
3.) The script automatically restarts tileserver-gl, and the tileserver config.json points to your mapbox files like this

Code: Select all

{
  "options": {
    "paths": {
      "root": "/opt/tileserver-gl/",
      "fonts": "fonts",
      "styles": "styles",
      "mbtiles": "data"
    }
  },
  "styles": {
    "WDB_ESRI": {
      "style": "WDB_ESRI/style.json",
      "tilejson": {
        "bounds": [
          -180,
          -85.0511,
          180,
          85.0511
        ]
      }
    },
    "WDB_ESRIOSM": {
      "style": "WDB_ESRIOSM/style.json",
      "tilejson": {
        "bounds": [
          -180,
          -85.0511,
          180,
          85.0511
        ]
      }
    },
    "WDB_LIBERTY": {
      "style": "WDB_LIBERTY/style.json",
      "tilejson": {
        "bounds": [
          -180,
          -85.0511,
          180,
          85.0511
        ]
      }
    },
    "WDB_OSM": {
      "style": "WDB_OSM/style.json",
      "tilejson": {
        "bounds": [
          -180,
          -85.0511,
          180,
          85.0511
        ]
      }
    },
    "WDB_POSITRON": {
      "style": "WDB_POSITRON/style.json",
      "tilejson": {
        "bounds": [
          -180,
          -85.0511,
          180,
          85.0511
        ]
      }
    }
  },
  "data": {
    "v3": {
      "mbtiles": "osm-2018-07-16-v3.8-planet.mbtiles"
    },
    "ESRI": {
      "mbtiles": "ERSI_WI_0-11.mbtiles"
    },
    "NE2": {
      "mbtiles": "NE2_HR_LC_SR_W_DR.mbtiles"
    },
    "WifiDB_newest": {
      "mbtiles": "WifiDB_newest.mbtiles"
    },
    "WifiDB": {
      "mbtiles": "WifiDB.mbtiles"
    },
    "WdbMapTiles": {
      "mbtiles": "WdbMapTiles.mbtiles"
    },
    "WifiDB_cells": {
      "mbtiles": "cell_networks.mbtiles"
    }
  }
}
4.) The are also custom styles referenced in the tileserver config.json, which tell tileserver how you want to layer the basemap layers and wifidb layers. I've included this in the attached zip

5.) Right now I haven't added any config options to set the tile server used in the wifidb (i plan to eventually). You would have to go into wifidb/opt/map.php and change all the references to omt.wifidb.net to wherever your tile server is located

Re: Admin access to local WifiDB

Posted: Sat Nov 16, 2019 12:05 pm
by mattydiah
Awesome! Thank you! It’ll take me a bit to digest that but I’ll be back with questions I’m sure!

Re: Admin access to local WifiDB

Posted: Sat Nov 16, 2019 12:59 pm
by ACalcutt
Also, if you use SQL express edition I think you should be fine, though you may be missing some features in management studio that are nice.

in SQL 2019 for linux there is also a developer version, which is free and has all the features of enterprise with the stipulation your aren't using it in production. (https://docs.microsoft.com/en-us/sql/li ... rver-ver15)

Re: Admin access to local WifiDB

Posted: Sat Nov 16, 2019 2:45 pm
by mattydiah
I completely forgot MSSQL was linux native now. I'll just use that, and this is for sure non-prod, so I should be able to put it into dev mode no sweat.

Re: Admin access to local WifiDB

Posted: Sat Nov 16, 2019 2:49 pm
by mattydiah
Also, if you'd be willing to share your your basemap, I'd love to use it. Thanks!

Re: Admin access to local WifiDB

Posted: Sun Nov 17, 2019 3:35 pm
by ACalcutt
Heres the two main basemaps you need with my styles, the OpenStreetMaps basemap and the natural earth basemap
https://drive.google.com/drive/folders/ ... sp=sharing