0

Determining a polygon as follows, and the points to check against, in ruby

@poly = GEOFACTORY.polygon(@line_string1)
@locations = Location.all
@valid_locations = Location.order("id").where("st_intersects(ST_GeomFromText(@poly), @locations.lonlat)").all

@poly returns POLYGON ((9.0395777869 45.6141806128, 9.0395777869 45.3141806128, 9.3395777869 45.3141806128, 9.3395777869 45.6141806128, 9.0395777869 45.6141806128)) and Location.lonlat is defined with :srid => 3785. Similar queries that check against database column data run properly, however this query states

PG::UndefinedColumn: ERROR:  column "poly" does not exist

Is it possible to query postGIS with arbitrary data? If so, where is this query off track?

Improve this question

1 Answer 1

Reset to default
1

You're not interpolating the Ruby variables into the string you pass to Postgres. In other words, the WHERE query doesn't know what "@poly" means, because it doesn't have access to your running Ruby environment.

Either:

  1. Interpolate the variables into the string, e.g. #{@poly}, or
  2. Use placeholders

How exactly you do this will depend on the syntax of the ORM you're using, but you haven't specified what it is (ActiveRecord?). It may be more of a general Ruby question than a GIS-specific one.

Improve this answer
5
  • I should have caught that, but I guess I'm getting tripped up between ruby, rgeo and straight sql. The interpolation jump started (via ActiveRecord), however PG did not like it too much, pointing at the space between lon and lat (chich I find curious as that is the syntax normally): PG::SyntaxError: ERROR: syntax error at or near "45.6141806128" LINE 1: ...es" WHERE (st_intersects((POLYGON ((9.0395777869 45.6141806...
    – Jerome
    Commented Aug 25, 2015 at 12:03
  • Removing ST_GeomFromText does not alter the error.
    – Jerome
    Commented Aug 25, 2015 at 12:06
  • st_intersects((POLYGON ((9.0395777869... isn't valid SQL - it should be ST_Intersects(ST_GeomFromText('POLYGON(9.0395777869 45.614... and so on. It would help if you could post the full SQL being generated.
    – Richard Fairhurst
    Commented Aug 25, 2015 at 14:21
  • Thanks Richard. I happend to have picked up on the single quote matter a while ago, viewing good examples. I did not have the CAPS correct. That did not impact the next hurdle PG::InternalError: ERROR: Operation on mixed SRID geometries : SELECT "locations".* FROM "locations" WHERE (ST_intersects(ST_GeomFromText('POLYGON ((9.0395777869 45.6141806128, [...] 9.0395777869 45.6141806128))'), locations.lonlat))
    – Jerome
    Commented Aug 25, 2015 at 14:36
  • "ST_intersects(ST_GeomFromText('#{@poly}', 3785), locations.lonlat)" for anyone who is following is the proper string for the where command in rails
    – Jerome
    Commented Aug 25, 2015 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.