Re: [sqlite] sqlite trig/geometry error
Hello,
Obviously these trig functions comes from SpatiaLite.
http://www.gaia-gis.it/gaia-sins/spatialite-sql-5.0.0.html
2019/1/4 1:01, Brent Wood:
> Yep, either a SQL file run from a shell script or SQL's directly piped into
> the sqlite3 command, eg:
>
>
>
> echo "insert into qgis_gps
> (stat_code, station_no, point_type, depth, geom)
>values
> ('R1A1',2,'s',23, setsrid(makepoint(175.5,-43.5),4326));" | \
>sqlite3 -cmd "SELECT load_extension('mod_spatialite');" $DB
>
> Thanks...
>
>
> From: Clemens Ladisch
> To: sqlite-users@mailinglists.sqlite.org
> Sent: Thursday, January 3, 2019 10:52 PM
> Subject: Re: [sqlite] sqlite trig/geometry error
>
>
>
> Brent Wood wrote:
>
>> Is there an easy way to tell where the sqlite math functions are coming from?
>
>
> Are you using the sqlite3 command-line shell, or something else?
>
>
>
> Regards,
>
> Clemens
>
> ___
>
> sqlite-users mailing list
>
> sqlite-users@mailinglists.sqlite.org
>
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite trig/geometry error
Yep, either a SQL file run from a shell script or SQL's directly piped into the
sqlite3 command, eg:
echo "insert into qgis_gps
(stat_code, station_no, point_type, depth, geom)
values
('R1A1',2,'s',23, setsrid(makepoint(175.5,-43.5),4326));" | \
sqlite3 -cmd "SELECT load_extension('mod_spatialite');" $DB
Thanks...
From: Clemens Ladisch
To: sqlite-users@mailinglists.sqlite.org
Sent: Thursday, January 3, 2019 10:52 PM
Subject: Re: [sqlite] sqlite trig/geometry error
Brent Wood wrote:
> Is there an easy way to tell where the sqlite math functions are coming from?
Are you using the sqlite3 command-line shell, or something else?
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite trig/geometry error
Brent Wood wrote: > Is there an easy way to tell where the sqlite math functions are coming from? Are you using the sqlite3 command-line shell, or something else? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite trig/geometry error
Thanks Keith...
I was indeed surprised to get a result at all - I have no idea where the math
functions are coming from - I'm using the standard Ubuntu Linux install...
which it seems may be broken in some way.
The only files I can find on my system relating to these functions are indeed
the GCC math libraries...
The workaround is giving an equivalent solution for sqlite, which meets my
needs for now...
FYI, the use case is a sql trigger, which, when inserting a file of random
positions, automatically generates a "rosette" of 6 points which are
equidistant sample locations around each random position. A series of triggers
drives a database back end using QGIS to save GPS points into a sqlite table as
a means of capturing survey site data. QGIS displays the target locations and
is used a navigation tool (we are sampling at sea) as well as a data capture
tool.
Is there an easy way to tell where the sqlite math functions are coming from?
I guess my next step should be to test each function individually to find out
which one(s) are causing the problem and progress it from there.
Cheers
Brent Wood
From: Keith Medcalf
To: SQLite mailing list
Sent: Thursday, January 3, 2019 9:57 AM
Subject: Re: [sqlite] sqlite trig/geometry error
SQLite 3.27.0 2018-12-31 21:43:55
b57c545a384ab5d62becf3164945b32b1e108b2fb4c8dbd939a1706c2079alt2
zlib version 1.2.11
gcc-8.1.0
sqlite> select degrees( radians(175) + atan2( sin(radians(90)) *
...> sin(0.2/6378.14)*cos(radians(-42)), cos(0.2/6378.14) -
...> sin(radians(-42))*sin(radians(-42;
QUERY PLAN
`--SCAN CONSTANT ROW
175.002417603759
sqlite>
Works fine for me when those trig functions are the standard library functions
from the GCC Math Library (I linked them myself -- you do not say how you
managed to get them since they do not exist in the default build). That you do
not get a correct answer would indicate that whatever is implementing those
functions has implemented them incorrectly.
I have implemented the "math" library into SQLite3 using the following
extension I wrote (which also includes some other useful functions and
constants) but simply dispatches the functions to the platform runtime (I use
GCC, so the GCC Math Library. If you compile elsewhere it will dispatch to
whatever function calls the compiler uses. If the platform compiler math
library is broken, then it will be broken. Otherwise it will produce whatever
the underlying platform thinks is the correct answer.) I have tested that it
works with GCC and MSVC and works within the capabilities of those compiler
platforms to work correctly.
#ifdef __cplusplus
extern "C" {
#endif
#include
#ifndef SQLITE_PRIVATE
#define SQLITE_PRIVATE static
#endif
#ifdef SQLITE_CORE
#include "sqlite3.h"
#else
#ifdef _HAVE_SQLITE_CONFIG_H
#include "config.h"
#endif
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
#endif
/*
** Hooks into the MSVC/GCC Runtime
** Return Constants, call Runtime Functions
*/
#ifndef SQLITE_OMIT_FLOATING_POINT
typedef double (*dfdPtr)(double);
typedef double (*dfddPtr)(double, double);
typedef double (*dfidPtr)(int, double);
typedef double (*dfdiPtr)(double, int);
#ifndef LONGDOUBLE_TYPE
#if defined(__GNUC__)
#define LONGDOUBLE_TYPE __float128
#else
#define LONGDOUBLE_TYPE long double
#endif
#endif
SQLITE_PRIVATE LONGDOUBLE_TYPE m_deg2rad, m_rad2deg, m_pi, m_pi_2, m_pi_4, m_e,
m_ln2, m_ln10, m_log2e, m_log10e;
SQLITE_PRIVATE LONGDOUBLE_TYPE m_1_pi, m_2_pi, m_2_sqrtpi, m_sqrt2, m_sqrt1_2;
/*
** Return Mathematical Constants
*/
SQLITE_PRIVATE void _dfc(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
sqlite3_result_double(context,
*((LONGDOUBLE_TYPE*)sqlite3_user_data(context)));
}
/*
** Dispatch to Math Library (Runtime) Functions
*/
SQLITE_PRIVATE void _dfd(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
sqlite3_result_double(context,
(*(dfdPtr)sqlite3_user_data(context))(sqlite3_value_double(argv[0])) );
}
SQLITE_PRIVATE void _dfdd(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
sqlite3_result_double(context,
(*(dfddPtr)sqlite3_user_data(context))(sqlite3_value_double(argv[0]),sqlite3_value_double(argv[1])));
}
SQLITE_PRIVATE void _dfid(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
sqlite3_result_double(context,
(*(dfidPtr)sqlite3_user_data(context))(sqlite3_value_int(argv[0]),sqlite3_value_double(argv[1])));
}
SQLITE_PRIVATE void _dfdi(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
sqlite3_result_double(context,
(*(dfdiPtr)sqlite3_user_data(context))(sqlite3_value_double(argv[0]),sqlite3_value_int(argv[1])));
}
/*
** Specific Mathematical Functions
*/
SQLITE_PRIVATE void _sign(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
double x = sqlite3_value_double(argv[0]);
int res =
Re: [sqlite] sqlite trig/geometry error
nErr += sqlite3_create_function(db, "copysign", 2,
SQLITE_UTF8|SQLITE_DETERMINISTIC, _copysign, _dfdd, 0, 0);
nErr += sqlite3_create_function(db, "fmod", 2,
SQLITE_UTF8|SQLITE_DETERMINISTIC, fmod, _dfdd, 0, 0);
nErr += sqlite3_create_function(db, "pow", 2,
SQLITE_UTF8|SQLITE_DETERMINISTIC, pow,_dfdd, 0, 0);
nErr += sqlite3_create_function(db, "hypot",2,
SQLITE_UTF8|SQLITE_DETERMINISTIC, _hypot, _dfdd, 0, 0);
nErr += sqlite3_create_function(db, "jn", 2,
SQLITE_UTF8|SQLITE_DETERMINISTIC, _jn,_dfid, 0, 0);
nErr += sqlite3_create_function(db, "yn", 2,
SQLITE_UTF8|SQLITE_DETERMINISTIC, _yn,_dfid, 0, 0);
nErr += sqlite3_create_function(db, "ldexp",2,
SQLITE_UTF8|SQLITE_DETERMINISTIC, ldexp, _dfdi, 0, 0);
nErr += sqlite3_create_function(db, "mantissa", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _mantissa, 0, 0);
nErr += sqlite3_create_function(db, "exponent", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _exponent, 0, 0);
nErr += sqlite3_create_function(db, "trunc",1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _intpart,0, 0);
nErr += sqlite3_create_function(db, "frac", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _fracpart, 0, 0);
nErr += sqlite3_create_function(db, "degrees", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _degrees,0, 0);
nErr += sqlite3_create_function(db, "radians", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _radians,0, 0);
nErr += sqlite3_create_function(db, "sign", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _sign, 0, 0);
#endif
nErr += sqlite3_create_function(db, "isset", -1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _isset, 0, 0);
nErr += sqlite3_create_function(db, "isclr", -1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _isclr, 0, 0);
nErr += sqlite3_create_function(db, "setbits", -1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _setbits,0, 0);
nErr += sqlite3_create_function(db, "clrbits", -1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _clrbits,0, 0);
nErr += sqlite3_create_function(db, "bitmask", -1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _bitmask,0, 0);
nErr += sqlite3_create_function(db, "ismaskset", -1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _ismaskset, 0, 0);
nErr += sqlite3_create_function(db, "ismaskclr", -1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _ismaskclr, 0, 0);
#if defined(SQLITE3_OMIT_WINDOWFUNC)
nErr += sqlite3_create_function(db, "aggbitmask", -1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, 0, _bitmaskStep, _bitmaskFinal);
#else
nErr += sqlite3_create_window_function(db, "aggbitmask", -1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _bitmaskStep, _bitmaskFinal,
_bitmaskFinal, _bitmaskInverse, 0);
#endif
#ifndef SQLITE_OMIT_FLOATING_POINT
{
register LONGDOUBLE_TYPE x;
x = asinl(1.0L);
m_pi_2 = x;
m_pi_4 = x / 2.0L;
m_pi = x * 2.0L;
m_1_pi = 0.5L / x;
m_2_pi = 1.0L / x;
m_2_sqrtpi = 2.0L / sqrtl(x * 2.0L);
m_deg2rad = x / 90.0L;
m_rad2deg = 90.0L / x;
}
m_e = expl(1.0L);
m_log10e = log10l(expl(1.0L));
m_sqrt2 = sqrtl(2.0L);
m_ln2 = logl(2.0L);
m_ln10 = logl(10.0L);
m_log2e = 1.0L / logl(2.0);
m_sqrt2 = sqrtl(2.0L);
m_sqrt1_2 = sqrtl(0.5L);
#endif
return nErr ? SQLITE_ERROR : SQLITE_OK;
}
#ifdef __cplusplus
}
#endif
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Brent Wood
>Sent: Wednesday, 2 January, 2019 11:19
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] sqlite trig/geometry error
>
>Hi,
>
>I'm trying to create an SQL in Sqlite3 which returns the X &
>Y coords for a point, given origin coordinates in degrees, with
>distance
>and bearing to the new location.
>
>My install of Spatialite (Mint Linux) does not support the Project()
>function, and it seems easier to write the SQL required, based on the
>code at this site, than sort this out:
>https://www.movable-type.co.uk/scripts/latlong.html
>
>I implemented the SQL for calculating the lat which works fine. I
>t
Re: [sqlite] sqlite trig/geometry error
sqlite> select degrees( radians(175) + atan2( sin(radians(90)) * sin(0.2/6378.14)*cos(radians(-42)), cos(0.2/6378.14) - sin(radians(-42))*sin(radians(-42; 264.997582396241 postgres=# select degrees( radians(175) + atan2( sin(radians(90)) * sin(0.2/6378.14)*cos(radians(-42)), cos(0.2/6378.14) - sin(radians(-42))*sin(radians(-42; degrees -- 175.002417603759 Are you sure the implementation of the functions degrees() and radians() are OK? When I try this I get the correct result: select 57.295779513082320876798154814105 * (175 * 0.01745329251994329576923690768489 + atan2(sin(90 * 0.01745329251994329576923690768489) * sin(0.2 / 6378.14) * cos(-42 * 0.01745329251994329576923690768489), cos(0.2 / 6378.14) - sin(-42*0.01745329251994329576923690768489) * sin(-42 * 0.01745329251994329576923690768489))); Output: 175,002417603759 That means that the trig functions in SQLIte are working fine, expectedly. Excuse the large number of pointless decimals, I just copy-pasted the values from a calculator. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite trig/geometry error
Hi, I'm trying to create an SQL in Sqlite3 which returns the X & Y coords for a point, given origin coordinates in degrees, with distance and bearing to the new location. My install of Spatialite (Mint Linux) does not support the Project() function, and it seems easier to write the SQL required, based on the code at this site, than sort this out: https://www.movable-type.co.uk/scripts/latlong.html I implemented the SQL for calculating the lat which works fine. I then wrestled with the SQL for longitude, which kept giving wrong answers. I tried it in a spreadsheet (Libre Office) which works fine. I then tried the same SQL in Postgres - and got the correct answer. Can anyone suggest a way to get this working in Sqlite3? For a point at 42S 175E moved east 200m: sqlite> select degrees( radians(175) + atan2( sin(radians(90)) * sin(0.2/6378.14)*cos(radians(-42)), cos(0.2/6378.14) - sin(radians(-42))*sin(radians(-42; 264.997582396241 postgres=# select degrees( radians(175) + atan2( sin(radians(90)) * sin(0.2/6378.14)*cos(radians(-42)), cos(0.2/6378.14) - sin(radians(-42))*sin(radians(-42; degrees -- 175.002417603759 Note that I have a workaround which should be OK for the small distances I'm using: Lat: select -42 + (200*cos(radians(0))/110540); Lon: select 175+(200*sin(radians(0))/(111320/cos(radians(-42; (change the "0" to the angle required and the "200" to the distance in m required, -42=start lat, 175 = start lon) Thanks Brent Wood ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
