Run queries

After the installation done, you can start using Sedona functions. Please log in to Snowflake again using the user that has the privilege to access the database. In our example, this user is wherosnow.

Interoperate with Snowflake native functions

A good practice is to always keep at least the schema name "SEDONA" to avoid conflicting with Snowflake's built-in functions

SELECT SEDONA.ST_S2CellIDs(SEDONA.ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'), 9);

Sedona uses EWKB type as the input/output type for geometry. If you have datasets of built-in Snowflake GEOMETRY/GEOGRAPHY type, you can easily transform them into EWKB through this function.

SELECT SEDONA.ST_X(st_asewkb(geom)) FROM {{ geometry_table }};

If you wish to pass the result of Sedona functions to built-in Snowflake spatial functions, you just need to call the built-in to_geometry function. As the below example shows, you can to_geometry(sedona_result) and then call the Snowflake functions from then on.

SELECT st_area(to_geometry(SEDONA.ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')));

Create a sample table

Let's create a city_tbl that contains the locations and names of cities. Each location is a WKT string.

CREATE TABLE city_tbl (wkt STRING, city_name STRING);
INSERT INTO city_tbl(wkt, city_name) VALUES ('POINT (-122.33 47.61)', 'Seattle');
INSERT INTO city_tbl(wkt, city_name) VALUES ('POINT (-122.42 37.76)', 'San Francisco');

Then we can show the content of this table:

SELECT *
FROM city_tbl;

Output:

WKT CITY_NAME
POINT (-122.33 47.61)   Seattle
POINT (-122.42 37.76)   San Francisco

Create a Geometry column

All geometrical operations in SedonaSQL are on Geometry type objects. Therefore, before any kind of queries, you need to create a Geometry type column on the table.

CREATE TABLE city_tbl_geom AS
SELECT Sedona.ST_GeomFromWKT(wkt) AS geom, city_name
FROM city_tbl

The geom column Table city_tbl_geom is now in a Binary type and data in this column is in a format that can be understood by Sedona. The output of this query will show geometries in WKB binary format like this:

GEOM CITY_NAME
010100000085eb51b81e955ec0ae47e17a14ce4740  Seattle
01010000007b14ae47e19a5ec0e17a14ae47e14240  San Francisco

To view the content of this column in a human-readable format, you can use ST_AsText. For example,

SELECT Sedona.ST_AsText(geom), city_name
FROM city_tbl_geom

Note

SedonaSQL provides lots of functions to create a Geometry column, please read SedonaSQL API.

Check the lon/lat order

In SedonaSnow v1.4.1 and before, we use lat/lon order in the following functions:

• ST_Transform
• ST_DistanceSphere
• ST_DistanceSpheroid

We use lon/lat order in the following functions:

• ST_GeomFromGeoHash
• ST_GeoHash
• ST_S2CellIDs

In Sedona v1.5.0 and above, all functions will be fixed to lon/lat order.

If your original data is in lon/lat order, you need to flip the coordinate using ST_FlipCoordinates(geom: Geometry) if you want to use those functions.

The sample data used above is in lon/lat order, we can flip the coordinates as follows:

CREATE OR REPLACE TABLE city_tbl_geom AS
SELECT Sedona.ST_FlipCoordinates(geom) AS geom, city_name
FROM city_tbl_geom

If we show the content of this table, it is now in lat/lon order:

SELECT Sedona.ST_AsText(geom), city_name
FROM city_tbl_geom

Output:

GEOM    CITY_NAME
POINT (47.61 -122.33)   Seattle
POINT (37.76 -122.42)   San Francisco

Transform the Coordinate Reference System

Sedona doesn't control the coordinate unit (degree-based or meter-based) of all geometries in a Geometry column. The unit of all related distances in SedonaSQL is same as the unit of all geometries in a Geometry column.

To convert Coordinate Reference System of the Geometry column created before, use ST_Transform (A:geometry, SourceCRS:string, TargetCRS:string

The first EPSG code EPSG:4326 in ST_Transform is the source CRS of the geometries. It is WGS84, the most common degree-based CRS.

The second EPSG code EPSG:3857 in ST_Transform is the target CRS of the geometries. It is the most common meter-based CRS.

This ST_Transform transform the CRS of these geometries from EPSG:4326 to EPSG:3857. The details CRS information can be found on EPSG.io.

Note

This function follows lat/lon order. If your data is in lon/lat order, you can use ST_FlipCoordinates to swap X and Y.

We can transform our sample data as follows

SELECT Sedona.ST_AsText(Sedona.ST_Transform(geom, 'epsg:4326', 'epsg:3857')), city_name
FROM city_tbl_geom

The output will be like this:

POINT (-13617713.308741156 6042216.250411431)  Seattle
POINT (-13627732.06291255 4545577.120361927)  San Francisco

ST_Transform also supports the CRS string in OGC WKT format. For example, the following query generates the same output but with a OGC WKT CRS string.

SELECT Sedona.ST_AsText(Sedona.ST_Transform(geom, 'epsg:4326', 'PROJCS["WGS 84 / Pseudo-Mercator",
     GEOGCS["WGS 84",
         DATUM["WGS_1984",
             SPHEROID["WGS 84",6378137,298.257223563,
                 AUTHORITY["EPSG","7030"]],
             AUTHORITY["EPSG","6326"]],
         PRIMEM["Greenwich",0,
             AUTHORITY["EPSG","8901"]],
         UNIT["degree",0.0174532925199433,
             AUTHORITY["EPSG","9122"]],
         AUTHORITY["EPSG","4326"]],
     PROJECTION["Mercator_1SP"],
     PARAMETER["central_meridian",0],
     PARAMETER["scale_factor",1],
     PARAMETER["false_easting",0],
     PARAMETER["false_northing",0],
     UNIT["metre",1,
         AUTHORITY["EPSG","9001"]],
     AXIS["Easting",EAST],
     AXIS["Northing",NORTH],
     EXTENSION["PROJ4","+proj=merc +a=6378137 +b=6378137 +lat_ts=0 +lon_0=0 +x_0=0 +y_0=0 +k=1 +units=m +nadgrids=@null +wktext +no_defs"],
     AUTHORITY["EPSG","3857"]]')), city_name
FROM city_tbl_geom

Range query

Use ST_Contains, ST_Intersects, ST_Within to run a range query over a single column.

The following example finds all geometries that are within the given polygon:

SELECT *
FROM city_tbl_geom
WHERE Sedona.ST_Contains(Sedona.ST_PolygonFromEnvelope(1.0,100.0,1000.0,1100.0), geom)

Note

Read SedonaSQL API to learn how to create a Geometry type query window.

KNN query

Use ST_Distance, ST_DistanceSphere, ST_DistanceSpheroid to calculate the distance and rank the distance.

The following code returns the 5 nearest neighbor of the given point.

SELECT geom, ST_Distance(Sedona.ST_Point(1.0, 1.0), geom) AS distance
FROM city_tbl_geom
ORDER BY distance DESC
LIMIT 5

Save as an ordinary column

To save a table to some permanent storage, you can simply convert each geometry in the Geometry type column back to a plain String and save it anywhere you want.

Use the following code to convert the Geometry column in a table back to a WKT string column:

SELECT ST_AsText(geom)
FROM city_tbl_geom

Note

SedonaSQL provides lots of functions to save the Geometry column, please read SedonaSQL API.

Functions that are only availble in Sedona

There are a number of functions that are available in Sedona but not in Snowflake native functions. For example:

• ST_3DDistance
• ST_Force_2D
• ST_GeometryN
• ST_MakeValid
• ST_Multi
• ST_NumGeometries
• ST_PrecisionReduce
• ST_SubdivdeExplode

You can click the links above to learn more about these functions. More functions can be found in SedonaSQL API.

Other queries

There are lots of other functions can be combined with these queries. Please read SedonaSQL API.

---

Last update: September 12, 2023 06:00:42