Hello,
I have what seems to be a massively complicated problem. If I were to
try to write it out, I think the description of it would go on for
pages. Before I go into it, I think I will ask a much simplified
version of my question and hopefully work my way out from there.
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>;
Obviously a btree index wouldn't work well and in the documentations
it said an rtree index works for 2 dimensional queries, but I would
like to filter first by status_id since that will probably eliminate
50%+ of the rows in the table. There are currently over 600 000 rows
in the table so far, and it will be growing to well over a million.
As an extra bonus, how can I setup tsearch2 to perform a full text
query on just the results of the previous query (as in, do a text
search on all profiles that are active and in a certain area)?
Thanks,
-carl
--
EPA Rating: 3000 Lines of Code / Gallon (of coffee)
