View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0001728||SkyChart||1-Software||public||17-06-05 06:09||17-06-25 11:55|
|Reporter||Sasa||Assigned To||Patrick Chevalley|
|Priority||normal||Severity||tweak||Reproducibility||have not tried|
|Platform||Linux 64-bit||OS||Lubuntu||OS Version||17.04|
|Product Version||4.1 SVN|
|Target Version||4.2||Fixed in Version||4.1 SVN|
|Summary||0001728: Searching asteroids and comets - a straightforward approach|
|Description||A straightforward approach may be the best solution when searching asteroid and comets using exact or partial name. |
|Tags||No tags attached.|
FindCometName.patch (907 bytes)
function TPlanet.FindCometName(comname: String; var ra,de,mag:double; cfgsc: Tconf_skychart; upddb:boolean; MeanPos:boolean=false):boolean; var dist,r,elong,phase,rad,ded : double; epoch,h,g,ap,an,ic,ec,eq,tp,q,diam,lc,car,cde,rc,xc,yc,zc : double; qry,id,nam,elem_id,s1,s2 :string; i,ira,idec,imag: integer; begin result := false; searchid := ''; if (not db1.Active) or (not cfgsc.ephvalid) then exit; s1 := UpperCase(trim(comname)); if s1 = '' then exit; id := ''; qry := 'SELECT id FROM cdc_com_name' + ' where name like "' + s1 + '"'; db1.Query(qry); if db1.RowCount > 0 then id := db1.QueryOne(qry); if id = '' then begin qry := 'SELECT id FROM cdc_com_name' + ' where name like "%'+ s1 +'%"' + ' limit 1'; db1.Query(qry); if db1.RowCount > 0 then id := db1.QueryOne(qry); end; if id = '' then exit;
FindCometName.patch (907 bytes)
In #1724 (http://www.ap-i.net/mantis/view.php?id=1724) I have added patch for solving the issue. My line of though was based on testing and speed of query on slow PC with 1GB of RAM.
The first though was simply a straightforward approach I attached here, then I notice two queries was even slower than one (retrieving maximum of few dozen rows), additionally perhaps using full text search (FTS feature offer mySQL and SQLite). However, CDC do not offer FTS feature of any of these DB engines, perhaps for the reason (enlarging size of database and slow addition), even benefit with partial name search and perhaps it is not worthed.
In that case and in case of PCs with larger amount of RAM (which is usual in modern PCs), simply straightforward approach is better choice.
1. First call to support exact name search (case insensitive)
2. Second (as it was before) to support partial name search, if first fail.
"I do the same for the comet but I not have a test case in the current data."
One test case my include simply searching "c/", returning 332P-C/Ikeya-Murakami.
"And in 3601 I add some safety limit to avoid to loop the 700000 asteroids if the search name is null"
This can be avoided if trim searched name first and exit if it is empty. Few dozen rows are maximum amount of some proper partial name search.
I like this new way that avoid to return lot of data for further filtering.
I use your patch for both comet and asteroid after a few change:
- use "=" instead of "LIKE" in the first query as this can be a bit more efficient, depending on the database engine.
- use the UPPER() function in the query because the names in database are not uppercase.
- Do not redo the query (queryone) when we already have the result.
This is in rev 3605:
|17-06-05 06:09||Sasa||New Issue|
|17-06-05 06:09||Sasa||File Added: FindCometName.patch|
|17-06-05 06:39||Sasa||Note Added: 0003851|
|17-06-05 07:15||Sasa||Note Added: 0003853|
|17-06-05 07:26||Sasa||Note Edited: 0003853||View Revisions|
|17-06-25 11:55||Patrick Chevalley||Assigned To||=> Patrick Chevalley|
|17-06-25 11:55||Patrick Chevalley||Status||new => resolved|
|17-06-25 11:55||Patrick Chevalley||Resolution||open => fixed|
|17-06-25 11:55||Patrick Chevalley||Fixed in Version||=> 4.1 SVN|
|17-06-25 11:55||Patrick Chevalley||Target Version||=> 4.2|
|17-06-25 11:55||Patrick Chevalley||Note Added: 0003862|