2

While most SQL databases have in-built projection tools (usually st_transform), BigQuery does not. I'm looking for a SQL query that takes decimal degrees in 4326 and projects them into the meter-based 3857. I have searched SO and the internet, and similar questions usually pertain to people having issues using tools like GDAL, rather than the pure expression itself, which is what I'm looking for. Can anyone share one?

Encomium
  • 3,133
  • 2
  • 14
  • 41
  • 1
    What languages can you use to extend "BigQuery"? - you aren't going to get a pure SQL expression to do the reprojection – Ian Turton May 04 '21 at 15:55
  • Would you mind elaborating on that, to help me understand? What is it that SQL can't handle natively? – Encomium May 04 '21 at 19:37

1 Answers1

2

I don't know the BigQuery dialect (if any?), but this is standard SQL:

SELECT 6378137.0 * <lon> * ACOS(-1)/180.0 AS x,
       6378137.0 * LN( TAN( (ACOS(-1) * 0.25) + (0.5 * <lat> * ACOS(-1) / 180.0) ) ) AS y
;

with

  • 6378137.0 = <radius_of_auxiliary_sphere>
  • ACOS(-1) = PI
  • <lonlat_in_degree> * PI/180.0 = <lonlat_in_radians>

However, in order to comply with the projection bounds, and to avoid going infinity at the poles, you have to clip the results to +/-20026376.39 | +/-20048966.10, e.g. with

SELECT LEAST(GREATEST(x, -20026376.39), 20026376.39) AS x,
       LEAST(GREATEST(y, -20048966.10), 20048966.10) AS y
FROM   (
  SELECT 6378137.0 * <lon> * ACOS(-1)/180.0 AS x,
         6378137.0 * LN( TAN( (ACOS(-1) * 0.25) + (0.5 * <lat> * ACOS(-1) / 180.0) ) ) AS y
  -- FROM   <exp>
) AS q

or drop outliers e.g. in a WHERE filter.

geozelot
  • 30,050
  • 4
  • 32
  • 56