0

I'm developing a location based application in which i was in the plan of dividing the globe into grids and when the client requests information, will create a table or similar data model which will expire in 5 mins and serving the same data for the clients within the same grid within that period. Which i explained in the following StackOverflow question.

https://stackoverflow.com/questions/19667637/caching-location-based-data-cluster

But i come across the term spatial databases today and now i like to know how it differs from the traditional model when searching from the databases of million records especially in terms of speed? Will it really speed up data retrieval of location based data? If so, can you explain how?

Problem: The problem is, i was in the idea of using traditional database, but if i use traditional DB and if i have more than a million records, when we execute each search query it needs to search through the the database of million records, so i planned to,

  1. Divide the globe by some sort of grid each of around 1 sq.km and when we showed all the values from that grid,then the next nearby grid and so on which i need to save in some buffer.
  2. And when someone request the data from the same grid in the next 5 mins, i don't want to search the entire DB again, and i need to give the values from the buffer and not querying the entire DB again.

Here the main problem is, buffering the data and retrieval from it to speed up the process and minimizing the server load. So i like to know that this spatial DB simplify these process in any way? Or is there any DB already available which provides any grid system like this?

@ike: What i meant as speed is the performance, i.e., the number of rows returned against the number of rows on which the query runs on(may be the entire table of few million records). I'm trying to find some way to reduce the number of rows(or the entire table) on which the query runs

  • I'm not sure what you mean by speed. If you are looking for the fastest algorithm for your purposes, only you can identify or design that algorithm. If you are asking in a general sense, then I would have to say my results with spatial databases exceed my results with traditional GIS tools when working with simple geometric and geographic concepts. As my needs become more complex, and the time I have to develop my own tools becomes lower, traditional GIS tools with previously implemented procedures becomes far more useful. – ike Oct 30 '13 at 21:44
  • Remember, other peoples solutions aren't designed just to solve your problem, they are designed to solve your problem plus whatever other problems might also arise. Finding the fastest path between two points is easy when it is a straight line. It gets slightly harder when it is a curve. It gets even more difficult when there are things in the way. More difficult still when your speed depends on your route. Then harder again when you must also consider the quality of your route and the type of traveler you are modeling, and so on. This example is meant in both a literal and meta sense. – ike Oct 30 '13 at 22:03
  • Without knowing anything about how your data and application are architected and used, all I can suggest is to have a thorough examination of the documentation for MySQL's built-in spatial capabilities. And if using other DBMS's, such as Postgresql, Oracle or SQL Server is a possibility, also consider those. – blah238 Oct 31 '13 at 0:53
  • @blah238: I'm using mysql as DB. And currently i'm in the process of creating the architecture for the above requirements. – Stranger Oct 31 '13 at 4:52
  • 2
    @Udhay, the primary optimization you seem to be missing here conceptually is that of a spatial index. I am sure you have heard of normal indices, however a spatial index will vastly speed up spatial queries on large numbers of spatial features. Please go through the MySQL Spatial Extensions help I linked above and if you have any more specific questions, please open a new question for each. – blah238 Oct 31 '13 at 5:35
3

How do spatial algorithms help?

There are many ways spatially based algorithms can improve upon traditional algorithms. Often, you can use spatial algorithms to drastically lower the amount of records you have to loop through, such as when using distance in your calculation.

Give me an example!

Let's use this question that was asked today as an example. The question asker wants to find the nearest location to certain points. He knows that there will always be at least one location within some distance, so instead of looping through every single record and getting the distance between the two, he creates a circular buffer around his target point so that he only loops through the locations within the buffer (he doesn't care about the other ones, he only wants the point with the smallest distance, so anything outside the buffer obviously couldn't be the nearest location).

The end result is that he speeds up his program significantly from implementing a spatial algorithm. This is a simple example of how to use spatial location to improve upon algorithms that are related to distance and location. However, as Ike noted in comments above, oftentimes the answer is not so simple.

  • Hi Conor, thanks for the answer. What is the possibility of doing this from server side, i.e., on database layer? – Stranger Oct 31 '13 at 8:57
  • 1
    PostGIS is the most widely known spatial database that supports simple spatial processing (like the buffering and distance calculations in my example) on the database side. It is generally faster and more efficient to carry out these kinds of spatial queries and analysis within the database layer through PostGIS rather than through a desktop GIS such as ArcGIS. If this is something you are interested in, I would look there first. Other spatial databases such as MS SQL Server Spatial and MySQL with spatial extensions do not support this kind of geoprocessing. – Conor Oct 31 '13 at 21:29
1

A spatial DBMS is usually (an extension to a) traditional, relational DBMS. It provides

  • At least a Point data type (X, Y) or (lat, long) and often Line and Region types.
  • A spatial indexing option such that queries seeking records that are geo-located within a simple bounding rectangle can be answered quickly.

Such databases may also vary according to the complexity of the spatial data types supported, the complexity of the type of spatial operators (within, overlaps, contains, etc), and the variety of spatial reference systems supported (flat, spherical, ellipsoidal).

MySQL, PostGIS and Ingres are examples, but there are others.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.