Caculate Point 50 Miles Away (North, 45% Ne, 45% Sw)

Caculate point 50 miles away (North, 45% NE, 45% SW)

Try combining ST_Project with a CTE - adjust the values of radians to the azimuth you need.

WITH j AS (
  SELECT poi::geography AS poi FROM t
)
SELECT 
  ST_AsText(ST_Project(j.poi, 80467.2, radians(90.0)),2),
  ST_AsText(ST_Project(j.poi, 80467.2, radians(45.0)),2),
  ST_AsText(ST_Project(j.poi, 80467.2, radians(180.0)),2),
  ST_AsText(ST_Project(j.poi, 80467.2, radians(135.0)),2),
  ST_AsText(ST_Project(j.poi, 80467.2, radians(270.0)),2),
  ST_AsText(ST_Project(j.poi, 80467.2, radians(225.0)),2),
  ST_AsText(ST_Project(j.poi, 80467.2, radians(360.0)),2),
  ST_AsText(ST_Project(j.poi, 80467.2, radians(315.0)),2)
FROM j;

      st_astext      |      st_astext      |    st_astext     |     st_astext      |      st_astext      |     st_astext      |    st_astext     |      st_astext      
---------------------+---------------------+------------------+--------------------+---------------------+--------------------+------------------+---------------------
 POINT(-73.05 40.71) | POINT(-73.32 41.22) | POINT(-74 39.99) | POINT(-73.33 40.2) | POINT(-74.95 40.71) | POINT(-74.67 40.2) | POINT(-74 41.43) | POINT(-74.68 41.22)
(1 Zeile)

Note: The buffer (circle) in the image is just for illustration.

How to add 2 points with distance between them (SRID = 32636)?

You could cast your geometry to geography and ST_Project it (in the azimuth you want). Doing so you can easily provide the distance in meters:

CREATE TEMPORARY TABLE test_table (name text, geo geometry(point,(32636)));
INSERT INTO test_table VALUES ('foo','SRID=32636;POINT(2076155.32235105 4828109.18280588)');

SELECT 
 ST_AsText(
  ST_Transform(
   ST_Project(
     ST_Transform(geo,4326)::geography,10,radians(45.0))::geometry,
   32636)
 )
FROM test_table;

                st_astext                 
------------------------------------------
 POINT(2076150.11319696 4828116.26815917)
(1 Zeile)

You can check the distance using ST_Distance:

SELECT 
  ST_Distance(
    ST_Transform(geo,4326)::geography,
    ST_Project(ST_Transform(geo,4326)::geography,10,radians(45.0))::geometry )
FROM test_table;

st_distance 
-------------
          10

NOTE: I'm using ST_Transform to get from your projected SRS to a lon/lat SRS, so that we can cast it to geography, otherwise we'd get an error:

SELECT geo::geography FROM test_table;

ERROR:  Only lon/lat coordinate systems are supported in geography.

Further reading: Caculate point 50 miles away (North, 45% NE, 45% SW)

QGIS & PostGIS (map points(lat and long with raduis on USA map)

First create a geometry or geography column, e.g. with AddGeometryColumn ..

SELECT AddGeometryColumn ('public','postgis_test','geom',4326,'POINT',2);

.. and then update it with the output of ST_Buffer

UPDATE postgis_test 
SET geom = ST_Buffer(ST_SetSRID(ST_MakePoint(lng,lat),4326),50, 'quad_segs=8');

Parameters of ST_Buffer in detail:

• ST_SetSRID(ST_MakePoint(lng,lat),4326) : As you table had no geometry or geography column I used the function ST_MakePoint to create one. The value 4326 corresponds to the SRS WGS84 - check which one suits your coordinates.
• 5 : radius of the buffer in degrees. If the first parameter is of type geography this value is interpreted as meters.
• 'quad_segs=' : number of segments used to approximate a quarter circle (text from the documentation)

After that you'll be able to import it into QGIS using the Add PostGIS Layer option.

Examples

Creating buffers with a radius of 5 degrees

CREATE TABLE public.postgis_test (city TEXT, lng NUMERIC, lat NUMERIC);
SELECT AddGeometryColumn ('public','postgis_test','geom',4326,'polygon',2);

INSERT INTO postgis_test 
VALUES ('New York',-74.00,40.71),
       ('Los Angeles',-118.24,34.05),
       ('Chicago',-87,41.87);

UPDATE postgis_test 
SET geom = ST_Buffer(ST_SetSRID(ST_MakePoint(lng,lat),4326),5, 'quad_segs=8');

Visualizing buffers in QGIS

If you want to display both points and buffers at the same time, you have to create an extra layer (table):

CREATE TABLE public.postgis_test (city TEXT, lng NUMERIC, lat NUMERIC);
SELECT AddGeometryColumn ('public','postgis_test','geom',4326,'point',2);

INSERT INTO postgis_test 
VALUES ('New York',-74.00,40.71),
       ('Los Angeles',-118.24,34.05),
       ('Chicago',-87,41.87);

UPDATE postgis_test SET geom = ST_SetSRID(ST_MakePoint(lng,lat),4326);

CREATE TABLE buffers AS
SELECT city, ST_Buffer(geom,5, 'quad_segs=8') 
FROM postgis_test;

If you prefer to work with meters just cast the geometry column to geography and pass the parameter in meters.

Creating buffers of 100 miles (~160934 meters)

CREATE TABLE public.postgis_test (city TEXT, lng NUMERIC, lat NUMERIC);
SELECT AddGeometryColumn ('public','postgis_test','geom',4326,'point',2);

INSERT INTO postgis_test 
VALUES ('New York',-74.00,40.71),
       ('Los Angeles',-118.24,34.05),
       ('Chicago',-87,41.87);

UPDATE postgis_test SET geom = ST_SetSRID(ST_MakePoint(lng,lat),4326);

CREATE TABLE buffers AS
SELECT city, ST_Buffer(geom::geography,160934, 'quad_segs=8')::geometry 
FROM postgis_test;

Further reading:

• ST_SetSRID
• World Borders Dataset
• ST_MakePoint

---

Related Topics