app/services/poi_services/poi_optimized_serializer.rb
# Divides the area in a grid of squares and keep only one POI per square
Divides the area in a grid of squares and keep only one POI per square
Divides the area in a grid of squares and keep only one POI per square
def metadata_with_clustering scope
      if @box_size >= 10
        grid_size = @box_size / 15
      elsif @box_size >= 5
        grid_size = @box_size / 30
      else
        grid_size = nil
      end
      scope = scope.unscope(:select)
      projections = [
        "pois.id",
        "to_char(pois.updated_at, 'YYYYMMDDHH24MISSUS000')"
      ]
      if grid_size
        cte_projections = [
          "pois.id", "pois.updated_at",     
          "pois.latitude", "pois.longitude" 
        ]
        cte_projections[0] = %{
          distinct on (
            ST_SnapToGrid(
              #{PostgisHelper.point(:"pois.latitude", :"pois.longitude")},
              #{grid_size * 1000})
          )
          #{cte_projections[0]}
        }
        sql = %(
          with pois as (
            #{scope.unscope(:order, :limit).select(*cte_projections).to_sql}
          )
          #{scope.unscope(:where, :joins).select(*projections).to_sql}
        ).squish
      else
        sql = scope.select(*projections).to_sql
      end
      result = ActiveRecord::Base.connection.execute(sql)
      metadata = result.values
      result.clear
      metadata
    end
def metadata_with_clustering scope if @box_size >= 10 grid_size = @box_size / 15 elsif @box_size >= 5 grid_size = @box_size / 30 else grid_size = nil end scope = scope . unscope ( :select ) projections = [ "pois.id" , "to_char(pois.updated_at, 'YYYYMMDDHH24MISSUS000')" ] if grid_size cte_projections = [ "pois.id" , "pois.updated_at" , "pois.latitude" , "pois.longitude" ] cte_projections [ 0 ] = %{
          distinct on (
            ST_SnapToGrid(
              #{PostgisHelper.point(:"pois.latitude", :"pois.longitude")},
              #{grid_size * 1000})
          )
          #{cte_projections[0]}
        } sql = %(
          with pois as (
            #{scope.unscope(:order, :limit).select(*cte_projections).to_sql}
          )
          #{scope.unscope(:where, :joins).select(*projections).to_sql}
        ) . squish else sql = scope . select ( * projections ) . to_sql end result = ActiveRecord :: Base . connection . execute ( sql ) metadata = result . values result . clear metadata end
Divides the area in a grid of squares and keep only one POI per square
Divides the area in a grid of squares and keep only one POI per square
# no clustering # The sorting expression of the `distinct on` clause conflicts # with the one we want to use to sort the results globally. # To solve this, we cluster in a CTE, then sort again in the main query. # for the metadata # for the sorting by distance # prefix a `distinct on` clause to the first expression for clustering
scope
<not_specific>