03-19-12, 09:55 #1Registered User
- Join Date
- Jan 2007
DB Concept for Location Based Application
Hi, I'm in the process of designing the architecture of a location based mobile application and since my experience with databases is very limited I could use some help with the DB aspect.
The following are the basic steps the application performs from the moment it is launched:
1.Register your location to DB
2.Query DB for nearby users
3.Query DB for info of nearby users
4.Display users info
5.If your location has changed go to 1
6.Go to 2
Basically what I need here is a scheme to manage user locations in real time and another to keep user info. The design of this DB should support potentially millions of users.
Now let's talk about the location management. You're the user and you have around you a maximum of a couple of thousands users (usually much less). What I need is to query the DB for these users given my location and a certain radius. If I have one big table with two fields, userID and userLocation it seems to me like it might take too much time to update or to query those few users out of the millions of rows in the table. Am I right about that?
If this is the case then what I thought about is dividing the world map into equal sections (NxN) and then each of these sections divide again into NxN sections and so on. I will hold a master (world) table where each section is an entry which points to another table that has the same format (table of sections). The lowest level section will be a table of users. Now I will not have to query a table of millions. I will however need to make more queries to get to the right lower level tables, but it seems much lighter to me to query table with N^2 rows. There are a few issues that need to be sorted out with this scheme but before I go into that what do you guys think? Is it a good solution? Do you see any major problems with it? Do I even need it or is querying and updating a table of millions of rows by millions of users not a problem?
03-19-12, 10:26 #2Jaded Developer
- Join Date
- Nov 2004
- out on a limb
depends on what database are you using
but I wouldn't reinvent the latitude and longitude system, its tried and tested and is known to work. essentially the lat/long deos what you are proposing. the only thign to bear in mind is that the latitude / longitude works if you are sea level if you want precision in distance. one minute of longitude at the equator approximates to 1 nautical mile and also approximates to 1 nautical mile of latitude. however as the lines of longitude come together the further from the equator the less one minuite of longitude represents. there's 60 minutes in a degree (60nm) and 21,600 nm at the equator, which equates to 24,856 statute miles or 40,000 odd km
if you select one that already supports GIS / location functions then you will make your development process much easier. MySQL supports SPATIAL extensions as of V5 something or other. the only downside is the table type for the location MUST be MyISAM so you cannot enforce RI checks on the location, but you can use a one to one to a INNODB to preserve the RI where possible and use a SPATIAL index
otherwise youcare going to have to write your own function to calculate the distance and or bearing information and that will almost certainly be a performance hog
As to querying rows you would need to index the location columns that will mean the query doesn't have to do a tablescan but instead use an index.I'd rather be riding on the Tiger 800 or the Norton