12.3.8. MySQL对空间坐标对支持

12.3.8.1. 举例

12.3.8.1.1. 创建用户轨迹表

create table customer_location_log (
    id bigint PRIMARY KEY ,
    customer_id bigint not null,
    location geometry COMMENT '用户的GPS坐标位置',
    crt_time timestamp DEFAULT  current_timestamp
);

12.3.8.1.2. 创建电子围栏表

create table fence_info
(
    id                   bigint not null,
    name                 varchar(200),
    csc_id               bigint,
    crt_user             bigint,
    crt_time             timestamp,
    primary key (id)
);

ALTER table fence_info add COLUMN fence geometry COMMENT '围栏的Polygon定义';

12.3.8.1.3. 插入用户轨迹

INSERT into customer_location_log (customer_id,location) VALUES (1025,ST_GeomFromText('Point(104.067516 30.538794)',4326));

12.3.8.1.4. 插入电子围栏

INSERT INTO fence_info(name,fence) values ('天府软件园',ST_GeomFromText(
       'Polygon((104.056744 30.542329,104.068997 30.542348, 104.069276 30.535584, 104.05683 30.537802, 104.056744 30.542329))',
       4326));

12.3.8.1.5. 查询用户坐标是否在电子围栏内

SELECT ST_Contains(
    ST_GeomFromText('Polygon((104.056744 30.542329,104.068997 30.542348, 104.069276 30.535584, 104.05683 30.537802, 104.056744 30.542329))',4326),
    ST_GeomFromText('Point(104.063632 30.54013)', 4326)
);

SELECT ST_Contains(
    (SELECT fence FROM fence_info where id=1),
    ST_GeomFromText('Point(104.063632 30.54013)', 4326)
);

12.3.8.1.6. 查询用户某天对坐标

SELECT id,customer_id,ST_X(location) AS LNG,ST_Y(location) AS LAT,crt_time from customer_location_log WHERE DATE_FORMAT(crt_time,’%Y%m%d’)=’20200825’;