地理围栏相关Sql（Pgsql）

• PostgreSql
• 查询车是否在圆形围栏-返回布尔类型

SELECT
    ST_DWithin (
        ST_SetSRID ( st_MakePoint ( 116.561364, 39.915226 ), 4326 ) :: geography,
        ST_SetSRID ( st_MakePoint ( 116.562083, 39.9169 ), 4326 ) :: geography,
    to_number( '200', '9999999999999.999999' ));

• 查询车是否在多边形围栏-返回布尔类型

SELECT
    ST_Covers (
        polygon,
    st_MakePoint ( 116.24092835076574, 40.073139284534705 ))
FROM
    bc_contact
WHERE
    contact_id = 6479891718668813333;    

• 查询圆形围栏里所有车辆-list<string>

SELECT
    bv."vehicle_no"
FROM
    bc_contact bc,
    bc_vehicle_location bv
WHERE
    ST_DWithin (
        ST_SetSRID ( st_MakePoint ( bv.lon, bv.lat), 4326 ) :: geography,
     ST_SetSRID ( bc.center_point, 4326 ) :: geography,
     to_number( bc.elect_radius, '99999999999.999999')
     )
    AND contact_id = 6522372873275835413;

• 查询多边形围栏里所有车辆-list<string>

SELECT
    bv."vehicle_no"
FROM
    bc_contact bc,
    bc_vehicle_location bv
WHERE
    ST_Covers (
        bc.polygon,
    st_MakePoint ( bv.lon, bv.lat ))
    AND contact_id = 6479891718668813333;

• 动态更新车辆位置信息

INSERT INTO bc_vehicle_location ( "vehicle_no", lon, lat )
VALUES
    ( '京AAW715', 2, 2 ) ON CONFLICT ( "vehicle_no" ) DO
UPDATE
    SET lon = 2,
    lat = 2;