0

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?

2
  • 3
    I would avoid going to and from json for every geometry - if you can't avoid that in geosparql then I would switch to another language/system
    – Ian Turton
    Jul 31, 2022 at 10:25
  • There are ways to improve performance of native data through indexing and dicing, but this is an extreme worst case scenario.
    – Vince
    Jul 31, 2022 at 12:49

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.