Find query using ST_Geometry, ST_Contains, ST_Distance, ST_Envelope, ST_Overlaps, ST_Point, ST_Rectangle, and ST_Union

Spatial Joins in a Distributed Environment with MapReduce on Apache Sedona

Goal: Given two datasets, one for all the point locations and another for all the rectangular regions, using mapreduce on Sedona, find the number of points inside each rectanglular region of these datasets and save the results.

Steps followed:

1. I loaded both the points and rectangles datasets with spark, converted them into data frames using the "toDF()" function, and created a Temporary View with them.

2. After that, I created a point using the ST_Point function with the x and y coordinates of the point dataset. I also made a geometric envelope using the ST_PolygonFromEnvelope function with the two diagonal points given in the reactangles dataset. 

3. I combined the two datasets to get all the sets of points and rectangles wherein the point belongs to the rectangle by using the ST_Contains function. 

4. I converted the created data frame to an RDD, mapped the RDD to <key, value> pairs of <reactangle,1> for each row, and then reduced these pairs using the keys. 

5. After performing this map reduce, I converted the resultant RDD to a DataFrame, selected the counts, and sorted the counts in ascending order. 

6. Lastly, I repartitioned the result to 1 single partition and returned theresult.

OPTIMIZING SPATIAL JOINS BY USING A DELTA FORMAT

Goal: Modify the abve spark code so that it uses the Delta format to store and process the data.

Advantages of the Delta format:

• It has two different caches: a delta cache and a result cache. The delta cache keeps local copies of remote data on the worker nodes, which helps avoidremote reads during larger workloads. The results cache stored subsets of the result of a query you run. This feature is helpful when you have a use case that runs a specific query multiple times.[3]

• It supports concurrent read/write operations via snapshot isolation.

• It allows background file optimization through compaction and z-order partitioning achieving better performance improvements.

Steps followed:

1. I read all the points in CSV format, found the set of all points having coordinates >500, and saved them in delta format.

2. I read this delta file and printed the count of the rows in this file.

3. I read all the rectangles in CSV format, found all the rectangles having all coordinates >900, and saved them in delta format. Then, I read this delta fileand printed the count of the rows in this file.

4. I read both the saved points and rectangles from the saved delta. Then, I found the data set of all the points belonging to some rectangle using a sub-select query. Lastly, I performed a self-join on this obtained result to obtain all the point pairs with a distance of less than two between them.

Find Operations with MongoDB

Goal: Create and run spatial queries in MongoDB using Python.

Conclusion

• PostGIS preprocesses the data and indexes it geospatially, while spark uses the raw data directly from its source. Hence, due to the added time complexity ofthis indexing, PostGIS’s time needed to respond to queries increases exponentially while the spark’s response time remains stable.[8]

• MongoDB stores all the data together in a document, which offers both a flexible format, and helps club information together. The advantage of this is that all the data you need is in one place, and you don’t have to join this data across multiple tables. Hence, it is fasterquerying data. Conversely, you would need large amounts of memory and processing power for this.

• Delta tables have a data cache, which would cache the data of the Delta format we loaded inter-mediately and reuse it in the final query. This woulddecrease the number of reads and increase the performance of our end query.