mysql - 根据 IP 地址对大量帖子进行地理定位。 (880,000 行)

Question

首先我要说的是，我已经用尽了我自己能想到的所有各种选项，并对每个选项进行了研究，以解决所有可见的死胡同。

我有一个典型的 MySQL 论坛数据库，其中包含一个大约 880,000 行的 post 表。 post 表包含 IP 地址列，我的最终目标是根据每个帖子的地理位置创建一个世界气泡图。如果我可以每月将它们分开并创建过去 8 年世界各地的帖子频率的动画，那就更好了。

因为这是一个个人项目，IP 地理定位的准确性并不重要，所以我不得不排除可以批量将 IP 转换为地理定位的付费 API。我在 stackoverflow 上发现了各种问题，这些问题链接到一个包含 IP 地理位置数据库的网站:https://dev.maxmind.com/geoip/geoip2/geolite2/

我最初的计划是将此数据库加载到我的论坛服务器上，并利用我在 mysql 方面的经验创建一个新表，其中包含:postid、日期(作为 unix 时间戳)、纬度、经度、城市、国家/地区。然后将该表导出到 R 并生成我想要的所有 map 和图表。然而，地理位置数据库在两个表中包含超过 300 万行，而我的死论坛采用的是简单的共享托管计划，不允许加载数据。我尝试了这些问题的所有解决方案，但没有运气: How to import CSV file to MySQL table access denied for load data infile in MySQL LOAD DATA INFILE within PHPmyadmin PHPMyAdmin saying: The used command is not allowed with this MySQL version

所以我的下一个想法是将相关列从我的帖子表导出到 .csv 或 .xml，然后将它们上传到我在 iacademy3.oracle.com 的帐户。但是，我没有 Oracle 经验，我知道的唯一方法是数据工作室中的数据加载/卸载 UI。 177MB XML 文件上传失败，出现以下错误:

ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00222: error received from SAX callback function


Error loading XML.
Return to application.

34MB .csv 文件两次尝试上传失败，并出现以下错误:

Failure of Web Server bridge:
No backend server available for connection: timed out after 10 seconds or idempotent set to OFF or method not idempotent.

现在我没有主意了。在逐个帖子的基础上，这是一个简单的查询，可以查看帖子 IP，将其与地理位置数据库进行比较，并获取纬度和经度。但是当处理数百万行时，我不知道如何得到最终结果。

任何有关新方法的建议或帮助解决我的死胡同，我将不胜感激。

Answer 1

我们将生成一些 IP 地址，对它们进行地理定位并绘制它们:

library(iptools)
library(rgeolocate)
library(tidyverse)

生成一百万个(分布太均匀的)随机 IPv4 地址:

ips <- ip_random(1000000)

并且，对它们进行地理定位:

system.time(
  rgeolocate::maxmind(
    ips, "~/Data/GeoLite2-City.mmdb", c("longitude", "latitude")
  ) -> xdf
)
##    user  system elapsed 
##   5.016   0.131   5.217 

1m IPv4 需要 5 秒。 👍🏼

现在，由于均匀性，气泡会变得非常小，因此在这个例子中，我们将它们四舍五入一下:

xdf %>% 
  mutate(
    longitude = (longitude %/% 5) * 5,
    latitude = (latitude %/% 5) * 5
  ) %>%  
  count(longitude, latitude) -> pts

然后，绘制它们:

ggplot(pts) +
  geom_point(
    aes(longitude, latitude, size = n), 
    shape=21, fill = "steelblue", color = "white", stroke=0.25
  ) +
  ggalt::coord_proj("+proj=wintri") +
  ggthemes::theme_map() +
  theme(legend.justification = "center") +
  theme(legend.position = "bottom")

你可以明白我的意思是“太统一”。但是，您拥有“真正的”IPv4，所以您应该是gtg。

考虑使用scale_size_area()，但说实话，请考虑根本不在地理 map 上绘制 IPv4。我以进行互联网规模的研究为生，其准确性声称还有很多不足之处。由于这个原因，我很少低于国家级归因(而且我们为“真实”数据付费)。