第二十四個夏天後: geolocation

顯示具有 geolocation 標籤的文章。顯示所有文章

2024年12月5日星期四

PHP 開發筆記 - 產生 CountryCode, CountryName, CityName, GeoLocation 列表

原本一直盧 AI 產出，發現產出的品質很難控，最後就轉個彎去把 GeoIP DB 內的資料輸出即可，而策略也很簡單，單純用 IP 暴力輪詢。理論上可以優雅一點去了解 DB Record format，總之，暴力解也很快，就順手先記錄一下，此例僅列出部分資訊（非輪詢所有 IPv4）：

```
<?php
if (!extension_loaded('geoip')) {
die("GeoIP extension is not installed\n");
}

class GeoIPParser {
private $dbPath;

public function __construct($dbPath = '/tmp/db.dat') {
$this->dbPath = $dbPath;
geoip_setup_custom_directory('/tmp');
}

public function parse() {
if (!file_exists($this->dbPath)) {
throw new Exception("GeoIP City database not found at {$this->dbPath}");
}

$locations = [];
$processed = [];

try {
// 遍歷 IP 範圍，每個 /16 subnet 取樣幾個 IP
for ($first = 1; $first <= 255; $first++) {
fprintf(STDERR, "\rProcessing IP block: %d/255", $first);

for ($second = 0; $second <= 255; $second += 5) {
$ip = "$first.$second.1.1";
$record = geoip_record_by_name($ip);

if ($record &&
!empty($record['country_code']) &&
!empty($record['city'])) {

$key = $record['country_code'] . '|' . $record['city'];

if (!isset($processed[$key])) {
$location = [
'country_code' => $record['country_code'],
'country_name' => $record['country_name'],
'city_name' => $record['city'],
'geo_location' => [
'latitude' => round($record['latitude'], 4),
'longitude' => round($record['longitude'], 4)
]
];

$locations[] = $location;
$processed[$key] = true;
}
}
}
}

fprintf(STDERR, "\nProcessing completed. Total locations found: " . count($locations) . "\n");

// 按國家代碼和城市名稱排序
usort($locations, function($a, $b) {
$countryComp = strcmp($a['country_code'], $b['country_code']);
return $countryComp === 0 ?
strcmp($a['city_name'], $b['city_name']) :
$countryComp;
});

return $locations;
} catch (Exception $e) {
fprintf(STDERR, "Error during processing: " . $e->getMessage() . "\n");
throw $e;
}
}
}

try {
$parser = new GeoIPParser();
$locations = $parser->parse();

// 輸出為格式化的 JSON
echo json_encode($locations,
JSON_PRETTY_PRINT |
JSON_UNESCAPED_UNICODE |
JSON_UNESCAPED_SLASHES
);

} catch (Exception $e) {
fwrite(STDERR, "Error: " . $e->getMessage() . "\n");
exit(1);
}
```

產出：

```
% php83 -ini | grep geoip
/opt/local/var/db/php83/geoip.ini,
geoip
geoip support => enabled
geoip extension version => 1.1.1
geoip library version => 1005000
geoip.custom_directory => no value => no value
% php83 geo-lookup.php
Processing IP block: 255/255
Processing completed. Total locations found: 2657
...
```

片段資料：

% php83 list-geo-city.php | jq '[.[]|select(.country_code=="TW")]'
Processing IP block: 255/255
Processing completed. Total locations found: 8020
[
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Anping District",
"geo_location": {
"latitude": 22.9965,
"longitude": 120.1617
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Bade District",
"geo_location": {
"latitude": 24.9259,
"longitude": 121.2763
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Banqiao",
"geo_location": {
"latitude": 25.0104,
"longitude": 121.4683
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Beitou",
"geo_location": {
"latitude": 25.1403,
"longitude": 121.4948
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Chang-hua",
"geo_location": {
"latitude": 24.0759,
"longitude": 120.5657
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Chiayi City",
"geo_location": {
"latitude": 23.4815,
"longitude": 120.4498
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Chiyayi County",
"geo_location": {
"latitude": 23.4461,
"longitude": 120.5728
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Daan",
"geo_location": {
"latitude": 25.0316,
"longitude": 121.5345
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Dacun",
"geo_location": {
"latitude": 23.9978,
"longitude": 120.547
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Dawan",
"geo_location": {
"latitude": 23.2073,
"longitude": 120.1906
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Daya",
"geo_location": {
"latitude": 24.2226,
"longitude": 120.6493
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Douliu",
"geo_location": {
"latitude": 23.7125,
"longitude": 120.545
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "East District",
"geo_location": {
"latitude": 22.9721,
"longitude": 120.2224
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Guishan",
"geo_location": {
"latitude": 25.0273,
"longitude": 121.359
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Hsinchu",
"geo_location": {
"latitude": 24.8065,
"longitude": 120.9706
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Hsinchu County",
"geo_location": {
"latitude": 24.673,
"longitude": 121.1614
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Hualien City",
"geo_location": {
"latitude": 23.9807,
"longitude": 121.6115
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Jian",
"geo_location": {
"latitude": 23.9516,
"longitude": 121.5639
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Jinshan",
"geo_location": {
"latitude": 25.0613,
"longitude": 121.5705
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Kaohsiung City",
"geo_location": {
"latitude": 22.6148,
"longitude": 120.3139
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Keelung",
"geo_location": {
"latitude": 25.1322,
"longitude": 121.742
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Linkou District",
"geo_location": {
"latitude": 25.0738,
"longitude": 121.3935
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Miaoli",
"geo_location": {
"latitude": 24.5641,
"longitude": 120.8275
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Nantou City",
"geo_location": {
"latitude": 23.9082,
"longitude": 120.6558
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Neihu District",
"geo_location": {
"latitude": 25.0811,
"longitude": 121.5838
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "New Taipei City",
"geo_location": {
"latitude": 24.9466,
"longitude": 121.586
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Penghu County",
"geo_location": {
"latitude": 23.5748,
"longitude": 119.6098
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Pingtung City",
"geo_location": {
"latitude": 22.6745,
"longitude": 120.491
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Puli",
"geo_location": {
"latitude": 23.9678,
"longitude": 120.9644
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Sanchong District",
"geo_location": {
"latitude": 25.0691,
"longitude": 121.4878
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Sanxia District",
"geo_location": {
"latitude": 24.9336,
"longitude": 121.372
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Shiding District",
"geo_location": {
"latitude": 24.9956,
"longitude": 121.6546
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Shoufeng",
"geo_location": {
"latitude": 23.8341,
"longitude": 121.521
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Taibao",
"geo_location": {
"latitude": 23.4603,
"longitude": 120.3284
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Taichung",
"geo_location": {
"latitude": 24.144,
"longitude": 120.6844
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Taichung City",
"geo_location": {
"latitude": 24.1547,
"longitude": 120.6716
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Tainan City",
"geo_location": {
"latitude": 22.9917,
"longitude": 120.2147
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Taipei",
"geo_location": {
"latitude": 25.0504,
"longitude": 121.5324
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Taitung",
"geo_location": {
"latitude": 22.7563,
"longitude": 121.1418
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Taoyuan",
"geo_location": {
"latitude": 24.9977,
"longitude": 121.2965
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Taoyuan District",
"geo_location": {
"latitude": 24.9889,
"longitude": 121.3175
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Xizhi District",
"geo_location": {
"latitude": 25.0696,
"longitude": 121.6577
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Yilan",
"geo_location": {
"latitude": 24.7574,
"longitude": 121.7421
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Yongkang District",
"geo_location": {
"latitude": 23.0204,
"longitude": 120.2591
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Yunlin",
"geo_location": {
"latitude": 23.7113,
"longitude": 120.3897
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Zhongli District",
"geo_location": {
"latitude": 24.9614,
"longitude": 121.2437
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Zhubei",
"geo_location": {
"latitude": 24.8351,
"longitude": 121.0056
}
},
{
"country_code": "TW",
"country_name": "Taiwan",
"city_name": "Zuoying",
"geo_location": {
"latitude": 22.6868,
"longitude": 120.2971
}
}
]

2023年10月3日星期二

PHP 開發筆記 - 用 Matomo Analytics HTTP API 取代 GA4 Measurement Protocol 服務 @ Ubuntu 22.04

由於 Google Analytics 4 在後端端數據搜集情境已殘，可能起源於 GA4 著重隱私等設計（？），現況數據收集已強調必須先從 Web or App 開始，透過 JS SDK 或 APP SDK 做事，而後端回報為輔助而已，在 Web & App 端可以做隱私宣告。如此，使得純後端回報幾乎無法妥善使用 GA4 的功能，包括無法區分 new user / old user 屬性等等，也不像 GA3 可以把 remote client 的 IP 回報出去而顯示使用者的世界全貌。

架設 Matomo 方式還滿簡單的，就 Web Server + PHP + MySQL ，並且官方也有 docker 安置方式：

官方

IP轉Geo資訊

matomo.org/faq/how-to/faq_163/

啟動定期分析資料

matomo.org/faq/on-premise/how-to-set-up-auto-archiving-of-your-reports/

由於看到別人的文章提到要記得設置定期分析任務，不然久久登入網頁會很卡的，推論也是從網頁端發送查詢資料，過程中也一同處理資料分析。這個用瀏覽器時，從開發者工具中也可以看到定期在發送 ajax 的 requests。

目前就先弄個 Matomo project 出來，根據上頭的指示很簡單就埋好 js script code 去追蹤網站流量，可以看得到 Matomo 比 GA 提供更多細膩的資訊，這就像隱私的那些規劃，在 GA 上頭只能很粗略地觀看到“趨勢”，而在 Matomo 上頭，則是可以細到把指定的 client 展開出他的 Profile：

我認為跟 GA 相比有滿明顯的設計差距。

接著，關於透過 REST API 回報數據與查詢回報的方式，可以參考：

github.com/changyy/study-matomo

回報 PageView/Event：

% cat test.php
require 'report.php';

print_r(matomoPageview(
[
[
'url' => 'http://localhost/page1',
'action_name' => 'PageView 01 - Title',
'uid' => '123456789012',
//'cip' => getUserIP(),
],
[
'url' => 'http://localhost/page2',
'action_name' => 'PageView 02 - Title',
'uid' => '123456789012',
//'cip' => getUserIP(),
],
[
'url' => 'http://localhost/page3',
'action_name' => 'PageView 03 - Title',
'uid' => '123456789012',
//'cip' => getUserIP(),
'e_c' => 'Service',
'e_a' => 'Auth',
'e_n' => 'Login',
],

]
,'idSite'
,'MatomoAPI'
));

% php test.php
Array
(
[status] => 1
[error] =>
[info] => Array
(
[0] => Array
(
[apiResult] => {"status":"success","tracked":1,"invalid":0}
)

)

)

得到 Report：

% cat test.php
require 'report.php';

print_r(matomoQueryReport(
[
'idSite' => 1,
'period' => 'day',
'date' => '2023-10-01,2023-10-03',
'method' => 'VisitsSummary.get',
]
, 'https://your-matomo.example.com/'
, 'access_token'
));

% php test.php
Array
(
[status] => 1
[data] => Array
(
[0] => Array
(
[2023-10-01] => Array
(
)

[2023-10-02] => Array
(
)

[2023-10-03] => Array
(
[nb_uniq_visitors] => 1
[nb_users] => 1
[nb_visits] => 1
[nb_actions] => 2
[nb_visits_converted] => 0
[bounce_count] => 0
[sum_visit_length] => 1
[max_actions] => 2
[bounce_rate] => 0%
[nb_actions_per_visit] => 2
[avg_time_on_site] => 1
)

)

)

[error] =>
[info] => Array
(
)

)

2014年9月12日星期五

[Python] 使用 Apache Web Server Access.log 把玩 CartoDB 視覺化地圖 @ Ubuntu 12.04

只要資料有 Geolocation，就能夠把玩 CartoDB 了 :P 若可以的話，再加上時間就更完美了。因此，最容易拿到的測資是 Apache web server log，把 access.log 挑點東西出來即可，至於 Geolocation 就用 ip 反查吧！

從 access.log 取出 ip list:


$ grep -v "^localhost\|::1" /var/log/apache2/access.log | awk '{print $1}' | uniq

首先，先到 Maxmind 下載最新的 GeoLite2-City 資訊：


$ wget http://geolite.maxmind.com/download/geoip/database/GeoLite2-City.mmdb.gz

$ gunzip GeoLite2-City.mmdb.gz

安裝 geoip-bin 工具:


$ sudo apt-get install geoip-bin

$ geoiplookup 8.8.8.8

GeoIP Country Edition: US, United States



$ geoiplookup -f GeoLite2-City.mmdb 8.8.8.8

Error Traversing Database for ipnum = 134744072 - Perhaps database is corrupt?

Segmentation fault (core dumped)

囧...只好裝一下新版 maxmind python sdk 寫一段小 code：


$ sudo pip install geoip2



$ vim t.py

mport sys

import geoip2.database

reader = geoip2.database.Reader('GeoLite2-City.mmdb')

try:

        response = reader.city(sys.argv[1])

        print str(response.location.latitude)+","+str(response.location.longitude)

except Exception, e:

        pass

$ python t.py 8.8.8.8

37.386,-122.0838

接著，就乾脆寫 python 來處理 access.log 吧 XDD 用 command line 好像太冗長了。


$ sudo cp /var/log/apache2/access.log /tmp/access.log

$ sudo chmod 644 /tmp/access.log

$ vim log.py

import geoip2.database

reader = geoip2.database.Reader('GeoLite2-City.mmdb')

try:

        log = open('/tmp/access.log','rb').read()

        for rec in log.split('\n'):

                fields = rec.split(' ')

                try:

                        if fields[0] == 'localhost' or fields[0] == '::1' :

                                continue

                        response = reader.city(fields[0])

                        print fields[3][1:]+","+str(response.location.latitude)+","+str(response.location.longitude)

                except Exception, e:

                        pass

except Exception, e:

        pass



$ python log.py > log.csv

$ cat log.csv

...

07/Sep/2014:22:12:43,35.685,139.7514

07/Sep/2014:22:13:53,39.4899,-74.4773

...

對於時間格式不用擔心，直接丟進 cartodb.com 請他幫你處理！

匯入後，預設都是 string，可以把 field1 設成 date type，field2 跟 field3 都設成 number type，弄完順便 rename 一下，接著再點選 geo 欄位，可以採用 field2 跟 field3 來生成，如此就完成 CartoDB table 製作。

最後再去視覺化那邊，挑一下以 date 為基準的時間變化，就可以有不錯的視覺圖表。

訂閱：文章 (Atom)

2024年12月5日 星期四

PHP 開發筆記 - 產生 CountryCode, CountryName, CityName, GeoLocation 列表

2023年10月3日 星期二

PHP 開發筆記 - 用 Matomo Analytics HTTP API 取代 GA4 Measurement Protocol 服務 @ Ubuntu 22.04

2014年9月12日 星期五

[Python] 使用 Apache Web Server Access.log 把玩 CartoDB 視覺化地圖 @ Ubuntu 12.04

Subscribe Now

2024年12月5日星期四

2023年10月3日星期二

2014年9月12日星期五