Store Locator | Geolocation | Location Search | using PostgreSQL GiST

[JayShoe]

Summary

This feature exposes the PostgreSQL GIST functionality via Global Parameters and Filters enabling users and administrators to search and sort items by distance from a point.

Basic Example

In this basic example, we are calling an API to reveal a list of items that are located within the distance of the coordinates specified.

{
	"geolocate": {
             "coordinates": [
              -74.8757947,
              39.9969954
             ]
             "distance": [
              50
             ]
	}
}

Motivation

Sometimes it makes sense to search items geospatially. This is useful for the following reasons.

• Store Locator to determine the closest store to a user.
• Warehouse Locator to determine the closest warehouse from a customer.
• Business Directory with the ability to find businesses by location.
• Social Apps, to find friends located nearby.
• Dating Apps, to find potential mates nearby.
• Transportation Apps, to find a carshare or food delivery service nearby.
• Sales Prospecting to find customers in a particular region and to plan sales visits.

The easiest example to consider is a Store Locator. On a retailers website, users want to search by zip code and receive a list of stores sorted by distance from that location. In this example the front end would accept an input for zip code. The front end will will determine the zip code's latitude and longitude information via an external service such as Google maps API, or potentially via a zip code database which would be loaded into the Directus database (separately configured by the website administrator, not this extension). Ultimately, the front end will submit the latitude and longitude for the point of interest. The directus API will return a list of items that have latitude and longitude data within x miles or kilometers from the initial point.

Detailed Design

I think this can be done as an extension to extend the filter/search capabilities of Directus. I hope to get input from the Directus team on how to maintain filtering standards so that the extension maintains high compatibility with the Directus ecosystem.

The Directus API must take lat/long values as an input, and return a list of objects sorted by distance from that original location. The data being queried must have Lat/Long values already populated in the data model (Map Field). No geocoding is being suggested for this extension. All Lat/Long conversions are done by the user or front end developer. Geocoding is another feature altogether.

PostgreSQL GIST has geo-spacial search functionality built it, we are using some of those features in the Directus Admin to show maps and filter items by boundaries. This feature will expose some of these features in the Directus API (via an extension) so that we can submit a latitude and longitude and it will return items sorted by distance.

Requirements List

Must Have:

• Filter Items by distance from point
• specify the distance to search (IE 50 miles)
• specify whether to sort from closest to farthest or farthest to closest.

Drawbacks

This will not work with MySQL because GIST is required and only installed with PostgreSQL + GIST. Otherwise this should be a non-breaking change.

Alternatives

This can be done using an external API such as google distance matrix. This would require fees, The PostgreSQL GIST implementation is "free" with the database engine. Being highly integrated with the Directus API would enable a slew of opportunities for application developers.

Adoption Strategy

Hopefully the filter parameters can maintain the same formatting and style as the rest of the filters. This should not be a breaking change, just a new feature.

Unresolved Questions

None at this time. This feature is open to discussion.

[BlackDahlia313]

I have a need for this use-case. We have a store locator currently that needs to be ported and I was trying to figure out how to do this. I would need a zipcode db. I see this which is neat but doesnt have attached zip codes:

https://github.com/kelvins/US-Cities-Database

[JayShoe]

Hello,

There are other zip code databases that can be used to search for a zip code. You can use Google maps API for this, or purchase a ZIP code database. There must be an open source model? Why did that link delete the zip codes? Maybe it was out of date.

I don't envision this feature handling this part of the equation. The system should accept a lat/long coordinate, and then find other items close to that location, sorted by distance.

If you load up a map view in Directus you will see that you can search by bounding box. This feature uses similar search as provided in the GIST functions of a PostgreSQL database.

Finding and converting the zip code to lat/long is a separate feature. Focusing on the core "search by distance from point" functionality will make this possible via numerous implementation methods in the future.

Jay

[mahendraHegde]

absolutely needed feature!!! i tried a quick POC with extensions, it is possible to build filter by distance as extension but sorting by distance is tricky as ST_Distance needs to be calculated on filtered results, directus only accepts the relation fields as sort option at the moment, so seems like only modifying the directus can provide sort feature.
I'd love to create PR if directus team is willing to accept. i feel its cleaner to implement this as part of directus core filter feature than an extension.

[JayShoe]

@mahendraHegde we need to get 15+ upvotes by April 14th for this to be considered as a target feature (if you are reading this, please consider giving us an upvote). I wonder why this post isn't getting traction. I'm pretty confident this is something a large percentage of users will (eventually) need. Does this fit into the Directus 80/20 rule? I tried to give enough detail to this feature description, but I also believe it's somewhat of an obvious one when it comes to store locators. Maybe I'm wrong and there is an angle I've not considered yet. Is the post title here accurately described?

If you have the capabilities of helping to map out the "Detailed Design" portion of this post, I think it would help define it better and maybe we can get more interest. Maybe you can share your POC code here so we can review. If we need to modify core in order to get sorting by distance, I think we need to further define the sort limitations of this feature. I've seen some discussion elsewhere about this issue. It might be a breaking change to the definition of what a sort is in Directus, unfortunately. What other benefits would this change make to the sort options? Are there any other feature requests or issues that can be referenced regarding sorting that would tie into this? How can we define this sort option in a way that benefits other use cases? How can we minimize breaking changes? I'm curious to know more.

And yes, if some of the Directus team is willing to discuss that would also help.

[JayShoe]

I found some other issues that may or may not relate to changes in sort.
#20318
#20407
#3240
#3054
#3874

[br41nslug]

I wonder why this post isn't getting traction. I'm pretty confident this is something a large percentage of users will (eventually) need.

This is a relatively obscure feature only supported by 2 of the supported database vendors (postgres/sqlite) which both require a plugin to work. So I'm assuming a large part of the users either doesn't have a compatible setup or is not aware this is possible.

[JayShoe]

Are you suggesting the use-case is obscure or that the technology that allows for geo-search is obscure because it's only supported on a few database engines? Since Postgres is the recommended database engine for Directus (AFAIK), I think it's safe to say it's pretty available to users.

[br41nslug]

Geo search as a feature for e-commerce websites is not uncommon however geo-features in Directus are relatively obscure. There are also plenty of non e-commerce use-cases with Directus for which geo location may not be applicable at all.

Just trying to add some context to your question about why this isn't getting any traction.

Since Postgres is the recommended database engine for Directus (AFAIK), I think it's safe to say it's pretty available to users.

Postgres is the quickstart example but far from the only engine used and still requires a plugin for geo-features to be properly supported.

[JayShoe]

@br41nslug IDK the plugin is pretty easy to add, but point noted. After all the mapbox integration in Directus is using these features already so it's already a dependency for some geospatial stuff. The postgis database image is widely available. The Pro Cloud and Enterprise editions support it. But again, I understand and appreciate your thoughts. Thank you for your input.

@Oreilles and @Nitwel since you were involved with the maps integration, I wonder if you had any input on this search/filter/sort concept?