空间数据实战（1）——MySQL

MySQL空间数据类型官方文档：https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html

• 创建一个支持空间数据类型的表

CREATE TABLE FEATURE
(
  ID VARCHAR(36) NOT NULL,
  LAYER_ID VARCHAR(36) NOT NULL,
  FEATURE_ID VARCHAR(36) NOT NULL,
  GEO_DATA GEOMETRY NOT NULL,
  PRIMARY KEY (ID)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

• 创建空间索引

• 插入一条数据

INSERT INTO FEATURE VALUES('1','','',ST_GeomFromText('POINT(112.927755 27.91046)'))
INSERT INTO FEATURE VALUES
('7','','',ST_GeomFromText('POLYGON((112.637997 28.314903,113.509569 28.286406,113.511869 27.813055,112.6196 27.79465,112.637997 28.314903))'),'矩形框测试')

MySQL空间分析函数官方文档：https://dev.mysql.com/doc/refman/8.0/en/spatial-analysis-functions.html

• 查询矩形框内的点
  

set @polygon = ( select GEO_DATA from `feature` where ID = '7');
select * from  feature WHERE ST_GeometryType(GEO_DATA)='POINT' and st_within(GEO_DATA,@polygon)

• 修改空间参考

UPDATE `feature` SET `GEO_DATA` = ST_GeomFromText(ST_AsText(`GEO_DATA`), 4490,'axis-order=long-lat');