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

小编典典

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

sql

我的服务器上有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字段的数据中获取几何对象

有任何想法吗？

阅读 319

2021-04-22

共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;

2021-04-22