Example of a .where query using contains for srid 3857?

[Daniel Donaldson]

I need to be able to find all polygons that contain a given point.

My table looks like this:

  create_table "places", force: :cascade do |t|

    t.geometry "loc", limit: {:srid=>3857, :type=>"st_point"}

    t.geometry "poly", limit: {:srid=>3857, :type=>"st_polygon"}

    t.datetime "created_at", null: false

    t.datetime "updated_at", null: false

  end

I can successfully store data, and I can project and unproject it.

I need to have a method in the Place class like this:

def self.for_point pt

where([:poly].contains?(pt))

end

But although on an instance contains? works:

def contains? pt

self[:poly].contains?(pt)

end

the Class method doesn't. I've tried permutations in the console of 

Place.where("st_contains(poly::polygon, ST_geometryFromText('POINT(2794119.218911167,2888032.378337789)')::point)" )

But this just produces errors:

ActiveRecord::StatementInvalid (PG::UndefinedFunction: ERROR:  function st_contains(polygon, point) does not exist

Can anyone provide an example that shows how I could accomplish this? 

[Barrett Clark]

I have an example that is similar: https://github.com/DataVizToolkit/departures/blob/master/app/models/airport.rb#L10

I think the main thing is that you are passing in the wrong datatype in the first parameter, or possibly both. I don't think you need to cast the values as the specific point or polygon. I think instead the function wants a Geometry or a Geography input, so use the ST_GeographyFromText function and pass in the values for the point. I'm not sure off the top of my head how the conversion for the polygon would go, but I think you need to do something similar with that.

HTH,

~Barrett