1

is there a way to reproduce ST_Translate using BigQuery, I am trying to move a polygons by 10 degrees longitude and latitude

Mim
  • 999
  • 10
  • 32
  • Please clarify - do you know how to calculate new lon/lat of just point that moved `10 degrees longitude and latitude` and your question is more about how to extend this to polygon? – Mikhail Berlyant Apr 09 '21 at 19:45
  • I don't know even for point – Mim Apr 09 '21 at 23:02
  • 1
    So, I suggest to start with just single point. Do a little googling and most likely you get solution that can be easily expressed in sql. Then go with multipoints, then with polygon and so on. Splitting “complex” problem to multiple simple and addressing them step by step usually helps! While trying above approach, you might experience specific issues - then it will be more appropriate to ask question(s). Specific rather than generic question! – Mikhail Berlyant Apr 10 '21 at 01:47
  • thanks, that's good, can you write that as an answer – Mim Apr 10 '21 at 02:57
  • 1
    I prefer to leave it as a comment o:) – Mikhail Berlyant Apr 10 '21 at 02:58

1 Answers1

0

Can you explain details of what you want to achieve by moving polygon 10 degrees longitude and latitude? This would affect the possible solution.

The issue with ST_Translate - it makes sense on flat map only. On a flat map, you move a shape - it stays exactly the same thing, only in a different place. So PostGIS implements it for flat Geometry type, but does not implement for spherical Geography type.

But BigQuery only has spherical Geography type, so you are asking for a new functionality. And on a sphere, it is complicated.

If you do point-wise "moving" on a sphere, it distorts shape. Moving a shape towards poles and shrinks it, and moving toward equator makes it bigger. E.g. we start with POLYGON((0 40, 20 40, 10 50, 0 40)) and "move" North to POLYGON((0 50, 20 50, 10 60, 0 50)), the polygon areas are 9.08e11 vs 7.61e11 - the polygon shrank 16%.

Alternatively, you can move whole shape by preserving its shape, by "rotating" it around the center of the Earth, it is a very different transformation.

How would you use ST_Translate, and what semantics do you need?

Michael Entin
  • 7,189
  • 3
  • 21
  • 26
  • my use case is a bit strange, I am trying to move Australia into arbitrary places on the glob to compare the area size, it is just a visual dashboard :) – Mim Apr 11 '21 at 23:30
  • 1
    For that you definitely want second option, to keep area size. This is usually done via quaternion rotation on a sphere, see e.g. https://stackoverflow.com/questions/4023161/whats-a-quaternion-rotation. But nothing native in BigQuery. Maybe try a javascript UDF. – Michael Entin Apr 12 '21 at 08:06
  • see here as an example, it works very well when I change x, but y generate distortion https://datastudio.google.com/u/0/reporting/d521c66d-2a20-46b9-86a0-a737a2b29775/page/8UdCC – Mim Apr 12 '21 at 09:24