analysing the performance of nosql vs sql databases with respect to - PowerPoint PPT Presentation

Routing Algorithms Sarthak Agarwal, KS Rajan September 18, 2015 Lab for Sptial Informatics International Institute of Information Technology Hyderabad FOSS4G Seoul, South Korea analysing the performance of nosql vs sql databases with respect to

∙ Routing in a place which do not have internet connection. ∙ Why do we need a Internet connection to know directions from one point to another? ∙ Can we make mobile phones as the routing machines itself? ∙ Why to query heavy SQL servers everytime? ∙ Can we use a alternate database system technology for routing FOSS4G Seoul, South Korea | September 14th – 19th , 2015 Sarthak Agarwal, KS Rajan 2 Problem Statement

Compare the performance of two database technologies - FOSS4G Seoul, South Korea | September 14th – 19th , 2015 Sarthak Agarwal, KS Rajan 3 Spatial Databases ∙ SQL ∙ No-SQL

∙ Spatial databases currently are primarily based on RDBMS. eg PostGIS ∙ RDBMS have a great potential to store, manage and query very large dataset. ∙ SQL databases face scalability and agility challenges. ∙ SQL databases generally have a fixed schema and uses lot of joins in a query. FOSS4G Seoul, South Korea | September 14th – 19th , 2015 Sarthak Agarwal, KS Rajan 4 SQL Databases

∙ PostGIS adds support for geographic objects to the PostgreSQL object-relational database. ∙ pgRouting extends the PostGIS/PostgreSQL geospatial database to provide geospatial routing and other network analysis functionality. ∙ It has support for many routing algorithms but we used dijkstras for our case FOSS4G Seoul, South Korea | September 14th – 19th , 2015 Sarthak Agarwal, KS Rajan 5 pgRouting

∙ Document based databases ∙ Great potential to store, manage and query very large dataset. ∙ Better query response time. ∙ Scalable over multiple servers ∙ Can handle the rise in the data storage and frequency at which it is accessed and processed. ∙ Spatial applications deals with problems like over time evolution of schema and data size FOSS4G Seoul, South Korea | September 14th – 19th , 2015 Sarthak Agarwal, KS Rajan 6 No-SQL Databases ∙ NoSQl or Not only Sql databases for non-relational data stores.

∙ Document oriented datastore. ∙ High performance and retains friendly properties of SQL. ∙ GeoJSON objects. ∙ Multiple geospatial indexes per collection - 2d, 2dsphere . ∙ Data can be imported from CSV files by converting it into GeoJSON objects. ∙ No support for R trees. FOSS4G Seoul, South Korea | September 14th – 19th , 2015 Sarthak Agarwal, KS Rajan 7 MongoDB

∙ Not designed for distributed System ∙ Good for structured data Unstructured data, points and lines not very suitable. ∙ Distributed Databases spread over multiple servers, ∙ Schema less databases where multiple geometries can be stored in the same column. FOSS4G Seoul, South Korea | September 14th – 19th , 2015 Sarthak Agarwal, KS Rajan 8 Spatial Databases SQL NoSQL

Does NoSQL hold a promise in the context of Spatial Databases and Spatial Queries? FOSS4G Seoul, South Korea | September 14th – 19th , 2015 Sarthak Agarwal, KS Rajan 9

∙ OSM data of Australia region . ∙ Standard machine was used to run all the tests. ∙ Small data size to very big data size. ∙ All the data in the analysis was processed using In-memory and no secondary memory was used. FOSS4G Seoul, South Korea | September 14th – 19th , 2015 Sarthak Agarwal, KS Rajan 10 How to Compare - PgRouting

Sarthak Agarwal, KS Rajan FOSS4G Seoul, South Korea | September 14th – 19th , 2015 11 OSM data

∙ Custom import program was written to parse OSM data and import into the database. ∙ Adjacency list is used to store graph in the database. ∙ Each row have two columns. ∙ The source node ∙ All the nodes connected to it and their respective weights. ∙ Standard Dijikstra algorithm was used and written in C++ using MongoDB C++ driver. ∙ MongoDb was used to read values from the database. ∙ All the data in the analysis was processed using In-memory and no secondary memory was used. FOSS4G Seoul, South Korea | September 14th – 19th , 2015 Sarthak Agarwal, KS Rajan 12 How to Compare - MongoDB

∙ Most of the time is taken in importing the data. ∙ Very simple implementation currently, just to test the initial performance. ∙ Performance restricted by the implementation and optimization of the algorithm. ∙ More tests required and more optimization are needed in the algorithm. ∙ Many assumptions were taken like- FOSS4G Seoul, South Korea | September 14th – 19th , 2015 Sarthak Agarwal, KS Rajan 13 Performance ∙ Normalization of weights was done on the same metrics as pgRouting . ∙ ways and relations table not maintained.

∙ pgRouting performs much better than MongoDb current implementation. ∙ However pgRotuing fails at very large datasets. ∙ Performance is better in some cases but no conclusive results can be drawn from the tests. FOSS4G Seoul, South Korea | September 14th – 19th , 2015 Sarthak Agarwal, KS Rajan 14 Results

∙ Potential to be implemented in servers with limited computational power. ∙ This can be helpful for routing in remote and rural areas. ∙ This can be a milestone for Web-GIS. FOSS4G Seoul, South Korea | September 14th – 19th , 2015 Sarthak Agarwal, KS Rajan 15 Discussions

∙ Right now project is in very initial stage. ∙ Very simple implementation currently, just to test the initial performance. ∙ Need to Optimize the implementation of dijisktra and find alternative platforms to run the tests. ∙ Use the native mongoDb functions to route. ∙ Also study and port pgRouting database from PostGis to MongoDB FOSS4G Seoul, South Korea | September 14th – 19th , 2015 Sarthak Agarwal, KS Rajan 16 Future Research

Sarthak Agarwal, KS Rajan FOSS4G Seoul, South Korea | September 14th – 19th , 2015 17 Questions?