achudars/GPIG

Expose a 'count' on the neighbourhood stats SQL view

Closed this issue · 1 comments

This way, any future style can make some changes based on the total number of incidents within the neighbourhood.

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;