flakes/private/buildbot/projects/immaeEu/scripts/lacells_download

   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     # Found on https://location.services.mozilla.com/downloads
  79     wget -qO- "https://d2koia3g127518.cloudfront.net/export/MLS-full-cell-export-${NW}T000000.csv.gz" | gunzip | egrep "^($RADIO),($MCC)," > $MOZ_FILE
  80     manage_backup $MOZ_FILE
  81   else
  82     echo $EMPTY > $MOZ_FILE
  83   fi
  84 }
  85
  86 function download_radiocells
  87 {
  88   if [ $ENABLE_RCO == "1" ]
  89   then
  90     RCO_SELECT="SELECT technology, mcc, mnc, area, cid, NULL, longitude, latitude, 1000 accuracy, measurements, NULL, NULL, NULL, NULL FROM cell_zone;"
  91     wget -qO- "https://cdn.radiocells.org/"$RCO_SRC_FILE > $TMPDIR"/"$RCO_SRC_FILE
  92     sqlite3 -header -csv $TMPDIR"/"$RCO_SRC_FILE "$RCO_SELECT" | egrep "^($RADIO),($MCC)," > $RCO_FILE
  93     rm $TMPDIR"/"$RCO_SRC_FILE
  94     manage_backup $RCO_FILE
  95   else
  96     echo $EMPTY > $RCO_FILE
  97   fi
  98 }
  99
 100 echo "Downloading data"
 101
 102 download_ocid &
 103 OP=$!
 104 download_mozilla &
 105 MO=$!
 106 download_radiocells &
 107 RO=$!
 108
 109 wait $OP
 110 wait $MO
 111 wait $RO
 112
 113 if [ -s $MOZ_FILE ] && [ -s $OCI_FILE ] && [ -s $RCO_FILE ]
 114 then
 115
 116 manage_backup lacells.db
 117 rm lacells.db
 118
 119 echo "Generating database"
 120
 121 sqlite3 lacells.db <<-SQL
 122 PRAGMA synchronous = OFF;
 123 PRAGMA journal_mode = OFF;
 124
 125 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);
 126 CREATE TABLE cells (mcc INTEGER,mnc INTEGER,lac INTEGER,cid INTEGER,longitude REAL,latitude REAL,altitude REAL,accuracy REAL,samples INTEGER);
 127
 128 .header on
 129 .mode csv
 130
 131 .import "$OCI_FILE" cells_import
 132 .import "$MOZ_FILE" cells_import
 133 .import "$RCO_FILE" cells_import
 134
 135 UPDATE cells_import SET samples=1 WHERE samples IS NULL OR samples < 1;
 136
 137 INSERT INTO cells
 138 SELECT mcc, mnc, lac, cid,
 139   sum(longitude * samples) / sum(samples) as longitude,
 140   sum(latitude  * samples) / sum(samples) as latitude,
 141   -1 as altitude,
 142   sum(accuracy  * samples) / sum(samples) as accuracy,
 143   sum(samples) as samples
 144 FROM cells_import
 145 GROUP BY mcc, mnc, lac, cid;
 146
 147 DROP TABLE cells_import;
 148
 149 UPDATE cells SET accuracy=500 WHERE accuracy < 500;
 150 UPDATE cells SET accuracy=100000 WHERE accuracy > 100000;
 151
 152 CREATE INDEX _idx1 ON cells (mcc, mnc, lac, cid);
 153 CREATE INDEX _idx2 ON cells (lac, cid);
 154
 155 VACUUM;
 156 SQL
 157
 158 else
 159   echo "Download error"
 160 fi
 161
 162 rm $OCI_FILE
 163 rm $MOZ_FILE
 164 rm $RCO_FILE