Performance with geospacial queries

Full Text Search
15

With the GSI, I can now pull 20k documents in 2s, over a very large area like 50000km,

This really helped me a lot from @vsr1 Appropriate index for query with unnest - #2 by vsr1

I’ve been reading a lot, and it clearly shows that the GSI is much faster than the internal spacial queries,

Now;

  • I’ve put together the spacial logic in the query,
  • Let’s say I have a Point A [43.65, -79.44]
  • I create a SELECT using the geo formula against this Point A, which generates the distance between Point A and the documents in DB, as distance in KM

This query works

SELECT 
       d.env, 
       d.geo, 
       d.city,
       (ACOS(SIN( RADIANS(43.65)) * SIN(RADIANS(d.geo.lat)) + COS( RADIANS(43.65)) * COS(RADIANS(d.geo.lat)) * COS(RADIANS(d.geo.lon) - RADIANS(-79.44))) * 6371) AS distance
FROM `dev` d
WHERE d.geo.lat IS NOT NULL AND d.geo.lat IS NOT MISSING
AND (ACOS(SIN( RADIANS(43.65)) * SIN(RADIANS(d.geo.lat)) + COS( RADIANS(43.65)) * COS(RADIANS(d.geo.lat)) * COS(RADIANS(d.geo.lon) - RADIANS(-79.44))) * 6371) <= 50000
ORDER BY distance DESC
LIMIT 20000;

**

This second one doesn’t work, I tried to make it cleaner like this but it fails,

**

SELECT 
       d.env, 
       d.geo, 
       d.city,
       (ACOS(SIN( RADIANS(43.65)) * SIN(RADIANS(d.geo.lat)) + COS( RADIANS(43.65)) * COS(RADIANS(d.geo.lat)) * COS(RADIANS(d.geo.lon) - RADIANS(-79.44))) * 6371) AS distance
FROM `dev` d
WHERE d.geo.lat IS NOT NULL AND d.geo.lat IS NOT MISSING
AND distance <= 50000
ORDER BY distance DESC
LIMIT 20000;

@vsr1 - How can you make the second query work, the first query is working but I am duplicating the geo formula, both in the SELECT clause and in the WHERE AND ... <= 50000

As you can see in the second query am trying to use distance but it breaks it and fails.