]>
Commit | Line | Data |
---|---|---|
60d328e2 IB |
1 | #!/usr/bin/env bash |
2 | ||
3 | # FLG - Fast Lacells.db Generator | |
4 | # | |
5 | # Simple script to quickly download and generate lacells.db for LocalGSMBackend by n76 | |
6 | # https://github.com/n76/Local-GSM-Backend | |
7 | # Uses Mozilla Location Service, OpenCellID and radiocells.org databases as source | |
8 | # Based on lacells-creator by wvengen and n76 | |
9 | # | |
10 | # Licensed under GPLv3 or later | |
11 | # (C)2016 Sebastian Obrusiewicz | |
12 | # sobrus@o2.pl | |
13 | ||
14 | if [ -z "$IN_BUILDBOT" ]; then | |
15 | #DEFAULT_CONFIG_BEGIN | |
16 | ENABLE_OCI=1 #enable OpenCellID data source | |
17 | ENABLE_MOZ=1 #enable Mozilla Location Services (MLS) data source | |
18 | ENABLE_RCO=0 #enable radiocells.org data source (it can be quite slow) | |
19 | ||
20 | # See https://en.wikipedia.org/wiki/Mobile_country_code | |
21 | # 208 France | |
22 | MCC="" #contry codes separated with "|", for example "260|262". Leave dot+asterisk ".*" for all countries | |
23 | 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 | |
24 | RADIO="" #you can remove LTE if your phone does not support it | |
25 | TOKEN="" #your OCID token, required to download from OpenCellID. Get your free token at https://opencellid.org/ | |
26 | fi | |
27 | 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 | |
28 | KEEP_FILES=1 #whether to keep (1) or delete (0) the CSV files after processing | |
29 | ||
30 | #do not edit following variables, unless you know what you're doing | |
31 | EMPTY=',,,,,,,,,,,,,' #dummy empty file for disabled sources | |
32 | OCI_FILE=$TMPDIR"/ocid.csv" #opencellid temporary file | |
33 | MOZ_FILE=$TMPDIR"/mozilla.csv" #mozilla temporary file | |
34 | RCO_FILE=$TMPDIR"/rco.csv" #radiocells.org temporary file | |
35 | #DEFAULT_CONFIG_END | |
36 | ||
37 | #USER_CONFIG_BEGIN | |
38 | BINDIR=$( dirname "$(readlink -f "$0")" ) #" | |
39 | if [[ -f "${BINDIR}/config" ]]; then | |
40 | . "${BINDIR}/config" | |
41 | fi | |
42 | #USER_CONFIG_END | |
43 | ||
44 | function manage_backup | |
45 | { | |
46 | file=$1 | |
47 | if [ -s $file ] | |
48 | then | |
49 | if [ $KEEP_FILES == "1" ] | |
50 | then | |
51 | gzip -kf $file | |
52 | fi | |
53 | elif [ -s $file".gz" ] && [ "${file##*.}" == "csv" ] | |
54 | then | |
55 | echo "Using" $file".gz backup file" | |
56 | gzip -dkf $file".gz" | |
57 | fi | |
58 | } | |
59 | ||
60 | ||
61 | function download_ocid | |
62 | { | |
63 | URL="https://opencellid.org/ocid/downloads?token=${TOKEN}&type=full&file=cell_towers.csv.gz" | |
64 | if [ $ENABLE_OCI == "1" ] | |
65 | then | |
66 | wget -qO- "$URL" | gunzip | egrep "^($RADIO),($MCC)," > $OCI_FILE | |
67 | manage_backup $OCI_FILE | |
68 | else | |
69 | echo $EMPTY > $OCI_FILE | |
70 | fi | |
71 | } | |
72 | ||
73 | function download_mozilla | |
74 | { | |
75 | if [ $ENABLE_MOZ == "1" ] | |
76 | then | |
77 | NW=`date -u "+%Y-%m-%d"` | |
78 | wget -qO- "https://d17pt8qph6ncyq.cloudfront.net/export/MLS-full-cell-export-${NW}T000000.csv.gz" | gunzip | egrep "^($RADIO),($MCC)," > $MOZ_FILE | |
79 | manage_backup $MOZ_FILE | |
80 | else | |
81 | echo $EMPTY > $MOZ_FILE | |
82 | fi | |
83 | } | |
84 | ||
85 | function download_radiocells | |
86 | { | |
87 | if [ $ENABLE_RCO == "1" ] | |
88 | then | |
89 | RCO_SELECT="SELECT technology, mcc, mnc, area, cid, NULL, longitude, latitude, 1000 accuracy, measurements, NULL, NULL, NULL, NULL FROM cell_zone;" | |
90 | wget -qO- "https://cdn.radiocells.org/"$RCO_SRC_FILE > $TMPDIR"/"$RCO_SRC_FILE | |
91 | sqlite3 -header -csv $TMPDIR"/"$RCO_SRC_FILE "$RCO_SELECT" | egrep "^($RADIO),($MCC)," > $RCO_FILE | |
92 | rm $TMPDIR"/"$RCO_SRC_FILE | |
93 | manage_backup $RCO_FILE | |
94 | else | |
95 | echo $EMPTY > $RCO_FILE | |
96 | fi | |
97 | } | |
98 | ||
99 | echo "Downloading data" | |
100 | ||
101 | download_ocid & | |
102 | OP=$! | |
103 | download_mozilla & | |
104 | MO=$! | |
105 | download_radiocells & | |
106 | RO=$! | |
107 | ||
108 | wait $OP | |
109 | wait $MO | |
110 | wait $RO | |
111 | ||
112 | if [ -s $MOZ_FILE ] && [ -s $OCI_FILE ] && [ -s $RCO_FILE ] | |
113 | then | |
114 | ||
115 | manage_backup lacells.db | |
116 | rm lacells.db | |
117 | ||
118 | echo "Generating database" | |
119 | ||
120 | sqlite3 lacells.db <<-SQL | |
121 | PRAGMA synchronous = OFF; | |
122 | PRAGMA journal_mode = OFF; | |
123 | ||
124 | 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); | |
125 | CREATE TABLE cells (mcc INTEGER,mnc INTEGER,lac INTEGER,cid INTEGER,longitude REAL,latitude REAL,altitude REAL,accuracy REAL,samples INTEGER); | |
126 | ||
127 | .header on | |
128 | .mode csv | |
129 | ||
130 | .import "$OCI_FILE" cells_import | |
131 | .import "$MOZ_FILE" cells_import | |
132 | .import "$RCO_FILE" cells_import | |
133 | ||
134 | UPDATE cells_import SET samples=1 WHERE samples IS NULL OR samples < 1; | |
135 | ||
136 | INSERT INTO cells | |
137 | SELECT mcc, mnc, lac, cid, | |
138 | sum(longitude * samples) / sum(samples) as longitude, | |
139 | sum(latitude * samples) / sum(samples) as latitude, | |
140 | -1 as altitude, | |
141 | sum(accuracy * samples) / sum(samples) as accuracy, | |
142 | sum(samples) as samples | |
143 | FROM cells_import | |
144 | GROUP BY mcc, mnc, lac, cid; | |
145 | ||
146 | DROP TABLE cells_import; | |
147 | ||
148 | UPDATE cells SET accuracy=500 WHERE accuracy < 500; | |
149 | UPDATE cells SET accuracy=100000 WHERE accuracy > 100000; | |
150 | ||
151 | CREATE INDEX _idx1 ON cells (mcc, mnc, lac, cid); | |
152 | CREATE INDEX _idx2 ON cells (lac, cid); | |
153 | ||
154 | VACUUM; | |
155 | SQL | |
156 | ||
157 | else | |
158 | echo "Download error" | |
159 | fi | |
160 | ||
161 | rm $OCI_FILE | |
162 | rm $MOZ_FILE | |
163 | rm $RCO_FILE |