PostGis nearest neighbors query with Ecto

Question

could you help me to write this kind of PostGis query with Ecto DSL?

SELECT streets.gid, streets.name
FROM
   nyc_streets streets,
   nyc_subway_stations subways
WHERE streets.geom && ST_Expand(subways.geom, 200)
ORDER BY ST_Distance(streets.geom, subways.geom) ASC;

I'm confused on how to select data from multiple tables without joining them on foreign keys.

Thanks

Answer 1

You may try to use raw queries like in this question:

How to use raw sql with ecto Repo

It will tie you to one database, but you are using very specific feature anyway. I don't think, that Ecto DSL can support queries specific to PostGis.