MySQL对空间坐标数据(GEOMETRY字段)的读写

布满荆棘的人生 2021-11-23 04:44 1348阅读 0赞

原始数据/数据库要显示的数据:

<table> 
 <thead> 
  <tr> 
   <th align="center">id</th> 
   <th align="center">city</th> 
   <th align="center">district</th> 
   <th align="center">name</th> 
   <th align="center">gcj02</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">494165</td> 
   <td align="center">成都市</td> 
   <td align="center">成华区</td> 
   <td align="center">成渝立交地铁站C口</td> 
   <td align="center">POINT(104.147775 30.625014)</td> 
  </tr> 
 </tbody> 
</table>

其中`gcj02`字段属于空间坐标字段**point**

### 插入 ###

**错误的插入语句**

insert into database.metro_info
    (city,district,name,gcj02)
    values
    ("成都市","成华区","成渝立交地铁站C口","POINT(104.147775 30.625014)")

**报错**:

1416 - Cannot get geometry object from data you send to the GEOMETRY field

对POINT数据加上**POINTFROMTEXT** or **GeomFromText**

**但使用Python操作MySQL语句，提示：**

Warning: (1287, "'POINTFROMTEXT' is deprecated and will be removed in a future release. Please use ST_POINTFROMTEXT instead")

警告：**POINTFROMTEXT** and **GeomFromText** 方法将被移除，建议使用**ST\_POINTFROMTEXT** or **ST\_GeomFromText**

**正确的插入语句**

insert into database.metro_info
    (city,district,name,gcj02)
    values
    ("成都市","成华区","成渝立交地铁站C口",ST_POINTFROMTEXT("POINT(104.147775 30.625014)"))

### 查询 ###

在**MySQL**中直接查询, 没有问题

select id, city, gcj02
    from metro_info

#### 但是 ####

在Python中查询, POINT数据会显示二进制, 想要达到MySQL中的查询效果

对POINT数据加上**ST\_ASTEXT**

**正确的查询语句**

select id, city, ST_ASTEXT(gcj02)
    from metro_info