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

问题描述

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

I have CSV file on my server in which the data looks as follows;

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"

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

I'm trying to insert this into my table which is structured as follows;

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字段上创建了一个空间索引

I also have a spatial index created on the ip_poly field

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

I'm trying to insert the csv data with the following code

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;

但是，当我尝试这样做时，会出现此错误

However when I try to do so I get this error

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

有什么想法吗?

推荐答案

在MySQL的更高版本中，不需要WKB/WKT转换即可构建几何对象.

In later versions of MySQL you don't need WKB/ WKT transformations to build the geometry objects.

此外，Polygon在这里是一个过大的杀伤力:MBR也可以由单个LineString构建.

Also, Polygon is an overkill here: MBR can be built from a single LineString just as well.

将您的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;

这篇关于MYSQL CSV导入-无法从发送到GEOMETRY字段的数据中获取几何对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！