It's a compilation mod which is supposed to overhaul all of the textures and models without compromising on the visuals, but in its current state it's not totally consistent and some of the new textures are of questionable quality or outright cause visual bugs. That alone does wonders, but it leaves out the countless world textures, which as far as I know, only that mod you found on ModDB addresses. There's one compilation that reworks a ton of props so they're higher res, and one that makes all of the characters look better. There are a few HD texture packs on Gamebanana that will go a long way in improving the look of the game. The official version is just plain broken in many ways (on a strictly visual basis). But it's an absolutely essential mod if you want to replay HL2. It fixes all of the lighting, changes some particle effects so they look better, and other more subtle stuff like that. It mostly just fixes a lot of ♥♥♥♥ that got broken when Valve moved the game to the newer version of the Source Engine. Half-Life 2: Update doesn't do a ton to the visuals. It also replaces all of the music with generic orchestral stuff. Cinematic mod takes way too many liberties with the game's visuals, and butchers the look of the game as a result.
