Create a new point geometry from origin point geometry, distance and bearing in PostgreSQL

Question

I am trying to create a new point geometry field from this command:

UPDATE cat.datos_parcelas 
SET    arb_geom = ST_SetSRID(ST_Project(geom, arbdist, radians(arbrumbo)), 32631)::geometry;

-- geom: contains the geometry of the origin point in 32631
-- arbdist: contains the distance from the origin to the new point
-- arbrumbo: contains the bearing.

However, I get the following error:

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

Any clue how to solve this? Shall I convert first my origin geometry to lat/long?

Answer 1

I was about to add a simple custom function overload to the answer, for ST_Project to accept GEOMETRY types, but I saw that with PostGIS 3.4 the core function was updated accordingly. So going forward this distinction below will be obsolete!

---

Two options:

• Spherical translation of the Point via de-projection into geographical coordinates:

  UPDATE
    <table>
  SET
    <geom> = ST_Transform(
               ST_Project(
                 ST_Transform(<geom>, 4326)::GEOGRAPHY,
                 <dist>,
                 <bearing>
               )::GEOMETRY,
               <SRID>
             )
  ;
  

  where
  • <distance> is given in meter
  • <bearing> is the azimuth from true north given in radians

• Cartesian coordinate shift via basic algebra:

  UPDATE
    <table>
  SET
    <geom> = ST_SetSRID(
               ST_MakePoint(
                 ST_X(<geom>) + <distance> * SIN(<bearing>),
                 ST_Y(<geom>) + <distance> * COS(<bearing>)
               ),
               <SRID>
             )
  ;
  

  where
  • <distance> is given in unit of CRS
  • <bearing> is the azimuth from true north given in radians