如何在sqlite中计算平方根

svgewumm  于 2023-06-30  发布在  SQLite
关注(0)|答案(7)|浏览(191)

我需要在sqlite数据库中计算欧氏距离。
除了编写和加载数学函数的动态库外,有谁知道如何在sqlite中计算平方根?
我接近诉诸快速反平方根算法在这里http://en.wikipedia.org/wiki/Fast_inverse_square_root虽然它可能会变成更有趣的比我需要现在。
顺便说一句,如果能弄清楚如何计算幂就好了(这是一个广义的问题,而且比将一个数字乘以它本身更干净)。
谢谢
西蒙尼

gv8xihay

gv8xihay1#

我有一个半答案。
是的,它涉及第三方,但你不必自己写:你查过this page上的最后一个分机号了吗?
它包括几个数学函数,其中包括sqrt()。

bvn4nwqk

bvn4nwqk2#

**警告:**此答案取决于编码语言。在我的例子中,C#

用户定义的SQLite函数对我来说是一个痛苦的实现。最后,经过长时间的搜索,我能够在我的C#代码中实现它。Main函数看起来像这样:

[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()));
    }
}

注册自定义函数:

SQLiteFunction.RegisterFunction(typeof(Sqrt));

并在select中使用:

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

你可以在这里下载完整的例子:http://db.tt/qzeNXwso
或者,如果你只想查看代码(或通过我的代码的所有部分),我粘贴下面的SQLite数据库中计算平方根的完整工作示例代码,因为很难找到任何工作代码。要创建并运行此示例,请执行以下6个步骤:
1.创建新项目(我的名字是Sqrt)
1.将SQLite引用包含到项目中:

  • 解决方案浏览器->参考(右键单击:添加引用)->程序集-扩展- System.Data.SQLite(选中)->确定 *

1.打开 App.config 并替换为这个(如果没有这个步骤,你可能会得到 Mixed mode assembly error):
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0"/>
</startup>
</configuration>
1.将 Form1.Designer.cs 替换为以下代码:

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;
 }
}

1.打开Form1.cs(code)并将代码替换为:使用系统;使用System.Data.SQLite;使用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;
        }
    }
}

1.奔跑吧,尽情享受。

htrmnn0y

htrmnn0y3#

对于10000以下的数,这是sqrt的近似值。它可以扩展到任意数量,并可以根据需要扩展到任意精度。这种表格插值是大多数快速实现中发生的事情:

case when weight >= 1 and weight<=10 then 1+0.240253073*(weight-1) 
     when weight>=10 and weight<=100 then 3.16227766+0.075974693*(weight-10) 
     when weight>=100 and weight<=1000 then 10+0.024025307*(weight-100)      
     else 31.6227766+0.007597469 *(weight-1000) end

还有一个奇怪的事实,你在这样一个10次方平方根插值表中使用的每个因子都是前一个因子的0.316227766倍-所以你可以让它适用于任意大的数字,甚至可以用这些值填充一个表格,使它适用于任何数字。(这会导致一些压缩吗?)
或者这个可爱的log 10整数,使用length函数(插值表可能在这里工作得更好,但我喜欢log 10和length()是相似的,这适用于任何整数-不需要插值。

((length(x)+length(x*2)+length(x*3)
  +length(x*4)+length(x*5))/5.0)-1.0

一个比我更好的数学头脑可能会想出更好和更密集的近似。考虑到c中的大多数sqrt函数无论如何都使用近似-这是一个相当不错的解决方案。

  • 这是当地人唯一的方式 *
ecfsfe2w

ecfsfe2w4#

据我所知-你不能只使用核心功能。
下面是原生函数Core functions的列表和聚合函数Aggregate functions的列表。
要解决这个问题,您可以编写自己的UDF(用户定义函数),如图所示HERE

55ooxyrt

55ooxyrt5#

仅当数学函数不可用时...真的只有在绝望中因为这不会很快

-- bisect to find the square root to any tolerance desired
with 
 input(n) as (select 500), --input
 sqrt(lo, hi, guess, n, i) as (
  select 1, n, n/2, n, 0 from input
  union all
  select case when guess*guess < n then guess else lo end,
         case when guess*guess < n then hi else guess end,
         case when guess*guess < n then (hi+guess)/2.0 else (lo+guess)/2.0 end,
         n ,
         i +1 from sqrt
         where abs(guess*guess - n) > 0.0001), -- tolerance
 sqrt_out(x, n) as (select guess, n from sqrt order by sqrt.i desc limit 1)
 select * from sqrt_out
8yoxcaq7

8yoxcaq76#

2021-03-12(3.35.0)

增加内置SQL数学函数()。(需要-DSQLITE_ENABLE_MATH_FUNCTIONS编译时选项。)
Built-In Mathematical SQL Functions
**sqrt(X)**返回X的平方根。如果X为负数,则返回NULL。

iovurdzv

iovurdzv7#

如果你需要的距离只是比较在您的选择,你不需要sqrt。你也可以和动力距离比较。。

poweredDistance = (x2 - x1)*(x2 - x1) + (y2 - y1)*(y2 - y1)

.和sqrt的距离稍后在你的代码以外的sqlite。
但如果你需要选择一个ceatain区域的gps点,这里是我在OpenAI(GPT-4)的帮助下制作的dart代码:

/// <pre>
///         [N++lat]
/// [W--lon]        [E++lon]
///         [S--lat]
///
/// Latitude zero is at Equator
/// Longitude zero is at GB London, Gateway to London Campus, University of the West of Scotland (UWS),
/// </pre>
class GpsArea {
    final GPS north;
    final GPS east;
    final GPS south;
    final GPS west;

    /// South border
    double get latMin => south.lat;

    /// North border
    double get latMax => north.lat;

    /// West border
    double get lonMin => west.lon;

    /// East border
    double get lonMax => east.lon;

    bool isInArea({required double lat, required double lon}) {
        return (lat >= latMin && lat <= latMax && lon >= lonMin && lon <= lonMax);
    }

    GpsArea(
            {required this.north,
            required this.east,
            required this.south,
            required this.west});

    /// based on ChatGPT-4 response
    static GpsArea calculateArea(
            {required double latitude,
            required double longitude,
            required int distance}) {
        // Constants for Earths radius in meters
        const earthRadius = 6371000.0;

        // Convert the start position to radians
        final startLatitudeRad = radians(latitude);
        final startLongitudeRad = radians(longitude);

        // Calculate distances in radians
        final latDistanceRad = distance / earthRadius;
        final lonDistanceRad = distance / (earthRadius * cos(startLatitudeRad));

        // Calculate new latitudes and longitudes
        final northernLatitude = asin(sin(startLatitudeRad) * cos(latDistanceRad) +
                cos(startLatitudeRad) * sin(latDistanceRad) * cos(0));
        final southernLatitude = asin(sin(startLatitudeRad) * cos(latDistanceRad) +
                cos(startLatitudeRad) * sin(latDistanceRad) * cos(180));

        final easternLongitude = startLongitudeRad +
                atan2(
                        sin(lonDistanceRad) * cos(startLatitudeRad),
                        cos(latDistanceRad) -
                                sin(startLatitudeRad) * sin(northernLatitude));
        final westernLongitude = startLongitudeRad -
                atan2(
                        sin(lonDistanceRad) * cos(startLatitudeRad),
                        cos(latDistanceRad) -
                                sin(startLatitudeRad) * sin(southernLatitude));

        // Convert the new latitudes and longitudes to degrees
        final northernLatitudeDeg = degrees(northernLatitude);
        final easternLongitudeDeg = degrees(easternLongitude);
        final southernLatitudeDeg = degrees(southernLatitude);
        final westernLongitudeDeg = degrees(westernLongitude);

        // Create the surrounding GPS points
        final north = GPS(northernLatitudeDeg, longitude);
        final east = GPS(latitude, easternLongitudeDeg);
        final south = GPS(southernLatitudeDeg, longitude);
        final west = GPS(latitude, westernLongitudeDeg);

        return GpsArea(north: north, east: east, south: south, west: west);

        /*
        void test() {
            final area =
                    calculateArea(latitude: 50, longitude: 30, distance: 1000.0);

            print("Northern Point: ${area.north.lat}, ${area.north.lon}");
            print("Eastern Point: ${area.east.lat}, ${area.east.lon}");
            print("Southern Point: ${area.south.lat}, ${area.south.lon}");
            print("Western Point: ${area.west.lat}, ${area.west.lon}");
        }

                            Northern Point: 50.008993216059196, 30
                            Eastern Point: 50, 30.021770141923543
                            Southern Point: 49.99100678394081, 30
                            Western Point: 50, 29.978238001159266
        */
    }
}

这将计算矩形面积。如果你需要圆,你可以先选择一个矩形,然后用距离计算方法切断边缘,就像这样:

/// found here: https://github.com/baseflow/flutter-geolocator/tree/main/geolocator
/// if I remember right

/// Calculates the distance between the supplied coordinates in meters.
///
/// The distance between the coordinates is calculated using the Haversine
/// formula (see https://en.wikipedia.org/wiki/Haversine_formula). The
/// supplied coordinates [startLatitude], [startLongitude], [endLatitude] and
/// [endLongitude] should be supplied in degrees.
static double distanceBetween(
    double startLatitude,
    double startLongitude,
    double endLatitude,
    double endLongitude,
) {
    var earthRadius = 6378137.0;
    var dLat = toRadians(endLatitude - startLatitude);
    var dLon = toRadians(endLongitude - startLongitude);

    var a = pow(sin(dLat / 2), 2) +
            pow(sin(dLon / 2), 2) *
                    cos(toRadians(startLatitude)) *
                    cos(toRadians(endLatitude));
    var c = 2 * asin(sqrt(a));

    return earthRadius * c;
}

static double toRadians(double degree) {
    return degree * pi / 180;
}

相关问题