gmaps - search pages from polygon

[e0f]

Hello guys,

I developed a view where people draw a shape on google map.

I'm able from that view to get all coordinates of my shape.

Now I need to query pw to find all pages with template "test" and with lat and long inside the polygon.

So, I need to find a way to extract all my pages with lat and long inside the polygon.

I can't create an array with all lat and long of all my pages and later check, because if I have a lot of pages with template test it requires a lot to process informations.

Any advice to solve that problem?

Thanks!

 

[bernhard]

Spatial operations are not trivial and in general not supported by PW. The problem is that you can't do simply "greater than" or "less than" operations like you can do with simple number fields. I think you have two options:

1) Use mysql's spatial functions. That would mean you'd have to create a custom DB table and use spatial analysis functions: https://dev.mysql.com/doc/refman/5.7/en/spatial-analysis-functions.html

2) If you don't have too many pages you could also transfer an array of all coordinates of your pages to the client and then use a client-side libary to check which entries are within the polygon and which are outside. Then you could just add the ones within the polygon to the map. https://github.com/hayeswise/Leaflet.PointInPolygon

[e0f]

@bernhard thanks for your suggestions!

The main problem is I'll have more then 50k pages with 'test' template.

Did you test already mysql spatial functions? How they perform? And which informations you will store in the new table you suggest me?

[bernhard]

I've never worked with mysql spatial functions but I have a little background in GIS so I know things are usually a lot more complicated than you'd think before 😄

I've done a little research and found some helpful and interesting resources:

1. Youtube video with a good overview over GIS and mysql: https://www.youtube.com/watch?v=6zJ0swD17ow
2. A blog post how to find points contained in a polygon using mysql: https://marcgg.com/blog/2017/03/13/mysql-viewport-gis/
3. And a little more details about performance (link from article 2): https://www.percona.com/blog/new-gis-features-in-mysql-5-7/

I've done this mysql query on one of my VPSs (3vCPU, 4GB RAM) with - as far as I understand - 50k calculations and got a result in 3.7ms:

MariaDB [(none)]> select benchmark(50000, st_distance_sphere(point(-78.7698947, 35.890334), point(-122.38657, 37.60954)));
+--------------------------------------------------------------------------------------------------+
| benchmark(50000, st_distance_sphere(point(-78.7698947, 35.890334), point(-122.38657, 37.60954))) |
+--------------------------------------------------------------------------------------------------+
|                                                                                                0 |
+--------------------------------------------------------------------------------------------------+
1 row in set (0.037 sec)

I might be wrong but I think that means that you can expect your calculations to be similarly fast. The same benchmark using 1 million instead of 50k tests took around 500ms.

Maybe @Mats knows more? I think he also has a lot of experience with (web) maps?!

[Mats]

I have never built anything like that unfortunately. 

[millipedia]

Could you first do a simple test to see if your lat and lng are between the highest and lowest values in your polygon (so treat it like a square on first pass). Then once you've got a (hopefully much smaller) list of pages that are in that rough area you can loop through the page array and see if they are actually within the polygon (either using Leaflet or there's bound to be some php code out there).

It might not help in your particular case but it might be a good strategy to try first.

And of course if you live near London (hello) or anywhere else at zero longitude then you'll have to deal with that...

[Pixrael]

45 minutes ago, millipedia said:

simple test to see if your lat and lng are between the highest and lowest values in your polygon

https://learn.microsoft.com/en-us/answers/questions/414790/check-if-lat-long-point-is-inside-polygons