

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]



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]



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]



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 (pxcx)*(pxcx)+(pycy)*(pycy) < 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((pxcx)^2 + (pycy)^2)
HTH
Dennis Cote

To unsubscribe, send email to [hidden email]



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]



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 (pxcx)*(pxcx)+(pycy)*(pycy) < 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((pxcx)^2 + (pycy)^2)
>
> HTH
> Dennis Cote
>
>
> 
> To unsubscribe, send email to [hidden email]
> 
>

To unsubscribe, send email to [hidden email]



Jakub,
The documentation for creating userdefined 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 stepbystep 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 (pxcx)*(pxcx)+(pycy)*(pycy) < 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((pxcx)^2 + (pycy)^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, UWMadison http://www.nelson.wisc.edu/Open Source Geospatial Foundation http://www.osgeo.org/education/
collaborate, communicate, compete
=====================================================================


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.htmlsqlite3_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]



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.mailarchive.com/sqliteusers@.../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 (pxcx)*(pxcx)+(pycy)*(pycy) < 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((pxcx)^2 + (pycy)^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]



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]



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.mailarchive.com/sqliteusers@.../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 (pxcx)*(pxcx)+(pycy)*(pycy) < 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((pxcx)^2 + (pycy)^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]



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.mailarchive.com/sqliteusers@.../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 (pxcx)*(pxcx)+(pycy)*(pycy) < 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((pxcx)^2 + (pycy)^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]


