[Qgis-user] Issues using Points in polygon with PostGIS layer

[Lee Hachadoorian]

On 11/24/2013 04:17 PM, Richard Duivenvoorde wrote:
> On 24-11-13 21:30, Lee Hachadoorian wrote:
>> I was using `Vector → Analysis Tools → Points in polygon` to count
>> events in a hexgrid (created with MMQGIS). The point data came from a
>> PostGIS layer. The layer has a query restriction and the filtered data
>> set has ~230k points. When I run it, it just crashes (sometimes
>> immediately, sometimes after several seconds). When I export the PostGIS
>> layer to shapefile and run it, it works (although it takes ~2 hours to
>> run).
>>
>> Are there issues with using this feature with PostGIS layers? Is it the
>> size of the dataset?
> Hi Lee,
>
> I did some some testing here, with a (small) postgis dataset. No
> problems here.
>
> The vector analysis tools are written in Python, and I think it has been
> asked before to redo at least some of them in cpp to make these faster.
> Especially those that are often used with larger datasets...
> (Google Summer of Code project?)
>
> I think in these cases the best way to test this is:
> - try it with the latest/testing version on different computers
> (preferably different OS's too)
> - if still a problem: create an issue, AND provide the test data
> somewhere (so others can test and confirm this issue)
> - depending on your tools: maybe run qgis via strace and append the
> output to the issue
>
> As you can see here:
>
> https://github.com/qgis/QGIS/blob/master/python/plugins/fTools/tools/doPointsInPolygon.py#L180
> the plugin apparently creates an index with (in your case) all 230K
> points in it.
> Later, the features are fetched one by one to test if they intersect
> (#206). I would think that could be done easier/faster(??) nowadays by
> using the inGeom.boundingBox
>
> Well as said: plz create an issue and provide some test data, maybe
> other people interested in this can try things out this too...
>
> Regards,
>
> Richard Duivenvoorde

Tested with a random sample from my original data, only 7k records, and 
it ran to completion. Will create an issue to document it.

BTW, because it wasn't working in QGIS I imported my hexgrid into 
PostGIS and ran the analysis in-database using 
`ST_Contains(hex_layer.geom, point_layer.geom) ... GROUP BY 
hex_layer.gid`, and the query ran in 2 seconds (literally).

Best,
--Lee