Re: [sqlite] RTree Documentation Error?

[Mohit Sindhwani]

Hi Richard, et al,

I'm sorry that it's bad manners for me to raise an issue and then 
disappear, but I didn't get a chance to check the list till a few 
minutes ago...


On 17/4/2013 9:36 PM, Richard Hipp wrote:

On Wed, Apr 17, 2013 at 9:25 AM, Michael Black  wrote:


Correct -- no results for the "real" 2nd query


I see.  I ran two queries together in my script so what I thought was the
2nd query was really the 3rd.

The SQLite HQ coordinates are fixed in
http://www.sqlite.org/draft/rtree.html now.


Thanks for the fix!  It all works now like it says it should.  We had a 
bit of a moment when we tried the query and it didn't seem correct :)


Best Regards,
Mohit.


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

Re: [sqlite] RTree Documentation Error?

[Michael Black]

It would also be nice if 3.3 showed the "expected output" too.
Would be a bit easier for the beginner to ensure they are sane.
Also would've prevented this GIGO error me thinkst.

Michael Black


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

Re: [sqlite] RTree Documentation Error?

[Richard Hipp]

On Wed, Apr 17, 2013 at 9:25 AM, Michael Black  wrote:

> Correct -- no results for the "real" 2nd query
>

I see.  I ran two queries together in my script so what I thought was the
2nd query was really the 3rd.

The SQLite HQ coordinates are fixed in
http://www.sqlite.org/draft/rtree.html now.

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

Re: [sqlite] RTree Documentation Error?

[Michael Black]

Correct -- no results for the "real" 2nd query ...you got no results for it
either.\
Then 2nd query is this and produces no results...nor should it with the data
given.
SELECT id FROM demo_index
 WHERE minX>=-81.08 AND maxX<=-80.58
   AND minY>=35.00  AND maxY<=35.44;

I think the data point you have for SQL HQ is supposed to be:
1|-80.77490234375|-80.7746963500977|35.3775978088379|35.3778038024902
You have "33" for miny which puts HQ outside of the query since the
query doesn't do intersections.

Change 33 to 35 and the query will work.  I don't think HQ is really 2
degrees N/S :-)

SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE VIRTUAL TABLE demo_index USING rtree(
   ...>id,  -- Integer primary key
   ...>minX, maxX,  -- Minimum and maximum X coordinate
   ...>minY, maxY   -- Minimum and maximum Y coordinate
   ...> );
sqlite> INSERT INTO demo_index VALUES(
   ...> 1,   -- Primary key
   ...> -80.7749, -80.7747,  -- Longitude range
   ...> 33.3776, 35.3778 -- Latitude range
   ...> );
sqlite> INSERT INTO demo_index VALUES(
   ...> 2,
   ...> -81.0, -79.6,
   ...> 35.0, 36.2
   ...> );
sqlite> SELECT * FROM demo_index WHERE id=1;
1|-80.77490234375|-80.7746963500977|33.3775978088379|35.3778038024902
sqlite> SELECT id FROM demo_index
   ...>  WHERE minX>=-81.08 AND maxX<=-80.58
   ...>AND minY>=35.00  AND maxY<=35.44;
Note that there's no output here  And there shouldn't
beminY is 33.377 for ID#1
So let's put in some correct numbers
sqlite> delete from demo_index where id=1;
sqlite> INSERT INTO demo_index VALUES(
   ...> 1,   -- Primary key
   ...> -80.7749, -80.7747,  -- Longitude range
   ...> 35.3776, 35.3778 -- Latitude range
   ...> );
sqlite> SELECT id FROM demo_index
   ...>  WHERE minX>=-81.08 AND maxX<=-80.58
   ...>AND minY>=35.00  AND maxY<=35.44;
1
sqlite> SELECT id FROM demo_index
   ...>  WHERE maxY>=35.0  AND minY<=35.0;
2

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: Wednesday, April 17, 2013 8:07 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] RTree Documentation Error?

On Wed, Apr 17, 2013 at 8:41 AM, Michael Black <mdblac...@yahoo.com> wrote:

> I think the point is that this query that produces nothing:
> SELECT id FROM demo_index WHERE minX>=-81.08 AND maxX<=-80.58 AND
> minY>=35.00  AND maxY<=35.44;
>
> Should produce something as the directions imply.
>

And my point is that the query DOES produce a result.  Are y'all saying you
are getting an empty result for the second query?  What version of SQLite
are you running?  What platform?

-- 
D. Richard Hipp
d...@sqlite.org
___
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] RTree Documentation Error?

[Richard Hipp]

On Wed, Apr 17, 2013 at 8:41 AM, Michael Black  wrote:

> I think the point is that this query that produces nothing:
> SELECT id FROM demo_index WHERE minX>=-81.08 AND maxX<=-80.58 AND
> minY>=35.00  AND maxY<=35.44;
>
> Should produce something as the directions imply.
>

And my point is that the query DOES produce a result.  Are y'all saying you
are getting an empty result for the second query?  What version of SQLite
are you running?  What platform?

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

Re: [sqlite] RTree Documentation Error?

[Michael Black]

I think the point is that this query that produces nothing:
SELECT id FROM demo_index WHERE minX>=-81.08 AND maxX<=-80.58 AND
minY>=35.00  AND maxY<=35.44;

Should produce something as the directions imply.

It's just that SQL HQ is not actually inside the city limits apparently
(mailing addresses are not restricted to city boundaries).
So add another data point of something downtown.
Like the Carolina Panthers stadium
-80.854201,-80.851489, 35.224511, 35.226925

sqlite> insert into demo_index
values(3,-80.854201,-80.851489,35.224511,35.226925);
sqlite> SELECT id FROM demo_index WHERE minX>=-81.08 AND maxX<=-80.58 AND
minY>=35.00  AND maxY<=35.44;
id
3

Michael Black



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

Re: [sqlite] RTree Documentation Error?

[Richard Hipp]

On Wed, Apr 17, 2013 at 7:48 AM, Clemens Ladisch  wrote:

> Richard Hipp wrote:
> > On Tue, Apr 16, 2013 at 1:15 PM, Mohit Sindhwani  wrote:
> >> Hi, I was looking at the RTree documentation page with one of my
> >> colleagues - 
> >>
> >> We tried the example with the schema in 3.1, populated the data from 3.2
> >> and then queried it using the query of 3.3 - we got no results while the
> >> documentation says that "the query would very quickly locate the id of 1
> >> even if the R*Tree contained millions of entries".
> >>
> >> Is the line "AND minY>=35.00  AND maxY<=35.44;" supposed to be "AND
> >> minY>=33.00  AND maxY<=35.44;" (33 instead of 35.00)?
> >
> > I copy/pasted the code and it all seems to work for me.  My copy/paste
> > follows:
> >
> > [...]
> > .print --- one
> > SELECT * FROM demo_index WHERE id=1;
> > SELECT id FROM demo_index
> >  WHERE minX>=-81.08 AND maxX<=-80.58
> >AND minY>=35.00  AND maxY<=35.44;
>
> These are two queries.  The second one outputs nothing, although its
> description implies it should.
>

I get:

drh@tallis:~/sqlite/bld$ ./sqlite3 
> > .print --- two
> > [...]
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] RTree Documentation Error?

[Clemens Ladisch]

Richard Hipp wrote:
> On Tue, Apr 16, 2013 at 1:15 PM, Mohit Sindhwani  wrote:
>> Hi, I was looking at the RTree documentation page with one of my
>> colleagues - 
>>
>> We tried the example with the schema in 3.1, populated the data from 3.2
>> and then queried it using the query of 3.3 - we got no results while the
>> documentation says that "the query would very quickly locate the id of 1
>> even if the R*Tree contained millions of entries".
>>
>> Is the line "AND minY>=35.00  AND maxY<=35.44;" supposed to be "AND
>> minY>=33.00  AND maxY<=35.44;" (33 instead of 35.00)?
>
> I copy/pasted the code and it all seems to work for me.  My copy/paste
> follows:
>
> [...]
> .print --- one
> SELECT * FROM demo_index WHERE id=1;
> SELECT id FROM demo_index
>  WHERE minX>=-81.08 AND maxX<=-80.58
>AND minY>=35.00  AND maxY<=35.44;

These are two queries.  The second one outputs nothing, although its
description implies it should.

> .print --- two
> [...]


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

Re: [sqlite] RTree Documentation Error?

[Richard Hipp]

On Tue, Apr 16, 2013 at 1:15 PM, Mohit Sindhwani  wrote:

> Hi, I was looking at the RTree documentation page with one of my
> colleagues - 
> http://www.sqlite.org/rtree.**html
>
> We tried the example with the schema in 3.1, populated the data from 3.2
> and then queried it using the query of 3.3 - we got no results while the
> documentation says that "the query would very quickly locate the id of 1
> even if the R*Tree contained millions of entries".
>
> Is the line "AND minY>=35.00  AND maxY<=35.44;" supposed to be "AND
> minY>=33.00  AND maxY<=35.44;" (33 instead of 35.00)?
>

I copy/pasted the code and it all seems to work for me.  My copy/paste
follows:

CREATE VIRTUAL TABLE demo_index USING rtree(
   id,  -- Integer primary key
   minX, maxX,  -- Minimum and maximum X coordinate
   minY, maxY   -- Minimum and maximum Y coordinate
);
INSERT INTO demo_index VALUES(
1,   -- Primary key
-80.7749, -80.7747,  -- Longitude range
33.3776, 35.3778 -- Latitude range
);
INSERT INTO demo_index VALUES(
2,
-81.0, -79.6,
35.0, 36.2
);
.print --- one
SELECT * FROM demo_index WHERE id=1;
SELECT id FROM demo_index
 WHERE minX>=-81.08 AND maxX<=-80.58
   AND minY>=35.00  AND maxY<=35.44;

.print --- two
SELECT id FROM demo_index
 WHERE maxX>=-81.08 AND minX<=-80.58
   AND maxY>=35.00  AND minY<=35.44;

.print --- three
SELECT id FROM demo_index
 WHERE maxY>=35.0  AND minY<=35.0;


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