PostGIS exercise : part 1

here we will have several question :

1. suppose we have 5 points (a, b, c, d, e), each having it’s own latitude and longitude, consider we have another point (x) on another latitude and longitude, find the nearest and the farthest point to x?

the nearest one

SELECT 
  LEAST(
  ST_Distance('SRID=4326;POINT(x_lat x_long)'::geometry,'SRID=4326;POINT(a_lat a_long)'::geometry),
  ST_Distance('SRID=4326;POINT(x_lat x_long)'::geometry,'SRID=4326;POINT(b_lat b_long)'::geometry),
  ST_Distance('SRID=4326;POINT(x_lat x_long)'::geometry,'SRID=4326;POINT(c_lat c_long)'::geometry),
  ST_Distance('SRID=4326;POINT(x_lat x_long)'::geometry,'SRID=4326;POINT(d_lat d_long)'::geometry),
  ST_Distance('SRID=4326;POINT(x_lat x_long)'::geometry,'SRID=4326;POINT(e_lat e_long)'::geometry)
);

the farthest one

SELECT 
  GREATEST(
  ST_Distance('SRID=4326;POINT(x_lat x_long)'::geometry,'SRID=4326;POINT(a_lat a_long)'::geometry),
  ST_Distance('SRID=4326;POINT(x_lat x_long)'::geometry,'SRID=4326;POINT(b_lat b_long)'::geometry),
  ST_Distance('SRID=4326;POINT(x_lat x_long)'::geometry,'SRID=4326;POINT(c_lat c_long)'::geometry),
  ST_Distance('SRID=4326;POINT(x_lat x_long)'::geometry,'SRID=4326;POINT(d_lat d_long)'::geometry),
  ST_Distance('SRID=4326;POINT(x_lat x_long)'::geometry,'SRID=4326;POINT(e_lat e_long)'::geometry

2. Find all points in certain radius from point x?

query bellow is one of an example finding all subway stations (which is represented by point) in 10 meter radius, you can change the ‘POINT(lat lon)’ and the radius

SELECT name
FROM nyc_subway_stations
WHERE ST_DWithin(
        geom,
     ST_Transform(ST_GeomFromText('POINT(-74.01067146887341 40.70710481558761)',4326), 26918),
        10
      );