# PostGIS function to move a polygon to centre over new point coordinates

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.

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.

• ST_Translate moves a geometry by a specified amount in x, y and, optionally, z directions. So, to calculate how much you need to move the polygon, calculate the change in x and y between your new point and the centroid of the polygon – John Powell Feb 22 at 12:12

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)
``````
• thank you so much! That worked perfectly. Really appreciate it. – Demus Feb 26 at 11:53