postgis - Given a lat/long find all points within a certain radius

I am looking to find multiple lat/long points (N,S,E,W) that are 100 metres from an input lat/lon point I have stored in PostGIS.

So, given a latitude and longitude I'd like to find the latitude and longitude point that is 100 metres North, 100 metres South etc.

I don't have anything but the input lat/lon values stored in the database, so SELECTing from PostGIS will not work in this case.

According to @ThingumaBob, there is a way to find these points with PostGIS. Could someone kindly alert me as to how I would do this?

Answer

To create 4 Points, one in each direction (NESW) at 100m distance to each input point, you can use ST_Project:

SELECT ip. AS id,
       dir.arr[n+1] as direction,
       ST_AsText(ST_Project(ip.::geography, 100.0, radians(90.0*n))) AS geom
FROM  AS ip CROSS JOIN generate_series(0, 3) AS n,

     (SELECT ARRAY['N', 'E', 'S', 'W'] AS arr) AS dir

I used generate_series to auto-increment the bearing and select the direction letter from the array.

This should return 4 points with their direction, for each point in , with the respective ids from their . It assumes your input points are georeferenced in EPSG:4326 (WGS84)!

Note: this returns the new point's geometries in WKT format (via ST_AsText(...)) so you can read their LatLng values. This doesn't save/store anything; if you want to use those geometries programmatically, you need to explain how you want them stored.