MySQL保存多边形，几何数据类型

Question

我在MySQL表中有多边形几何数据。数据存储为文本，我需要将数据保存为geometry数据类型。

state_fips_code tract_ce    tract_geom
6               576700      POLYGON((-118.169788 33.74847, -118.167625 33.747597, -118.165187 33.747139, -118.165741 33.739433, -118.162769 33.739184, -118.162638 33.73965, -118.162508 33.740117, -118.161791 33.742436, -118.161766 33.742512, -118.160742 33.746281, -118.160714 33.746387, -118.159262 33.751741, -118.158781 33.753463, -118.157518 33.757987, -118.156954 33.760101, -118.156884 33.760362, -118.156531 33.761425, -118.15616 33.76175, -118.155959 33.762416, -118.15577 33.763042, -118.155603 33.763596, -118.155444 33.764124, -118.155268 33.764727, -118.154575 33.764584, -118.154574 33.765026, -118.15458 33.766035, -118.154581 33.766266, -118.154584 33.767057, -118.154589 33.767951, -118.15459 33.768073, -118.155696 33.768074, -118.155904 33.768074, -118.156762 33.768076, -118.157844 33.768077, -118.158646 33.768078, -118.158925 33.768079, -118.159534 33.76808, -118.159557 33.76808, -118.160268 33.76808, -118.160584 33.768081, -118.161666 33.768076, -118.161683 33.768076, -118.162772 33.768078, -118.162938 33.768078, -118.163854 33.768079, -118.16421 33.76808, -118.164738 33.768074, -118.165013 33.768097, -118.165136 33.768129, -118.165204 33.768161, -118.165299 33.768232, -118.165379 33.768324, -118.165389 33.768291, -118.165424 33.768173, -118.165457 33.768062, -118.165463 33.768042, -118.165621 33.767529, -118.16568 33.767337, -118.165806 33.766926, -118.16599 33.766328, -118.166149 33.765794, -118.166308 33.76528, -118.166632 33.764199, -118.166191 33.764109, -118.166348 33.763579, -118.16643 33.763468, -118.166608 33.762587, -118.166728 33.761999, -118.16676 33.761851, -118.166769 33.761802, -118.166792 33.761705, -118.16816 33.755431, -118.168649 33.753374, -118.169788 33.74847))

在熊猫中，我做:

import pandas as pd
import geopandas as gpd
import shapely.geometry
from shapely import wkt
df['geometry'] = df['tract_geom'].apply(wkt.loads)
df_gdf = gpd.GeoDataFrame(df, geometry='geometry')

我试着:

ALTER TABLE geom MODIFY tract_geom Polygon;

我:

Error Code: 1416. Cannot get geometry object from data you send to the GEOMETRY field

如何更新mysql表和列的数据类型为geometry?

Answer 1

您可以使用ST_GeomFromTxt将WKT字符串几何列转换为几何列:

ALTER TABLE geom
ADD COLUMN geometry Polygon;
# insert column into table
UPDATE geom
SET geometry = (
SELECT ST_GeomFromTxt(prev.tract_geom)
FROM geom as prev
WHERE prev.tract_ce = geom.tract_ce
)

创建一个新表并插入所有行，这样做肯定更容易:

INSERT INTO geom_polygons
(
SELECT 
geom.state_fips_code as state_fips_code,
geom.tract_ce as tract_ce,
ST_GeomFromTxt(geom.tract_geom) as tract_geom
FROM geom
)

Answer 2

你可以先添加一个POLYGON类型的新列，然后使用ST_PolygonFromText从你的文本列

设置该列的值

ALTER TABLE GEOM ADD tract_geom_poly POLYGON;

UPDATE GEOM SET tract_geom_poly = ST_PolygonFromText(tract_geom) WHERE tract_geom!='' AND tract_geom IS NOT NULL;