Re: [sqlite] Optimizing 'ends with' queries

[Simon Slavin]

On 24 Aug 2009, at 5:05am, Tito Ciuro wrote:

> What I meant is that if a table contains several columns, some or all
> of which need to be searched using 'ends with', then replicating the
> columns (by reversing the string) and keeping extra indices could
> potentially affect performance (obviously, it depends on the number of
> rows). Also, the extra logic needed to keep track seems a bit  
> overkill.

You might find, when you analyse just why you need to match endings,  
that you could match on any part of the string instead.  In that case  
you could just concatenate all the strings together and use LIKE to do  
some initial filtering, then narrow down the initial set in your own  
software.

> Perhaps I should be consider FTS instead. I'd like to play with the
> idea of matching a particular string using FTS and then and query the
> result set using 'ends with' (i.e.  LIKE '*foo'). This way the query
> would scan the FTS result set, as opposed to the entire table.

Sure.  FTS would do it.  Another way would be to write your own  
extension to SQLite which did exactly the matching you wanted.  This  
would be annoying but provide you with a custom-written function which  
worked very quickly.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing 'ends with' queries

[Tito Ciuro]

Hi Simon,

On Aug 23, 2009, at 8:52 PM, Simon Slavin wrote:

>
> On 24 Aug 2009, at 4:22am, Tito Ciuro wrote:
>
>> But that would introduce the overhead of doubling the space required
>> for every string + an additional column index.
>
> One of the options I mentioned was to store the reversed string and
> not the original.  If you need to reconstruct the original string,
> read the reversed one and reverse it your software.
>
> Will you actually be short on space ?  If you're programming for an
> embedded platform then I can understand space is a concern.  If you're
> using a standard desktop computer and your database doesn't have
> millions of records it might not be.  By all means, work to your
> priorities.
>
>> If the schema contains
>> more columns where this type of query needs to take place, it seems  
>> to
>> me that this would not be a good solution.
>
> 'good' relative to what ?  I'm always interested in better solutions.

Yeah, 'good' is too ambiguous.

What I meant is that if a table contains several columns, some or all  
of which need to be searched using 'ends with', then replicating the  
columns (by reversing the string) and keeping extra indices could  
potentially affect performance (obviously, it depends on the number of  
rows). Also, the extra logic needed to keep track seems a bit overkill.

Perhaps I should be consider FTS instead. I'd like to play with the  
idea of matching a particular string using FTS and then and query the  
result set using 'ends with' (i.e.  LIKE '*foo'). This way the query  
would scan the FTS result set, as opposed to the entire table.

Thanks again,

-- Tito

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing 'ends with' queries

[Simon Slavin]

On 24 Aug 2009, at 4:22am, Tito Ciuro wrote:

> But that would introduce the overhead of doubling the space required
> for every string + an additional column index.

One of the options I mentioned was to store the reversed string and  
not the original.  If you need to reconstruct the original string,  
read the reversed one and reverse it your software.

Will you actually be short on space ?  If you're programming for an  
embedded platform then I can understand space is a concern.  If you're  
using a standard desktop computer and your database doesn't have  
millions of records it might not be.  By all means, work to your  
priorities.

> If the schema contains
> more columns where this type of query needs to take place, it seems to
> me that this would not be a good solution.

'good' relative to what ?  I'm always interested in better solutions.

Simon.

PS: new text /below/ quoted text please.  If you don't thing readers  
need to read the original text, there's no need to include it at all.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] can sqlite result be sorted by using "prepare-step" API

[Jay A. Kreibich]

On Mon, Aug 24, 2009 at 01:55:41AM +, nick huang scratched on the wall:
> 
> I am a kind of new to Sqlite and just wonder if the query result row
> could be sorted by using Sqlite_prepare followed by Sqlite_Step.

  Prepare/step is the only way to get data out of the database, so yes.

> For example, query statement is something like "select * from
> sometable order by somefield;" and we call prepare followed by step.
> And are all results returned from "step" in order of "somefield"?

  Yes.  Did you try it and see?

> As I read the document, it seems the step will return the first awailable 
> row ASAP. That is why I wonder the sorting is not possible as according 
> to what we learned from books the sorting of dataset is done at the 
> last stage of SQL query when all result set is available.

  It depends.  If SQLite is sorting based off an indexed column, it may
  be able to start returning rows right away before the full result set
  has been computed.  There are plenty of cases when the query
  optimizer can rearrange the query pipeline to produce "presorted"
  in-order results without the whole result set at hand.

  On the other hand, if you're sorting on a non-indexed column or
  computed result-set column, then the database engine has to compute
  the entire result, sort it, and then start to return it.

  In the first case, the cost of doing the query will be spread across
  each call to sqlite3_step().  In the second case, the first call to
  sqlite3_step() may be quite long, but all calls after that should be
  quite fast.

> However, this also seems to contradictive to that all other query API 
> like "exec", "getTable" etc. which all support "sorting" are all
> based on prepare-step. Therefore the only conclusion is that "exec",
> "getTable" etc. retrieve dataset and sort by themselves after they
> call "prepare-step". 

  No, the short-cut functions are not that smart.  The much simpler and
  more logical conclusion is that the database engine does the sorting.

  sqlite3_step() returns rows as they become available.  You're reading
  too deeply into "as they become available", however.  The database
  engine is still required to return the correct result set.  If the
  query needs to be sorted, it needs to be sorted.  That may or may not
  require computing the full result set before returning the first row.
  It depends on the query.  But either way, the database will do the
  right thing and return the correct results.
  
   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing 'ends with' queries

[Tito Ciuro]

Hi Simon,

But that would introduce the overhead of doubling the space required  
for every string + an additional column index. If the schema contains  
more columns where this type of query needs to take place, it seems to  
me that this would not be a good solution.

Thanks for your help,

-- Tito

On Aug 23, 2009, at 8:15 PM, Simon Slavin wrote:

>
> On 24 Aug 2009, at 3:44am, Tito Ciuro wrote:
>
>> Is there a way to optimize this type of queries? (column Value is
>> indexed):
>>
>> SELECT Value FROM MyValues WHERE Value LIKE '%crashed.'
>>
>> I've seen the document where 'begins with' queries can be optimized
>> using >= and < (end of the '4.0 The LIKE optimization' section):
>>
>> http://www.sqlite.org/optoverview.html
>>
>> Can I optimize this query to take advantage of the index?
>
> Sure.  When you use INSERT either define two columns (value and
> valueReversed) or just store valueReversed.  Then use LIKE with
> valueReversed instead of value.  There is no 'reverse string' function
> built into SQLite, but I bet whichever programming language you're
> using makes it easy to reverse a string.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Jean-Christophe Deschamps]

I want to use SQLite in a GIS application where I create a database
>containing terrain data (coordinates, height).
>I would like to query this database with start and end points of a 
>line and
>get a vector with all heights point along this line.
>I can, of course create a query for each point along the line, but 
>this will
>be very time consuming as I have hundreds of lines with hundreds of 
>points.
>Any suggestions?

Google for "Voronoï diagrams" and "Delaunay triangularization". These 
are the very basic tools for modelling terrain at small scales (with 
small details). From then, if you need profile approximation, then you 
can use one of the many versions of "splines" or "parametric curves" to 
fit the data.

You're certainly better off using a proven dedicated extension for 
doing so.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing 'ends with' queries

[Simon Slavin]

On 24 Aug 2009, at 3:44am, Tito Ciuro wrote:

> Is there a way to optimize this type of queries? (column Value is
> indexed):
>
> SELECT Value FROM MyValues WHERE Value LIKE '%crashed.'
>
> I've seen the document where 'begins with' queries can be optimized
> using >= and < (end of the '4.0 The LIKE optimization' section):
>
> http://www.sqlite.org/optoverview.html
>
> Can I optimize this query to take advantage of the index?

Sure.  When you use INSERT either define two columns (value and  
valueReversed) or just store valueReversed.  Then use LIKE with  
valueReversed instead of value.  There is no 'reverse string' function  
built into SQLite, but I bet whichever programming language you're  
using makes it easy to reverse a string.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Optimizing 'ends with' queries

[Tito Ciuro]

Hello,

Is there a way to optimize this type of queries? (column Value is  
indexed):

SELECT Value FROM MyValues WHERE Value LIKE '%crashed.'

I've seen the document where 'begins with' queries can be optimized  
using >= and < (end of the '4.0 The LIKE optimization' section):

http://www.sqlite.org/optoverview.html

Can I optimize this query to take advantage of the index?

Thanks in advance,

-- Tito


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] can sqlite result be sorted by using "prepare-step" API

[Igor Tandetnik]

nick huang wrote:
> For example, query statement is something like "select * from
> sometable order by somefield;" and we call prepare followed by step.
> And are all results returned from "step" in order of "somefield"?

Of course. What would be the point of specifying "order by somefield" 
otherwise?

> As I read the document, it seems the step will return the first
> awailable row ASAP. That is why I wonder the sorting is not possible
> as according to what we learned from books the sorting of dataset is
> done at the last stage of SQL query when all result set is available.

ASAP stands for "as soon as possible". For a query with ORDER BY clause, 
"possible" is after the whole resultset is retrieved and sorted 
(assuming the order cannot be satisfied using an index).

> However, this also seems to contradictive to that all other query API
> like "exec", "getTable" etc. which all support "sorting" are all
> based on prepare-step. Therefore the only conclusion is that "exec",
> "getTable" etc. retrieve dataset and sort by themselves after they
> call "prepare-step".

You can look at the source code for sqlite3_exec and sqlite3_get_table, 
and convince yourself that they do no such thing.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] can sqlite result be sorted by using "prepare-step" API

[nick huang]

hi all,

 

I am a kind of new to Sqlite and just wonder if the query result row could be 
sorted by using Sqlite_prepare followed by Sqlite_Step.

For example, query statement is something like "select * from sometable order 
by somefield;" and we call prepare followed by step. And are all results 
returned from "step" in order of "somefield"?

 

 

As I read the document, it seems the step will return the first awailable row 
ASAP. That is why I wonder the sorting is not possible as according to what we 
learned from books the sorting of dataset is done at the last stage of SQL 
query when all result set is available.

However, this also seems to contradictive to that all other query API like 
"exec", "getTable" etc. which all support "sorting" are all based on 
prepare-step. Therefore the only conclusion is that "exec", "getTable" etc. 
retrieve dataset and sort by themselves after they call "prepare-step". 

 

Anybody has any idea about this? 

Nick Huang/Qingzhe Huang 


_
Stay in the loop and chat with friends, right from your inbox!
http://go.microsoft.com/?linkid=9671354
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Kit]

2009/8/23 P Kishor :
> If a line is expressed by (y = ax + c), you need to find all x,y that
> will satisfy that equation. So,
>
> SELECT x, y, z
> FROM terrain
> WHERE a*x - y + c = 0

General equation of line is a*x + b*y + c = 0. If you delete parameter
"b", you will have a problem with some pair of points - e.g.
[0,0],[0,1].

Better way to save unit squares to file is a simple 2D matrix. No
database. Fast and easy for samples with fixed steps of coordinates.
You can load entire matrix into memory.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[P Kishor]

On Sun, Aug 23, 2009 at 12:08 AM, Itzchak
Raiskin wrote:
> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?

or, you can build on the work already done by USGS for you (they have
worldwide data as well).

http://gisdata.usgs.gov/xmlwebservices2/elevation_service.asmx


>
> Thanks, Itzik
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[P Kishor]

On Sun, Aug 23, 2009 at 11:34 AM, P Kishor wrote:
> On Sun, Aug 23, 2009 at 11:11 AM, Simon
> Slavin wrote:
>>
>> On 23 Aug 2009, at 5:00pm, P Kishor wrote:
>>
>>> WHERE a*x - y + c = 0
>>
>> Here's the problem.  This works only when the equation is exact.
>
> Indeed. We already laid out those presumptions. One, your height
> coverage has to be continuous as you can get via an image. Two, you
> can substitute any equation for your line, however, any complex line
> can be broken up into segments of simple, straight lines. All GIS
> software do just that. Once you can solve the problem for a simple
> segment, you can repeat the solution for every segment in the line.
>
> Postgis/Spatialite by themselves won't solve the problem, but they may
> have (don't know about Spatialite, but Postgis has a boatload of
> geographic functions created by the developers) ready made functions
> that can help build the solution.
>
> A really nice application would allow the user to click, click, click
> a line (of many segments) on a representation of a terrain, go
> retrieve the height values, and construct an image of the elevation
> profile.
>

Kinda like http://veloroutes.org/bikemaps/


>
>> Which
>> under normal circumstances means that all the numbers fit neatly with some
>> imaginary integer formula.  This isn't how real life works, especially when
>> you've correctly noted that your measured heights weren't measured at
>> exactly regular intervals.
>>
>> You're going to have to do some proper programming.  For example, how to
>> interpolate the height at an arbitrary position when you have a collection
>> of heights measured nearby.  There's no easy solution and you certainly
>> can't do the majority of the work inside a SQL query.
>>
>> Simon.
>>
>
>
>
> --
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[P Kishor]

On Sun, Aug 23, 2009 at 11:11 AM, Simon
Slavin wrote:
>
> On 23 Aug 2009, at 5:00pm, P Kishor wrote:
>
>> WHERE a*x - y + c = 0
>
> Here's the problem.  This works only when the equation is exact.

Indeed. We already laid out those presumptions. One, your height
coverage has to be continuous as you can get via an image. Two, you
can substitute any equation for your line, however, any complex line
can be broken up into segments of simple, straight lines. All GIS
software do just that. Once you can solve the problem for a simple
segment, you can repeat the solution for every segment in the line.

Postgis/Spatialite by themselves won't solve the problem, but they may
have (don't know about Spatialite, but Postgis has a boatload of
geographic functions created by the developers) ready made functions
that can help build the solution.

A really nice application would allow the user to click, click, click
a line (of many segments) on a representation of a terrain, go
retrieve the height values, and construct an image of the elevation
profile.


> Which
> under normal circumstances means that all the numbers fit neatly with some
> imaginary integer formula.  This isn't how real life works, especially when
> you've correctly noted that your measured heights weren't measured at
> exactly regular intervals.
>
> You're going to have to do some proper programming.  For example, how to
> interpolate the height at an arbitrary position when you have a collection
> of heights measured nearby.  There's no easy solution and you certainly
> can't do the majority of the work inside a SQL query.
>
> Simon.
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Stephen Woodbridge]

Itzchak Raiskin wrote:
> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?

You might want to look at SpatiaLite which is a GIS addon to SQLite and 
has most of the capabilities of PostGIS.

http://www.gaia-gis.it/spatialite/

-Steve
  http://imaptools.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Simon Slavin]

On 23 Aug 2009, at 5:00pm, P Kishor wrote:

> WHERE a*x - y + c = 0

Here's the problem.  This works only when the equation is exact.   
Which under normal circumstances means that all the numbers fit neatly  
with some imaginary integer formula.  This isn't how real life works,  
especially when you've correctly noted that your measured heights  
weren't measured at exactly regular intervals.

You're going to have to do some proper programming.  For example, how  
to interpolate the height at an arbitrary position when you have a  
collection of heights measured nearby.  There's no easy solution and  
you certainly can't do the majority of the work inside a SQL query.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[P Kishor]

On Sun, Aug 23, 2009 at 12:08 AM, Itzchak
Raiskin wrote:
> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?
>


The problem to solve is that you have a discontinuous surface
(discrete points) with height values. You want to do a line-on-poly
overlay and find the surface values that intersect with the line. Of
course, the line may or may not intersect with any of your stored
points, so you have to do some interpolation.

If you can "fill" all the coords on a surface, typically done by an
image where every pixel holds the height value, you can find all the
points that coincide with a line.

If a line is expressed by (y = ax + c), you need to find all x,y that
will satisfy that equation. So,

SELECT x, y, z
FROM terrain
WHERE a*x - y + c = 0

But you can make the search more efficient by only searching within
the bounding box of the line, so, assuming xmin, ymin, xmax, ymax are
the end points of the line

SELECT x, y, z
FROM terrain
WHERE a*x - y + c = 0
  AND x BETWEEN xmin AND xmax
  AND y BETWEEN ymin AND ymax


In other words, Kit gave you the answer already. I hope my explanation
helped flesh it out more. You can use your own line equation in the
SELECT statement above.


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Huge numbers of savepoints.

[Doug Currie]

On Aug 23, 2009, at 6:46 AM, Chris Dew wrote:

> Note: this is not for production code, just an experiment in keeping a
> history of application 'state', allowing current state to be
> recalculated if an historic input is received 'late'.  See
> http://www.finalcog.com/haskell-decoupling-time-from-physics for a
> similar idea (implemented in Haskell).

This page might give you some ideas:

http://www.sqlite.org/cvstrac/wiki?p=UndoRedo

e

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Huge numbers of savepoints.

[Pavel Ivanov]

With these requirements you can't implement it just on database level
because it doesn't fit into the standard savepoint/transaction
paradigm of databases. Only committed data and finished transactions
will be available after OS crash or to other processes. After
transaction is committed it cannot be rollbacked - data is saved in
database and DBMS cannot do anything with it until application says
what exactly it should do.
So what you need to do can be implemented only on application level.
You can do some sort of logging of your actions and doing opposite
actions and cleaning the log when you need it.

Pavel

On Sun, Aug 23, 2009 at 6:46 AM, Chris Dew wrote:
> Hi, thanks for your questions.
>
> 1. restarts or OS crashes should leave the data in a sane state - the
> last savepoint would be fine.
> 2. there's no requirement to revert to old savepoints set during a
> previous application run.
> 3. no need for more than one process to access the data, though
> letting other processes see the data at the last save point would be
> nice.
>
> Note: this is not for production code, just an experiment in keeping a
> history of application 'state', allowing current state to be
> recalculated if an historic input is received 'late'.  See
> http://www.finalcog.com/haskell-decoupling-time-from-physics for a
> similar idea (implemented in Haskell).
>
> Regards,
>
> Chris.
>
> On Aug 19, 2:36 pm, Pavel Ivanov  wrote:
>> But how do you expect your application to deal with restarts and/or OS
>> crashes? Do you want to still be able to revert to "marks" set in
>> previous application run or not? And what about accessing to the data
>> stored between "marks" from other processes?
>>
>> Pavel
>>
>>
>>
>> On Wed, Aug 19, 2009 at 4:07 AM, Chris Dew wrote:
>> >http://www.sqlite.org/lang_savepoint.html
>> > I'm looking for a datastore with the following properties:
>> >  * I need to 'mark' the state frequently (sub second interval).
>> >  * I need to be able to revert the datastore to a previous mark (with no
>> > appreciable delay).
>> >  * I only need to keep the last few hundred 'marks'. (i.e. I never need to
>> > revert to a datastore marked more than a few minutes ago.)
>>
>> > The savepoint functionality in sqlite looks to almost fit the bill, but its
>> > savepoints seem to be nested - i.e. you cannot 'forget about' old 
>> > savepoints
>> > while retaining recent savepoints. Is my understanding correct here? I'm
>> > concerned that this would cause a performance issue when millions of nested
>> > savepoints have accumulated.
>>
>> > Obviously I can roll my own data structure here, but is sqlite was 
>> > feasible,
>> > it would be good.
>>
>> > Does anyone have any suggestions?
>>
>> > Thanks,
>>
>> > Chris.
>>
>> > --
>>
>> >http://www.finalcog.com/
>> > ___
>> > sqlite-users mailing list
>> > sqlite-us...@sqlite.org
>> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Derrell Lipman]

On Sun, Aug 23, 2009 at 01:08, Itzchak Raiskin wrote:

> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this
> will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?
>

The "rtree" extension may be of use to you.
http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/README
More information is available here: http://en.wikipedia.org/wiki/R-tree

Derrell
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Rich Shepard]

On Sun, 23 Aug 2009, Itzchak Raiskin wrote:

> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height). I would like to query this
> database with start and end points of a line and get a vector with all
> heights point along this line. I can, of course create a query for each
> point along the line, but this will be very time consuming as I have
> hundreds of lines with hundreds of points. Any suggestions?

Itzik,

   Other responders have suggested how to query for specific lines, but that
will not fulfill the requirements of a GIS. You could create a
computer-aided drafting (CAD) application this way, but you need to
incorporate coodinate geometry if you want a spatial analytical tool. You
need to accommodate a single edge that defines adjacent polygons and store
the spatial relationship. SQLite is not the best tool for spatial
applications.

   I suggest that you look at PostGIS, OpenGIS, GRASS, and other
well-developed applications. Yes, they use PostgreSQL for the data storage
and can store geometric objects as data entities.

   You are, of course, welcome to reinvent the wheel, but I suggest that you
learn something about spatial databases and coordinate geometry if you want
an application that actually works.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Huge numbers of savepoints.

[Chris Dew]

Hi, thanks for your questions.

1. restarts or OS crashes should leave the data in a sane state - the
last savepoint would be fine.
2. there's no requirement to revert to old savepoints set during a
previous application run.
3. no need for more than one process to access the data, though
letting other processes see the data at the last save point would be
nice.

Note: this is not for production code, just an experiment in keeping a
history of application 'state', allowing current state to be
recalculated if an historic input is received 'late'.  See
http://www.finalcog.com/haskell-decoupling-time-from-physics for a
similar idea (implemented in Haskell).

Regards,

Chris.

On Aug 19, 2:36 pm, Pavel Ivanov  wrote:
> But how do you expect your application to deal with restarts and/or OS
> crashes? Do you want to still be able to revert to "marks" set in
> previous application run or not? And what about accessing to the data
> stored between "marks" from other processes?
>
> Pavel
>
>
>
> On Wed, Aug 19, 2009 at 4:07 AM, Chris Dew wrote:
> >http://www.sqlite.org/lang_savepoint.html
> > I'm looking for a datastore with the following properties:
> >  * I need to 'mark' the state frequently (sub second interval).
> >  * I need to be able to revert the datastore to a previous mark (with no
> > appreciable delay).
> >  * I only need to keep the last few hundred 'marks'. (i.e. I never need to
> > revert to a datastore marked more than a few minutes ago.)
>
> > The savepoint functionality in sqlite looks to almost fit the bill, but its
> > savepoints seem to be nested - i.e. you cannot 'forget about' old savepoints
> > while retaining recent savepoints. Is my understanding correct here? I'm
> > concerned that this would cause a performance issue when millions of nested
> > savepoints have accumulated.
>
> > Obviously I can roll my own data structure here, but is sqlite was feasible,
> > it would be good.
>
> > Does anyone have any suggestions?
>
> > Thanks,
>
> > Chris.
>
> > --
>
> >http://www.finalcog.com/
> > ___
> > sqlite-users mailing list
> > sqlite-us...@sqlite.org
> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Huge numbers of savepoints.

[Chris Dew]

Apologies for multiple posting - these extra copies were sent from
googlemail.com, whereas I has subscribed to the list as gmail.com.

Chris.

On Aug 19, 8:35 am, Chris Dew  wrote:
> http://www.sqlite.org/lang_savepoint.html
> I'm looking for a datastore with the following properties:
>  * I need to 'mark' the state frequently (sub second interval).
>  * I need to be able to revert the datastore to a previous mark (with no
> appreciable delay).
>  * I only need to keep the last few hundred 'marks'.  (i.e. I never need to
> revert to a datastore marked more than a few minutes ago.)
>
> The savepoint functionality in sqlite looks to almost fit the bill, but its
> savepoints seem to be nested - i.e. you cannot 'forget about' old savepoints
> while retaining recent savepoints.  Is my understanding correct here?  I'm
> concerned that this would cause a performance issue when millions of nested
> savepoints have accumulated.
>
> Obviously I can roll my own data structure here, but is sqlite was feasible,
> it would be good.
>
> Does anyone have any suggestions?
>
> Thanks,
>
> Chris.
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[John Machin]

On 23/08/2009 3:08 PM, Itzchak Raiskin wrote:
> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?

Specify with some precision what tables of data you expect to have:

create table terrain_data (id, x, y, height) -- ??
create table line (?)

How is the terrain data expressed? I.e. are "cordinates" (lon, lat) or 
something else? Height above what in what units?

What is a "line"? 2D or 3D?

Note you say you have hundreds of lines but don't say how they are 
related to the terrain data ...

Explain "get a vector with all heights point along this line" in 
mathematical terms

Explain "for each point along the line"

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Kit]

2009/8/23 Itzchak Raiskin :
> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?
> Thanks, Itzik

SELECT x,y,height FROM terrain WHERE round(a*x+b*y+c)=0
AND x BETWEEN xmin AND xmax
AND y BETWEEN ymin AND ymax;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users