Wednesday, January 27, 2010

Apple predictions

I wanted to get some quick predictions out for the Apple event today in the event that the device is a tablet:

- WiFi
- Bluetooth
- WhisperNet (like Kindle) via Verizon or possibly Clearwire/Sprint (and thus be carrier independent when LTE is rolled out across the board.)
- Wireless HDMI (aka WiDi)
- Wireless Charging

I don't think the device will have ANY external ports whatsoever. Depending on the range of WiDi, you can sit on your couch and broadcast a iTunes show to the TV while doing other stuff on the tablet itself. If you want, you leave the charging pad by the television or somewhere else.

Imagine you're riding on the train watching the latest episode of whatever. You get home and the device picks up where it left off broadcasting to your television.

I think social gaming a la Farmville will be there as well. Garage band on this thing would actually be pretty cool.

Anyway, some of these ideas are my own and others are things I've picked up from various podcasts but if I were building the next gen "device", those are the features I would pick.

Sunday, January 10, 2010

Ugly queries

I've never been a fan of ORMs in general. I love the concept. I think it's awesome but since the early days of hibernate, I've always had problems with automatically generated SQL statements. It's not as bad as it used to be but sometimes you get "gems" in the wild.

Today's gem comes courtesy of the Geokit plugin for Rails. Our newest application at AJC uses Geokit for finding properties within a 10-mile radius of the entered address. We've got a table with about 500k rows of geocoded metro Atlanta property data in MySQL.

Enter the query:


SELECT
*,
(ACOS(least(1,COS(0.593807786352424)
*COS(-1.47255067854929)
*COS(RADIANS(parcels.latitude))
*COS(RADIANS(parcels.longitude))+
COS(0.593807786352424)
*SIN(-1.47255067854929)
*COS(RADIANS(parcels.latitude))
*SIN(RADIANS(parcels.longitude))+
SIN(0.593807786352424)*SIN(RADIANS(parcels.latitude))))*3963.19)
AS distance FROM `parcels`
WHERE (((parcels.latitude>32.5769814714641
AND parcels.latitude<35.4683785285359>-86.1150637195914
AND parcels.longitude<-82.6268142804086))
AND ((ACOS(least(1,COS(0.593807786352424)
*COS(-1.47255067854929)*COS(RADIANS(parcels.latitude))
*COS(RADIANS(parcels.longitude))+ COS(0.593807786352424)
*SIN(-1.47255067854929)
*COS(RADIANS(parcels.latitude))
*SIN(RADIANS(parcels.longitude))+
SIN(0.593807786352424)
*SIN(RADIANS(parcels.latitude))))*3963.19)<= 100))
ORDER BY distance asc
LIMIT 21;


This bad boy is a DOOZY. It's got all the makings of exploiting everything in MySQL that can't be optimized - function results for comparison, ordering by function results and even a LIMIT thrown in for good measure.

And guess what? There's currently not much that can be done to optimize this query. Indexes won't help. The query cache is useless because A) the queries are unique to each source address and B) it'll just get pushed out anyway.

I'm looking around for some tips on this and the most likely solution is using spatial indexes in MySQL but that's a whole other issue.