I have a requirement of getting a bounding box filtered into exact country polygon boundary. For this I am using Sedona ST_intersects query. But this query when I run for 2 GB of data takes 15 mins and when it has 500GB of data the job timesout after 24 hours.
This is the filter query I am using
df.createOrReplaceTempView("dataframe_raw")
spark.sql(
s"""
SELECT *
FROM dataframe_raw
WHERE ST_Intersects(
ST_GeomFromGeoJSON(to_json(dataframe_raw.geojson.geometry)),
ST_GeomFromGeoJSON('${spatialBoundary}')
)
"""
).repartition()
Spatial Boundary is a string of data for the polygon boundary in coordinates.
spatial data is ->{"coordinates":[[[[-125.86255828379149,48.7601383766729],[-125.862572123105,48.76014259610412],[-133.16410052521906,52.73907483513551],[-134.22891038500615,54.03179559489081],[-133.75353482277387,54.61950739395121],[-132.79364111124823,54.66223557471204],[-131.75101092351514,54.687095568206075],[-130.85073112374226,54.70133556028793],[-130.61692123142961,54.7085043270132],[-130.6293765404949,54.723997297737185],[-130.6263880969331,54.738055556618804],[-130.65777333916822,54.76182777513179],[-130.63675732329182,54.7784619063919],[-130.61710783266034,54.781564876817754],[-130.56937367285656,54.79087372514249],[-130.47461910198996,54.8381110050189],[-130.40978037196652,54.881200150266864],[-130.3395208182311,54.921379931138915],[-130.30803217914166,54.94758879258876],[-130.27558221154982,54.9729387727063],[-130.2590938505008,54.98764730797694],[-130.24797334309528,55.00234772416826],[-130.2429716816976,55.00731917913686],[-130.23231248243508,55.01559855552898],[-130.22153386939243,55.025997215798895],[-130.20953399589723,55.040827029144225],[-130.20216347256616,55.04908758542376],[-130.19375378440947,55.05762726698475],[-130.18756570499312,55.06466508252754],[-130.18239698643163,55.07938143200755],[-130.1827253244141,55.09321527496513],[-130.16931324910712,55.10542781114464]]]],"type":"MultiPolygon"}
The dataframe_raw.geojson.geometry is like:
{"geometry":{"type":"Point","coordinates":[-99.8813182,16.8620113]}
How can I optimize this data?