CODEWITHSUNDEEP
mysqlsql
Michael Farah
Michael Farah

Reputation: 421

MYSQL CSV Import - Cannot get geometry object from data you send to the GEOMETRY field

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

I also have a spatial index created on the ip_poly field

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

ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

Any ideas?

Upvotes: 2

Views: 5657

Answers (1)

Quassnoi
Quassnoi

Reputation: 425471

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

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

Change your ip_poly to iprange LINESTRING NOT NULL and use this:

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;

Upvotes: 1

Related Questions