CODEWITHSUNDEEP
sqlpostgresqlpostgis
geoinfo
geoinfo

Reputation: 305

Get number of times an action occured

I need to get the number of times each person enter a specific place defined in as a polygon using PostGIS.

Here is how I began the SQL statement : 

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

enter image description here

Upvotes: 1

Views: 41

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522311

You can try doing a basic count query:

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;

To speed up this query you could consider adding indices to the columns used in the WHERE clause, namely datehour and speed. You could also consider a geospatial index for the columns involved in the call to ST_Contains.

Upvotes: 2

Related Questions