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?

  • It sounds pretty straightforward, but can you link to the Ed Williams algorithms you reference? – JoshC Feb 28 '18 at 1:24
  • 1
    Welcome to GIS SE. As a new user, please take the Tour. Please Edit the question in response to requests for clarification. SQL questions are expected to contain SQL code. The ST_DWithin function should feature prominently within your code. – Vince Feb 28 '18 at 5:06
  • 3
    It really is a SQL question – Ian Turton Feb 28 '18 at 7:33
  • 1
    you'll need to clarify that in your answer, use the edit option; if I get this right, you are interested in simply deriving (i.e. creating) points at 100m distance from some input, in all four directions? do you want those stored in PostGIS then, or for what purpose are those points? – geozelot Mar 1 '18 at 9:50
  • 1
    now your question reads differently...do you want four points, created each 100m in NESW direction from your input points (that's what I assumed from before you edited your question), or do you want to find arbitrary points in a radius of 100m around those input points? or do you want the maximum extend in lonlat of a 100m buffer around those input points? – geozelot Mar 2 '18 at 11:30

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

SELECT ip.<id_column> AS id,
       dir.arr[n+1] as direction,
       ST_AsText(ST_Project(ip.<geometry_column>::geography, 100.0, radians(90.0*n))) AS geom
FROM <input_points_table> 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 <input_points_table>, with the respective ids from their <id_column>. 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.