0

I'm trying to return a count of crimes (points) that occur in each police grid (polygons), as well as the geometry data, but the query takes ~22 seconds. This seems oddly high and will be problematic for using it to draw vectors in OpenLayers.

I have 28000 crime points, and 56 grid polygons in my dataset.

This is the query I'm using:

SELECT ST_AsText(ST_Collect(public.cspd_patrol_grid.geom)) AS geo, count(*) AS total_crimes, public.cspd_patrol_grid.command FROM public.crimes, public.cspd_patrol_grid WHERE ST_Contains(public.cspd_patrol_grid.geom, public.crimes.geo) GROUP BY public.cspd_patrol_grid.command;

I thought that it was possibly the size of the geometries that was slowing it down, so I removed that from the SELECT statement, and the query took a more appropriate 2 seconds to return only the total number of crimes in each polygon:

SELECT count(*) AS total_crimes, public.cspd_patrol_grid.command FROM public.crimes, public.cspd_patrol_grid WHERE ST_Contains(public.cspd_patrol_grid.geom, public.crimes.geo) GROUP BY public.cspd_patrol_grid.command;

However, where I'm getting confused is when I do return the geometry, but limit the crimes being queried from all of them to just 'Assault', which makes up about 3% of my crime points. This query only takes 600 ms, even though it still returns 100% of my grid geometries. This makes me think that it has nothing to do with the geometry data, and that there's something wrong with my query.

SELECT ST_AsText(ST_Collect(public.cspd_patrol_grid.geom)) AS geo, count(*) AS total_crimes, public.cspd_patrol_grid.command FROM public.crimes, public.cspd_patrol_grid WHERE ST_Contains(public.cspd_patrol_grid.geom, public.crimes.geo) AND public.crimes.crimetype = 'Assault' GROUP BY public.cspd_patrol_grid.command;

Is there anything noticeably wrong with the original query, or is there something else going on that results in the latter 2 queries being more efficient?

  • 1
    Perhaps you could benefit from spatial/regular indices? – Moggy Aug 1 '14 at 20:00
  • It is unlikely that your grid geometries are slowing down your query, as there are only 56, while there are 28000 points. Your normal query takes 22 seconds, and the query that returns 3% of your data returns in 600 ms (about 3% of 22s). I don't know your data or your conditions, but the problem is probably not the grid. Could be the indexes or the aggregation method. – ike Aug 1 '14 at 22:15
  • PostGIS version? – Paul Ramsey Aug 2 '14 at 1:49
  • Your data is tiny, it really is an odd result. – Paul Ramsey Aug 2 '14 at 1:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.