cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ApocryphalApe
Level: Powered On

Filtering a gallery by distance?

Hello,

 

I'm using SQL Server as a data source. My powerapp is displaying items in a gallery. These items are being pulled from a view.

 

I want to be able to filter the gallery by distance less than or equal to a user entered location. I have a table in my SQL server that contains the latitude and longitude of every city and zip in the US. I can write either a scalar or table valued function to calculate distance using the haversine formula. Not sure which would be the best in this use case.

 

My half baked idea is that I could create a function and then add it to the view, and pass the location entered into powerapps into the parameter in my function.

 

How would I implement this such that I had a delegable query?

 

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Filtering a gallery by distance?

Hi @ApocryphalApe ,

Could you please share a bit more about your scenario?

Do you want to calcualte the distance between the user's current location and the location user entered, then compare this calculated distance within your SQL Table, to filter these records whose are close to the end user?

 

Based on the needs that you mentioned, I think the Bing Maps connector could achieve your needs. Within your app, you could use the BingMaps.GetRoute() function to calculate the distance between two different locations. The sample formula as below:

BingMaps.GetRoute("35.05321,-93.367623", "Space Needle, Seattle",{distanceUnit:"Kilometer",optimize:"distance"}).travelDistance

1.JPG

 

On your side, you should take a try with the following workaround:

Set the Items property of the Gallery to to following:

Filter(
         '[dbo].[YourSQLTable]',
         BingMaps.GetRoute(Location.Latitude&","&Location.Longitude, LatitudeColumn&","&LongitudeColumn,{distanceUnit:"Kilometer",optimize:"distance"}).travelDistance <= BingMaps.GetRoute(Location.Latitude&","&Location.Longitude, LocationTextBox.Text, {distanceUnit:"Kilometer",optimize:"distance"}).travelDistance
)

Note: The LatitudeColumn and LongitudeColumn are columns in your SQL Table, which used to store the  latitude and longitude value or every city.  The LocationTextBox represents the Text box in your app where the end user could enter a location manually.

 

More details about Bing Maps connector in PowerApps, please check the following article:

https://docs.microsoft.com/en-us/connectors/bingmaps/

 

Please take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ApocryphalApe
Level: Powered On

Re: Filtering a gallery by distance?

Hello,

 

Thank you for the reply. The records we're working with are job postings. I want to return all records within a maximum search radius.

 

I want the user to enter:

 

1. Origin location

2. Max distance

 

The locations are stored in a record. The location latitude / longitude is stored in a separate table in SQL. I want to

then return records that are within the user specified distance from the user specified location.

 

I'm sure Bing maps will work, but I want to know how to do this using a delegable SQL query as I have other uses for this approach.

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,808)