mjhjmtu

Background:

(Note: I am trying to do as much of this as possible in PostGIS/PostgreSQL)

I have a PostgreSQL table containing location data of an aircraft in orbit around a city. Every second a new row is added with new position data.

From this table I have created a view which shows a constantly updating point of only the current position. This view therefore contains only one row.

The Task:

I have created a custom polygon in a different table, which acts as a buffer around the current position point. I want to reposition/move this polygon to always be centred over the constantly updating current position coordinates. Does anyone know how to do this? I have already experimented with rotate and scale. I am happy with them. It is moving the polygon that is confusing me.

I have tried creating a view that uses ST_Translate for the polygon, however from my understanding, this function only moves coordinates by a fixed amount from the origin. It doesn’t allow me to specify exactly what lon lat coordinates I want it to move to. Does anyone know a function/how to move a polygon to new coordinates, centred on a point? Again, I am trying to achieve this from PostGIS.

The easiest would be to have your polygon centered on (0;0) and to use st_translate using the target point X;Y as the delta X and delta Y.

Using any polygon, you would compute the delta by removing the polygon centroid X and Y from the target point X and Y:

WITH pt AS (SELECT 'point(-75.5 47.2)'::geometry geom),
poly AS (SELECT 'polygon((-70 41, -71 41, -71 40,-70 40, -70 41))'::geometry geom)
SELECT st_asText(
 ST_TRANSLATE(
 poly.geom, 
 st_x(pt.geom) - st_x(st_centroid(poly.geom)), 
 st_y(pt.geom) - st_y(st_centroid(poly.geom))
 )
 )
FROM pt, poly;

 st_astext
---------------------------------------------------------
 POLYGON((-75 47.7,-76 47.7,-76 46.7,-75 46.7,-75 47.7))
(1 row)