CODEWITHSUNDEEP
geolocationsnowflake-cloud-data-platformgeospatial
Fraser G.S.
Fraser G.S.

Reputation: 15

How do I convert a list of long/lat coordinates in a row to a polygon?

I have a table with three columns, like the below:

Timezone_Name Longitude Latitude
America/Chicago 151.2095611 -31.8862892
America/Chicago 152.2095611 -32.8862892
America/Cancun 150.2095611 -34.8862892
America/Cancun 149.2095611 -35.8862892

I have loaded this data into Snowflake as a string. The coordinates field contains pairs of longitude/latitude coordinates, and when these points are combined they represent a polygon for each time zone.

Separately, I have also have a table mapping users to longitude/latitude coordinates. What I'd like to do is convert the coordinates in the table above into a polygon, then use the ST_COVERS function (here: https://docs.snowflake.com/en/sql-reference/functions/st_contains.html) with my user's long/lat coordinates to figure out what time zone they're in.

What I've Tried

Select Timezone_Name
,ST_COLLECT(ST_MAKEPOINT(table.longitude, table.latitude)) as coordinate_points
from table
group by 1

The code above works, and creates a set of geographic points for each timezone. However, when I try to call ST_MAKEPOLYGON on the coordinate_points field, I get an error saying:

"Type MultiPoint is not supported as argument to ST_MAKEPOLYGON"

What am I missing? I thought by calling ST_MAKEPOINT, it was returning a GEOGRAPHY data type?

Upvotes: 0

Views: 1961

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26078

So there are some important thing to point out, for a polygon the order of the points matters, and here we can see the two generations had different orders, AND the first point is supposed to be the last point, so that the loop is closed.

So if you are using "square" corners, you can build a valid polygon like so, I have altered you data to suit my demo needs, because while converting from your data to this, it was more pain than I need.

with data(tz_name, lon1, lat1, lon2, lat2) as (
    select * from values
    ('America/Chicago', '151.2095611', '-31.8862892', '152.2095611', '-32.8862892'),
    ('America/Cancun', '150.2095611', '-34.8862892', '149.2095611', '-35.8862892')
)
select tz_name
    ,'LINESTRING('||lon1||' '||lat1||', '||lon2||' '||lat1||', '||lon2||' '||lat2||', '||lon1||' '||lat2||', '||lon1||' '||lat1||')' as line_str
    ,to_geography(line_str) as g_ls
    ,st_makepolygon(g_ls) as poly
from data;

If you have the orders vertex's of your polygon, but the set is not closed, you can grab the last and place it first, like so:

with data(tz_name, _order, lon, lat) as (
    select * from values
    ('America/Chicago', 1, '151.2095611', '-31.8862892'),
    ('America/Chicago', 2, '152.2095611', '-31.8862892'),
    ('America/Chicago', 3, '152.2095611', '-32.8862892'),
    ('America/Chicago', 4, '151.2095611', '-32.8862892')
)
select 
    tz_name
    ,listagg(part,', ') within group (order by _order) as p_str
    ,'LINESTRING('||p_str||')' as line_str
    ,to_geography(line_str) as g_ls
    ,st_makepolygon(g_ls) as poly
from (
    select 
        tz_name, 
        _order, 
        lon, 
        lat,
        last_value(lon)over(partition by tz_name order by _order) as l_lon,
        last_value(lat)over(partition by tz_name order by _order) as l_lat,
        iff(_order = 1, l_lon ||' '|| l_lat ||', '|| lon ||' '|| lat, lon ||' '|| lat) as part
    from data
)
group by 1

Upvotes: 1

Related Questions