Re: [Qgis-user] Faster Intersects
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 > 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 >> Need MikroTik Cloud Management: https://cloud.linktechs.net >> <https://cloud.linktechs.net> >> *From:* Alexandre Neto >> *Sent:* Friday, September 9, 2022 12:20 PM >> *To:* Dennis Burgess >> *Cc:* Raymond Nijssen ; QGIS User >> >> *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 >> mailto:qgis-user@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 ><mailto:qgis-user-boun...@lists.osgeo.org>> On Behalf Of Raymond >>Nijssen via Qgis-user >>Sent: Friday, September 9, 2022 11:11 AM >>To: qgis-user@lists.osgeo.org <mailto:qgis-user@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@lists.osgeo.org <mailto:Qgis-user@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@lists.osgeo.org <mailto:Qgis-user@lists.osgeo.org> >>List info: https://lists.osgeo.org/mailman/listinfo/qgis-user >><https://lists.osgeo.org/mailman/listinfo/q
Re: [Qgis-user] Faster Intersects
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 Need MikroTik Cloud Management: https://cloud.linktechs.net <https://cloud.linktechs.net> *From:* Alexandre Neto *Sent:* Friday, September 9, 2022 12:20 PM *To:* Dennis Burgess *Cc:* Raymond Nijssen ; QGIS User *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 mailto:qgis-user@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 mailto:qgis-user-boun...@lists.osgeo.org>> On Behalf Of Raymond Nijssen via Qgis-user Sent: Friday, September 9, 2022 11:11 AM To: qgis-user@lists.osgeo.org <mailto:qgis-user@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@lists.osgeo.org <mailto:Qgis-user@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@lists.osgeo.org <mailto:Qgis-user@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@lists.osgeo.org <mailto:Qgis-user@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@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Faster Intersects
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 Need MikroTik Cloud Management: https://cloud.linktechs.net From: Alexandre Neto Sent: Friday, September 9, 2022 12:20 PM To: Dennis Burgess Cc: Raymond Nijssen ; QGIS User 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 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 mailto:qgis-user@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 Create Wireless Coverage’s with www.towercoverage.com<http://www.towercoverage.com> Need MikroTik Cloud Management: https://cloud.linktechs.net -Original Message- From: Qgis-user mailto:qgis-user-boun...@lists.osgeo.org>> On Behalf Of Raymond Nijssen via Qgis-user Sent: Friday, September 9, 2022 11:11 AM To: qgis-user@lists.osgeo.org<mailto:qgis-user@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@lists.osgeo.org<mailto:Qgis-user@lists.osgeo.org> > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org<mailto:Qgis-user@lists.osgeo.org> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org<mailto:Qgis-user@lists.osgeo.org> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
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 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@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 > Create Wireless Coverage’s with www.towercoverage.com > Need MikroTik Cloud Management: https://cloud.linktechs.net > > -Original Message- > From: Qgis-user On Behalf Of Raymond > Nijssen via Qgis-user > Sent: Friday, September 9, 2022 11:11 AM > To: qgis-user@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@lists.osgeo.org > > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user > ___ > Qgis-user mailing list > Qgis-user@lists.osgeo.org > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user > ___ > Qgis-user mailing list > Qgis-user@lists.osgeo.org > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user > ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Faster Intersects
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 Create Wireless Coverage’s with www.towercoverage.com Need MikroTik Cloud Management: https://cloud.linktechs.net -Original Message- From: Qgis-user On Behalf Of Raymond Nijssen via Qgis-user Sent: Friday, September 9, 2022 11:11 AM To: qgis-user@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@lists.osgeo.org > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
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@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Faster Intersects
Is there a postGIS or other community, I need an automated method to move data from MSSQL to POSTGIS for processing? And will postgis run faster than MSSQL? Will it use all cores? [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 Need MikroTik Cloud Management: https://cloud.linktechs.net From: Alexandre Neto Sent: Friday, September 9, 2022 11:19 AM To: Dennis Burgess Cc: QGIS User Subject: Re: [Qgis-user] Faster Intersects I don't know what is your data format, but make sure you have spatial index in it. For things with millions of points I would move the data into PostGIS for definitely faster processing. Alexandre Neto User Support A sexta, 9/09/2022, 15:50, Dennis Burgess via Qgis-user mailto:qgis-user@lists.osgeo.org>> escreveu: 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@lists.osgeo.org<mailto:Qgis-user@lists.osgeo.org> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Faster Intersects
I don't know what is your data format, but make sure you have spatial index in it. For things with millions of points I would move the data into PostGIS for definitely faster processing. Alexandre Neto User Support A sexta, 9/09/2022, 15:50, Dennis Burgess via Qgis-user < qgis-user@lists.osgeo.org> escreveu: > 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@lists.osgeo.org > List info: https://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user > ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] Faster Intersects
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@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
