MYSQL CSV 导入 - 无法从您发送到 GEOMETRY 字段的数据中获取几何对象

Question

我的服务器上有 CSV 文件，其中的数据如下所示；

16777216,17039359,"APNIC Debogon Project"
17367040,17432575,"Tmnet, Telekom Malaysia Bhd."
17435136,17435391,"APNIC Debogon Project"
17498112,17563647,"CJ-HELLOVISION"
17563648,17825791,"Beijing Founder Broadband Network Technology Co.,L"
17825792,18087935,"Allocated to KRNIC Member."
18153984,18154239,"Double Cast"
18157056,18163711,"FAMILY NET JAPAN INCORPORATED"

我正在尝试将其插入到结构如下的表中；

ipoid    INTEGER  11 NOT NULL PRIMARY KEY
beginip  INTEGER  14 NOT NULL UNSIGNED
endip    INTEGER  14 NOT NULL UNSIGNED
org      VARCHAR  255
ip_poly  POLYGON

我还在 ip_poly 字段上创建了一个空间索引

我正在尝试使用以下代码插入 csv 数据

LOAD DATA INFILE "/home/GeoIPOrg.csv"
INTO TABLE crm_geo_org
FIELDS
TERMINATED BY ","
ENCLOSED BY "\""
LINES
TERMINATED BY "\n"
(@beginIp,@endIp,@org)
SET
ipoid      := NULL,
beginip := @beginIp,
endip   := @endIp,
ip_poly := GEOMFROMWKB(POLYGON(LINESTRING(
/* clockwise, 4 points and back to 0 */
POINT(@beginIp, -1), /* 0, top left */
POINT(@endIp,   -1), /* 1, top right */
POINT(@endIp,    1), /* 2, bottom right */
POINT(@beginIp,  1), /* 3, bottom left */
POINT(@beginIp, -1)  /* 0, back to start */
))),
org:= @org;

但是当我尝试这样做时，我得到了这个错误

错误 1416 (22003):无法从您发送到 GEOMETRY 字段的数据中获取几何对象

有什么想法吗？

Answer 1

在 MySQL 的更高版本中，您不需要 WKB/WKT 转换来构建几何对象。

另外，Polygon 在这里有点矫枉过正:MBR 也可以从单个 LineString 构建。

将您的 ip_poly 更改为 iprange LINESTRING NOT NULL 并使用:

LOAD DATA INFILE "/home/GeoIPOrg.csv"
INTO TABLE
        crm_geo_org
FIELDS
        TERMINATED BY
                ","
        ENCLOSED BY
                "\""
LINES
        TERMINATED BY "\n"
        (@beginIp, @endIp, @org)
SET     ipoid   := NULL,
        beginip := @beginIp,
        endip   := @endIp,
        iprange := GeomFromWKB(
                        LineString(
                                Point(@beginIp, -1),
                                Point(@endIp, 1)
                                )
                        ),
        org     := @org;