Sqlite从点信息生成距离表-Sqlitegeneratedistancetablefrompointinformation

I have a table that contains coordinates of several points.

我有一个包含几个点坐标的表。

| PointName | XCoor | YCoor |
+-----------+-------+-------+
|    P1     |  X1   |  Y1   |
+-----------+-------+-------+
|    P2     |  X2   |  Y2   |
+-----------+-------+-------+
....

I want to generate a table that includes distance between two points.

我想要生成一个表，其中包括两点之间的距离。

| Point1Name | Point2Name | Distance |
+------------+------------+----------+
|    P1      |    P2      |  ZZZZZ   |
+------------+------------+----------+
....

To be able to calcualte the distance between two points, I enabled dynamic extension loading and used extension-functions module for arithmetic calculations. However, I am not quite sure if it is possible to create the output table with any sort of SQL tricks?

为了计算两点之间的距离，我启用了动态扩展加载，并使用扩展函数模块进行算术计算。但是，我不太确定是否可以使用任何SQL技巧创建输出表?

I need this functionelity in a C++ program, so in the worst case I will try to do it in the C++ way rather than using an SQL statement. This ,however, will require me to use sqlite3_exec() and define my callback function, and most probably I will end up running another sqlite3_exec() within the first callback funstion to get the desired output.

我需要在c++程序中使用这个函数，所以在最坏的情况下，我将尝试使用c++而不是SQL语句。但是，这需要我使用sqlite3_exec()并定义我的回调函数，并且很可能在第一个回调函数中运行另一个sqlite3_exec()来获得所需的输出。

Thanks in advance

谢谢提前

2 个解决方案

create table distances as
select a.PointName as Point1Name, b.PointName as Point2Name,
  sqrt((a.XCoor - b.XCoor) * (a.XCoor - b.XCoor) + (a.YCoor - b.YCoor) * (a.YCoor - b.YCoor)) as Distance
from points a, points b
where a.rowid != b.rowid

SELECT
   a.PointName AS Point1Name,
   b.PointName AS Point2Name, 
   POWER(POWER(a.xCoor - b.xCoor, 2) + POWER(a.yCoor - b.yCoor, 2), 1 / 2) AS Distance
FROM
   Points a, Points b
WHERE
   a.PointName <> b.PointName
;