On 2/1/07 10:42 AM, "Carl Lerche" <carl.lerche@xxxxxxxxx> wrote: > How can I index 2 dimensional data (latitude / longitude) with a > status_id column too (integer) so that I can perform the following > query as fast as possible: > > SELECT * FROM profiles WHERE status_id = 1 AND latitude BETWEEN > <y_1> AND <y_2> AND longitude BETWEEN <x_1> AND <x_2>; Depending on what your needs are, I'd look into PostGIS and make your data points and doing bounding box queries using a GIST index either with a partial index or indexes depending on how many status ids: create index xyz on profiles using gist(mypoint) where status_id = 1; Or a multicolumn index using the btree_gist contrib module: create index xyz on profiles using gist (status_id,mypoint); I use the first format as I only have 2 statuses, valid and not valid and it works quite well. If PostGIS won't work for you you still can possibly use a partial index if it works for your status_ids. -Steve Wormley Senior Consultant inCode Wireless a Verisign Company
