Radius search with filter variables

Get Help
1

Hello

I am trying to work out the PostgresSQL syntax to return all rows within X radius of Y location, where X and Y are filter variables for the user.

I have tried ST_Distance and ST_DWithin but cannot get the query to work.

Each row contains Location, Latitude and Longitude

Something like

SELECT *
FROM table
WHERE {{Location}} WITHIN {{radius}}

Can anyone help?

Thanks!

2

Hi @r989ll
I'm unsure which variable type you're using, but if it is a Field Filter, then you cannot do that:
https://www.metabase.com/learn/building-analytics/sql-templates/field-filters.html
You would have to use simple Number filters.

3

Thanks @flamber

I was planning to use Number filter for the radius and Category field filter for Location (the field is a formatted address).

However I don’t know how to calculate the radius from the Location using the coordinates.

4

@r989ll You cannot use Field Filters like that. Metabase controls the SQL generated. You would have to do it in a different way, but it would help if you wrote the query without variables (just a hardcoded value), since it's easier to see what is possible then.