On Fri, Jun 19, 2009 at 11:27 AM, Brent Baisley<brentt...@gmail.com> wrote: > It sounds like you want to use spatial indexes, but they only became > available in v4.1 > http://dev.mysql.com/doc/refman/5.0/en/create-index.html > http://dev.mysql.com/doc/refman/5.0/en/using-a-spatial-index.html
That "feels" like the right thing (spatial calculations = spatial indexes?) but I looked at the docs and my head exploded. Can anyone recommend a good book that takes me through it gently? That said I'm intreged by the MBRContains and the Polygon functions... If I read those right I could create a simplified "circle" (probably just an octogon) to help eliminate false positives in the "corners" when using a plain square as the enclosure. > You don't have to do any re-architecture to change you subquery to a join: > SELECT custzip FROM customers > JOIN > (SELECT zip FROM > zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ > cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude))))*60*1.1515 > < 5) AS zips > ON custzip=zip Will that work after a where clause? Multiple Times? For example... (pseudo-code...) SELECT * FROM customers WHERE saleslastyear > 100000 JOIN (SELECT zip FROM etc....) AS zips ON custzip=zip JOIN (SELECT MAX(date) FROM phonecalls) AS LastCalledOn ON custid=custid Just from thinking about that... I assume that the only limitation is that in a subselect you can do something like WHERE NOT IN (select etc) but with a JOIN you are assuming a "positive" relationship? For example using the JOIN methods above there isn't a way to simply do "AND custid NOT IN (SELECT custid FROM ordersplacedthisyear)" other than doing exactly that and adding this clause to the saleslastyear clause. (In this particular case a column "lastorderdate" in customer that was programatically updated on ordering would also be useful but I'm thinking examples here... ;) ) I've never seen JOIN used outside of a traditional "SELECT t1.*,t2.* FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.id=t2.id" type of structure so I kinda feel like I have a new toy... Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
