sin and similar functions

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

sin and similar functions

Bugzilla from ladmanj@volny.cz
Hi Friends

Is there a possibility to have sin cos an acos, functions in sqlite?
How to write it in?
I really need it and i have fear, that sinus values table will be so slow and
so big for critical precission.

I will need to select coordinates which are laying in circle of known center
and radius.
Unfortunately at small time intervals and on a slow machine.

Thanks

Jakub

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sin and similar functions

Bugzilla from ladmanj@volny.cz
I am sorry, it seems to be unreadable bacause of my very bad english.
I try to correct it little bit.

Hi Friends :-)

Is there a possibility to have sin, cos anD acos, functions in sqlite?
How to write it in? I have read (Mike Owens article at LinuxJournal), that it
is possible to write new function to expand standard set of functions as
avg(), min(), sum() etc.

I will have table with sequence of coordinates (two dimensional space) and
corresponding radiuses, so sequence of circles. And i need to use a sqlite
query to detect if a actual coordinates (after their measurement) match some
of the circle's square or not. And which circle, if match.
And this must be for low CPU consumption optimised, so i am not sure, if
separate sin table queries will be enough as fast as i need at needed
precission.

The whole algorithm is proven on mssql by my colegue, but he is using the
native math functions.

Thanks

Jakub

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sin and similar functions

John Stanton-3
In reply to this post by Bugzilla from ladmanj@volny.cz
Just add a sine function in the normal manner of adding a function.  It
is very simple.

Jakub Ladman wrote:

> Hi Friends
>
> Is there a possibility to have sin cos an acos, functions in sqlite?
> How to write it in?
> I really need it and i have fear, that sinus values table will be so slow and
> so big for critical precission.
>
> I will need to select coordinates which are laying in circle of known center
> and radius.
> Unfortunately at small time intervals and on a slow machine.
>
> Thanks
>
> Jakub
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sin and similar functions

Dennis Cote
In reply to this post by Bugzilla from ladmanj@volny.cz
Jakub Ladman wrote:

> I will have table with sequence of coordinates (two dimensional space) and
> corresponding radiuses, so sequence of circles. And i need to use a sqlite
> query to detect if a actual coordinates (after their measurement) match some
> of the circle's square or not. And which circle, if match.
> And this must be for low CPU consumption optimised, so i am not sure, if
> separate sin table queries will be enough as fast as i need at needed
> precission.
>
> The whole algorithm is proven on mssql by my colegue, but he is using the
> native math functions.
>
>  
Jakub,

I may not understand your problem completely, but it seems to me you can
solve your problem without using any trigonometric functions.

If you have a table of circles like this

    create table circle (
        id  integer primary key,
        cx  real,
        cy  real,
        r   real
    );

You can find all the circles that contain a given point (px,py) using a
simple query based in the distance between the point and the center of
the circle.

    select id from circle
    where (px-cx)*(px-cx)+(py-cy)*(py-cy) < r*r;

If you want to create a user defined distance function you could
possibly speed up the calculation somewhat. You could then use a query like:

    select id from circle
    where distance(cx, cy, px, py) < r;

where

    distance(cx, cy, px, py) = sqrt((px-cx)^2 + (py-cy)^2)

HTH
Dennis Cote
       

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sin and similar functions

Bugzilla from ladmanj@volny.cz
In reply to this post by John Stanton-3
But the question was "how"

Probably i am asking time after time stupid questions only - It is because my
english is not half good to orientate oneself easily in the documentation, so
quick summary in someones answer makes more than hours of reading docs.
Usualy i am asking if i understand not to some paragraph in docs.
Sometime it is enough to read it in other words.
(Something you consider as absolutely simple, may be difficult to understand,
not technicaly, but as english text, or easy to overlook.

If you feel my questions  incommodious, please ignore it.

Thank You
Jakub Ladman




Dne pátek 09 březen 2007 22:57 John Stanton napsal(a):

> Just add a sine function in the normal manner of adding a function.  It
> is very simple.
>
> Jakub Ladman wrote:
> > Hi Friends
> >
> > Is there a possibility to have sin cos an acos, functions in sqlite?
> > How to write it in?
> > I really need it and i have fear, that sinus values table will be so slow
> > and so big for critical precission.
> >
> > I will need to select coordinates which are laying in circle of known
> > center and radius.
> > Unfortunately at small time intervals and on a slow machine.
> >
> > Thanks
> >
> > Jakub
> >
> > -------------------------------------------------------------------------
> >---- To unsubscribe, send email to [hidden email]
> > -------------------------------------------------------------------------
> >----
>
> ---------------------------------------------------------------------------
>-- To unsubscribe, send email to [hidden email]
> ---------------------------------------------------------------------------
>--

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sin and similar functions

Bugzilla from ladmanj@volny.cz
In reply to this post by Dennis Cote
Thank you very much.
Not only my english is poor, but my coordinated geometry too :-(
But my software will make much more geometrical computations, so probably some
in queries too.
Please let You (or someone) direct me to the relevant part of documentation
for defining new functions.

Thank You
Jakub


Dne pátek 09 březen 2007 23:35 Dennis Cote napsal(a):

> Jakub Ladman wrote:
> > I will have table with sequence of coordinates (two dimensional space)
> > and corresponding radiuses, so sequence of circles. And i need to use a
> > sqlite query to detect if a actual coordinates (after their measurement)
> > match some of the circle's square or not. And which circle, if match.
> > And this must be for low CPU consumption optimised, so i am not sure, if
> > separate sin table queries will be enough as fast as i need at needed
> > precission.
> >
> > The whole algorithm is proven on mssql by my colegue, but he is using the
> > native math functions.
>
> Jakub,
>
> I may not understand your problem completely, but it seems to me you can
> solve your problem without using any trigonometric functions.
>
> If you have a table of circles like this
>
>     create table circle (
>         id  integer primary key,
>         cx  real,
>         cy  real,
>         r   real
>     );
>
> You can find all the circles that contain a given point (px,py) using a
> simple query based in the distance between the point and the center of
> the circle.
>
>     select id from circle
>     where (px-cx)*(px-cx)+(py-cy)*(py-cy) < r*r;
>
> If you want to create a user defined distance function you could
> possibly speed up the calculation somewhat. You could then use a query
> like:
>
>     select id from circle
>     where distance(cx, cy, px, py) < r;
>
> where
>
>     distance(cx, cy, px, py) = sqrt((px-cx)^2 + (py-cy)^2)
>
> HTH
> Dennis Cote
>
>
> ---------------------------------------------------------------------------
>-- To unsubscribe, send email to [hidden email]
> ---------------------------------------------------------------------------
>--

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sin and similar functions

Puneet Kishor-2
Jakub,

The documentation for creating user-defined functions is at
<http://www.sqlite.org/capi3.html>. See section 2.3. Unfortunately, I
don't know the A,B of C, so I can't guide you in this. On the other
hand, if you are using Perl, I can write you a step-by-step process
for creating your own functions -- it takes about 2 lines of code. If
you know C, the above mentioned documentation should point you in the
right direction.

In any case, Dennis Cote's suggestion is really fantastic. You don't
need the trig functions to solve your existing problem. Use those
suggestions and you will be well on your way.

On 3/9/07, Jakub Ladman <[hidden email]> wrote:

> Thank you very much.
> Not only my english is poor, but my coordinated geometry too :-(
> But my software will make much more geometrical computations, so probably some
> in queries too.
> Please let You (or someone) direct me to the relevant part of documentation
> for defining new functions.
>
> Thank You
> Jakub
>
>
> Dne pátek 09 březen 2007 23:35 Dennis Cote napsal(a):
> > Jakub Ladman wrote:
> > > I will have table with sequence of coordinates (two dimensional space)
> > > and corresponding radiuses, so sequence of circles. And i need to use a
> > > sqlite query to detect if a actual coordinates (after their measurement)
> > > match some of the circle's square or not. And which circle, if match.
> > > And this must be for low CPU consumption optimised, so i am not sure, if
> > > separate sin table queries will be enough as fast as i need at needed
> > > precission.
> > >
> > > The whole algorithm is proven on mssql by my colegue, but he is using the
> > > native math functions.
> >
> > Jakub,
> >
> > I may not understand your problem completely, but it seems to me you can
> > solve your problem without using any trigonometric functions.
> >
> > If you have a table of circles like this
> >
> >     create table circle (
> >         id  integer primary key,
> >         cx  real,
> >         cy  real,
> >         r   real
> >     );
> >
> > You can find all the circles that contain a given point (px,py) using a
> > simple query based in the distance between the point and the center of
> > the circle.
> >
> >     select id from circle
> >     where (px-cx)*(px-cx)+(py-cy)*(py-cy) < r*r;
> >
> > If you want to create a user defined distance function you could
> > possibly speed up the calculation somewhat. You could then use a query
> > like:
> >
> >     select id from circle
> >     where distance(cx, cy, px, py) < r;
> >
> > where
> >
> >     distance(cx, cy, px, py) = sqrt((px-cx)^2 + (py-cy)^2)
> >
> > HTH
> > Dennis Cote
> >
> >
> > ---------------------------------------------------------------------------
> >-- To unsubscribe, send email to [hidden email]
> > ---------------------------------------------------------------------------
> >--
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>
>


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
Reply | Threaded
Open this post in threaded view
|

Re: sin and similar functions

John Stanton-3
In reply to this post by Bugzilla from ladmanj@volny.cz
I could copy the example from the Sqlite documentation or you could just
look it up in the API description.  You write your function, in this
case only a few lines of code, and bind it to Sqlite using the API.

http://www.sqlite.org/capi3ref.html
sqlite3_create_function

Jakub Ladman wrote:

> But the question was "how"
>
> Probably i am asking time after time stupid questions only - It is because my
> english is not half good to orientate oneself easily in the documentation, so
> quick summary in someones answer makes more than hours of reading docs.
> Usualy i am asking if i understand not to some paragraph in docs.
> Sometime it is enough to read it in other words.
> (Something you consider as absolutely simple, may be difficult to understand,
> not technicaly, but as english text, or easy to overlook.
>
> If you feel my questions  incommodious, please ignore it.
>
> Thank You
> Jakub Ladman
>
>
>
>
> Dne pátek 09 březen 2007 22:57 John Stanton napsal(a):
>
>>Just add a sine function in the normal manner of adding a function.  It
>>is very simple.
>>
>>Jakub Ladman wrote:
>>
>>>Hi Friends
>>>
>>>Is there a possibility to have sin cos an acos, functions in sqlite?
>>>How to write it in?
>>>I really need it and i have fear, that sinus values table will be so slow
>>>and so big for critical precission.
>>>
>>>I will need to select coordinates which are laying in circle of known
>>>center and radius.
>>>Unfortunately at small time intervals and on a slow machine.
>>>
>>>Thanks
>>>
>>>Jakub
>>>
>>>-------------------------------------------------------------------------
>>>---- To unsubscribe, send email to [hidden email]
>>>-------------------------------------------------------------------------
>>>----
>>
>>---------------------------------------------------------------------------
>>-- To unsubscribe, send email to [hidden email]
>>---------------------------------------------------------------------------
>>--
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sin and similar functions

Clark Christensen
In reply to this post by Bugzilla from ladmanj@volny.cz
A poster here, "Mikey C", wrote some math functions and posted them a month or so ago.  You might have a look at http://www.mail-archive.com/sqlite-users@.../msg21791.html

 -Clark

----- Original Message ----
From: Jakub Ladman <[hidden email]>
To: [hidden email]
Sent: Friday, March 9, 2007 3:00:22 PM
Subject: Re: [sqlite] sin and similar functions

Thank you very much.
Not only my english is poor, but my coordinated geometry too :-(
But my software will make much more geometrical computations, so probably some
in queries too.
Please let You (or someone) direct me to the relevant part of documentation
for defining new functions.

Thank You
Jakub


Dne pátek 09 březen 2007 23:35 Dennis Cote napsal(a):

> Jakub Ladman wrote:
> > I will have table with sequence of coordinates (two dimensional space)
> > and corresponding radiuses, so sequence of circles. And i need to use a
> > sqlite query to detect if a actual coordinates (after their measurement)
> > match some of the circle's square or not. And which circle, if match.
> > And this must be for low CPU consumption optimised, so i am not sure, if
> > separate sin table queries will be enough as fast as i need at needed
> > precission.
> >
> > The whole algorithm is proven on mssql by my colegue, but he is using the
> > native math functions.
>
> Jakub,
>
> I may not understand your problem completely, but it seems to me you can
> solve your problem without using any trigonometric functions.
>
> If you have a table of circles like this
>
>     create table circle (
>         id  integer primary key,
>         cx  real,
>         cy  real,
>         r   real
>     );
>
> You can find all the circles that contain a given point (px,py) using a
> simple query based in the distance between the point and the center of
> the circle.
>
>     select id from circle
>     where (px-cx)*(px-cx)+(py-cy)*(py-cy) < r*r;
>
> If you want to create a user defined distance function you could
> possibly speed up the calculation somewhat. You could then use a query
> like:
>
>     select id from circle
>     where distance(cx, cy, px, py) < r;
>
> where
>
>     distance(cx, cy, px, py) = sqrt((px-cx)^2 + (py-cy)^2)
>
> HTH
> Dennis Cote
>
>
> ---------------------------------------------------------------------------
>-- To unsubscribe, send email to [hidden email]
> ---------------------------------------------------------------------------
>--

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------





-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sin and similar functions

Dennis Cote
In reply to this post by Bugzilla from ladmanj@volny.cz
Jakub Ladman wrote:
> Thank you very much.
> Not only my english is poor, but my coordinated geometry too :-(
> But my software will make much more geometrical computations, so probably some
> in queries too.
> Please let You (or someone) direct me to the relevant part of documentation
> for defining new functions.
>
>  
Jakub,

SQLite itself use the public C API to add the standard functions when it
initializes, so there are some very good examples of writing custom
functions in the sqlite source file func.c (see
http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/func.c&v=1.136 ).

You will need to write C functions that do similar things as the samples
in func.c and add them to your instance of sqlite using the API at
http://www.sqlite.org/capi3ref.html#sqlite3_create_function.

Then you can call your custom functions from your SQL statements.

I have also posted some samples of user defined functions on this list.
You may want to search for them if you don't finfd the sqlite source
clear enough.

HTH
Dennis Cote



-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sin and similar functions

Bugzilla from ladmanj@volny.cz
In reply to this post by Clark Christensen
Thank you very much.
I am new to this mailing list, so i missed it.
Jakub Ladman


Dne sobota 10 březen 2007 00:48 Clark Christensen napsal(a):

> A poster here, "Mikey C", wrote some math functions and posted them a month
> or so ago.  You might have a look at
> http://www.mail-archive.com/sqlite-users@.../msg21791.html
>
>  -Clark
>
> ----- Original Message ----
> From: Jakub Ladman <[hidden email]>
> To: [hidden email]
> Sent: Friday, March 9, 2007 3:00:22 PM
> Subject: Re: [sqlite] sin and similar functions
>
> Thank you very much.
> Not only my english is poor, but my coordinated geometry too :-(
> But my software will make much more geometrical computations, so probably
> some in queries too.
> Please let You (or someone) direct me to the relevant part of documentation
> for defining new functions.
>
> Thank You
> Jakub
>
> Dne pátek 09 březen 2007 23:35 Dennis Cote napsal(a):
> > Jakub Ladman wrote:
> > > I will have table with sequence of coordinates (two dimensional space)
> > > and corresponding radiuses, so sequence of circles. And i need to use a
> > > sqlite query to detect if a actual coordinates (after their
> > > measurement) match some of the circle's square or not. And which
> > > circle, if match. And this must be for low CPU consumption optimised,
> > > so i am not sure, if separate sin table queries will be enough as fast
> > > as i need at needed precission.
> > >
> > > The whole algorithm is proven on mssql by my colegue, but he is using
> > > the native math functions.
> >
> > Jakub,
> >
> > I may not understand your problem completely, but it seems to me you can
> > solve your problem without using any trigonometric functions.
> >
> > If you have a table of circles like this
> >
> >     create table circle (
> >         id  integer primary key,
> >         cx  real,
> >         cy  real,
> >         r   real
> >     );
> >
> > You can find all the circles that contain a given point (px,py) using a
> > simple query based in the distance between the point and the center of
> > the circle.
> >
> >     select id from circle
> >     where (px-cx)*(px-cx)+(py-cy)*(py-cy) < r*r;
> >
> > If you want to create a user defined distance function you could
> > possibly speed up the calculation somewhat. You could then use a query
> > like:
> >
> >     select id from circle
> >     where distance(cx, cy, px, py) < r;
> >
> > where
> >
> >     distance(cx, cy, px, py) = sqrt((px-cx)^2 + (py-cy)^2)
> >
> > HTH
> > Dennis Cote
> >
> >
> > -------------------------------------------------------------------------
> >-- -- To unsubscribe, send email to [hidden email]
> > -------------------------------------------------------------------------
> >-- --
>
> ---------------------------------------------------------------------------
>-- To unsubscribe, send email to [hidden email]
> ---------------------------------------------------------------------------
>--
>
>
>
>
>
> ---------------------------------------------------------------------------
>-- To unsubscribe, send email to [hidden email]
> ---------------------------------------------------------------------------
>--

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: sin and similar functions

Bugzilla from ladmanj@volny.cz
In reply to this post by Clark Christensen
Hi Dear Friens
I have found this advice in mailing list history:

I think you can compile the functions into a loadable
library (follow the instructions on creating a sqlite
loadable extension), and then load them in the console
app.  In linux:

  gcc myLoadableLibs.c -shared -o myLoadableLibs.so

then

  sqlite3> .load /home/jp/myLoadableLibs.so


Do someone knows which files from Mikey C's zip archive needs to be compiled
into loadable extension?

Where are the instructions on creating loadable extension?

Thank You

Jakub Ladman


Dne sobota 10 březen 2007 00:48 Clark Christensen napsal(a):

> A poster here, "Mikey C", wrote some math functions and posted them a month
> or so ago.  You might have a look at
> http://www.mail-archive.com/sqlite-users@.../msg21791.html
>
>  -Clark
>
> ----- Original Message ----
> From: Jakub Ladman <[hidden email]>
> To: [hidden email]
> Sent: Friday, March 9, 2007 3:00:22 PM
> Subject: Re: [sqlite] sin and similar functions
>
> Thank you very much.
> Not only my english is poor, but my coordinated geometry too :-(
> But my software will make much more geometrical computations, so probably
> some in queries too.
> Please let You (or someone) direct me to the relevant part of documentation
> for defining new functions.
>
> Thank You
> Jakub
>
> Dne pátek 09 březen 2007 23:35 Dennis Cote napsal(a):
> > Jakub Ladman wrote:
> > > I will have table with sequence of coordinates (two dimensional space)
> > > and corresponding radiuses, so sequence of circles. And i need to use a
> > > sqlite query to detect if a actual coordinates (after their
> > > measurement) match some of the circle's square or not. And which
> > > circle, if match. And this must be for low CPU consumption optimised,
> > > so i am not sure, if separate sin table queries will be enough as fast
> > > as i need at needed precission.
> > >
> > > The whole algorithm is proven on mssql by my colegue, but he is using
> > > the native math functions.
> >
> > Jakub,
> >
> > I may not understand your problem completely, but it seems to me you can
> > solve your problem without using any trigonometric functions.
> >
> > If you have a table of circles like this
> >
> >     create table circle (
> >         id  integer primary key,
> >         cx  real,
> >         cy  real,
> >         r   real
> >     );
> >
> > You can find all the circles that contain a given point (px,py) using a
> > simple query based in the distance between the point and the center of
> > the circle.
> >
> >     select id from circle
> >     where (px-cx)*(px-cx)+(py-cy)*(py-cy) < r*r;
> >
> > If you want to create a user defined distance function you could
> > possibly speed up the calculation somewhat. You could then use a query
> > like:
> >
> >     select id from circle
> >     where distance(cx, cy, px, py) < r;
> >
> > where
> >
> >     distance(cx, cy, px, py) = sqrt((px-cx)^2 + (py-cy)^2)
> >
> > HTH
> > Dennis Cote
> >
> >
> > -------------------------------------------------------------------------
> >-- -- To unsubscribe, send email to [hidden email]
> > -------------------------------------------------------------------------
> >-- --
>
> ---------------------------------------------------------------------------
>-- To unsubscribe, send email to [hidden email]
> ---------------------------------------------------------------------------
>--
>
>
>
>
>
> ---------------------------------------------------------------------------
>-- To unsubscribe, send email to [hidden email]
> ---------------------------------------------------------------------------
>--

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------