# GeoSparkSQL query optimizer¶

GeoSpark Spatial operators fully supports Apache SparkSQL query optimizer. It has the following query optimization features:

- Automatically optimizes range join query and distance join query.
- Automatically performs predicate pushdown.

## Range join¶

Introduction: Find geometries from A and geometries from B such that each geometry pair satisfies a certain predicate. Most predicates supported by GeoSparkSQL can trigger a range join.

Spark SQL Example:

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

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

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

== Physical Plan == RangeJoin polygonshape#20: geometry, pointshape#43: geometry, false :- Project [st_polygonfromenvelope(cast(_c0#0 as decimal(24,20)), cast(_c1#1 as decimal(24,20)), cast(_c2#2 as decimal(24,20)), cast(_c3#3 as decimal(24,20)), mypolygonid) AS polygonshape#20] : +- *FileScan csv +- Project [st_point(cast(_c0#31 as decimal(24,20)), cast(_c1#32 as decimal(24,20)), myPointId) AS pointshape#43] +- *FileScan csv

Note

All join queries in GeoSparkSQL are inner joins

## Distance join¶

Introduction: Find geometries from A and geometries from B such that the internal Euclidean distance of each geometry pair is less or equal than a certain distance

Spark SQL Example:

*Only consider fully within a certain distance*

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

*Consider intersects within a certain distance*

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

Spark SQL Physical plan:

== Physical Plan == DistanceJoin pointshape1#12: geometry, pointshape2#33: geometry, 2.0, true :- Project [st_point(cast(_c0#0 as decimal(24,20)), cast(_c1#1 as decimal(24,20)), myPointId) AS pointshape1#12] : +- *FileScan csv +- Project [st_point(cast(_c0#21 as decimal(24,20)), cast(_c1#22 as decimal(24,20)), myPointId) AS pointshape2#33] +- *FileScan csv

Warning

GeoSpark doesn't control the distance's unit (degree or meter). It is same with the geometry. To change the geometry's unit, please transform the coordinate reference system. See ST_Transform.

## Predicate pushdown¶

Introduction: Given a join query and a predicate in the same WHERE clause, first executes the Predicate as a filter, then executes the join query*

Spark SQL Example:

SELECT * FROM polygondf, pointdf WHERE ST_Contains(polygondf.polygonshape,pointdf.pointshape) AND ST_Contains(ST_PolygonFromEnvelope(1.0,101.0,501.0,601.0), polygondf.polygonshape)

Spark SQL Physical plan:

== Physical Plan == RangeJoin polygonshape#20: geometry, pointshape#43: geometry, false :- Project [st_polygonfromenvelope(cast(_c0#0 as decimal(24,20)), cast(_c1#1 as decimal(24,20)), cast(_c2#2 as decimal(24,20)), cast(_c3#3 as decimal(24,20)), mypolygonid) AS polygonshape#20] : +- Filter **org.apache.spark.sql.geosparksql.expressions.ST_Contains$** : +- *FileScan csv +- Project [st_point(cast(_c0#31 as decimal(24,20)), cast(_c1#32 as decimal(24,20)), myPointId) AS pointshape#43] +- *FileScan csv