Expose a 'count' on the neighbourhood stats SQL view
Closed this issue · 1 comments
henrinormak commented
This way, any future style can make some changes based on the total number of incidents within the neighbourhood.
henrinormak commented
ec38d93 Added column crimecount that is a total number of incidents in the stats for the neighbourhood.
Update SQL query that is used, which may be a bit slower (due to the third SELECT), but all in all should be usable for smaller/medium regions.
SELECT * FROM (SELECT gid, name, geom, (SELECT COUNT(*) FROM incidents i WHERE ST_Within(i.geom, n.geom)) AS crimecount, (SELECT array_to_json(array_agg(row_to_json(i)))::text FROM (SELECT crime, COUNT(*) AS count FROM incidents i WHERE ST_Within(i.geom, n.geom) GROUP BY crime) i) AS stats FROM neighbourhoods n WHERE ST_Intersects(n.geom, ST_MakeEnvelope(%LEFT%, %BOTTOM%, %RIGHT%, %TOP%, 4326))) t1;