1

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.

3
  • Please do not delete and repost questions.
    – Vince
    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 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.
    – Vince
    Nov 10, 2017 at 15:23

1 Answer 1

1

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;
2
  • Quick question can i use it with ST_Centroid? 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. Nov 10, 2017 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.