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 = 1497 AND i.igate IN ('usahud','nyhud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'nyhud', 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 = 1497 AND i.igate IN ('usahud','nyhud','ushud') AND m.expertType = 'lender' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'nyhud', 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 = 1497 AND i.igate IN ('usahud','nyhud','ushud') AND m.expertType = 'other' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'nyhud', 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;

512 Rain Tree Drive
St Joseph, MO 64506

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $479,000
Beds: 4
Baths: 4
Status: Pending
SqFt: 3660
Agency: Keller Williams northland partners llc

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: --
Property Type: Residential
Year Built: 1993
Notes: Amazing opportunity to claim this sprawling, well-maintained estate as your own! Beautiful, lush front lawn and gardens bordering tree-lined, cul-de-sac street. Circle drive curves up to the 3 car garage. Upon entering you are greeted with an expansive foyer opening into a first living room, beautiful hardwood floors, an office to your left and formal dining room to your right, turning staircase up to the 2nd floor that houses 3 bedrooms and a full bathroom with double vanities plus a large additional storage room. Main floor master with adjoining master bathroom that boasts a jetted tub and a huge 5.5 x 9.5 walk-in closet and a separate shower. There is a well-appointed half bath for guests just off of the living room. Also on the main floor are the laundry room with cabinets and sink convenietly right off of the garage, kitchen with custom cabinetry, built-in pantry with amazing wood shelving system, island with cooktop and a built-in desk, and an additional living room with media built-ins, a beautiful gas fireplace and double sliding doors that open to the private, fenced back yard. Head down to the lower level and you will be blown away with amenities! Built-in hot stone sauna is ajoined by the gym space, an additional large finished room with hardwood floors and an enormous den with room for a pool table, lots of built-in features and an additional gas fireplace. Plus a 13.8x9.5 wet bar! The luxurious basement bath is your own personal spa with a steam room and 3 person whirlpool tub, double vanities and a bidet. There is also a reinforced shelter room in the basement. Outside in the fenced back yard are a beautiful leafy garden, orchard, plants, trees and flowers, a large patio and an enclosed gazebo with hot tub. You won't want to miss this rare opportunity in Stonecrest!
MlsNumber: 2482753


Listing Provided By: Keller Williams northland partners llc, original listing
Name: Keller Williams northland partners llc
Phone: (816) 452-4200
Office Name: Keller Williams KC North
Office Phone: (816) 452-4200
Agent Name: AgentRoc Group
Disclaimer: Copyright © 2024 Heartland Multiple Listing Service. 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