Run Geospatial Queries

This page shows one example of each kind of spatial query in WherobotsDB. For how WherobotsDB plans each join, with physical plans and more variants, see Optimizers.

Transform the Coordinate Reference System

Planar distances in Spatial SQL use the unit of the geometries in the Geometry column, degrees or meters. To convert the Coordinate Reference System of the Geometry column in the spatialdf view from Load Data From External Storage, use ST_Transform. This saves the result as the countydf view, which the range and KNN queries below read:

CREATE OR REPLACE TEMPORARY VIEW countydf AS
SELECT ST_Transform(countyshape, "epsg:4326", "epsg:3857") AS newcountyshape, _c1, _c2, _c3, _c4, _c5, _c6, _c7
FROM spatialdf

The first EPSG code, EPSG:4326, is the source CRS of the geometries. It is WGS84, the most common degree-based CRS. The second EPSG code, EPSG:3857, is the target CRS, the most common meter-based CRS. For the supported CRS formats and grid files, see CRS Transformation.

Range query

Use ST_Contains, ST_Intersects, ST_Within to run a range query over a single column. The following example finds all counties that are within the given polygon:

SELECT *
FROM countydf
WHERE ST_Contains (ST_PolygonFromEnvelope(1.0,100.0,1000.0,1100.0), newcountyshape)

Read Spatial SQL API to learn how to create a Geometry type query window

KNN query

Use ST_Distance to calculate the distance and rank the distance. The following code returns the 5 nearest neighbor of the given polygon.

SELECT _c6 AS countyname, ST_Distance(ST_PolygonFromEnvelope(1.0,100.0,1000.0,1100.0), newcountyshape) AS distance
FROM countydf
ORDER BY distance ASC
LIMIT 5

Range join

A range join finds geometries from A and geometries from B such that each geometry pair satisfies a certain predicate, such as ST_Contains, ST_Intersects, or ST_Within:

SELECT *
FROM polygondf, pointdf
WHERE ST_Contains(polygondf.polygonshape,pointdf.pointshape)

WherobotsDB join performance is heavily affected by the number of partitions. If the join performance is not ideal, please increase the number of partitions by doing df.repartition(XXX) right after you create the original DataFrame.

For the supported predicates and the physical plan, see Range join.

Distance join

A distance join finds geometries from A and geometries from B such that the distance of each geometry pair is less than or equal to a certain distance:

SELECT *
FROM pointdf1, pointdf2
WHERE ST_Distance(pointdf1.pointshape1,pointdf2.pointshape2) <= 2

ST_Distance is a planar distance in the unit of the geometries, so for longitude and latitude data the distance is in degrees. For distances in meters, use ST_DistanceSpheroid or ST_DistanceSphere:

SELECT *
FROM pointdf1, pointdf2
WHERE ST_DistanceSpheroid(pointdf1.pointshape1,pointdf2.pointshape2) <= 2

For the other distance functions, the difference between < and <=, and the limits of geodesic distance joins, see Distance join.

Broadcast index join

A broadcast index join broadcasts one side of a range join or distance join and builds a spatial index on it, so the other side keeps its partitioning and isn’t shuffled. Add a broadcast hint to the side to broadcast:

pointDf.alias("pointDf").join(broadcast(polygonDf).alias("polygonDf"), expr("ST_Contains(polygonDf.polygonshape, pointDf.pointshape)"))

When one table is smaller than a threshold, WherobotsDB chooses a broadcast index join automatically. For the supported join types and the threshold, see Broadcast index join.

K Nearest-Neighbor join / KNN join

A KNN join returns the k nearest geometries from the objects side table for each geometry in the queries side table:

SELECT
    QUERIES.ID AS QUERY_ID,
    QUERIES.GEOMETRY AS QUERIES_GEOM,
    OBJECTS.GEOMETRY AS OBJECTS_GEOM
FROM QUERIES
JOIN OBJECTS
ON ST_KNN(QUERIES.GEOMETRY, OBJECTS.GEOMETRY, 4, FALSE)

This query returns the 4 nearest neighbors from the OBJECTS table for each geometry in the QUERIES table based on planar Euclidean distance, measured in the geometries’ coordinate units. Set the fourth argument to TRUE to use great-circle distance on a sphere, in meters. For the distance models, the optional search radius, the approximate ST_AKNN join, and more examples, see Nearest-Neighbor Searching.

Create a User-Defined Function (UDF)

User-Defined Functions (UDFs) are user-created procedures that can perform operations on a single row of information. To cover almost all use cases, we will showcase 4 types of UDFs for a better understanding of how to use geometry with UDFs. Sedona’s serializer deserializes the SQL geometry type to JTS Geometry (Scala/Java) or Shapely Geometry (Python). You can implement any custom logic using the rich ecosystem around these two libraries.

Geometry to primitive

This UDF example takes a geometry type input and returns a primitive type output:

Python
Scala
Java

from sedona.spark import *
from pyspark.sql.types import DoubleType

def lengthPoly(geom: GeometryType()):
		return geom.length

sedona.udf.register("udf_lengthPoly", lengthPoly, DoubleType())

df.selectExpr("udf_lengthPoly(geom)").show()

import org.locationtech.jts.geom.Geometry
import org.apache.spark.sql.types._

def lengthPoly(geom: Geometry): Double = {
		geom.getLength
}

sedona.udf.register("udf_lengthPoly", lengthPoly _)

df.selectExpr("udf_lengthPoly(geom)").show()

import org.apache.spark.sql.api.java.UDF1;
import org.apache.spark.sql.types.DataTypes;

// using lambda function to register the UDF
sparkSession.udf().register(
				"udf_lengthPoly",
				(UDF1<Geometry, Double>) Geometry::getLength,
				DataTypes.DoubleType);

df.selectExpr("udf_lengthPoly(geom)").show();

Output:

+--------------------+
|udf_lengthPoly(geom)|
+--------------------+
|   3.414213562373095|
+--------------------+

Geometry to Geometry

This UDF example takes a geometry type input and returns a geometry type output:

Python
Scala
Java

from sedona.spark import *
from pyspark.sql.types import DoubleType

def bufferFixed(geom: GeometryType()):
	return geom.buffer(5.5)

sedona.udf.register("udf_bufferFixed", bufferFixed, GeometryType())

df.selectExpr("udf_bufferFixed(geom)").show()

import org.locationtech.jts.geom.Geometry
import org.apache.spark.sql.types._

def bufferFixed(geom: Geometry): Geometry = {
	geom.buffer(5.5)
}

sedona.udf.register("udf_bufferFixed", bufferFixed _)

df.selectExpr("udf_bufferFixed(geom)").show()

import org.apache.spark.sql.api.java.UDF1;
import org.apache.spark.sql.types.DataTypes;

// using lambda function to register the UDF
sparkSession.udf().register(
		"udf_bufferFixed",
		(UDF1<Geometry, Geometry>) geom ->
			geom.buffer(5.5),
		new GeometryUDT());

df.selectExpr("udf_bufferFixed(geom)").show();

Output of the Scala and Java examples:

+--------------------------------------------------+
|                             udf_bufferFixed(geom)|
+--------------------------------------------------+
|POLYGON ((1 -4.5, -0.0729967710887076 -4.394319...|
+--------------------------------------------------+

The Python UDF buffers with Shapely, which approximates each quarter circle with 16 segments where JTS uses 8, so its polygon has more vertices and differs after the first one: it starts POLYGON ((1 -4.5, 0.4609057281874175 -4.4735159....

Geometry, primitive to geometry

This UDF example takes a geometry type input and a primitive type input and returns a geometry type output:

Python
Scala
Java

from sedona.spark import *
from pyspark.sql.types import DoubleType

def bufferIt(geom: GeometryType(), distance: DoubleType()):
		return geom.buffer(distance)

sedona.udf.register("udf_buffer", bufferIt, GeometryType())

df.selectExpr("udf_buffer(geom, distance)").show()

import org.locationtech.jts.geom.Geometry
import org.apache.spark.sql.types._

def bufferIt(geom: Geometry, distance: Double): Geometry = {
		geom.buffer(distance)
}

sedona.udf.register("udf_buffer", bufferIt _)

df.selectExpr("udf_buffer(geom, distance)").show()

import org.apache.spark.sql.api.java.UDF2;
import org.apache.spark.sql.types.DataTypes;

// using lambda function to register the UDF
sparkSession.udf().register(
				"udf_buffer",
				(UDF2<Geometry, Double, Geometry>) Geometry::buffer,
				new GeometryUDT());

df.selectExpr("udf_buffer(geom, distance)").show();

Output of the Scala and Java examples:

+--------------------------------------------------+
|                        udf_buffer(geom, distance)|
+--------------------------------------------------+
|POLYGON ((1 -9, -0.9509032201612866 -8.80785280...|
+--------------------------------------------------+

For the same reason, the Python UDF’s polygon starts POLYGON ((1 -9, 0.0198285967043955 -8.951847266....

Geometry, primitive to Geometry, primitive

This UDF example takes a geometry type input and a primitive type input and returns a geometry type and a primitive type output:

Python
Scala
Java

from sedona.spark import *
from pyspark.sql.functions import expr
from pyspark.sql.types import *

schemaUDF = StructType([
	StructField("buffed", GeometryType()),
	StructField("length", DoubleType())
])

def bufferAndLength(geom: GeometryType(), distance: DoubleType()):
	buffed = geom.buffer(distance)
	length = buffed.length
	return [buffed, length]

sedona.udf.register("udf_bufferLength", bufferAndLength, schemaUDF)

df.withColumn("bufferLength", expr("udf_bufferLength(geom, distance)")) \
			.select("geom", "distance", "bufferLength.*") \
			.show()

import org.locationtech.jts.geom.Geometry
import org.apache.spark.sql.types._
import org.apache.spark.sql.api.java.UDF2

val schemaUDF = StructType(Array(
	StructField("buffed", GeometryUDT),
	StructField("length", DoubleType)
))

val udf_bufferLength = udf(
	new UDF2[Geometry, Double, (Geometry, Double)] {
		def call(geom: Geometry, distance: Double): (Geometry, Double) = {
			val buffed = geom.buffer(distance)
			val length = buffed.getLength
			(buffed, length)
		}
	}, schemaUDF)

sedona.udf.register("udf_bufferLength", udf_bufferLength)

df.withColumn("bufferLength", expr("udf_bufferLength(geom, distance)"))
	.select("geom", "distance", "bufferLength.*")
	.show()

import org.apache.spark.sql.api.java.UDF2;
import org.apache.spark.sql.types.DataTypes;
import org.apache.spark.sql.types.StructType;
import scala.Tuple2;

StructType schemaUDF = new StructType()
			.add("buffedGeom", new GeometryUDT())
			.add("length", DataTypes.DoubleType);

// using lambda function to register the UDF
sparkSession.udf().register("udf_bufferLength",
			(UDF2<Geometry, Double, Tuple2<Geometry, Double>>) (geom, distance) -> {
				Geometry buffed = geom.buffer(distance);
				Double length = buffed.getLength();
				return new Tuple2<>(buffed, length);
			},
			schemaUDF);

df.withColumn("bufferLength", functions.expr("udf_bufferLength(geom, distance)"))
			.select("geom", "distance", "bufferLength.*")
			.show();

Output of the Scala and Java examples:

+------------------------------+--------+--------------------------------------------------+-----------------+
|                          geom|distance|                                        buffedGeom|           length|
+------------------------------+--------+--------------------------------------------------+-----------------+
|POLYGON ((1 1, 1 2, 2 1, 1 1))|    10.0|POLYGON ((1 -9, -0.9509032201612866 -8.80785280...|66.14518337329191|
+------------------------------+--------+--------------------------------------------------+-----------------+

The Python UDF buffers with Shapely, which approximates each quarter circle with 16 segments where JTS uses 8, so it returns a length of 66.22083670146812.

Spatial vectorized UDFs (Python only)

By default, when you create a user defined function (UDF) in Python, that function isn’t vectorized. Processing User-Defined Functions (UDFs) one row at a time often leads to slower performance. A faster alternative is to use vectorized UDFs, which leverage Apache Arrow to handle data in batches. To create a vectorized UDF, use the @sedona_vectorized_udf decorator. Currently, @sedona_vectorized_udf supports scalar UDFs. Vectorized UDFs offer significantly improved performance, potentially running up to 2x faster than standard UDFs.

When you use geometry as an input type, please include the BaseGeometry type, like Point from shapely or geopandas GeoSeries, when you use GEO_SERIES vectorized UDF. That’s how Sedona infers the type and knows if the data should be cast.

The decorator’s signature is the following:

def sedona_vectorized_udf(udf_type: SedonaUDFType = SedonaUDFType.SHAPELY_SCALAR, return_type: DataType)

udf_type is the type of the UDF function. Supported UDF types are:

SHAPELY_SCALAR
GEO_SERIES

Let’s examine the two examples below, which create buffers from a given geometry:

Shapely scalar UDF

import shapely.geometry.base as b
from sedona.spark import *

@sedona_vectorized_udf(return_type=GeometryType())
def vectorized_buffer(geom: b.BaseGeometry) -> b.BaseGeometry:
    return geom.buffer(0.1)

GeoSeries UDF

import geopandas as gpd
from sedona.spark import *


@sedona_vectorized_udf(udf_type=SedonaUDFType.GEO_SERIES, return_type=GeometryType())
def vectorized_geo_series_buffer(series: gpd.GeoSeries) -> gpd.GeoSeries:
    buffered = series.buffer(0.1)

    return buffered

To call the UDFs, you can use the following code:

# Shapely scalar UDF
df.withColumn("buffered", vectorized_buffer(df.geom)).show()

# GeoSeries UDF
df.withColumn("buffered", vectorized_geo_series_buffer(df.geom)).show()

S2/H3 based approximate equi-join

When the performance of the WherobotsDB-optimized join doesn’t suit your workflow needs, possibly because of intricate and overlapping geometries, WherobotsDB also provides Google S2 or Uber H3-based approximate equi-joins. Generate cell IDs for both tables with ST_S2CellIDs or ST_H3CellIDs, then join the tables on the cell ID:

SELECT lcs.id as lcs_id, lcs.geom as lcs_geom, rcs.id as rcs_id, rcs.geom as rcs_geom
FROM lcs JOIN rcs ON lcs.cellId = rcs.cellId

For the full steps, including how to refine and de-duplicate the results and how to run a distance join this way, see S2 or H3 based approximate equi-join.

Other queries

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

Start here

Havasu data catalogs

Vector data

Raster data

Iceberg tables

Maps and visualization

Run Geospatial Queries

Transform the Coordinate Reference System

Range query

KNN query

Range join

Distance join

Broadcast index join

K Nearest-Neighbor join / KNN join

Create a User-Defined Function (UDF)

Geometry to primitive

Geometry to Geometry

Geometry, primitive to geometry

Geometry, primitive to Geometry, primitive

Spatial vectorized UDFs (Python only)

Shapely scalar UDF

GeoSeries UDF

S2/H3 based approximate equi-join

Other queries

​Transform the Coordinate Reference System

​Range query

​KNN query

​Range join

​Distance join

​Broadcast index join

​K Nearest-Neighbor join / KNN join

​Create a User-Defined Function (UDF)

​Geometry to primitive

​Geometry to Geometry

​Geometry, primitive to geometry

​Geometry, primitive to Geometry, primitive

​Spatial vectorized UDFs (Python only)

​Shapely scalar UDF

​GeoSeries UDF

​S2/H3 based approximate equi-join

​Other queries

Transform the Coordinate Reference System

Range query

KNN query

Range join

Distance join

Broadcast index join

K Nearest-Neighbor join / KNN join

Create a User-Defined Function (UDF)

Geometry to primitive

Geometry to Geometry

Geometry, primitive to geometry

Geometry, primitive to Geometry, primitive

Spatial vectorized UDFs (Python only)

Shapely scalar UDF

GeoSeries UDF

S2/H3 based approximate equi-join

Other queries