How to do Spatial query in spark & Scala 3.3.1 and 2.12. with apache sedona to find the intersection in coordinates

Question

I'm trying to do Spatial operation on data which i have lat/long and one static geojson file. Now i need to load the geojson and find for each row from DF lat/long if they belong to which location using intersection.

source Data as

1111:150458,025.22826N,055.30022E,348,39,JOB_ONBOARD
2222:150448,025.22746N,055.29962E,32,48, CAR_AVAILABLE
3333,20072023:150612,025.30559N,055.38272E,130,50,CAR_AVAILABLE
4444,20072023:150740,025.21794N,055.28569E,0,0,JOB_ONBOARD

I tried to follow Apache Sedona documents but didn't succeed .

Kindly guide me to proceed . Thank you

 val spark: SparkSession = SparkSession.builder()
      .appName("test")
      .config("spark.master", "local[*]")
      .config("spark.serializer", classOf[KryoSerializer].getName)
      .config("spark.kryo.registrator", classOf[SedonaKryoRegistrator].getName)
      .getOrCreate()

 SedonaSQLRegistrator.registerAll(spark)

val inputLocation = "C:\\communities_0.geojson"

val schema = "type string, crs string, totalFeatures long, features array<struct<type string, geometry string, properties map<string, string>>>"

    spark.read.schema(schema).json(inputLocation)
      .selectExpr("explode(features) as features") // Explode the envelope to get one feature per row.
      .select("features.*") // Unpack the features struct.
      .withColumn("geometry", expr("ST_GeomFromGeoJSON(geometry)")) // Convert the geometry string.
      .printSchema()

End result DF should be as below

1111:150458,025.22826N,055.30022E,348,39,JOB_ONBOARD, community_A
2222:150448,025.22746N,055.29962E,32,48, CAR_AVAILABLE, community_B
3333,20072023:150612,025.30559N,055.38272E,130,50,CAR_AVAILABLE, community_C
4444,20072023:150740,025.21794N,055.28569E,0,0,JOB_ONBOARD, community_D

Answer 1

Your source data is not in GeoJSON or any typical geospatial format. Please consider cleaning up your data first to the following format by dropping the letter N and E.

1111:150458,025.22826,055.30022,348,39,JOB_ONBOARD
2222:150448,025.22746,055.29962,32,48, CAR_AVAILABLE
3333,20072023:150612,025.30559,055.38272,130,50,CAR_AVAILABLE
4444,20072023:150740,025.21794,055.28569,0,0,JOB_ONBOARD

Then you can use Sedona ST_Point to create a geometry column: https://sedona.apache.org/1.4.1/api/sql/Constructor/#st_point