How to calculate square root in sqlite

Question

I need to calculate an euclidean distance in a sqlite database.

Does anyone know how to calculate square roots in sqlite beside writing and loading a dynamic library for math functions?

I am close to resorting to the fast inverse square root algorithm in here http://en.wikipedia.org/wiki/Fast_inverse_square_root though it might to turn into more fun than I need right now.

And as a side note, it'd be great to figure out how to do power (which is the generalized question, and is cleaner coding than multiplying a number by itself).

Thanks,

Simone

Answer 1

Well, I have a semi-answer.

Yes it involves a 3rd party, but you don't have to write it yourself : did you check the last extension on this page ?

It includes several math functions, and amongst them is sqrt().

Answer 2

Warning: this answer is dependent on the coding language. In my case C#.

User defined SQLite functions was for me a pain to implement. Finally, after a long time of searching I was able to implement it in my C# code. Main function looks like this:

[SQLiteFunction(Arguments = 1, FuncType = FunctionType.Scalar, Name = "Sqrt")]
class Sqrt : SQLiteFunction
{
    public override object Invoke(object[] args)
    {
        return Math.Sqrt(Double.Parse(args[0].ToString()));
    }
}

Registration of custom function:

SQLiteFunction.RegisterFunction(typeof(Sqrt));

And using in select:

SQLiteCommand com = new SQLiteCommand("select sqrt(10.42)", connection);

You can download full example here: http://db.tt/qzeNXwso

Or, if you want only view code (or get through all parts of my code), I paste below full working example code for calculate square root in SQLite database, because is very hard to find any working code for this. To create and run this example do this 6 steps:

1. Create new project (my name is Sqrt)
2. Include SQLite reference to your project:
   Solution Explorer -> References (right click: Add reference) -> Assemblies - Extensions - System.Data.SQLite (check) -> OK

3. Open App.config and replace to this (without this step you maybe get Mixed mode assembly error):

   <?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0"/>
</startup>
</configuration>

4. Replace your Form1.Designer.cs with this code:

   namespace Sqrt
   {
    partial class Form1
    {
    /// <summary>
    /// Required designer variable.
    /// </summary>
    private System.ComponentModel.IContainer components = null;
   
    /// <summary>
    /// Clean up any resources being used.
    /// </summary>
    /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
    protected override void Dispose(bool disposing)
    {
       if (disposing && (components != null))
       {
           components.Dispose();
       }
       base.Dispose(disposing);
    }
   
    #region Windows Form Designer generated code
   
    /// <summary>
    /// Required method for Designer support - do not modify
    /// the contents of this method with the code editor.
    /// </summary>
    private void InitializeComponent()
    {
       this.txb_Input = new System.Windows.Forms.TextBox();
       this.txb_Output = new System.Windows.Forms.TextBox();
       this.label1 = new System.Windows.Forms.Label();
       this.label2 = new System.Windows.Forms.Label();
       this.btn_Calcualte = new System.Windows.Forms.Button();
       this.SuspendLayout();
       // 
       // txb_Input
       // 
       this.txb_Input.Location = new System.Drawing.Point(131, 12);
       this.txb_Input.Name = "txb_Input";
       this.txb_Input.Size = new System.Drawing.Size(201, 20);
       this.txb_Input.TabIndex = 0;
       // 
       // txb_Output
       // 
       this.txb_Output.BackColor = System.Drawing.Color.WhiteSmoke;
       this.txb_Output.Location = new System.Drawing.Point(131, 38);
       this.txb_Output.Name = "txb_Output";
       this.txb_Output.ReadOnly = true;
       this.txb_Output.Size = new System.Drawing.Size(201, 20);
       this.txb_Output.TabIndex = 0;
       // 
       // label1
       // 
       this.label1.AutoSize = true;
       this.label1.Location = new System.Drawing.Point(12, 15);
       this.label1.Name = "label1";
       this.label1.Size = new System.Drawing.Size(31, 13);
       this.label1.TabIndex = 1;
       this.label1.Text = "Input";
       // 
       // label2
       // 
       this.label2.AutoSize = true;
       this.label2.Location = new System.Drawing.Point(12, 41);
       this.label2.Name = "label2";
       this.label2.Size = new System.Drawing.Size(39, 13);
       this.label2.TabIndex = 1;
       this.label2.Text = "Output";
       // 
       // btn_Calcualte
       // 
       this.btn_Calcualte.Location = new System.Drawing.Point(257, 64);
       this.btn_Calcualte.Name = "btn_Calcualte";
       this.btn_Calcualte.Size = new System.Drawing.Size(75, 23);
       this.btn_Calcualte.TabIndex = 2;
       this.btn_Calcualte.Text = "Calculate";
       this.btn_Calcualte.UseVisualStyleBackColor = true;
       // 
       // Form1
       // 
       this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
       this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
       this.ClientSize = new System.Drawing.Size(344, 98);
       this.Controls.Add(this.btn_Calcualte);
       this.Controls.Add(this.label2);
       this.Controls.Add(this.label1);
       this.Controls.Add(this.txb_Output);
       this.Controls.Add(this.txb_Input);
       this.Name = "Form1";
       this.Text = "Root square example";
       this.ResumeLayout(false);
       this.PerformLayout();
    }
    #endregion
   
    private System.Windows.Forms.TextBox txb_Input;
    private System.Windows.Forms.TextBox txb_Output;
    private System.Windows.Forms.Label label1;
    private System.Windows.Forms.Label label2;
    private System.Windows.Forms.Button btn_Calcualte;
    }
   }
   

5. Open Form1.cs (code) and replace code with this: using System; using System.Data.SQLite; using System.Windows.Forms;

   namespace Sqrt
   {
       // definition of custom sqlite function
       [SQLiteFunction(Arguments = 1, FuncType = FunctionType.Scalar, Name = "Sqrt")]
       class Sqrt : SQLiteFunction
       {
           public override object Invoke(object[] args)
           {
               return Math.Sqrt(Double.Parse(args[0].ToString())); // return result of math sqrt function
           }
       }
   
       public partial class Form1 : Form
       {
           public Form1()
           {
               InitializeComponent();
               this.btn_Calcualte.Click += new System.EventHandler(this.btn_Calcualte_Click);
           }
   
           private void btn_Calcualte_Click(object sender, EventArgs e)
           {
               if (txb_Input.Text.Length == 0)
                   return;
               try { SQLiteConnection.CreateFile(AppDomain.CurrentDomain.BaseDirectory + "test.s3db"); }
               catch { }
               SQLiteConnection con = new SQLiteConnection("Data Source=test.s3db");
               SQLiteFunction.RegisterFunction(typeof(Sqrt)); // register custom function
               con.Open();
               SQLiteCommand com = new SQLiteCommand("select sqrt(" + txb_Input.Text.Replace(',', '.') + ")", con); // select result
               string res = com.ExecuteScalar().ToString();
               txb_Output.Text = res;
           }
       }
   }
   

6. Run, try and enjoy.