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 = 2613 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 = 2613 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 = 2613 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;

1506 Hamby Rd.
Longview, TX 75605

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $2,650,000
Beds: 4
Baths: 6
Status: Active
SqFt: 5214
Agency: Keller Williams Realty

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 18 acre(s)
Property Type: Residential
Year Built: 2013
Notes: Welcome to your dream country estate nestled on 18.8 acres in Longview, Texas. This stunning property offers unparalleled luxury, privacy, and functionality across three amazing properties and sprawling grounds. Main House - 5214 square feet of elegant living space - Four bedrooms, three full bathrooms, two half bathrooms, and a full pool bath. Grand features including pine hardwood floors, wood beams, and a butler’s pantry with wet bar. Entertainment options abound with a media room, playroom, and study/formal living area. Outdoor paradise featuring a spectacular pool with waterslide and a fully equipped outdoor kitchen. Built in 2013, this home exudes modern comfort and timeless charm. Impressive 6654 square foot Coach House with space for a large motorcoach. Versatile layout on the ground level includes an office with a safe room, guest bedroom, full bathroom, workshop, gym, motorcoach parking and 5 bay doors. The second level boasts a one-bedroom apartment for added convenience and privacy. Climate-controlled storage (not included in the SF count) on the third level, accessible via a lift servicing all 3 floors. Charming 2626 square foot guest home with four bedrooms, two bathrooms and a 3 car garage. Serene grounds with a long tree-lined driveway and solar-powered gate for security. Stay prepared with whole-home generators serving all three buildings. Equestrian enthusiasts will appreciate the 1296 square foot barn with five horse stalls and a tack room, horse pens, and horse pasture. Additional features include two chicken coops and a free-range pen with metal covering, a dog pen with runs and a cozy dog house. This extraordinary estate offers a rare opportunity to live in luxury while enjoying the peace and tranquility of country living. Schedule your private tour today and experience the epitome of Texas elegance! Ask for additional photos, floor plans and features. Available to be shown by appointment on Fridays, Saturdays and Sundays.
MlsNumber: 24006283


Listing Provided By: Keller Williams Realty, original listing
Name: Keller Williams Realty
Phone: (903) 534-6600
Office Name: Keller Williams Realty-Tyler
Office Phone: (903) 534-6600
Agent Name: Meredith Roel
Disclaimer: Copyright © 2024 Greater Tyler 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