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 <kmedc...@dessus.com>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
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 <math.h>
#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 = 0;
if (x < 0)
res = -1;
else if (x > 0)
res = 1;
sqlite3_result_int(context, res);
}
SQLITE_PRIVATE void _mantissa(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
int exponent;
sqlite3_result_double(context, frexp(sqlite3_value_double(argv[0]),
&exponent));
}
SQLITE_PRIVATE void _exponent(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
int exponent;
double mantissa = frexp(sqlite3_value_double(argv[0]), &exponent);
sqlite3_result_int(context, exponent);
}
SQLITE_PRIVATE void _fracpart(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
double ipart, fpart;
fpart = modf(sqlite3_value_double(argv[0]), &ipart);
sqlite3_result_double(context, fpart);
}
SQLITE_PRIVATE void _intpart(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
double ipart, fpart;
fpart = modf(sqlite3_value_double(argv[0]), &ipart);
sqlite3_result_double(context, ipart);
}
SQLITE_PRIVATE void _radians(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
sqlite3_result_double(context, sqlite3_value_double(argv[0]) * m_deg2rad);
}
SQLITE_PRIVATE void _degrees(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
sqlite3_result_double(context, sqlite3_value_double(argv[0]) * m_rad2deg);
}
#endif
/*
** Test (Set/Clear) Set/Clear bits
*/
/*
* isset(value, bit, bit, bit ...)
* return true if all bits are set in value
*/
SQLITE_PRIVATE void _isset(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
sqlite_uint64 value = sqlite3_value_int64(argv[0]);
sqlite_uint64 mask = 0;
int bit, i;
if (sqlite3_value_type(argv[0]) == SQLITE_NULL) return;
for (i=1; i<argc; i++)
{
if (sqlite3_value_type(argv[i]) == SQLITE_NULL)
continue;
bit = sqlite3_value_int(argv[i]);
if ((bit >= 0) && (bit <= 63))
mask |= (1ULL << bit);
}
sqlite3_result_int(context, (value & mask) == mask);
}
/*
* isclr(value, bit, bit, bit ...)
* return true if all bits are clr in value
*/
SQLITE_PRIVATE void _isclr(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
sqlite_uint64 value = sqlite3_value_int64(argv[0]);
sqlite_uint64 mask = 0;
int bit, i;
if (sqlite3_value_type(argv[0]) == SQLITE_NULL) return;
for (i=1; i<argc; i++)
{
if (sqlite3_value_type(argv[i]) == SQLITE_NULL)
continue;
bit = sqlite3_value_int(argv[i]);
if ((bit >= 0) && (bit <= 63))
mask |= (1ULL << bit);
}
sqlite3_result_int(context, (value & mask) == 0ULL);
}
/*
* ismaskset(value, mask)
* return true if all set bits in mask set in value
*/
SQLITE_PRIVATE void _ismaskset(sqlite3_context *context, int argc,
sqlite3_value **argv)
{
sqlite_uint64 value = sqlite3_value_int64(argv[0]);
sqlite_uint64 mask = sqlite3_value_int64(argv[1]);
if (sqlite3_value_type(argv[0]) == SQLITE_NULL) return;
if (sqlite3_value_type(argv[1]) == SQLITE_NULL) return;
sqlite3_result_int(context, (value & mask) == mask);
}
/*
* ismaskclr(value, mask)
* return true if all set bits set in mask are clr in value
*/
SQLITE_PRIVATE void _ismaskclr(sqlite3_context *context, int argc,
sqlite3_value **argv)
{
sqlite_uint64 value = sqlite3_value_int64(argv[0]);
sqlite_uint64 mask = sqlite3_value_int64(argv[1]);
if (sqlite3_value_type(argv[0]) == SQLITE_NULL) return;
if (sqlite3_value_type(argv[1]) == SQLITE_NULL) return;
sqlite3_result_int(context, (value & mask) == 0ULL);
}
/*
* bitmask(bit, bit, bit ...)
* return value of bitmask with bits set
*/
SQLITE_PRIVATE void _bitmask(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
sqlite_uint64 mask = 0;
int bit, i;
for (i=0; i<argc; i++)
{
if (sqlite3_value_type(argv[i]) == SQLITE_NULL)
continue;
bit = sqlite3_value_int(argv[i]);
if ((bit >= 0) && (bit <= 63))
mask |= (1ULL << bit);
}
sqlite3_result_int64(context, (sqlite_int64)mask);
}
/* setbits(value, bit, bit, ...)
* return value with bits set
*/
SQLITE_PRIVATE void _setbits(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
sqlite_uint64 value = sqlite3_value_int64(argv[0]);
int bit, i;
if (sqlite3_value_type(argv[0]) == SQLITE_NULL) return;
for (i=1; i<argc; i++)
{
if (sqlite3_value_type(argv[i]) == SQLITE_NULL)
continue;
bit = sqlite3_value_int(argv[i]);
if ((bit >= 0) && (bit <= 63))
value |= (1ULL << bit);
}
sqlite3_result_int64(context, value);
}
/* clrbits(value, bit, bit, ...)
* return value with bits cleared
*/
SQLITE_PRIVATE void _clrbits(sqlite3_context *context, int argc, sqlite3_value
**argv)
{
sqlite_uint64 value = sqlite3_value_int(argv[0]);
int bit, i;
if (sqlite3_value_type(argv[0]) == SQLITE_NULL) return;
for (i=1; i<argc; i++)
{
if (sqlite3_value_type(argv[i]) == SQLITE_NULL)
continue;
bit = sqlite3_value_int(argv[i]);
if ((bit >= 0) && (bit <= 63))
value &= ~(1ULL << bit);
}
sqlite3_result_int64(context, value);
}
/*
** bitmask aggregate -- set bits and return resulting mask
*/
SQLITE_PRIVATE void _bitmaskFinal(sqlite3_context *context)
{
sqlite_uint64 *mask = sqlite3_aggregate_context(context,
sizeof(sqlite_uint64));
sqlite3_result_int64(context, *mask);
}
SQLITE_PRIVATE void _bitmaskStep(sqlite3_context *context, int argc,
sqlite3_value **argv)
{
sqlite_uint64 *mask = sqlite3_aggregate_context(context,
sizeof(sqlite_uint64));
int bit, i;
for (i=0; i<argc; i++)
{
if (sqlite3_value_type(argv[i]) == SQLITE_NULL)
continue;
bit = sqlite3_value_int(argv[i]);
if ((bit >= 0) && (bit <= 63))
*mask |= (1ULL << bit);
}
}
SQLITE_PRIVATE void _bitmaskInverse(sqlite3_context *context, int argc,
sqlite3_value **argv)
{
sqlite_uint64 *mask = sqlite3_aggregate_context(context,
sizeof(sqlite_uint64));
int bit, i;
for (i=0; i<argc; i++)
{
if (sqlite3_value_type(argv[i]) == SQLITE_NULL)
continue;
bit = sqlite3_value_int(argv[i]);
if ((bit >= 0) && (bit <= 63))
*mask &= ~(1ULL << bit);
}
}
/*
** If compiling as a builtin extension, don't export the initializer -- make it
SQLITE_PRIVATE
*/
/* SQLite invokes this routine once when it loads the extension.
** Create new functions, collating sequences, and virtual table
** modules here. This is usually the only exported symbol in
** the shared library.
*/
#ifdef _WIN32
#ifndef SQLITE_CORE
__declspec(dllexport)
#endif
#endif
#ifdef SQLITE_CORE
static
#endif
int sqlite3_sqlmath_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *pApi)
{
int nErr = 0;
SQLITE_EXTENSION_INIT2(pApi);
#ifndef SQLITE_OMIT_FLOATING_POINT
nErr += sqlite3_create_function(db, "m_e", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_e, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "m_log2e", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_log2e, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "m_log10e", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_log10e, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "m_ln2", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_ln2, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "m_ln10", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_ln10, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "m_pi", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_pi, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "m_pi_2", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_pi_2, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "m_pi_4", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_pi_4, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "m_1_pi", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_1_pi, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "m_2_pi", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_2_pi, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "m_2_sqrtpi", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_2_sqrtpi, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "m_sqrt2", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_sqrt2, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "m_sqrt1_2", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_sqrt1_2, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "m_deg2rad", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_deg2rad, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "m_rad2deg", 0,
SQLITE_UTF8|SQLITE_DETERMINISTIC, &m_rad2deg, _dfc, 0, 0);
nErr += sqlite3_create_function(db, "acos", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, acos, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "asin", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, asin, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "atan", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, atan, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "chgsign", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, _chgsign, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "cos", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, cos, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "cosh", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, cosh, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "exp", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, exp, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "fabs", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, fabs, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "ln", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, log, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "log", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, log10, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "sin", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, sin, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "sinh", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, sinh, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "tan", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, tan, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "tanh", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, tanh, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "sqrt", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, sqrt, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "ceil", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, ceil, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "floor", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, floor, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "j0", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, _j0, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "j1", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, _j1, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "y0", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, _y0, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "y1", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, _y1, _dfd, 0, 0);
nErr += sqlite3_create_function(db, "atan2", 2,
SQLITE_UTF8|SQLITE_DETERMINISTIC, atan2, _dfdd, 0, 0);
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
>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
_______________________________________________
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
