PostGIS: Finding points within certain radius from other points

Question

I have a table in PostgreSQL/PostGIS named trip with two geometry columns: source_geom (POINT) and destination_geom (POINT) indicating starting and ending location of a journey.

I have one more separate table named business with geometry column office_geom (POINT) that indicates the location of offices.

My aim is to select the records from the table trip whose destination is within 1000 meters from any of the office location.

What query do I need to fire to get the results that I require?

Answer 1

It can be done using subquery or joins. Example using subquery:

SELECT * FROM business 
WHERE EXISTS(
    SELECT 1 FROM trip
    WHERE ST_Distance_Sphere(trip.destination_geom, business.office_geom) < 1000
)

But this query will not use indexes and can take a long time on big datasets. If you need this, you can create geography columns from geometry, create spatial indexes on geography columns, and use ST_DWithin:

select * 
  from business b
  join trip t on ST_DWithin(trip.destination_geogr, business.office_geogr, 1000)