SQL Interface Development for Spatial Data Retrieval on Cassandra
William Fu, Tricya Esterina Widagdo · 2022
Cassandra can store spatial data natively, however, it still provides limited number of spatial operations and lacks the features for data retrieval querying when compared to SQL databases. In this paper, a system was developed to perform spatial data retrieval using SQL queries by utilizing the PostGIS extension. The system developed complements the spatial operations provided by Cassandra and enables data retrieval by using SQL queries. The system works by transforming an input SQL query into one or more CQL queries to retrieve data from Cassandra. Afterwards, the data is used to fill in the table declaration of the input SQL query. To utilize Cassandra's advantages in storing data per column inside a block of memory, the CQL query being constructed will involve only the necessary columns that are needed from a table. To optimize the system's performance in retrieving large amount of data, it identifies and translates the predicates that can be executed on Cassandra. The system also uses the streaming method and stores data temporarily in PostgreSQL for the excess data. To test its performance, the system will be given input queries with various spatial functions, subquery expressions, and the number of tables involved. The execution time for each query will then be measured for further analysis. Based on the evaluation results, it is concluded that the SQL interface has successfully able to retrieve spatial data records stored in Cassandra.