Perl 6 daemon to slurp netXML files and parse the data into a PostgreSQL database.
- Rakudo 2019.03 (Perl 6.d)
- libpq-dev
zef install XML DB::Pg JSON::Fast Terminal::ANSIColor
To prepare your PostgreSQL database, use the provided example database-tables.sql file to create the two required tables. I've personally been testing with the mdillon/postgis Dockerfile which gives us a not-so unreasonable PostgreSQL 11.2 to work with.
CREATE DATABASE geotrack;
CREATE EXTENSION postgis;
CREATE TABLE "access-points" (
name text NOT NULL,
description text,
location geography(POINT),
PRIMARY KEY (name)
);
CREATE TABLE "client-data" (
client text NOT NULL,
"client-manufacturer" text DEFAULT NULL,
"access-point" text NOT NULL,
"last-signal-dbm" int,
"last-noise-dbm" int,
"last-signal-rssi" int,
"last-noise-rssi" int,
"min-signal-dbm" int,
"min-noise-dbm" int,
"min-signal-rssi" int,
"min-noise-rssi" int,
"max-signal-dbm" int,
"max-noise-dbm" int,
"max-signal-rssi" int,
"max-noise-rssi" int,
timestamp timestamptz NOT NULL,
FOREIGN KEY ("access-point") REFERENCES "access-points"(name)
);For each wireless basestation, you'll need to do some manual SQL queries to add a new 'access point' to the access-points table. This involves first discovering the coordinates of the physical location and using the PostGIS extension to convert that data into a geography(POINT).
geotrack=# SELECT ST_GeometryFromText('POINT(52.3710507 -1.264331)', 4326);
st_geometryfromtext
----------------------------------------------------
0101000020E610000036D4DE967E2F4A40871A8524B33AF4BF
(1 row)With this information, we can now add our new access point.
INSERT INTO "access-points" VALUES ('opusvlwifi', 'OpusVL Office Wifi', '0101000020E610000036D4DE967E2F4A40871A8524B33AF4BF');