jeudi 23 août 2018

Syntax error in spatial query?

I wrote a function for found all pois around a track

controller :

def index
  @track = Track.friendly.find(params[:track_id])
  @tracks = Track.where(way_id: @track.id)
  @way = Way.find(1)
  @poi_start = Poi.find(@way.point_start)
  @pois = @track.pois.sleepsAndtowns
  @pois = @way.poi_around_track_from(@poi_start, 50000, @pois)
end

way.rb

def poi_around_track_from(poi, dist, pois)
  around_sql = <<-SQL
  SELECT
  ST_DWithin(
    ST_LineSubstring(
    way.path,
    ST_LineLocatePoint(way.path, pta.lonlat::geometry) + #{dist} / ST_Length(way.path::geography),
    ST_LineLocatePoint(way.path, pta.lonlat::geometry) + 100000 / ST_Length(way.path::geography)
  ),
  ptb.lonlat,
  2000) is true as pois
  FROM ways way, pois pta, pois ptb
  WHERE way.id = #{self.id}
    and pta.id = #{poi.id}
    and ptb.id = #{pois.ids}
  SQL
  Poi.find_by_sql(around_sql).pois
end

This function return : syntax error at or near "[" LINE 13: and ptb.id = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]

What's wrong, how can I fix it ?

Aucun commentaire:

Enregistrer un commentaire