postgis - What approach should I use in PostGIS to find all services that have a radius that intersect a customer

Question

I'm new to PostGIS and looking for suggestions on a reasonable approach to solve the following problem. I have services that have a lat/lng and service radius (an area that the service is available in). I have customers that just have a lat/lng. I'd like to setup up a table(s) that support indexed queries that return all services that have a service areas where the users location is contained.

Service
- latitude
- longitude
- radius
- type

Customer
- latitude
- longitude

• Bonus points if the query is easy to extend to filter services by other attributes as well *

标签： postgis

Answer 1

You can create and index geography columns in both tables, then you can make use of ST_DWITHIN() which makes use of the indexes

SELECT service.*
FROM service
JOIN customer
 ON ST_DWITHIN(service.geog, customer.geom, service.radius)
WHERE customer.id=123
 AND service.type='abc';