sql - 获取 Action 发生的次数

Question

我需要使用 PostGIS 获取每个人进入定义为多边形的特定地点的次数。

下面是我如何开始 SQL 语句:

SELECT id_p 
FROM Movement 
WHERE ST_Contains('POLYGON((long1 lat1, long2 lat2, long3 lat3, long4 lat4))',
                  ST_MakePoint(longitude,latitude)) 
  AND (datehour::date BETWEEN '2017-01-01' AND '2017-01-31') 
  AND speed = 0

Answer 1

您可以尝试进行基本计数查询:

SELECT
    id_p,
    COUNT(*) AS cnt
FROM
    Movement
WHERE
    ST_Contains('POLYGON((long1 lat1, long2 lat2, long3 lat3, long4 lat4))',
                ST_MakePoint(longitude,latitude)) AND
    datehour::date BETWEEN '2017-01-01' AND '2017-01-31' AND
    speed = 0
GROUP BY
    id_p;

要加快此查询，您可以考虑向 WHERE 子句中使用的列添加索引，即 datehour 和 speed。您还可以考虑为调用 ST_Contains 时涉及的列创建地理空间索引。