1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
|
#!/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
|
