美文网首页
地理围栏相关Sql(Pgsql)

地理围栏相关Sql(Pgsql)

作者: JavaHub | 来源:发表于2019-04-23 15:42 被阅读0次
  • 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;

相关文章

网友评论

      本文标题:地理围栏相关Sql(Pgsql)

      本文链接:https://www.haomeiwen.com/subject/dmgvgqtx.html