Efficiency of the (currently very slow) spatial queries in scrollable Mapview

[jteresco]

To investigate related to efficiency of the (currently very slow) spatial queries:

https://dev.mysql.com/doc/refman/5.7/en/spatial-analysis-functions.html

If that's too complex or unhelpful, another idea is to add a "latlngblock" field to record which lat/lng block each waypoint is in, and query on that hoping it would short circuit the actual lat/lng comparisons for points not in any of the blocks currently visible. Also maybe only when zoomed in fairly far.

Originally posted by @jteresco in #212 (comment)

[mapcat]

Not sure if this is related to the slow response, but is it necessary to generate the table on each scroll, especially if a user is scrolling multiple times in quick succession? For example, I waited for the original location (Siena) to load fully, then scrolled to the west multiple times until it was focused on northern Ohio, and waited for the segments to be drawn. I don't know if the number of times the table regenerated matched the number of click-drags, but it populated with data from Pennsylvania at least twice, even though nothing from that state showed on the initial or final map.

[jteresco]

#423 is about how we might be able to wait until the pan/zoom operations have stopped before making the request. As of now, we have two efficiency problems: each query is slow, and queries are generated for each pan/zoom, even if part of a quick sequence.

I hope the box to jump to a desired starting location will reduce the need to lots of scrolling around to get from an area to another that's far away, making this a less urgent issue at the moment.

[jteresco]

Disappointing experiment today with the mysql spatial data types. No consistent speed differences when I introduced a coordinates column to the waypoints table that holds POINT values and is a spatial index.

The query as in the current mapview implementation:

select segments.root, if (cl.segmentId is null, false, true) as clinched, w1.pointName as w1name, w1.latitude as w1lat, w1.longitude as w1lng, w1.pointId as w1id, w2.pointName as w2name, w2.latitude as w2lat, w2.longitude as w2lng, w2.pointId as w2id from segments join waypoints as w1 on segments.waypoint1=w1.pointId join waypoints as w2 on segments.waypoint2=w2.pointId left join clinched as cl on (cl.segmentId=segments.segmentId and cl.traveler='terescoj') where ((w1.latitude>42.4 and w1.latitude<42.9 and w1.longitude<-73.3 and w1.longitude>-73.8) or (w2.latitude>42.4 and w2.latitude<42.9 and w2.longitude<-73.3 and w2.longitude>-73.8)) order by segments.root;

has similar run time to the equivalent with the spatial functions:

set @bb = 'polygon((-73.3 42.4, -73.8 42.4, -73.8 42.9, -73.3 42.9, -73.3 42.4))'; select segments.root, if (cl.segmentId is null, false, true) as clinched, w1.pointName as w1name, w1.latitude as w1lat, w1.longitude as w1lng, w1.pointId as w1id, w2.pointName as w2name, w2.latitude as w2lat, w2.longitude as w2lng, w2.pointId as w2id from segments join waypoints as w1 on segments.waypoint1=w1.pointId join waypoints as w2 on segments.waypoint2=w2.pointId left join clinched as cl on (cl.segmentId=segments.segmentId and cl.traveler='terescoj') where mbrwithin(w1.coordinates,st_geomfromtext(@bb)) or mbrwithin(w2.coordinates,st_geomfromtext(@bb)) order by segments.root;

[jteresco]

New possibility: break into three queries instead of two.

1. Get waypoints in visible area
2. Get segments that have any of those waypoints as an endpoint
3. Get route info for each of those segments (as done now)

[jteresco]

Leaving open until there's some testing of a good threshold for when to use the list of points for the segments query and when to use segment endpoints coordinates in a join.

Web/lib/getVisibleSegments.php

Lines 55 to 59 in 8f0c4fb

	if (count($waypoints) < 500) {
	$result = tmdb_query("select segments.root, if (cl.segmentId is null, false, true) as clinched, w1.pointName as w1name, w1.latitude as w1lat, w1.longitude as w1lng, w2.pointName as w2name, w2.latitude as w2lat, w2.longitude as w2lng from segments join waypoints as w1 on segments.waypoint1=w1.pointId join waypoints as w2 on segments.waypoint2=w2.pointId left join clinched as cl on (cl.segmentId=segments.segmentId and cl.traveler='".$params['traveler']."') where ((w1.pointId in ('".implode("','",$waypoints)."')) or (w2.pointId in ('".implode("','",$waypoints)."'))) order by segments.root;");
	}
	else {
	$result = tmdb_query("select segments.root, if (cl.segmentId is null, false, true) as clinched, w1.pointName as w1name, w1.latitude as w1lat, w1.longitude as w1lng, w2.pointName as w2name, w2.latitude as w2lat, w2.longitude as w2lng from segments join waypoints as w1 on segments.waypoint1=w1.pointId join waypoints as w2 on segments.waypoint2=w2.pointId left join clinched as cl on (cl.segmentId=segments.segmentId and cl.traveler='".$params['traveler']."') where ((w1.latitude>".$params['minLat']." and w1.latitude<".$params['maxLat']." and w1.longitude<".$params['maxLng']." and w1.longitude>".$params['minLng'].") or (w2.latitude>".$params['minLat']." and w2.latitude<".$params['maxLat']." and w2.longitude<".$params['maxLng']." and w2.longitude>".$params['minLng'].")) order by segments.root;");