Snowflake Get MIN/MAX values from Nested JSON

Question

I have the following sample structure in snowflake column, indicating a series of coordinates for a map polygon. Each pair of values is formed of "longitude, latitude" like this:

COLUMN ZONE

{
  "coordinates": [
    [
      [
        -58.467372,
        -34.557908
      ],
      [
        -58.457565,
        -34.569341
      ],
      [
        -58.446836,
        -34.573511
      ],
      [
        -58.43482,
        -34.553367
      ],
      [
        -58.441944,
        -34.547923
      ]
    ]
  ],
  "type": "POLYGON"
}

I need to get the smallest longitud and the smallest latitude and the biggest longitud and latitude for every table row. So, for this example, I need to get something like:

MIN: -58.467372,-34.573511 MAX: -58.43482,-34.547923

Do you guys know if this is possible using a query?

I got as far as to navigate the json to get the sets of coordinates, but I'm not sure how to proceed from there. I tried doing a MIN to the coordinates column, but I'm not sure how to reference only the "latitude" or "longitude" value.

This obviously doesn't work:

MIN(ZONE['coordinates'][0])

Any suggestions?

user_132438 · Answer

You can do this with some Snowflake GIS functions, and massaging the input data for an easier parsing:

with data as ( 
select '
{
  "coordinates": [
    [
      [
        -58.467372,
        -34.557908
      ],
      [
        -58.457565,
        -34.569341
      ],
      [
        -58.446836,
        -34.573511
      ],
      [
        -58.43482,
        -34.553367
      ],
      [
        -58.441944,
        -34.547923
      ]
    ]
  ],
  "type": "POLYGON"
}
' x
)


select st_xmin(g), st_xmax(g), st_ymin(g), st_ymax(g)
from (
    select to_geography(replace(x, 'POLYGON', 'MultiLineString')) g
    from data
)

Snowflake Get MIN/MAX values from Nested JSON

1 Answers