<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.E-mailStijl19
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=NL link=blue vlink=purple style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>Below mail was sent to the developers list instead of the user list.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>Jeroen<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b>Van:</b> mail@groenebij.nl <mail@groenebij.nl> <br><b>Verzonden:</b> dinsdag 9 februari 2021 12:11<br><b>Aan:</b> 'C Hamilton' <adenaculture@gmail.com>; 'qgis-developer' <qgis-developer@lists.osgeo.org><br><b>CC:</b> 'Richard Duivenvoorde' <rdmailings@duif.net>; 'Tim Sutton' <tim@kartoza.com>; 'Nyall Dawson' <nyall.dawson@gmail.com><br><b>Onderwerp:</b> RE: timzonefinder vs point in polygon<o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>Hi Calvin,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>I have run a few tests, and might have found the algorithm you need.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>First sharing some result here. It might help in understanding what is going on.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>On my system (qgis 3.16, windows10, i510600 cpu at 3.3 GHz, 32 GB mem and a nvme SSD), the </span><span lang=EN-US>Vector->Geoprocessing Tools->Intersection took about 3 minutes and 10 seconds.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>It also duplicated 32 of the 10000 points, they got 2 different time zones. That might suggest there is something going on with the precision of both the vector layer and the point layer.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>Removing those 32 points and running the algorithm again did not change the run time. So no luck there.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>Using postgis and sql queries within DB Manager, things were really quick.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>Postgresql 11.10 and postgis 2.5.3<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>SELECT p.id, p.fid, t.tzid<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>FROM points_10_32 AS p<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>JOIN timezones AS t<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>ON st_within (p.geom, t.geom)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>This took just 12 seconds.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>Using st_intersects in stead of st_within made no difference, also done in 12 seconds.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>I must note, when importing the layers into my database, I always check the ‘create spatial index’<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>So, I also imported the layers into the database without creating the spatial index.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>This time the query took 40 seconds. Still, much faster than the Intersection algorithm in Geoprocessing Tools.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>A quick Google search (qgis geoprocessing tool intersect vs st_intersects) got me this page:<o:p></o:p></span></p><p class=MsoNormal><a href="https://gis.stackexchange.com/questions/307511/faster-alternative-to-intersection-in-qgis-to-just-retain-overlapping-features"><span lang=EN-US style='mso-fareast-language:EN-US'>https://gis.stackexchange.com/questions/307511/faster-alternative-to-intersection-in-qgis-to-just-retain-overlapping-features</span></a><span lang=EN-US style='mso-fareast-language:EN-US'><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>And that got me searching for alternatives and I actually found one:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>Vector->Datamanagement Tools->join attributes by location<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>Settings:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>input layer = point layer<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>Join = timezones layer<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>Geometric predicate = intersects<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>Join type = one-to-many<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>This seems to do the same thing as Geoprocessing Tools->Intersection, however, this one just took 2.9 seconds!<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>There is definitely something different in how these algorithms work and I have no idea what that might be. But the Join Attributes by Location tool might just be what you are looking for.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>Also, 3 seconds is even 4 times faster than the sql query, but that is a different issue.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'>It might actually have something to do with qgis3.12 and above. Running the sql in DB Manager in qgis 3.10 takes 6 seconds compared to 12 seconds (qgis3.16), see this bug report </span><a href="https://github.com/qgis/QGIS/issues/38256"><span lang=EN-US style='mso-fareast-language:EN-US'>https://github.com/qgis/QGIS/issues/38256</span></a><span lang=EN-US style='mso-fareast-language:EN-US'><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'>Kind regards,<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'>Jeroen<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b>Van:</b> C Hamilton <<a href="mailto:adenaculture@gmail.com">adenaculture@gmail.com</a>> <br><b>Verzonden:</b> vrijdag 5 februari 2021 22:23<br><b>Aan:</b> qgis-developer <<a href="mailto:qgis-developer@lists.osgeo.org">qgis-developer@lists.osgeo.org</a>>; Richard Duivenvoorde <<a href="mailto:rdmailings@duif.net">rdmailings@duif.net</a>>; Tim Sutton <<a href="mailto:tim@kartoza.com">tim@kartoza.com</a>>; Nyall Dawson <<a href="mailto:nyall.dawson@gmail.com">nyall.dawson@gmail.com</a>>; Groene Bij <<a href="mailto:mail@groenebij.nl">mail@groenebij.nl</a>><br><b>Onderwerp:</b> timzonefinder vs point in polygon<o:p></o:p></p></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal><span lang=EN-US>I need the ability to do a fast time zone lookup in QGIS and found the popular timezonefinder library which I encorported into the Date/Time Tools plugin. See<o:p></o:p></span></p><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div><div><p class=MsoNormal><a href="https://github.com/NationalSecurityAgency/qgis-datetimetools-plugin/releases/tag/3.0.1" target="_blank"><span lang=EN-US>https://github.com/NationalSecurityAgency/qgis-datetimetools-plugin/releases/tag/3.0.1</span></a><span lang=EN-US><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US>Rather than using this, Nyall suggested that I just use the original polygon data set and do a point in polygon lookup. So I have done some testing on a 10,000 random point data set where I was looking up the timezone of each point.<o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US>Time results for 10,000 points<o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US>Using timezonefinder: 44 seconds<o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US>Using the algorithm Vector->Geoprocessing Tools->Intersection: 4 minutes, 4 seconds<o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US>I also tried iterating over each of the 10,000 points then testing each of the time zone polygons to see if the point feature was contained in it using this statement. It returned once it found a match.<o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US>polyfeature.geometry().contains(point_feature) <o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US>This was definitely not the way to do it and I killed qgis before it finished.<o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US>My conclusion is that timezonefinder has been optimized for very fast lookups. I'm sure there is probably a better way for me to work with a point in polygon lookup and if anyone has any further suggestions let me know. I can see games I could play such as first testing the bounding box or setting up lookup tables, but that is what timezonefinder does so for now that is what I will use.<o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US>If I have missed some fast point in polygon lookup up in the QGIS API please let me know.<o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div><div><p class=MsoNormal>Thanks,<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Calvin<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div></div></div></body></html>