2

Is there was a way to convert a geography value to a 3D geometry value (in ECEF, for example), and vice versa? I know how to convert between geography using a 2D projection, but not a 3D reference frame.

I could probably create a function to do this myself (using this and this, for example), but is there already a function to do so, (hopefully more general or efficient than I could write)?

2

Well, how about simply using a 3D CRS then? EPSG:4978 is an ECEF CRS based on EPSG:4326 and in widespread use as the core (3D) CRS for the GPS system.

Running

ALTER TABLE <table>
  ALTER COLUMN geom TYPE GEOMETRY(<TYPE>, 4978)
    USING ST_Transform(ST_Force3D(<geog>::GEOMETRY, 4978))
;

should give you meter based XYZ (ECEF) coordinates of your points.

  • Thank you! I did not realize that there were ones in higher dimensions. One thing I had to adjust was that ST_Force3D needs to happen before transforming, not after. – Chris King Oct 22 '19 at 16:52
  • @ChrisKing You sure? Does ST_AsText show no/0/false Zs? It's just I couldn't explain how copypaste should have failed (and it doesn't feel right), but I cannot test right now, things happen, and for all I know you are right ,) Corrected the query...if this helped you, consider an upvote (up arrow next to the answer) and/or accepting the answer (the tick next to the answer - however, customarily you'd wait some 24h so others can join and answer). – ThingumaBob Oct 22 '19 at 19:01
  • Yes, it showed as 0s with your version. Putting the ST_Force3D inside avoids this problem. – Chris King Oct 22 '19 at 19:13
  • @ChrisKing Ah man...I was under the constant impression I had both functions in the exact order that you corrected them into; I didn't see (on mobile app) that you edited my answer already, assumed then that you meant to switch ST_Force3D to be the outer function and was confused a lot; my comment above refers to a state where I believed I gave the actual correct query (you edited into) and you wanted me to change it to the actual wrong order... so yes, I did make a copy paste error in the first place... – ThingumaBob Oct 23 '19 at 11:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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