How use st_buffer function in ecto query in postgis?

Question

I try do query in postgis like this

    from(r in Region,
          select: count(r.ogc_fid),
          where:
            st_intersects(
              r.region_geometry,
              fragment(
                "ST_Buffer(ST_GeomFromText('POINT(? ?)', '4326'), ?, 'quad_segs=2')",
                ^long,
                ^lat,
                0.05
              )
            )
        )
        |> Repo.one() > 0

but this doesn`t work because parameters inside quotes invisible for pg..

I try like

point = %Geo.Point{coordinates: {long, lat}, srid: 4326}

from(r in Region,
  select: count(r.ogc_fid),
  where: st_intersects(r.region_geometry, st_buffer(^point, 0.05))
)
|> Repo.one() > 0

but this throw error: Postgrex expected a binary, got %Geo.Point{coordinates: {34.427798809874055, 53.26427478941554}, srid: 4326, properties: %{}}.

How use st_buffer function in ecto query in postgis?

Answer 1

I can't quite follow what you're doing but from the error message I'd guess you need to use a different value for point.

Again, not familiar with the interface you're trying to call but given the error message I think you might find something if you tried this:

point = "34.427798809874055, 53.26427478941554"

from(r in Region,
  select: count(r.ogc_fid),
  where: st_intersects(r.region_geometry, st_buffer(^point, 0.05))
)
|> Repo.one() > 0

It may not (and probably won't) fix your problem but it might give you some sort of clue about what's wrong.