Re: [sqlite] rtree extension - Windows Binary?
В сообщении от Friday 13 June 2008 20:38:42 Andrew Brampton написал(а): > Hi Donald, > > I have a index on both lat and long, but please correct me if I'm wrong, > but I think SQLite will only use a single index per SELECT. So only one of > the index is in use. I think the EXPLAIN command confirms this for me. You can use multiple indices as http://www.mail-archive.com/sqlite-users%40sqlite.org/msg27224.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree extension - Windows Binary?
Hi Noël,
Sorry I wasn't clear... I don't have a windows compiler, hence being unable
to compile it :)
Andrew
- Original Message -
From: "noel frankinet" <[EMAIL PROTECTED]>
> However at the moment I'm unable to compile it, so I was wondering if
> anyone had a DLL for windows which I could use. I am using PHP 5.x to
> access my SQLite database, so the extension must be compatible with the
> sqlite included with that.
>
What do you use to compile, I just added rtree.c to my vc6 project and
it compiled right away.
I've not tested it yet, however :-{
Best wishes
Noël
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree extension - Windows Binary?
Hi Donald, I have a index on both lat and long, but please correct me if I'm wrong, but I think SQLite will only use a single index per SELECT. So only one of the index is in use. I think the EXPLAIN command confirms this for me. I have not played with the cache size, but I have used VACUUM, and also recreated the database from a fresh dump of all the data (ie so no data has ever been removed). But from what I've read the rtree extension is designed for lat/long style problems, and thus would be perfect for my problem. However if you feel you can help me more by without the extension then the schema and query are below thanks Andrew CREATE TABLE Towers ( towerID INTEGER PRIMARY KEY NOT NULL, latitude REAL NOT NULL, longitude REAL NOT NULL, zoom INTEGER NOT NULL ); CREATE INDEX Towers_Latitude ON Towers (latitude); /* I was using these index, but without has not decreased the query time CREATE INDEX Towers_Longitude ON Towers (longitude); CREATE INDEX Towers_Zoom ON Towers (zoom); */ SELECT towerID AS i, latitude AS y, longitude AS x FROM towers WHERE longitude > -27.0263671875 AND longitude < 19.7314453125 AND latitude > 44.72599125467816 AND latitude < 64.6631165643883 ORDER BY zoom LIMIT 50; - Original Message - From: "Griggs, Donald" <[EMAIL PROTECTED]> Sent: Friday, June 13, 2008 5:15 PM Subject: Re: [sqlite] rtree extension - Windows Binary? > Hi Andrew, > > Maybe this goes without saying, but I guess you've already worked on the > "usual suspects" such as: > > -- An index for Lat and another for Lon. > -- Larger cache > -- EXPLAIN QUERY PLAN prefix to ensure that index used > -- One-time VACUUM has any effect? > -- ANALYZE > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] rtree extension - Windows Binary?
Griggs, Donald a écrit :
> Hi Andrew,
>
> Maybe this goes without saying, but I guess you've already worked on the
> "usual suspects" such as:
>
> -- An index for Lat and another for Lon.
> -- Larger cache
> -- EXPLAIN QUERY PLAN prefix to ensure that index used
> -- One-time VACUUM has any effect?
> -- ANALYZE
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Brampton
> Sent: Friday, June 13, 2008 11:52 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] rtree extension - Windows Binary?
>
> Hi,
> I've recently created a database with latitude/longitude points in it,
> and now I need to search for points without a given rectangle. With
> 60,000 points I'm able to retrieve a rectangle of 50 points in ~1
> second. This is way too slow, so I wanted to try out the rtree
> extension.
>
> However at the moment I'm unable to compile it, so I was wondering if
> anyone had a DLL for windows which I could use. I am using PHP 5.x to
> access my SQLite database, so the extension must be compatible with the
> sqlite included with that.
>
What do you use to compile, I just added rtree.c to my vc6 project and
it compiled right away.
I've not tested it yet, however :-{
Best wishes
Noël
> Would anyone be able to give me such a DLL? Also is there a good write
> up on how to use this extension? I've been following the mailing list,
> but another source would be helpful.
>
> thanks
> Andrew
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> This email and any attachments have been scanned for known viruses using
> multiple scanners. We believe that this email and any attachments are virus
> free, however the recipient must take full responsibility for virus checking.
> This email message is intended for the named recipient only. It may be
> privileged and/or confidential. If you are not the named recipient of this
> email please notify us immediately and do not copy it or use it for any
> purpose, nor disclose its contents to any other person.
> ___
> 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 extension - Windows Binary?
Hi Andrew, Maybe this goes without saying, but I guess you've already worked on the "usual suspects" such as: -- An index for Lat and another for Lon. -- Larger cache -- EXPLAIN QUERY PLAN prefix to ensure that index used -- One-time VACUUM has any effect? -- ANALYZE -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Brampton Sent: Friday, June 13, 2008 11:52 AM To: sqlite-users@sqlite.org Subject: [sqlite] rtree extension - Windows Binary? Hi, I've recently created a database with latitude/longitude points in it, and now I need to search for points without a given rectangle. With 60,000 points I'm able to retrieve a rectangle of 50 points in ~1 second. This is way too slow, so I wanted to try out the rtree extension. However at the moment I'm unable to compile it, so I was wondering if anyone had a DLL for windows which I could use. I am using PHP 5.x to access my SQLite database, so the extension must be compatible with the sqlite included with that. Would anyone be able to give me such a DLL? Also is there a good write up on how to use this extension? I've been following the mailing list, but another source would be helpful. thanks Andrew ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
