If you’re wondering why there’s nothing on MOJO HD, it’s because MOJO HD has been cancelled (iN DEMAND confirms). Now the question is, what will it be replaced with.

In the Seattle/Tacoma area, Comcast has been shuffling channels around.

• MHD has been renamed to Palladia.
• ESPN HD is now being mapped to both 173 (old) and 623 (new), the old channel presumably pending removal.
• ESPN2 HD is now being mapped to both 174 (old) and 624 (new), the old channel presumably pending removal.
• FSN HD has been rolled out (using the space freed by MOJO???). It’s channel 627.
• The hybrid channel, VS/Golf, is going to be split into two separate channels (VS and Golf). Whether or not both (or which, if any) will be carried in this area, is unconfirmed at this point.
• King-5 Weather Plus (channel 115) is pending removal, scheduled to be removed on 12-31-2008. The reason is, the parent company has decided to pull the plug on it.

I’ve been following some spurious rumors, that Comcast could be preparing to ramp up their HD capacity in the Seattle/Tacoma area. A technician said that next month, they’re supposedly going to cut the number of analog channels they provide. Why is this important? Because the old analog channels take up twice as much space as the average HD channel.

A single analog channel consumes a 6mhz slot (while 2-3 HD channels can occupy the same slot). If they’re running 70 analog channels on a 860mhz system, then the analog channels consume roughly half of it’s capacity. Even removing a few of those channels frees up a lot of space for more HD channels. In some cities that are all digital, Comcast runs 50+ HD channels.

I have my fingers crossed.

Followup Post: PostGIS: Bounding Box Indexing

Recently, I’ve been playing around with PostGIS (that’s geographic information system). The function, distance_sphere (or ST_distance_sphere, depending on the version), is of particular interest to me. Here’s what the documentation has to say about it:

“ST_distance_sphere(point, point), Returns linear distance in meters between two lat/lon points. Uses a spherical earth and radius of 6370986 meters. Faster than distance_spheroid(), but less accurate. Only implemented for points.”

This function is especially useful for calculating the approx. distance between zip codes. Lets say you have a table like this:

CREATE TABLE zip (
    zip CHAR(5) NOT NULL PRIMARY KEY,
    latitude FLOAT8 NOT NULL,
    longitude FLOAT8 NOT NULL
);

You could simply add a new column like so:

ALTER TABLE zip ADD COLUMN geom GEOMETRY;

Then populate the new column:

UPDATE zip SET geom = makepoint(longitude, latitude);

The function distance_sphere, calculates the distance in meters. In order to convert the result to miles, it needs to by divided by 1609.344. Lets say, you want to get all zip codes within 15 miles another zip code, say 10001. You could simply do:

SELECT zip FROM zip WHERE
    distance_sphere(geom,
        (SELECT geom FROM zip WHERE zip = '10001'))
    <= (15 * 1609.344);

There doesn’t seem to be a way to use an index to speed the query up, but it is still significantly faster than other methods. On the application side, you could use something like Memcached to cache the results, if applicable.

There are more accurate ways to find the distance between lat/lon points, however, they also require more work, and are slower.

I found this while setting up my Airport Extreme.

It’s a little unclear why having the status light blink amber for a couple seconds, is such a big issue, that it needs it’s own confirmation dialog. I’m sure they could have come up with an explanation that, you know, actually elaborates on what the actual problem is, or at least conveys some sort of semi-useful message.

This is something you shouldn’t have to learn the hard way: How to create a ratings system without using aggregate functions. Specifically, what I’m talking about by an aggregate, is the AVG() SQL function. The problem with using AVG(), is it reads every row in the table, which works well when you’re not dealing with very many rows, but as your data grows, the query speed progressively slows down. This is because aggregate functions essentially perform a sequential scan on the table. The solution for this, is to store the average for each individual item in a table, and use simple math calculations for updates.

Such a table may look something like this:

CREATE TABLE item_ratings (
    item_id INTEGER NOT NULL PRIMARY KEY,
    rating DOUBLE PRECISION,
    votes INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX item_ratings_rating_key ON item_ratings (rating);
CREATE INDEX item_ratings_votes_key ON item_ratings (votes);

There’s a number of options for adding the initial entry, that updates will eventually be done on, such as doing it in code or via a trigger/rule. For the sake of simplicity, lets say there’s already a blank entry for item #1.

INSERT INTO item_ratings (item_id, rating, votes) VALUES (1, 0, 0);

Now, instead of adding a new entry for each vote cast, you simply update the previous entry. Lets say someone casts a vote, giving it a 4 star rating. That would be handled as such:

UPDATE item_ratings SET
    rating = ((rating * votes) + 4) / (votes + 1),
    votes = (votes + 1)
    WHERE item_id = 1;

SELECT * FROM item_ratings WHERE item_id = 1;
 item_id | rating | votes
---------+--------+-------
      1          4        1

That’s pretty much it. Now all ratings are stored per item, instead of generated by an aggregate. This can save major headaches when dealing with large datasets, since AVG() in PostgreSQL tends to be slow when working with such datasets.

I’m experiencing a number of different issues. For one, the connection speed is wildly inconsistent. Sometimes I get 5mbps, other times I get under 1mbps. The connection is always dropping, and reconnecting. Sometimes it spends 30+ minutes disconnecting and reconnecting, before it finally gets a stable connection. Large downloads are constantly being interrupted because of the connection dropping.

This is crazy. I’m giving them another angry phone call on Monday.

There’s many different methods to use caching, with memcached. I call this one the Aggressive approach.

The basic logic is this: if you don’t even touch the database, you will be able to serve far more requests, than if you did. The biggest downside is, you can’t really do things too dynamically, and really, this only works well for flat pages (ie., it’s not for anything that displays content, based on the user).

In my case, I added the following code (shown as pseudo code) to the main/controller/dispatch script:

$memcache = new Memcache;
$memcache->connect(...);
$data = $memcache->get($_SERVER['REQUEST_URI'])

if ($data) {
    echo $data;
    die();
 }


$db = new PDO(...);
ob_start();
ob_implicit_flush(false);
...
... do whatever ...
...
$data = ob_get_contents();
$memcache->set($_SERVER['REQUEST_URI'], $data, 0, 3600);
ob_end_flush(); 

One of the biggest downsides to this approach, is figuring out how to delete cached objects when something’s updated. If you have a single table that holds the pages that you’re caching, however, you can just add a hook that deletes the cached object, by URI, when anything is written that would affect it.

This really doesn’t work well for anything super dynamic, but it’s great for increasing performance in areas that would otherwise waste resources.

Qwest finally got my Internet working, *phew*, after an agonizing 13 days. Now a new problem has reared it’s ugly head: My internet connection is ridiculously slow. I mean seriously slow. My gateway shows the downlink speed at 1100kbps. At my old apartment, it was usually around 6900. What’s worse: my actual connection speed is slower than what the gateway displays.

SpeedTest.net gives me an average of ~800kbps, just barely faster than my uplink speed! The meter jumped wildly between 1200kbps and 200kbps, during the test. This is absolutely ridiculous. I haven’t called yet, but I’m hoping it will end up being a simple fix. This whole experience has left me dismayed and flabbergasted. This is the first time I’ve ever considered Comcast as a serious alternative.