fastest way to search in database

Put Claude · #1 (**permalink**) 10-05-2007, 01:19 PM

hi,

Have 1500 utm waypoint in a table, what is the fastest way to search for a match?
By now, I have not the time between two fetches of GPS to search for a match.

Put Claude Belgium

dzt · #2 (**permalink**) 10-05-2007, 02:01 PM

Hi,

Store them in a SQLite database table and run SQL SELECT queries. It is much faster than filtering a table control.

alfcen · #3 (**permalink**) 10-05-2007, 02:10 PM

Hello Put,

Hard to say without seeing the data and without knowing what you wish to match, however, you could save the table as XML and use the RegEx.dll to match a criteria, such as:

Code:

Match1.New1
Regex1.New2("<longitude\b[^>]*>(.*?)</longitude>", true, false)
Match1.Value = Regex1.Match(m)
longitude=StrReplace(Match1.String,"<longitude>","")
longitude=StrReplace(longitude,"</longitude>","")

This example will pick a longitude embraced in the XML <longitude> </longitude> tags or whatever the tag names is in the XML file.

I have only little experience with the RegEx.dll, but it seems to be very fast. Please have a look at Help for the RegEx.dll.

Cheers
Robert

Erel · #4 (**permalink**) 10-05-2007, 02:56 PM

You can also try to use ArrayList.IndexOf method.
BTW, what do you mean with "between two fetches of a GPS"?
Maybe you need to increase the interval.

Put Claude · #5 (**permalink**) 10-06-2007, 11:48 AM

Yes Erel,

Between 2 NMEA sentences, I have to work with the fetched data...(every second)
In a list of 1500 waypoint, I have to search for match with my actual position point from the sentence.
Suppose I store my position on the last place of the waypointlist, I have to search the list to the end...
On my 400MHz PPC, the time to search the whole waypointlist takes 15 seconds, only to see of a point is in a square with a simple validation routine...
This is what you never hear on a forum... in fact this must be no big deal, because other PPC navigation software must do the job I think.
I am going to try your suggestion to work with arrays, if this is will not do the job, I assume there are special algoritme for PPC use...
Of coarse, I let you hear the results ( otherwise there was no need for a forum |:-)
By the way Erel: it is luxery in speed and use, working with B4PPC

Put Claude Belgium

Erel · #6 (**permalink**) 10-06-2007, 12:15 PM

Can you upload your code? It may be interesting to try to optimize it.

Put Claude · #7 (**permalink**) 10-06-2007, 12:51 PM

Of coarse it can, give me a day to restore the little changes made to experiment with the data, then you have the absolute working stuff with a lot of comment. I use it very often with a short waypoints list, it warns me for the camera poals in my enviroment where I usely do not think about them...
It warns constantly wen I drive towart, stop warning when I stop / are driving away from the poal, it is made to comfort. Can set or remove waypoints in a easy way while driving, etc... Also (re)connecting Bluetooth is automated...
Wil also write a little Help for it... Thanks for the interest anyway...
I prefare this above many commercial stuff with fancy things, that you can not use, especialy when driving... Oh yes, there is a plotter to, you can drag the plot file into Google Earth to see your plot with speed and all (so I can prove my bike did 150 on that piece of road ;-) ). Have compassion with my code: I am a beginner, and it is still under construction, all comments are welcome...

Put Claude Belgium

Put Claude · #8 (**permalink**) 10-06-2007, 07:45 PM

Hi,

Tryed arrays of doubles with UTM point, so being close to just do a compare for a match...
Only gain a few seconds, still get 13 seconds for 1500 points. Have to studie search algorithme...

Put Claude Belgium

LineCutter · #9 (**permalink**) 10-06-2007, 09:20 PM

Small tables work, large tables are too slow, but work. You're using GPS data, so can't jump from one end of the table to another without passing through a fair amount of it.

Sort the big data file
Find your start location & filter Lat & Long within a reasonable distance of this (how fast do you drive?).
Copy this to another table & refer to this for the immediate data.
At some distance from the boundary of the GPS data for your filtered table you delete the "wrong" side & add by using a filter the data for the same distance in the direction you are traveling.

Haven't tested this for speed with the filter process in the background & the coordinate processing in full flow.

Alternative would be to use a list & a binary sort routine with boundaries set according to the region of interest, rather than the whole table each time.

Put Claude · #10 (**permalink**) 10-07-2007, 10:07 AM

hi LineCutter,

Was thinking this way to, to eliminate as much point as possible, but from the old times, I now some search algorithme that eliminates in jumps of halve the list, so the point of interest from the list must be reached, lets say for a list of 1600 points: in 9 to 10 compares, if this is working, the solution is there... I let the forum now in what I succeeded, and in what not, because I think this is usable for other app to...
Thanks for the interest...

Put Claude Belgium