#!/usr/bin/env bash
# FLG - Fast Lacells.db Generator
#
# Simple script to quickly download and generate lacells.db for LocalGSMBackend by n76
# https://github.com/n76/Local-GSM-Backend
# Uses Mozilla Location Service, OpenCellID and radiocells.org databases as source
# Based on lacells-creator by wvengen and n76
#
# Licensed under GPLv3 or later
# (C)2016 Sebastian Obrusiewicz
# sobrus@o2.pl
if [ -z "$IN_BUILDBOT" ]; then
#DEFAULT_CONFIG_BEGIN
ENABLE_OCI=1 #enable OpenCellID data source
ENABLE_MOZ=1 #enable Mozilla Location Services (MLS) data source
ENABLE_RCO=0 #enable radiocells.org data source (it can be quite slow)
# See https://en.wikipedia.org/wiki/Mobile_country_code
# 208 France
MCC="" #contry codes separated with "|", for example "260|262". Leave dot+asterisk ".*" for all countries
RCO_SRC_FILE="fr.sqlite" #radiocells.org source database file, set "openbmap.sqlite" for entire world database, see https://radiocells.org/downloads for smaller country specific files, for example "pl.sqlite" for Poland
RADIO="" #you can remove LTE if your phone does not support it
TOKEN="" #your OCID token, required to download from OpenCellID. Get your free token at https://opencellid.org/
fi
TMPDIR='.' #for temporary files only, use disk if you don't have enough RAM, AND remember to have enough disk space in /var/tmp for sqlite temporary files
KEEP_FILES=1 #whether to keep (1) or delete (0) the CSV files after processing
#do not edit following variables, unless you know what you're doing
EMPTY=',,,,,,,,,,,,,' #dummy empty file for disabled sources
OCI_FILE=$TMPDIR"/ocid.csv" #opencellid temporary file
MOZ_FILE=$TMPDIR"/mozilla.csv" #mozilla temporary file
RCO_FILE=$TMPDIR"/rco.csv" #radiocells.org temporary file
#DEFAULT_CONFIG_END
#USER_CONFIG_BEGIN
BINDIR=$( dirname "$(readlink -f "$0")" ) #"
if [[ -f "${BINDIR}/config" ]]; then
. "${BINDIR}/config"
fi
#USER_CONFIG_END
function manage_backup
{
file=$1
if [ -s $file ]
then
if [ $KEEP_FILES == "1" ]
then
gzip -kf $file
fi
elif [ -s $file".gz" ] && [ "${file##*.}" == "csv" ]
then
echo "Using" $file".gz backup file"
gzip -dkf $file".gz"
fi
}
function download_ocid
{
URL="https://opencellid.org/ocid/downloads?token=${TOKEN}&type=full&file=cell_towers.csv.gz"
if [ $ENABLE_OCI == "1" ]
then
wget -qO- "$URL" | gunzip | egrep "^($RADIO),($MCC)," > $OCI_FILE
manage_backup $OCI_FILE
else
echo $EMPTY > $OCI_FILE
fi
}
function download_mozilla
{
if [ $ENABLE_MOZ == "1" ]
then
NW=`date -u "+%Y-%m-%d"`
wget -qO- "https://d17pt8qph6ncyq.cloudfront.net/export/MLS-full-cell-export-${NW}T000000.csv.gz" | gunzip | egrep "^($RADIO),($MCC)," > $MOZ_FILE
manage_backup $MOZ_FILE
else
echo $EMPTY > $MOZ_FILE
fi
}
function download_radiocells
{
if [ $ENABLE_RCO == "1" ]
then
RCO_SELECT="SELECT technology, mcc, mnc, area, cid, NULL, longitude, latitude, 1000 accuracy, measurements, NULL, NULL, NULL, NULL FROM cell_zone;"
wget -qO- "https://cdn.radiocells.org/"$RCO_SRC_FILE > $TMPDIR"/"$RCO_SRC_FILE
sqlite3 -header -csv $TMPDIR"/"$RCO_SRC_FILE "$RCO_SELECT" | egrep "^($RADIO),($MCC)," > $RCO_FILE
rm $TMPDIR"/"$RCO_SRC_FILE
manage_backup $RCO_FILE
else
echo $EMPTY > $RCO_FILE
fi
}
echo "Downloading data"
download_ocid &
OP=$!
download_mozilla &
MO=$!
download_radiocells &
RO=$!
wait $OP
wait $MO
wait $RO
if [ -s $MOZ_FILE ] && [ -s $OCI_FILE ] && [ -s $RCO_FILE ]
then
manage_backup lacells.db
rm lacells.db
echo "Generating database"
sqlite3 lacells.db <<-SQL
PRAGMA synchronous = OFF;
PRAGMA journal_mode = OFF;
CREATE TEMP TABLE cells_import (radio TEXT,mcc INTEGER,mnc INTEGER,lac INTEGER,cid INTEGER,unit STRING,longitude NUMERIC,latitude NUMERIC,accuracy INTEGER,samples INTEGER,changeable BOOLEAN,created INTEGER,updated INTEGER, avgSignal INTEGER);
CREATE TABLE cells (mcc INTEGER,mnc INTEGER,lac INTEGER,cid INTEGER,longitude REAL,latitude REAL,altitude REAL,accuracy REAL,samples INTEGER);
.header on
.mode csv
.import "$OCI_FILE" cells_import
.import "$MOZ_FILE" cells_import
.import "$RCO_FILE" cells_import
UPDATE cells_import SET samples=1 WHERE samples IS NULL OR samples < 1;
INSERT INTO cells
SELECT mcc, mnc, lac, cid,
sum(longitude * samples) / sum(samples) as longitude,
sum(latitude * samples) / sum(samples) as latitude,
-1 as altitude,
sum(accuracy * samples) / sum(samples) as accuracy,
sum(samples) as samples
FROM cells_import
GROUP BY mcc, mnc, lac, cid;
DROP TABLE cells_import;
UPDATE cells SET accuracy=500 WHERE accuracy < 500;
UPDATE cells SET accuracy=100000 WHERE accuracy > 100000;
CREATE INDEX _idx1 ON cells (mcc, mnc, lac, cid);
CREATE INDEX _idx2 ON cells (lac, cid);
VACUUM;
SQL
else
echo "Download error"
fi
rm $OCI_FILE
rm $MOZ_FILE
rm $RCO_FILE