SQL Query Question

[borg359]

Hi everyone,
I have a pretty basic question about SQL queries. I have a database that contains the latitude and longitude as well as the name and other information of particular markers around the country. Finding all the markers within a certain area around the user's current location requires a complex query involving both the latitude and longitude. For example:

sprintf (nameCommand, "select name from Markers where(longitude between %f and %f AND latitude between %f and %f)", longitudeMin, longitudeMax, latitudeMin, latitudeMax);
sqlite3_exec(database, nameCommand, NameCallback, names, NULL);

Besides the name, there are 6 other parameters that I'd like to obtain for each of these markers and issuing this query 6 more times ends up taking an unacceptable amount of time.

So my question is this: Once I've identified which markers fulfill my search criteria, is there a way to use the index of that search to extract the other 6 parameters without having to search over the latitude and longitude columns 6 additional times? This would be something akin to:

i = where(LatitudeArray > x)
NameSubset = NameArray[i]
Property1Subset = Property1Array[i]
Property2Subset = Property2Array[i]
etc...

Any suggestions on how to get this done in SQL would be greatly appreciated!

-dan

[kalx]

Are the other parameters you want to obtain in the same table? If so you can easily fetch multiple parameters in one query by:

select name, AnotherColumnName, AnotherColumnName, etc from Markers where(longitude between %f and %f AND latitude between %f and %f)", longitudeMin, longitudeMax, latitudeMin, latitudeMax)

If they are in different tables you can most likely make use of the INNER JOIN operation to combine different tables and get all the data you want in one query.

- kalx

[rarindeed]

Also, are you setting up indices? It would speed up query exec times.

[borg359]

Thanks for the suggestion kalx. All of the parameters are indeed in a single table. The only problem is that my current callback functions are designed to expect only a single parameter list per query. Do you know of any examples of callback functions that can parse multiple parameters in a single query?

I've also read a lot about the performance improvements that can be achieved through indexing, but I haven't been able to find any workable examples of how this is done. If anyone knows of any, I'd love to see them. Thanks!


-dan

[kalx]

Originally Posted by borg359

Thanks for the suggestion kalx. All of the parameters are indeed in a single table. The only problem is that my current callback functions are designed to expect only a single parameter list per query. Do you know of any examples of callback functions that can parse multiple parameters in a single query?

I've also read a lot about the performance improvements that can be achieved through indexing, but I haven't been able to find any workable examples of how this is done. If anyone knows of any, I'd love to see them. Thanks!


-dan

Hi Dan,
Are you sure about that? I've never used sql_exec and the callback feature, but from looking at the function, it looks it already supports multiple parameters.

Here's the prototype for the callback function:

int Callback(void *pArg, int argc, char **argv, char **columnNames)

int argc: Specifies the number of parameters/columns
char **argv: This is basically an array of char strings containing the value for each parameter/column
char **columnNames: This is an array of char strings containing the name of each parameter/column

Example:

int Callback(void *pArg, int argc, char **argv, char **columnNames) {

   //Loop through all the parameters
   for (int i=0; i < argc; i++) {
      NSLog(@"Column #%d Name:%s  with Value: %s", i, columnNames[i], argv[i]);
   }

}

I haven't tested this code so there could be some syntax errors, etc, but you should be able to get the gist of it.

The callback will get called for each record in the result set from your query.

- kalx