-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathgen_lacells_mergedModWithoutDownload
122 lines (116 loc) · 5.12 KB
/
gen_lacells_mergedModWithoutDownload
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
#! /bin/bash
#
# Quick and dirty script to build and install a new
# cell tower database on phone for microg/nogapps
# network location.
#
# For OpenCellID we need an API key
#API_KEY='put your OpenCellID API key string here'
# We don't need this, since download is already done
#
# Get latest cell tower data from Mozilla Location Services
#
#echo 'Getting cell tower data from Mozilla Location Services'
#if [ -e towers_mozilla.csv.gz ] ; then
# rm towers_mozilla.csv.gz
#fi
#if [ -e towers_mozilla.csv ] ; then
# mv -f towers_mozilla.csv towers_opencellid.csv.bak
#fi
NOW=`date -u "+%Y-%m-%d"`
echo "${FNAME}"
#wget --no-check-certificate --output-document=towers_mozilla.csv.gz "https://d17pt8qph6ncyq.cloudfront.net/export/MLS-full-cell-export-${NOW}T000000.csv.gz"
#gunzip towers_mozilla.csv.gz
#
# Get latest cell tower data from opencellid
#
#echo 'Getting cell tower data from Open Cell ID'
#if [ -e towers_opencellid.csv.gz ] ; then
# rm towers_opencellid.csv.gz
#fi
#if [ -e towers_opencellid.csv ] ; then
# mv -f towers_opencellid.csv towers_opencellid.csv.bak
#fi
#wget --output-document=towers_opencellid.csv.gz "http://opencellid.org/downloads/?apiKey=${API_KEY}&filename=cell_towers.csv.gz"
#gunzip towers_opencellid.csv.gz
#
# Backup previous database and build new one.
#
# Since Mozilla and OpenCellId have agreed to a common CSV format
# we can use the same import logic for both. Buried in the SQL is
# basically the following logic:
# 1. Import towers known to OpenCellId with weighted values
# 2. Import towers known to Mozilla with weighted values (will result in tower duplication)
# 3. Merge towers with weighted average values
# 4. Using MCC value(s), delete towers not in North America
#
# DATA CLEANUP
#
# The database for the whole world is fairly large, so the line:
#
# DELETE FROM cells WHERE mcc != 310 AND mcc != 311;
#
# Gets rid of stuff outside the United States. This can be removed if
# towers for the whole world are deput your OpenCellID API key string heresired. Or it can be modified to
# select other countries as desired.
#
# The opencellid database contains lots of towers with no coverage
# range information (aproxy for accuracy). And due to sampling issues,
# it is possible that low coverage range estimates are incorrect.
#
# In addition, there are towers which have a coverage range reported
# to be 1000s of KM which is probably incorrect and do not help on
# locating the phone. So the expression:
#
# min(max(range, 500),100000)
#
# Is used to force each tower coverage range into the bounds of 0.5KM
# to 100KM coverage range. These can be adjusted as needed but the
# lower bound should be above zero to avoid divide by zero errors.
#
#
# ===============================================
# As of 6Sep2014, CSV file has following header:
# radio,mcc,net,area,cell,unit,lon,lat,range,samples,changeable,created,updated,averageSignal
# GSM,262,2,148,28260,,7.27268,50.68719,0,0,0,1207455686,1357937166,
#
# ===============================================
#
echo 'Building combined CSV file'
if [ -e lacells.db ] ; then
mv -f lacells.db lacells.db.bak
fi
#
# Create a merged CSV file with the columns we care about sorted by
# MCC, MNM, LAC, and CID. Could do this in PHP or other language but
# CSV import and output in sqlite3 make this pretty easy.
#
sqlite3 lacells.db <<!
PRAGMA synchronous = OFF;
PRAGMA journal_mode = OFF;
CREATE TABLE cells(mcc INTEGER, mnc INTEGER, lac INTEGER, cid INTEGER, longitude REAL, latitude REAL, altitude REAL, accuracy REAL, samples INTEGER);
CREATE TEMP TABLE cells_weighted AS SELECT * FROM cells;
CREATE TEMP TABLE cells_new(radio TEXT, mcc INTEGER, mnc INTEGER, lac INTEGER, cellId INTEGER, unit TEXT, long REAL, lat REAL, range INTEGER, samples INTEGER, changeable BOOL, created INTEGER, updated INTEGER, averageSignalStrength INTEGER);
.mode csv
.import towers_opencellid.csv cells_new
INSERT INTO cells_weighted SELECT mcc, mnc, lac, cellId, long*max(1,samples), lat*max(1,samples), -1*max(1,samples), min(max(range, 500),100000)*max(1,samples), max(1,samples) FROM cells_new;
DROP TABLE cells_new;
CREATE TEMP TABLE cells_new(radio TEXT, mcc INTEGER, mnc INTEGER, lac INTEGER, cellId INTEGER, unit TEXT, long REAL, lat REAL, range INTEGER, samples INTEGER, changeable BOOL, created INTEGER, updated INTEGER, averageSignalStrength INTEGER);
.mode csv
.import towers_mozilla.csv cells_new
INSERT INTO cells_weighted SELECT mcc, mnc, lac, cellId, long*max(1,samples), lat*max(1,samples), -1*max(1,samples), min(max(range, 500),100000)*max(1,samples), max(1,samples) FROM cells_new;
DROP TABLE cells_new;
INSERT INTO cells SELECT mcc, mnc, lac, cid, SUM(longitude)/SUM(samples), SUM(latitude)/SUM(samples), SUM(altitude)/SUM(samples), SUM(accuracy)/SUM(samples), SUM(samples) FROM cells_weighted GROUP BY mcc, mnc, lac, cid;
DROP TABLE cells_weighted;
CREATE INDEX _idx1 ON cells (mcc, mnc, lac, cid);
CREATE INDEX _idx2 ON cells (lac, cid);
VACUUM;
.quit
!
#
# Push the new database to the phone. Reboot may not
# be required but probably won't hurt.
#
echo 'Pushing database to phone'
#adb push lacells.db /storage/emulated/0/.nogapps/lacells.db
#adb reboot