[Qgis-user] Faster Intersects

[Nicolas Cadieux]

Hi,
Geopackage has a build in index. So you don’t need to build it.

Nicolas Cadieux
https://gitlab.com/njacadieux

> Le 9 sept. 2022 à 13:50, Raymond Nijssen via Qgis-user <qgis-user at lists.osgeo.org> a écrit :
> 
> Great! \o/
> 
> 
>> On 09-09-2022 19:25, Dennis Burgess wrote:
>> That did it!!!  I was unware that GeoPackage’s are SUPER fast and that I could SAVE a CSV as a GeoPackage file. . There is no metho to create indexes though once I do this, but now it runs in about 2 min!  lol
>> *LTI-Full_175px*
>> *Dennis Burgess*
>> *
>> *Author of "Learn RouterOS- Second Edition”
>> *Link Technologies, Inc*-- Mikrotik & WISP Support Services
>> *Office*: 314-735-0270  Website: http://www.linktechs.net <http://www.linktechs.net/>
>> Create Wireless Coverage’s with www.towercoverage.com <www.towercoverage.com>
>> Need MikroTik Cloud Management: https://cloud.linktechs.net <https://cloud.linktechs.net>
>> *From:* Alexandre Neto <senhor.neto at gmail.com>
>> *Sent:* Friday, September 9, 2022 12:20 PM
>> *To:* Dennis Burgess <dmburgess at linktechs.net>
>> *Cc:* Raymond Nijssen <r.nijssen at terglobo.nl>; QGIS User <qgis-user at lists.osgeo.org>
>> *Subject:* Re: [Qgis-user] Faster Intersects
>> Following raymond advice,
>> Move the CSV to a shapefile or geopackage and run the create spatial index on it.
>> If you have very complex polygons, use suvdivide to create smaller ones save it in shapefile or geopackage and run create spatial index on it.
>> https://docs.qgis.org/3.22/en/docs/user_manual/processing_algs/qgis/vectorgeometry.html#subdivide <https://docs.qgis.org/3.22/en/docs/user_manual/processing_algs/qgis/vectorgeometry.html#subdivide>
>> I would try with a smaller subset of points to test the improvement speed before trying to run on the full dataset again.
>> Answering your question, yes I am pretty sure postgis is much faster than mssql, and yes I think most recent versions of PostgreSQL PostGIS make use of more than one core.
>> Good luck
>> A sexta, 9/09/2022, 17:59, Dennis Burgess via Qgis-user <qgis-user at lists.osgeo.org <mailto:qgis-user at lists.osgeo.org>> escreveu:
>>    In the format, are you talking about the shape or the CSV with
>>    2million lines?
>>    Dennis Burgess
>>    Author of "Learn RouterOS- Second Edition”
>>    Link Technologies, Inc -- Mikrotik & WISP Support Services
>>    Office: 314-735-0270  Website: http://www.linktechs.net
>>    <http://www.linktechs.net>
>>    Create Wireless Coverage’s with www.towercoverage.com
>>    <http://www.towercoverage.com>
>>    Need MikroTik Cloud Management: https://cloud.linktechs.net
>>    <https://cloud.linktechs.net>
>>    -----Original Message-----
>>    From: Qgis-user <qgis-user-bounces at lists.osgeo.org
>>    <mailto:qgis-user-bounces at lists.osgeo.org>> On Behalf Of Raymond
>>    Nijssen via Qgis-user
>>    Sent: Friday, September 9, 2022 11:11 AM
>>    To: qgis-user at lists.osgeo.org <mailto:qgis-user at lists.osgeo.org>
>>    Subject: Re: [Qgis-user] Faster Intersects
>>    Some things to check:
>>    * Is your data local?
>>    * Is it in a fast file format? (for example .gpkg or .shp and not
>>    .csv or .geojson)
>>    * Does the data have a spatial index?
>>    * Is the geometry very complicated, like a huge multi polygon with
>>    thousands of vertices and islands? In that case, split it up in
>>    several polygons.
>>    Hope this helps. Anyway, it should not need to take this long.
>>    Raymond
>>    On 09-09-2022 16:28, Dennis Burgess via Qgis-user wrote:
>>     > How can I speed up Intersects?  Right now I have a single geometry
>>     > file, but then have about 2 million points that I need to understand
>>     > what intersects that geometry.  Right now its taking 3 days?
>>     > QGIS-bin.exe is only using 11% CPU.. ☹
>>     >
>>     > Dennis
>>     >
>>     >
>>     > _______________________________________________
>>     > Qgis-user mailing list
>>     > Qgis-user at lists.osgeo.org <mailto:Qgis-user at lists.osgeo.org>
>>     > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
>>    <https://lists.osgeo.org/mailman/listinfo/qgis-user>
>>     > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
>>    <https://lists.osgeo.org/mailman/listinfo/qgis-user>
>>    _______________________________________________
>>    Qgis-user mailing list
>>    Qgis-user at lists.osgeo.org <mailto:Qgis-user at lists.osgeo.org>
>>    List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
>>    <https://lists.osgeo.org/mailman/listinfo/qgis-user>
>>    Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
>>    <https://lists.osgeo.org/mailman/listinfo/qgis-user>
>>    _______________________________________________
>>    Qgis-user mailing list
>>    Qgis-user at lists.osgeo.org <mailto:Qgis-user at lists.osgeo.org>
>>    List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
>>    <https://lists.osgeo.org/mailman/listinfo/qgis-user>
>>    Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
>>    <https://lists.osgeo.org/mailman/listinfo/qgis-user>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20220922/61123c07/attachment.htm>