Calling PostGIS functions within Ruby

Question

I need to execute the PostGIS function st_intersection within an SQL SELECT clause in Ruby. At the moment I am doing it as raw SQL query:

sql_query = "SELECT id, ST_ASEWKT(ST_INTERSECTION(geometry, ?)) FROM trips WHERE status='active';"
intersections = Trip.execute_sql(sql_query, self[:geometry].to_s)

This way has the disadvantage that I receive the result as text and I need to parse the objects out of the strings. Much nicer would be the use of the ActiveRecord interface to make queries. However, I could not find any solution yet to run PostGIS functions (e.g. st_intersection) within ActiveRecord.

An earlier version of the activerecord-postgis-adapter's README showed a nice example using the gem squeel:

my_polygon = get_my_polygon    # Obtain the polygon as an RGeo geometry
MySpatialTable.where{st_intersects(lonlat, my_polygon)}.first

As this is not part of the current README anymore, I am wondering whether this is not recommended or if there are any better alternatives.

Answer 1

There are two problems to solve here.

The first is using an SQL function within a .select clause. Ordinarily this is pretty easy—you just use AS to give the result a name. Here's an example from the ActiveRecord Rails Guide:

Order.select("date(created_at) as ordered_date, sum(price) as total_price").group("date(created_at)")

The resulting Order objects would have ordered_date and total_price attributes.

This brings us to the second problem, which is that Rails doesn't give us an easy way to parameterize a select (i.e. use a ? placeholder), so (as far as I can tell) you'll need to do it yourself with sanitize_sql_array:

sql_for_select_intersection = sanitize_sql_array([
  "ST_ASEWKT(ST_INTERSECTION(geometry, ?)) AS intersection",
  geometry,
])

This will return a sanitized SQL fragment like ST_ASEWKT(ST_INTERSECTION(geometry, '...')), which you can then use to specify a field in select:

Trip.where(status: "active").select(:id, sql_for_select_intersection))

The resulting query will return Trip objects with id and intersection attributes.