12.2.1. 举例

12.2.1.1. 创建用户轨迹表

CREATE TABLE user_loc (
    id serial PRIMARY KEY ,
    user_id bigint not NULL ,
    geom geometry(Point,4326) not NULL ,
    crt_time timestamp DEFAULT current_timestamp
);

12.2.1.2. 创建围栏表

CREATE TABLE fence(
    id serial PRIMARY KEY ,
    name varchar(50),
    fence geometry(Polygon,4326),
    crt_time timestamp DEFAULT current_timestamp
);

12.2.1.3. 插入一个GPS坐标

INSERT INTO user_loc(user_id,geom) VALUES (1,ST_GeomFromText('POINT(104.075476 30.542983)',4326));

12.2.1.4. 插入电子围栏记录

INSERT INTO fence(name,fence) values ('天府软件园',ST_MakePolygon( ST_GeomFromText('LINESTRING(104.063403 30.548403 , 104.063403 30.544142 ,104.069493 30.54408 , 104.073356 30.541669 , 104.075656 30.541607, 104.075332 30.548325, 104.063403 30.548403)',4326) ));

注意:第一个坐标点和最后一个坐标点必须一样。

12.2.1.5. 判断点是否在多边形内

SELECT  ST_Contains( ST_MakePolygon(ST_GeomFromText('LINESTRING ( 104.063403 30.548403 , 104.063403 30.544142 ,104.069493 30.54408 , 104.073356 30.541669 , 104.075656 30.541607, 104.075332 30.548325 ) ', 4326)) ,st_point(104.072601 30.54537) )

12.2.1.6. 判断用户坐标是否在电子围栏内

SELECT ST_Contains(
    (SELECT fence FROM fence WHERE id=1)
    ,
    ST_GeomFromText('POINT(104.069474 30.574392)', 4326)
);