# Get my x, y, z position as 0,0,0

I have a set of data in the column geometry(geom) as WKB.

I am using ST_AsGeoJSON(ST_Transform(geom, 32633)) to transform WKB into meters.

my results are like this:

{"type":"LineString","coordinates":[[100,100,100],[300,300,300]]

these basically are x, y and z coordinates accordingly. Namely i am getting results with 3 numbers for each set

What i want to do is deduct from each set of numbers the first set. That would mean that i want to do -[100,100,100] from all the next sets, because the first one should be my position on a GPS and it should be 0,0,0.

• Please do not delete and repost questions. Commented Nov 10, 2017 at 10:35
• Well i had a message from the admins in order to clarify the question better, so that is what i did Commented Nov 10, 2017 at 12:15
• One clarifies a question by editing it in place. Making a habit of deleting and re-asking questions can generate undesirable consequences. Commented Nov 10, 2017 at 15:23

https://postgis.net/docs/ST_Translate.html lets you translate a geometry by x-, y- and z-offsets

https://postgis.net/docs/ST_StartPoint.html gives you the first point of a LineString.

https://postgis.net/docs/ST_RemovePoint.html lets you remove a point.

https://postgis.net/docs/ST_X.html gives you the X coordinate of a point. Same for _Y and _Z.

So, you need to get the start points of your LineStrings and feed their negated coordinate values to a transformation of your LineString reduced by its first point. Here is an example with lazy redundancy, slowness etc, understand it, then make it better (and post please!).

``````WITH t AS (
SELECT
ST_GeomFromText('LineString(100 100 100, 200 200 200, 300 300 300)') as geom)
SELECT
ST_AsText(
ST_Translate(
ST_RemovePoint(t.geom, 0),
-ST_X(ST_StartPoint(t.geom)),
-ST_Y(ST_StartPoint(t.geom)),
-ST_Z(ST_StartPoint(t.geom))
)
)
FROM t;
``````
• Quick question can i use it with ST_Centroid? Commented Nov 10, 2017 at 12:13
• That would be something quite different but of course you can use that function anywhere in this if needed. Commented Nov 10, 2017 at 13:50