= Opencell ID

Ref [https://github.com/janjongboom/opencellid/blob/master/README.md here]

Download and create an sqlite database from the csv data that you can get from [http://opencellid.org/downloads/ opencellid]. The thing is that it now requires a login, so the best way is to download it from browser and store it as cells.txt.gz in this folder. If you want to run this on a server then use Firefox Network panel and 'copy as cURL'. Then run that command on the server and store as cells.txt.gz.

Then run:


{{{
#!sh
~]$ cat cells.txt.gz | gunzip - > cells.txt
~]$ cat schema.sql | sqlite3 cells.sqlite
~]$ cat import.sql | sqlite3 cells.sqlite
}}}

After update the sqlite3 DB file (cells.sqlite), we can query the lat,lon by passing MCC/MNC/LAC/CellID as following
{{{
#!sh
~]$ cat exSql.sh 
#SELECT * FROM cell WHERE mcc='520' AND mnc='3' AND lac='842' AND cellid='164612740';
sqlite3 -cmd '.header on' -cmd '.mode line' cells.sqlite 'SELECT * FROM cell WHERE mcc='520' AND mnc='3' AND lac='842' AND cellid='164612740''
~]$ ./exSql.sh 
         radio = LTE
           mcc = 520
           mnc = 3
           lac = 842
        cellid = 164612740
          unit = -1
           lon = 100.6512
           lat = 13.7159
         range = 1000
     nbSamples = 6
    changeable = 1
    created_at = 1739503195
    updated_at = 1743847142
average_signal = 0
~]$ 
}}} 

If use sqlite3 command, we can query to change epoch time to  date time.
{{{
sqlite> SELECT 
    lat, 
    lon, 
    datetime(created_at, 'unixepoch', 'localtime') AS created_local, 
    datetime(updated_at, 'unixepoch', 'localtime') AS updated_local
FROM cell WHERE mcc='520' AND mnc='3' AND lac='842' AND cellid='164612740';
13.7159|100.6512|2025-02-14 10:19:55|2025-04-05 16:59:02
sqlite>
}}} 

We can use [attachment:exSql2.sh exSql2.sh ] to get as command line.