Re: [sqlite] sin and similar functions

[Jakub Ladman]

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@sqlite.org/msg21791.html
>
>  -Clark
>
> - Original Message 
> From: Jakub Ladman <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> 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 [EMAIL PROTECTED]
> > -
> >-- --
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--
>
>
>
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sin and similar functions

[Jakub Ladman]

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@sqlite.org/msg21791.html
>
>  -Clark
>
> - Original Message 
> From: Jakub Ladman <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> 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 [EMAIL PROTECTED]
> > -
> >-- --
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--
>
>
>
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sin and similar functions

[Dennis Cote]

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=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 [EMAIL PROTECTED]
-

Re: [sqlite] sin and similar functions

[Clark Christensen]

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@sqlite.org/msg21791.html

 -Clark

- Original Message 
From: Jakub Ladman <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
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 [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sin and similar functions

[John Stanton]

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 [EMAIL PROTECTED]
-



---
-- To unsubscribe, send email to [EMAIL PROTECTED]
---
--



-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sin and similar functions

[P Kishor]

Jakub,

The documentation for creating user-defined functions is at
. 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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-





--
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
=

Re: [sqlite] sin and similar functions

[Jakub Ladman]

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 [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sin and similar functions

[Jakub Ladman]

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 [EMAIL PROTECTED]
> > -
> >
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sin and similar functions

[Dennis Cote]

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 [EMAIL PROTECTED]
-

Re: [sqlite] sin and similar functions

[John Stanton]

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 [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sin and similar functions

[Jakub Ladman]

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 [EMAIL PROTECTED]
-

[sqlite] sin and similar functions

[Jakub Ladman]

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 [EMAIL PROTECTED]
-