New answers tagged optimisation
You have a couple of problems within your query. The usage of CTEs you completely prevent using your geometry index, which I hope you have on gps_nodes.geog. The usage of ST_DISTANCE does not use your geometry index as well. Use something like ST_DWITHIN. By the way ST_DWITHIN with your CTE construct does not use the geometry index. (not performance) I ...
Add AND ST_DWithin("geog",ST_GeogFromText('POINT( -1.54790 53.79424 )'), 40000) to Select where distance is smaller than 40km , so it doesn not calculate distance to all points just for those it needs And you probably can use just one gps_nodes_1 "table", far as i can see it is same query as _2
The above solution works great for me and was very quick. Using the above code and referenced code from the other post this is how I built it: # Local Variables OriginTable = "This must be a Table View or Feature Layer" DestinationTable = "This must be a Table View or Feature Layer" PrimaryKeyField = "Matching Origin Table Field" ForiegnKeyField = "Matching ...
Top 50 recent answers are included