On Fri, Jun 19, 2009 at 11:27 AM, Brent Baisley<brenttech@stripped> wrote:
> It sounds like you want to use spatial indexes, but they only became
> available in v4.1
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
> (SELECT zip FROM
> zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+
> < 5) AS zips
> ON custzip=zip
Will that work after a where clause? Multiple Times? For example...
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...