View Issue Details

IDProjectCategoryView StatusLast Update
0001728SkyChart1-Softwarepublic17-06-25 11:55
ReporterSasa Assigned ToPatrick Chevalley  
PrioritynormalSeveritytweakReproducibilityhave not tried
Status resolvedResolutionfixed 
PlatformLinux 64-bitOSLubuntuOS Version17.04
Product Version4.1 SVN 
Target Version4.2Fixed in Version4.1 SVN 
Summary0001728: Searching asteroids and comets - a straightforward approach
DescriptionA straightforward approach may be the best solution when searching asteroid and comets using exact or partial name.

Attached example.
TagsNo tags attached.

Activities

Sasa

17-06-05 06:09

reporter  

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)   

Sasa

17-06-05 06:39

reporter   ~0003851

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.

Sasa

17-06-05 07:15

reporter   ~0003853

Last edited: 17-06-05 07:26

0001724:0003840

"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.

Patrick Chevalley

17-06-25 11:55

administrator   ~0003862

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:
https://sourceforge.net/p/skychart/code/3605

Issue History

Date Modified Username Field Change
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
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