Skip to content

Instantly share code, notes, and snippets.

@anirudh-ramesh

anirudh-ramesh/_backup_restore.sql

Last active July 9, 2023 05:54
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save anirudh-ramesh/8e62c033ec8de433148c1e70387d0259 to your computer and use it in GitHub Desktop.
Save anirudh-ramesh/8e62c033ec8de433148c1e70387d0259 to your computer and use it in GitHub Desktop.
Download ZIP
Clean Irasus data
Raw
_backup_restore.sql
SET statement_timeout TO 1200000;
--
\COPY (SELECT * FROM "traccar"."tc_devices" ORDER BY "id" ASC) TO '/goodenough_0/devices.csv' WITH CSV DELIMITER ',' HEADER QUOTE '"' ESCAPE '''';
\COPY (SELECT * FROM "traccar"."tc_users" ORDER BY "id" ASC) TO '/goodenough_0/users.csv' WITH CSV DELIMITER ',' HEADER QUOTE '"' ESCAPE '''';
\COPY (SELECT * FROM "traccar"."tc_positions" ORDER BY "id" ASC) TO '/goodenough_0/positions.csv' WITH CSV DELIMITER ',' HEADER QUOTE '"' ESCAPE '''';
--
\COPY (SELECT "id", "name", "uniqueid" FROM "traccar"."tc_devices" ORDER BY "id" ASC) TO '/goodenough_0/_devices.csv' WITH CSV DELIMITER ',' HEADER QUOTE '"' ESCAPE '''';
\COPY (SELECT "protocol", "deviceid", "devicetime", "latitude", "longitude", "altitude", "speed", "course", "attributes", "accuracy", "network" FROM "traccar"."tc_positions" ORDER BY "fixtime" ASC, "deviceid" ASC) TO '/goodenough_0/_positions.csv' WITH CSV DELIMITER ',' HEADER QUOTE '"' ESCAPE '''';
--
\COPY "traccar"."tc_devices" ("id", "name", "uniqueid") FROM '/goodenough_0/_devices.csv' WITH CSV DELIMITER ',' HEADER QUOTE '"' ESCAPE '''';
--
CREATE TABLE "traccar"."_positions" (
"id" integer NOT NULL,
"protocol" character varying(128),
"deviceid" integer NOT NULL,
"devicetime" timestamp without time zone NOT NULL,
"latitude" double precision NOT NULL,
"longitude" double precision NOT NULL,
"altitude" double precision NOT NULL,
"speed" double precision NOT NULL,
"course" double precision NOT NULL,
"attributes" character varying(4000),
"accuracy" double precision DEFAULT 0 NOT NULL,
"network" character varying(4000)
);
ALTER TABLE "traccar"."_positions" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
SEQUENCE NAME "traccar"."_positions_id_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
ALTER TABLE ONLY "traccar"."_positions"
ADD CONSTRAINT "_positions_pkey" PRIMARY KEY ("id");
ALTER TABLE ONLY "traccar"."_positions"
ADD CONSTRAINT "fk_positions_deviceid" FOREIGN KEY ("deviceid") REFERENCES "traccar"."tc_devices"("id") ON DELETE CASCADE;
SELECT pg_catalog.setval('"traccar"."_positions_id_seq"', 1, false);
SELECT "create_hypertable"('"traccar"."_positions"', 'id', chunk_time_interval => 100000);
\COPY "traccar"."_positions" ("protocol", "deviceid", "devicetime", "latitude", "longitude", "altitude", "speed", "course", "attributes", "accuracy", "network") FROM '/goodenough_0/_positions.csv' WITH CSV DELIMITER ',' HEADER QUOTE '"' ESCAPE '''';
INSERT INTO "traccar"."tc_positions" ("protocol", "deviceid", "devicetime", "fixtime", "valid", "latitude", "longitude", "altitude", "speed", "course", "attributes", "accuracy", "network") SELECT "protocol", "deviceid", "devicetime", "devicetime" AS "fixtime", true AS "valid", "latitude", "longitude", "altitude", "speed", "course", "attributes", "accuracy", "network" FROM "traccar"."_positions";
Raw
clean.sh
find . -type f -empty -print -delete
date +%s
du -a --max-depth=1 | sort -n
du -a -h --max-depth=1 | sort -hr
find . -name '*.dbc' -exec grep -rl 'Vector' {} \;
Raw
extract.sh
# Run the following on osmand HTTPPASS files
# Traccar
cat $1 | LANG=C sed -e 's/{\"id\":\"//g' -e 's/\",\"timestamp\":\"/,/g' -e 's/\",\"lat\":\"/,/g' -e 's/\",\"lon\":\"/,/g' -e 's/\",\"speed\":\"/,/g' -e 's/\",\"bearing\":\"/,/g' -e 's/\",\"altitude\":\"/,/g' -e 's/\",\"accuracy\":\"/,/g' -e 's/\",\"batt\":\"/,/g' -e 's/\",\"charge\":\"/,/g' -e 's/\"}//g' -e 's/\(\.[0-9][0-9]\)\([0-9]\)\{6,13\}/\1/g' -e 's/\([0-9]\)*\.\([0-9]\)*E-\([0-9]\)\?/0/g' -e 's/0\.00\([0-9]\)\?/0/g' -e 's/\.0*$//g' -e 's/\.0*,/,/g' -e 's/\([0-9]\)\?$/\1,false/g' | sort | awk -F '[,]' '!uniq[$1$2]++' > $2.csv
# id, timestamp, latitude, longitude, speed, bearing, altitude, accuracy, battery_SoC, battery_SoCMOSFET
# Run the following on ATLANTA files
# Traccar
cat $1 | LANG=C sed -e 's/[\d128-\d255]//g' -e 's/ATL\([0-9]\{15\}\),/\nATL\1,/g' -e '/,,,,,,,/d' -e 's/\(.*\)ATL.\{1\}$/\1/' -e 's/\(.*\)ATL.\{2\}$/\1/' -e 's/\(.*\)ATL.\{3\}$/\1/' -e 's/\(.*\)ATL.\{4\}$/\1/' -e 's/ATL//g' -e 's/\$GPRMC,//g' -e 's/,,\*[0-9A-F]\{2\},\#//g' | sort | uniq > $2
# IMEI, timestamp, validity, latitude, latitude_reference, longitude, longitude_reference, speed, direction, date, io, voltage_external, x1, x2, odometer, temperature, voltage_internal, RSSI, MCC, MNC, LAC, cell
# Traccar TrakMate fix for new format
sed -e '/000000/d' -e '/,,,/d' Geographical_$1
Raw
extract_TCPPASS.sh
wd=`pwd`
for file in TCPPASS_*; do
timestamp=`echo $(date +%Y%m%d%H%M%S)_${file}`
echo ${timestamp}
echo ${timestamp} >> extract.log
cat ${file} | LANG=C sed -e '/^\^TM[A-Z]\{2\}R|\([0-9]\)\{15\}|/!d' -e '/-(\./d' -e '/^\^TM[A-Z]\{2\}R|\([0-9]\)\{15\}|\([0-9]\)*|0.00000|0.00000/d' -e '/^\^TM[A-Z]\{2\}R|\([0-9]\)\{15\}|\([0-9]\)*|\([0-9]\)\{2\}\.\([0-9]\)\{5\}|\([0-9]\)\{2\}\.\([0-9]\)\{5\}|0|0|/d' -e '/[\d128-\d255]/d' | sort -T ${wd} | uniq | awk '{ o=$0; print gsub(/\|/,""), ",", o }' | sed -e 's/ //g' | awk -F, '{print $2 >> "Geographical_"$1}'
cat ${file} | LANG=C sed -e '/^\^TMBMS|\([0-9]\)\{15\}|/!d' -e '/-(\./d' -e '/^\^TMBMS|\([0-9]\)\{15\}|\([0-9]\)*|0|0|/d' -e '/[\d128-\d255]/d' | sort -T ${wd} | uniq | awk '{ o=$0; print gsub(/\|/,""), ",", o }' | sed -e 's/ //g' | awk -F, '{print $2 >> "Electrical_0_"$1}'
cat ${file} | LANG=C sed -e '/^\^OEM03|\([0-9]\)\{15\}|/!d' -e '/-(\./d' -e '/^\^OEM03|\([0-9]\)\{15\}|\([0-9]\)*|0|0|/d' -e '/[\d128-\d255]/d' | sort -T ${wd} | uniq | awk '{ o=$0; print gsub(/\|/,""), ",", o }' | sed -e 's/ //g' | awk -F, '{print $2 >> "Electrical_1_"$1}'
done
Raw
extract_tcpserverout.sh
wd=`pwd`
for file in tcp-server-out.log.?; do
timestamp=`echo $(date +%Y%m%d%H%M%S)_${file}`
echo ${timestamp}
echo ${timestamp} >> extract.log
cat ${file} | LANG=C sed -e '/#\^/d' -e '/^\[[0-9: A-Z]*\] Rx:,\^TM[A-Z]\{2\}R|\([0-9]\)\{15\}|/!d' -e 's/\[[0-9: A-Z]*\] Rx:,\^//g' -e '/-(\./d' -e '/^TM[A-Z]\{2\}R|\([0-9]\)\{15\}|\([0-9]\)*|0.00000|0.00000/d' -e '/^TM[A-Z]\{2\}R|\([0-9]\)\{15\}|\([0-9]\)*|\([0-9]\)\{2\}\.\([0-9]\)\{5\}|\([0-9]\)\{2\}\.\([0-9]\)\{5\}|0|0|/d' -e 's/#$//g' -e '/[\d128-\d255]/d' | sort -T ${wd} | uniq | awk '{ o=$0; print gsub(/\|/,""), ",", o }' | sed -e 's/ //g' | awk -F, '{print $2 >> "Geographical_"$1}'
cat ${file} | LANG=C sed -e '/#\^/d' -e '/^\[[0-9: A-Z]*\] Rx:,\^TMBMS|\([0-9]\)\{15\}|/!d' -e 's/\[[0-9: A-Z]*\] Rx:,\^//g' -e '/-(\./d' -e '/^TMBMS|\([0-9]\)\{15\}|\([0-9]\)*|0|0|/d' -e 's/#$//g' -e '/[\d128-\d255]/d' | sort -T ${wd} | uniq | awk '{ o=$0; print gsub(/\|/,""), ",", o }' | sed -e 's/ //g' | awk -F, '{print $2 >> "Electrical_0_"$1}'
cat ${file} | LANG=C sed -e '/#\^/d' -e '/^\[[0-9: A-Z]*\] Rx:,\^OEM03|\([0-9]\)\{15\}|/!d' -e 's/\[[0-9: A-Z]*\] Rx:,\^//g' -e '/-(\./d' -e '/^OEM03|\([0-9]\)\{15\}|\([0-9]\)*|0|0|/d' -e 's/#$//g' -e '/[\d128-\d255]/d' | sort -T ${wd} | uniq | awk '{ o=$0; print gsub(/\|/,""), ",", o }' | sed -e 's/ //g' | awk -F, '{print $2 >> "Electrical_1_"$1}'
cat ${file} | LANG=C sed -e '/#\^/!d' -e 's/\[[0-9: A-Z]*\] Rx:,//g' -e 's/#\^/#\n\^/g' -e '/-(\./d' -e 's/#//g' -e '/[\d128-\d255]/d' | sort -T ${wd} | uniq > rework_${file}
done
Raw
import.sql
-- osmand HTTPPASS
CREATE TABLE "public"."osmand" (
"id" BIGINT NOT NULL,
"timestamp" BIGINT NOT NULL,
"latitude" DOUBLE PRECISION NOT NULL,
"longitude" DOUBLE PRECISION NOT NULL,
"speed" NUMERIC NOT NULL,
"bearing" NUMERIC NOT NULL,
"altitude" NUMERIC NOT NULL,
"accuracy" NUMERIC NOT NULL,
"SoC" NUMERIC NOT NULL,
"SoCMOSFET" BOOLEAN NOT NULL
);
-- osmand HTTPPASS
ALTER TABLE osmand ADD CONSTRAINT "osmand_pkey" PRIMARY KEY ("id", "timestamp");
-- atlanta
CREATE TABLE "public"."atlanta" (
"IMEI" bigint NOT NULL,
"timestamp" "text" NOT NULL,
"validity" "text" NOT NULL,
"latitude" numeric NOT NULL,
"latitude_reference" "text" NOT NULL,
"longitude" numeric NOT NULL,
"longitude_reference" "text" NOT NULL,
"speed" numeric NOT NULL,
"direction" numeric NOT NULL,
"date" "text" NOT NULL,
"io" "text" NOT NULL,
"voltage_external" numeric NOT NULL,
"x1" numeric NOT NULL,
"x2" numeric NOT NULL,
"odometer" numeric NOT NULL,
"temperature" numeric NOT NULL,
"voltage_internal" numeric NOT NULL,
"RSSI" numeric NOT NULL,
"MCC" numeric NOT NULL,
"MNC" numeric NOT NULL,
"LAC" "text" NOT NULL,
"cell" "text" NOT NULL
);
-- trakmate
CREATE TABLE "public"."trakmate_g20" (
"packetType" "text" NOT NULL,
"IMEI" bigint NOT NULL,
"sequenceNumber" numeric NOT NULL,
"latitude" double precision NOT NULL,
"longitude" double precision NOT NULL,
"time" "text" NOT NULL,
"date" "text" NOT NULL,
"speed" numeric NOT NULL,
"direction" numeric NOT NULL,
"satelliteCount" numeric NOT NULL,
"BMSStatus" numeric NOT NULL,
"accelerometer" numeric NOT NULL,
"ignition" numeric NOT NULL,
"immobilization" numeric NOT NULL,
"x1" numeric NOT NULL,
"odometer" numeric NOT NULL,
"x2" numeric NOT NULL,
"validity" numeric NOT NULL,
"live" numeric NOT NULL,
"x3" numeric NOT NULL,
"firmwareVersion" numeric NOT NULL
);
-- trakmate
CREATE TABLE "public"."trakmate_g23" (
"packetType" "text" NOT NULL,
"IMEI" bigint NOT NULL,
"sequenceNumber" numeric NOT NULL,
"latitude" double precision NOT NULL,
"longitude" double precision NOT NULL,
"time" "text" NOT NULL,
"date" "text" NOT NULL,
"speed" numeric NOT NULL,
"direction" numeric NOT NULL,
"satelliteCount" numeric NOT NULL,
"ignition" numeric NOT NULL,
"x" numeric NOT NULL,
"y" numeric NOT NULL,
"z" numeric NOT NULL,
"digitalinput" numeric NOT NULL,
"voltage_external" numeric NOT NULL,
"voltage_internal" numeric NOT NULL,
"odometer" numeric NOT NULL,
"power" numeric NOT NULL,
"validity" numeric NOT NULL,
"live" numeric NOT NULL,
"digitaloutput" numeric NOT NULL,
"analoginput" numeric NOT NULL,
"t" numeric
);
-- osmand HTTPPASS
COPY osmand from '/goodenough_0/x.csv' WITH CSV DELIMITER ',' HEADER QUOTE '"' ESCAPE '''';
INSERT INTO "traccar"."tc_devices" ("name", "uniqueid") SELECT DISTINCT "IMEI"::TEXT AS "name", "IMEI" AS "uniqueid" FROM "public"."trakmate_g20" LEFT JOIN "traccar"."tc_devices" ON "public"."trakmate_g20"."IMEI"::TEXT = "traccar"."tc_devices"."uniqueid" WHERE "traccar"."tc_devices"."uniqueid" IS NULL ORDER BY 1;
-- osmand HTTPPASS
INSERT INTO "traccar"."tc_positions" ("protocol", "deviceid", "servertime", "devicetime", "fixtime", "valid", "latitude", "longitude", "altitude", "speed", "course", "accuracy", "attributes")
SELECT
'' AS "protocol",
"id"::INT AS deviceid,
'2022-12-31 23:59:59'::TIMESTAMP WITHOUT TIME ZONE AS servertime,
TO_TIMESTAMP("timestamp") AS "devicetime",
TO_TIMESTAMP("timestamp") AS "fixtime",
true AS "valid",
"latitude",
"longitude",
"altitude"::DOUBLE PRECISION AS "altitude",
"speed"::DOUBLE PRECISION AS "speed",
"bearing"::DOUBLE PRECISION AS "course",
"accuracy"::DOUBLE PRECISION AS "accuracy",
'{"SoC":"' || "SoC"::TEXT || '","SoCMOSFET":"' || "SoCMOSFET"::TEXT || '"}' AS "attributes"
FROM "osmand" ORDER BY 1, 2;
INSERT INTO "traccar"."tc_positions" ("protocol", "deviceid", "servertime", "devicetime", "fixtime", "valid", "latitude", "longitude", "altitude", "speed", "course", "accuracy", "attributes")
SELECT
'trakmate_0' AS "protocol",
"traccar"."tc_devices"."id" AS "deviceid",
'2022-12-31 23:59:59'::TIMESTAMP WITHOUT TIME ZONE AS "servertime",
TO_TIMESTAMP(LPAD("date"::TEXT, 6, '0') || ' ' || LPAD("time"::TEXT, 6, '0'), 'DDMMYY HH24MISS') AS "devicetime",
TO_TIMESTAMP(LPAD("date"::TEXT, 6, '0') || ' ' || LPAD("time"::TEXT, 6, '0'), 'DDMMYY HH24MISS') AS "fixtime",
CASE WHEN "validity" = 1 THEN true::BOOLEAN ELSE false::BOOLEAN END AS "valid",
"latitude",
"longitude",
-1 AS "altitude",
"speed"::DOUBLE PRECISION AS "speed",
"direction"::DOUBLE PRECISION AS "course",
-1 AS "accuracy",
'{"packetType":"' || "packetType"::TEXT || '","sequence":"' || "sequenceNumber"::TEXT || '","satellites":"' || "satelliteCount"::TEXT || '","odometer":"' || "odometer"::TEXT || '","firmwareVersion":"' || "firmwareVersion"::TEXT || '","SoBMS":"' || "BMSStatus"::TEXT || '"}' AS "attributes"
FROM "public"."trakmate_g20"
INNER JOIN "traccar"."tc_devices" ON "traccar"."tc_devices"."uniqueid" = "public"."trakmate_g20"."IMEI"::TEXT
ORDER BY 4 ASC, 2 ASC
UPDATE "public"."trakmate_g20" SET "packetType" = 'TA' WHERE "packetType" = '^TMATR';
UPDATE "public"."trakmate_g20" SET "packetType" = 'TA' WHERE "packetType" = 'TMATR';
UPDATE "public"."trakmate_g20" SET "packetType" = 'TD' WHERE "packetType" = '^TMDTR';
UPDATE "public"."trakmate_g20" SET "packetType" = 'TD' WHERE "packetType" = 'TMDTR';
UPDATE "public"."trakmate_g20" SET "packetType" = 'TP' WHERE "packetType" = '^TMPER';
UPDATE "public"."trakmate_g20" SET "packetType" = 'TP' WHERE "packetType" = 'TMPER';
INSERT INTO "traccar"."tc_positions" ("protocol", "deviceid", "servertime", "devicetime", "fixtime", "valid", "latitude", "longitude", "altitude", "speed", "course", "accuracy", "attributes")
SELECT
'trakmate_0' AS "protocol",
"traccar"."tc_devices"."id" AS "deviceid",
'2022-12-31 23:59:59'::TIMESTAMP WITHOUT TIME ZONE AS "servertime",
TO_TIMESTAMP(LPAD("date"::TEXT, 6, '0') || ' ' || LPAD("time"::TEXT, 6, '0'), 'DDMMYY HH24MISS') AS "devicetime",
TO_TIMESTAMP(LPAD("date"::TEXT, 6, '0') || ' ' || LPAD("time"::TEXT, 6, '0'), 'DDMMYY HH24MISS') AS "fixtime",
CASE WHEN "validity" = 1 THEN true::BOOLEAN ELSE false::BOOLEAN END AS "valid",
"latitude",
"longitude",
-1 AS "altitude",
"speed"::DOUBLE PRECISION AS "speed",
"direction"::DOUBLE PRECISION AS "course",
-1 AS "accuracy",
'{"packetType":"' || "packetType"::TEXT || '","sequence":"' || "sequenceNumber"::TEXT || '","satellites":"' || "satelliteCount"::TEXT || '","odometer":"' || "odometer"::TEXT || '","DI1":"' || "digitalinput"::TEXT || '","DO1":"' || "digitaloutput"::TEXT || '","AI1":"' || "analoginput"::TEXT || '","voltage_external":"' || "voltage_external"::TEXT || '","voltage_internal":"' || "voltage_internal"::TEXT || '","SoBMS":"' || "power"::TEXT || '"}' AS "attributes"
FROM "public"."trakmate_g23"
INNER JOIN "traccar"."tc_devices" ON "traccar"."tc_devices"."uniqueid" = "public"."trakmate_g23"."IMEI"::TEXT
ORDER BY 4 ASC, 2 ASC
Raw
maintenance.sql
UPDATE "traccar"."tc_devices" SET "attributes" = '{}' WHERE "attributes" = '';
UPDATE "traccar"."tc_devices" SET "attributes" = '{}' WHERE "attributes" IS NULL;
UPDATE "traccar"."tc_devices" SET "groupid" = -1 WHERE "groupid" IS NULL;
Raw
restore.sql
CREATE TABLE "public"."_devices" (
"id" integer NOT NULL,
"name" character varying(128) NOT NULL,
"uniqueid" character varying(128) NOT NULL
);
ALTER TABLE "public"."_devices" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
SEQUENCE NAME "public"."_devices_id_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
ALTER TABLE ONLY "public"."_devices"
ADD CONSTRAINT "_devices_pkey" PRIMARY KEY ("id");
ALTER TABLE ONLY "public"."_devices"
ADD CONSTRAINT "_devices_uniqueid_key" UNIQUE ("uniqueid");
CREATE INDEX "idx_devices_uniqueid" ON "public"."_devices" USING "btree" ("uniqueid");
SELECT pg_catalog.setval('"public"."_devices_id_seq"', 1, false);
COPY "_devices" from '/goodenough_0/_devices.csv' WITH CSV DELIMITER ',' HEADER QUOTE '"' ESCAPE '''';
INSERT INTO "traccar"."tc_devices" ("id", "name", "uniqueid") SELECT * FROM "public"."_devices";
CREATE TABLE "public"."_positions" (
"protocol" character varying(128),
"deviceid" integer NOT NULL,
"devicetime" timestamp without time zone NOT NULL,
"latitude" double precision NOT NULL,
"longitude" double precision NOT NULL,
"altitude" double precision NOT NULL,
"speed" double precision NOT NULL,
"course" double precision NOT NULL,
"attributes" character varying(4000),
"accuracy" double precision DEFAULT 0 NOT NULL,
"network" character varying(4000)
);
ALTER TABLE ONLY "public"."_positions"
ADD CONSTRAINT "_positions_pkey" PRIMARY KEY ("deviceid", "devicetime");
COPY "_positions" from '/goodenough_0/_positions.csv' WITH CSV DELIMITER ',' HEADER QUOTE '"' ESCAPE '''';
INSERT INTO "traccar"."tc_positions" ("protocol", "deviceid", "servertime", "devicetime", "fixtime", "valid", "latitude", "longitude", "altitude", "speed", "course", "attributes", "accuracy", "network") SELECT "protocol", "deviceid", '2022-12-31 23:59:59' AS "servertime", "devicetime", "devicetime" AS "fixtime", TRUE::BOOLEAN AS "valid", "latitude", "longitude", "altitude", "speed", "course", "attributes", "accuracy", "network" FROM "public"."_positions";
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment