title: Pagination by geometry distance using a cursor
body: <p>I have a table as this (I am using MySQL 8.0):</p>
  <pre class="lang-sql prettyprint-override"><code>create table `studio` (
      `id` int unsigned not null auto_increment primary key,
      `position` point srid 4326 not null
  ) default character set utf8mb4 engine = InnoDB;
  </code></pre>
  <p>Now I am triying to paginate by geometry distance using a cursor, but none of the methods I can think of seems very efficient.</p>
  <p>Using <code>having</code> (cursor: { distance: 5502111.294724894, id: 24 }):</p>
  <pre class="lang-sql prettyprint-override"><code>select *,
    ST_Distance_Sphere(e0.position, ST_GeomFromGeoJSON(
      '{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[42.605556,-5.57]}', 1, 4326)
    ) as distance
  from `studio` as `e0`
  having (distance &gt;= 5502111.294724894 and (distance &gt; 5502111.294724894 or id &lt; 24))
  order by `distance` asc, `e0`.`id` desc limit 6
  </code></pre>
  <p>Using <code>where</code> (cursor: { distance: 5502111.294724894, id: 24 }):</p>
  <pre class="lang-sql prettyprint-override"><code>select *,
    ST_Distance_Sphere(e0.position, ST_GeomFromGeoJSON(
      '{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[42.605556,-5.57]}', 1, 4326)
    ) as distance
  from `studio` as `e0`
  where (
    ST_Distance_Sphere(e0.position, ST_GeomFromGeoJSON(
      '{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[42.605556,-5.57]}', 1, 4326)
    ) &gt;= 5502111.294724894
  ) and (
    ST_Distance_Sphere(e0.position, ST_GeomFromGeoJSON(
      '{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[42.605556,-5.57]}', 1, 4326)
    ) &gt; 5502111.294724894 
    or id &lt; 24
  )
  order by `distance` asc, `e0`.`id` desc limit 6
  </code></pre>
  <p>I too tried using a &quot;user-defined variable&quot;, but this don't work correctly. I don't sure if this method is possible, I never used a &quot;user-defined variable&quot; before (cursor: { distance: 5502111.294724894, id: 24 }):</p>
  <pre class="lang-sql prettyprint-override"><code>select *,
    @distance := ST_Distance_Sphere(e0.position, ST_GeomFromGeoJSON(
      '{&quot;type&quot;:&quot;Point&quot;,&quot;coordinates&quot;:[42.605556,-5.57]}', 1, 4326)
    ) as distance
  from `studio` as `e0`
  where (@distance &gt;= 5502111.294724894) and (@distance &gt; 5502111.294724894 or id &lt; 24)
  order by `distance` asc, `e0`.`id` desc limit 6
  </code></pre>
  <p>I have few experience with SQL, so my questions are:</p>
  <ul>
  <li>Am I forgetting any possible method that works better or more efficiently?</li>
  <li>Which of the above solutions is better or more efficient?</li>
  <li>Can the last method be valid? What could be failing with it?</li>
  </ul>