mysql - 在 phpMyAdmin 中导入带有多边形数据的 CSV

Question

我正在尝试使用 phpMyAdmin 导入 CSV。这是我的 CSV 的样子:

1,1,1,'POLYGON ((879350.00000000012 4542950.0,882236.75134594808 4547950.0,888010.25403784448 4547950.0,890897.00538379245 4542950.0,888010.25403784448 4537950.0,882236.75134594808 4537950.0,879350.00000000012 4542950.0))','POINT (885123.50269189617 4542950.0)'
2,1,2,'POLYGON ((879350.00000000012 4552950.0,882236.75134594808 4557950.0,888010.25403784448 4557950.0,890897.00538379245 4552950.0,888010.25403784448 4547950.0,882236.75134594808 4547950.0,879350.00000000012 4552950.0))','POINT (885123.50269189617 4552950.0)'
3,1,3,'POLYGON ((879350.00000000012 4562950.0,882236.75134594808 4567950.0,888010.25403784448 4567950.0,890897.00538379245 4562950.0,888010.25403784448 4557950.0,882236.75134594808 4557950.0,879350.00000000012 4562950.0))','POINT (885123.50269189617 4562950.0)'

我的列名和类型如下:

CellId      int(11),
Type        int(1),
Name        varchar(255),
Geometry    polygon,
Centroid    point

无论我尝试什么，我似乎总是得到错误

'Invalid column count in CSV input on line 1' or 'Cannot get geometry object from data you send to the GEOMETRY field'

我可以更改 phpMyAdmin 中的 CSV 或导入设置以使其正常工作吗？

Answer 1

您无法轻松导入几何类型。我建议创建临时表

CREATE TABLE IF NOT EXISTS `tmptbl` (
  `a` int(11),
  `b` int(11),
  `c` varchar(255),
  `d` text,
  `e` text   
)

将 csv 导入该表，然后使用将其转换为您的表

INSERT INTO mytable (CellId,Type,Name,Geometry,Centroid)
   SELECT a,b,c,GeomFromText(d),PointFromText(e) FROM tmptbl