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

2478 N Mountain Avenue
Upland, CA 91784

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $4,645,000
Beds: 6
Baths: 9
Status: Active
SqFt: 9339
Agency: RE/MAX Resources

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 32,400 sqft
Property Type: Residential
Year Built: 2010
Notes: Be prepared to be amazed by this one-of-a-kind, Tuscan-inspired estate located in prestigious San Antonio Heights. This architectural masterpiece was meticulously crafted & stands as a beacon for those discerning buyers searching for only the finest quality of construction. As you arrive at the residence, you will find stunning wrought iron fencing that wraps the property & an automatic gate giving entrance to the grounds. The smooth stucco walls, terracotta roof tiles, & precast pillars invite you to experience the interior. You enter the main residence through the iron doors as the epitome of quality, luxury & timeless grace unfold before you. The use of travertine, glass, copper, stone slabs & tiles were all thoughtfully engaged to provide a sense of excitement when moving through the home. There are hand-hewn wood floors, mahogany encased archways that flow along the first floor, & the ambiance of six fireplaces accent various spaces throughout the property. The kitchen offers everything for a professional chef to enjoy. Viking appliances, Typhoon Bordeaux granite counters, the custom crafted cabinets, & all the intricate details in between, the butler's pantry & walk-in pantry & a center island, all making for a spectacular place to create meals & memories to share with family & friends. The kitchen graciously opens to the huge great room perfect for entertaining intimate gatherings or larger parties & is accented by walls of French doors, allowing for natural light to bathe the space. The office is paneled with mahogany built-ins & boasts a mahogany coffered ceiling. Down the hall, you will find the fully self-contained guest/family quarters. There is a living room, dining area, a full kitchen, as well as two bedrooms & baths, & a two-car garage with direct access. You will find the primary suite is accented with Acanthus moldings & Cappuccino Onyx appointments, creating an environment that artfully melds luxury & tranquility. The loggias provide a space to decompress as you overlook the infinity edge pool, spa, & outdoor kitchen. There is a hidden walkway below the cascading water feature that leads to the lush gardens of scented fruit trees, lawns, palms & flowering vegetation. There are breathtaking morning & evening views of the valley & snow-capped mountains. This estate is truly a spectacular representation of immense luxury, quality of construction & design perfection. The amenities are too numerous to list but are available upon request.
MlsNumber: CV24029445


Listing Provided By: RE/MAX Resources, original listing
Name: RE/MAX Resources
Phone: (909) 398-1166
Office Name: The Real Estate Resource Group
Office Phone: (909) 575-4517
Agent Name: Laura Dandoy
Disclaimer: Copyright © 2024 California Regional Multiple Listing Service, Inc. All rights reserved. All information provided by the listing agent/broker is deemed reliable but is not guaranteed and should be independently verified.


Local Real Estate Expert

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 = 3369 AND i.igate IN ('usahud','txhud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'txhud', 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;
Sandra Rosetti
ASCIIRE/MAX Proper
Call Today!: (909) 333-9962

USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App