Title Page Background
HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 431 AND i.igate IN ('usahud','nwhud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'nwhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 431 AND i.igate IN ('usahud','nwhud','ushud') AND m.expertType = 'lender' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'nwhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 431 AND i.igate IN ('usahud','nwhud','ushud') AND m.expertType = 'other' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'nwhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1;

1345 Castle Rock Road
Trenton, GA 30752

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $5,900,000
Beds: 9
Baths: 8
Status: Pending
SqFt: 6406
Agency: Century 21 BELLORA

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 843 acre(s)
Property Type: Residential
Year Built: --
Notes: Welcome to this incredible 843-acre estate nestled right where Alabama, Georgia, and Tennessee converge, boasting breathtaking natural beauty and timeless charm. Picture this: you're driving down a mile-long paved driveway, flanked by a classic white vinyl fence and trees bursting with blooms in spring and rich burgundy hues come fall, setting the scene for your journey. As you arrive, sweeping vistas of rolling fields and enchanting southern sunsets unfold before you, painting a scene straight out of a postcard. But the real magic happens when you explore further. With 4.5 miles of rugged bluffs overlooking Nickajack Lake and the Tennessee River, this estate is a nature lover's dream. And that's just the beginning. Imagine hosting events in the 100x100 custom barn, with its vaulted ceilings, crystal chandeliers, and tranquil lake views. Need a place to call home? Take your pick from three remarkable residences: The Manor House, a true masterpiece boasting four bedrooms, four full baths, overlooking the 2-acre lake; The Second House, offering three bedrooms, lake views, and ample garage space; and the Caretaker's House, a charming abode with two bedrooms and a carport. But wait, there's more! A large shop/garage with three roll-up bays and a vintage barn add to the allure of this extraordinary property, perfect for those seeking an unforgettable retreat in the heart of the South. Don't miss out on this once-in-a-lifetime opportunity to embrace nature and luxury in perfect harmony. Welcome to your new season of beauty and wonder.
MlsNumber: 1390308


Listing Provided By: Century 21 BELLORA, original listing
Name: Century 21 BELLORA
Phone: (706) 657-3000
Office Name: Century 21 BELLORA
Office Phone: (706) 657-3000
Agent Name: Lora Poore
Disclaimer: Copyright © 2024 Greater Chattanooga Association of REALTORS. All rights reserved. All information provided by the listing agent/broker is deemed reliable but is not guaranteed and should be independently verified.


USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App