I get asked all the time about what mods I use and where to get them. I have compiled a list of useful links and I wanted to share them here!
Simtropolis Exchange
-Most popular: http://community.simtropolis.com/files/category/1-simcity-4/?dosort=1&sort_key=file_downloads&sort_order=desc
-Highest rated: http://community.simtropolis.com/files/category/1-simcity-4/?dosort=1&sort_key=file_rating&sort_order=desc
SC4 Devotion
-Most popular: http://sc4devotion.com/csxlex/lex_lotlist.php?orderBY=popular
STEX Hall of fame: http://community.simtropolis.com/topic/42323-the-stex-hall-of-fame/
Toutsimcity
-Most popular: http://www.toutsimcities.com/downloads/index/simcity4/sort:Download.downloaded/direction:desc
Capital SimCity: http://descargas.capitalsim.net/?sitio=csc
Japanese SC4 site links: http://sc4devotion.com/forums/index.php?topic=361.0
-SOMY is a great example: http://members3.jcom.home.ne.jp/somy000/index.htm
Simtropolis STEX CD/DVDs (costs $ but its easy and helps support the site): http://www.simtropolis.com/stexcollections
...That's what I've got for what's popular and easy to access. If you have questions or others to add, please do so!