tec4 - 2:29 am on Feb 7, 2012 (gmt 0)
@Dijkgraaf - I'm trying to play with the idea of storing the closest entities in another table and just doing another simple look-up based on the entry's ID. I'm trying to figure out how/if it would work with my current set up....I think it would work decently for 1 of the 2 types of entities that I have this going for but not sure about the other - example:
has about 1 million rows. This table is updated/modified approximately every 5 hours w/ new entries and modifications to existing entries. Some of the entries, once modified, are updated to certain status's that cannot be displayed to the end user anymore and thus cannot be contained in the "nearby items" group. So, not really 100% sure how I could store this one efficiently...but then again, maybe any storing would be more efficient than doing it all on the fly. Would also need to take into consideration that I would probably need to update the nearby items at a minimum of a weekly basis locally, on a couple of my home computers, and upload and overwrite to the existing data - which isn't really much of a problem I don't think -- just trying to wrap my head around how to do it the best way possible.
I am actually already storing the closest items from this DB to other items in this DB (this one is much more static and never changes to a non-show-able status)...this one is about 210,000 rows and also queries against Database#1 for "nearby items" (this one is also done on the fly...but when looking for nearest entries from DB#2 to DB#2, it is stored in an array in the database).
Thanks for the pic regarding GIS donut, i'll look that up a bit later tonight when I'm back on the PC.
I'll try some ideas I come up with over tonight and see if I can come up with anything that works - it does seem a bit odd to be doing all the resource intensive calculations on the fly....any ideas or thoughts are always welcome :)