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?

Aleksandr Dezhin Aleksandr Dezhin · Accepted Answer · 2012-10-10T15:30:20

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)

PostGIS: Finding points within certain radius from other points

1 Answers