MySQL对空间坐标对支持
==================================================

举例
----------------------------------------

创建用户轨迹表
``````````````````````````````

.. code-block:: sql

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

创建电子围栏表
``````````````````````````````

.. code-block:: sql

    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定义';


插入用户轨迹
``````````````````````````````

.. code-block:: sql

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

插入电子围栏
``````````````````````````````

.. code-block:: sql

    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));

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

.. code-block:: sql

    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)
    );

查询用户某天对坐标
``````````````````````````````

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';