google-bigquery - 将 PostGis 几何转换为 BigQuery 地理

Question

我有一个 Geometry (看起来像 WKB) 我的 postgres/postgis 数据库中的对象，我已经转储到 BigQuery 来做一些有趣的事情。我看到它存储为 bytes .我如何构建一个 GEOGRAPHY从这个对象？

SELECT
ST_GEOGFROMWKB(B"010300000001000000100000006E8F34200ED453C035455D8E58DA4540FFFFFFD325D453C01128368CD7DA4540FFFFFFF7D4D353C0762E21CA9FDB454001000078BED353C0762E21CA9FDB4540FDFFFF830CD453C03240CE4CC8DA45400100009000D453C092AD5B945EDA4540E6C706D102D453C0CCC3FF605DDA454009707A17EFD253C0D0B9DBF5D2DA4540B134F0A31AD253C09D82FC6CE4D645409702D2FE07D253C0E275FD82DDD645401C23D923D4D153C05471E316F3D545401346B3B27DD653C0AE2990D959D2454093C6681D55DB53C0AF05BD3786D04540FD2FD7A205DD53C01F2BF86D88D14540F9BD4D7FF6DD53C04F90D8EE1ED645406E8F34200ED453C035455D8E58DA4540")
;

抛出的错误是:

ST_GeogFromWKB failed: Invalid WKB byte order '48'; input might be HEX-encoded string that should be decoded first; during WKB parsing 

这很有帮助，但 WKB 需要 bytes而不是 string ，那么我什么时候进行十六进制解码？

Answer 1

下面是 BigQuery 标准 SQL

#standardSQL
SELECT
  ST_GEOGFROMWKB(FROM_HEX("010300000001000000100000006E8F34200ED453C035455D8E58DA4540FFFFFFD325D453C01128368CD7DA4540FFFFFFF7D4D353C0762E21CA9FDB454001000078BED353C0762E21CA9FDB4540FDFFFF830CD453C03240CE4CC8DA45400100009000D453C092AD5B945EDA4540E6C706D102D453C0CCC3FF605DDA454009707A17EFD253C0D0B9DBF5D2DA4540B134F0A31AD253C09D82FC6CE4D645409702D2FE07D253C0E275FD82DDD645401C23D923D4D153C05471E316F3D545401346B3B27DD653C0AE2990D959D2454093C6681D55DB53C0AF05BD3786D04540FD2FD7A205DD53C01F2BF86D88D14540F9BD4D7FF6DD53C04F90D8EE1ED645406E8F34200ED453C035455D8E58DA4540")) AS geo

哪个(如果要运行)返回

POLYGON((-79.46817 43.672819, -79.453469 43.636976, -79.42707 43.629096, -79.351422 43.643367, -79.278573 43.671481, -79.281738 43.678635, -79.282876 43.678846, -79.295843 43.709563, -79.3126719061107 43.7059746979572, -79.3125343322754 43.7060113380095, -79.3132638931274 43.709237671574, -79.308500289917 43.7158138906497, -79.3098735809326 43.7158138906497, -79.31480884552 43.709702993816, -79.3133621705326 43.7058275180265, -79.46817 43.672819))  

可视化为