Syntax error in spatial query?

Question

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 ?

sameera207 · Accepted Answer · 2018-08-23 14:56:21Z

1

Since you are using standard sql to build the query, (not the ActiveRecord), you will have to use the standard IN clues with where

It looks like pois.ids is returning an array, so, you will have to turn it to a string in the format as below

[1,2] #=> (1,2)

Change,

WHERE way.id = #{self.id}
    and pta.id = #{poi.id}
    and ptb.id = #{pois.ids}

to

WHERE way.id = #{self.id}
    and pta.id = #{poi.id}
    and ptb.id IN (#{pois.ids.join(',')})

answered Aug 23, 2018 at 14:56

sameera207

16.6k20 gold badges92 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Grzegorz Grabek · Accepted Answer · 2018-08-23 23:27:55Z

0

You can change pois.ids as @semeera207 wrote to string or go another way and compare ptb.id to pois.ids as an array.

  WHERE way.id = #{self.id}
and pta.id = #{poi.id}
and array[ptb.id] && #{pois.ids}

To make it faster create gin index

Create index on pois using gin((array[id]));

edited Aug 23, 2018 at 23:27

answered Aug 23, 2018 at 19:25

Grzegorz Grabek

1,0007 silver badges16 bronze badges

2 Comments

Ben Over a year ago

Thanks sameera207 and Grzegorz Grabek for your help. I know gist index but no gin index.... I don't know how it work. Could you explain ?

Grzegorz Grabek Over a year ago

@Ben Best info is here postgresql.org/docs/9.6/static/gin.html

Collectives™ on Stack Overflow

Syntax error in spatial query?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related