sqlite trig/geometry error

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite trig/geometry error

Brent Wood
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite trig/geometry error

Jean-Christophe Deschamps-3

>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite trig/geometry error

Keith Medcalf
In reply to this post by Brent Wood

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-
>[hidden email]] On Behalf Of Brent Wood
>Sent: Wednesday, 2 January, 2019 11:19
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite trig/geometry error

Brent Wood


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 <[hidden email]>
To: SQLite mailing list <[hidden email]>
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-
>[hidden email]] On Behalf Of Brent Wood
>Sent: Wednesday, 2 January, 2019 11:19
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite trig/geometry error

Clemens Ladisch
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite trig/geometry error

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 <[hidden email]>
To: [hidden email]
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

[hidden email]

http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite trig/geometry error

Dingyuan Wang
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 <[hidden email]>
> To: [hidden email]
> 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
>
> [hidden email]
>
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users