Presto SQL - Transforming array(BingTile) into geometry-CodePudding

I'm trying to calculate the City area size by using Geospatial Functions like the bing_tiles_around(), geometry_union(), and st_area() with the below sample data.

City ID | Store ID | latitude | longitude | radius (km)
---------------------------------------------------------
12345   | store_01 | 36.1234  | 31.1234   | 3.11
12345   | store_02 | 36.5678  | 31.5678   | 2.52

I believe I should use the above functions to calculate the area size. As long as I understand based on this reference, the bing_tiles_around() returns an array(BingTile) and I need to transform it into geometry types to use both the geometry_union() and st_area() but I'm not sure how to transform 'array(BingTile)' into 'geometry' type.

with store_info as (
select city_id, store_id, latitude, longitude, radius,
bing_tiles_around(latitude, longitude, 10, radius) as bingTiles_around
from store_detail
)

select city_id, store_id, geometry_union(bingTiles_around)
from store_info

The above query is the current one that I'm working on and it showed me the below error when I executed it. (I know it's not correct usage but just want to share the below error message to resolve this problem)

QueryValidation: user_error: Attempt 1: presto: query failed (200 OK): 
"USER_ERROR: com.facebook.presto.sql.analyzer.SemanticException: line 17:70: 
Unexpected parameters (array(BingTile)) for function geometry_union. 
Expected: geometry_union(array(Geometry)) "

The ultimate goal is to create a sample result dataset as below.

City ID | Area_size 
--------------------
12345   | 40.12345
12346   | 50.56789

Can anyone please help me to create this table using the above information? Any help would be appreciated.

Thanks!

CodePudding user response：

bing_tiles_around returns array of BingTile while geometry_union expects array of Geometry so you need to transform one to another:

-- sample data
WITH dataset (City_ID , Store_ID , latitude , longitude , radius ) AS (
    VALUES (12345   , 'store_01' , 36.1234  , 31.1234   , 3.11),
(12345   , 'store_02' , 36.5678  , 31.5678   , 2.52)
) 

--query
select city_id,
    store_id,
    geometry_union(
        transform(bingTiles_around, t->bing_tile_polygon(t))
    )
from(
        select city_id,
            store_id,
            latitude,
            longitude,
            radius,
            bing_tiles_around(latitude, longitude, 10, radius) as bingTiles_around
        from dataset
    )