On Sun, Feb 13, 2011 at 11:47:48AM -0600, Puneet Kishor scratched on the wall:
> I have a simple table like so
>
> CREATE TABLE points (
> id INTEGER PRIMARY KEY,
> lon REAL,
> lat REAL,
> tile INTEGER
> );
>
> It has about 13.25 million rows. I want to be able to return rows
> given a bounding box (min_lon, min_lat, max_lon, max_lat). Is the
> following the right strategy?
>
> CREATE VIRTUAL TABLE points_rtree USING rtree (
> id, min_lon, min_lat, max_lon, max_lat
> );
Almost. You need to match min/max pairs, so the column order would
need to be:
( id, min_lon, max_lon, min_lat, max_lat )
...or...
( id, min_lat, max_lat, min_lon, max_lon )
> and then querying like so
>
> SELECT p.id, tile
> FROM points p JOIN points_rtree pr ON p.id = pr.id
> WHERE min_lon >= -91 AND max_lon <= -89 AND min_lat >= 43 AND max_lat <= 45;
Yes, that's the general idea. Since the R-Tree column can only hold
an ID column (in addition to the min/max pairs), it is normally
JOINed against a standard table with more specific information.
When using lat/lons, be sure to account for bounding boxes that cross
the +180/-180 meridian. This can be done by taking the union of a
box on each side of the anti-meridian.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
