select * from (Select a.`postalCode`,a.`uniturl`, `region`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `source`, `property_type`, `address`,round(price) as price,minimum_nights, `rating`, `review_count`,`latitude`, `longitude`,case when propertyName='' then trim(address) else CONCAT(CONCAT(propertyName,'(',trim(address)),'',')') end as addre,propertyName,
(6371 *
acos(
cos( radians( 21.2759167 ) ) *
cos( radians( `latitude` ) ) *
cos(
radians( `longitude` ) - radians( -157.8233677 )
) +
sin(radians(21.2759167)) *
sin(radians(`latitude`))
)
) as distance from `vacationrentalindex` a inner join vacationrentalindexfeed b on a.uniturl=b.unitUrl and a.headline!='' and a.thumbnailUrl!='' and b.enabled=1 and b.blocked=0 ) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%Mauna Lani Terrace%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Mauna Lani Terrace K202 is a beautiful 1 bedroom, 2 bath upgraded Deluxe unit only a short walk away from the beach! Located on the second floor (stairs, no elevator), the condo has great views of the Mauna Lani fishponds and gardens. The ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kamuela
J104 is a gorgeous renovated 2-bedroom, 2-bathroom condo with easy access to the Mauna Lani's private Beach Club. Located on the first floor and only steps away from the ocean and ancient Hawaiian fishponds, the condo's covered lanai opens to a ...
- 2 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Kamuela
A104 is a beautiful oceanfront 2 bedroom / 2.5 bath deluxe unit. Enjoy gorgeous ocean and sunset views from the large covered lanai that is only steps away from the sandy beach. Complete renovations provide guests with brand new tile flooring, ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
L302 is a newly renovated top-floor condo in a fantastic location only a short walk away from the beach. This 2 bedroom, 2-bath condo sits up on the third floor and features vaulted ceilings, gorgeous ocean and lagoon views with tasteful dark ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
Ocean Front Mauna Lani Terrace – Walk to White Sand Beaches
Enjoy Mauna Lani Terrace at its finest! This villa is tastefully decorated with a tropical flare, has high ceilings with extensive mahogany trims and is completely equipped to...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kohala Coast
The Mauna Lani Terrace is located within the world class Mauna Lani Resort. It is on the ocean and adjacent to the royal fishponds and between two beautiful white sand beaches. The water is crystal clear with one of Hawaii’s healthiest ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kohala Coast
The Mauna Lani Terrace is a premier oceanfront property located within the 5-star Mauna Lani Resort on the Kohala Coast of the Big Island. Adjacent to the Mauna Lani Bay Hotel on the only beachfront condominium site in the Mauna Lani Resort, the...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Waimea
Enjoy superior oceanfront living in C102, a condo only steps away from the beach. Located on the ground floor of the world renowned Mauna Lani Terrace, this very desirably located unit overhangs historic Hawaiian fish ponds and offers ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
â• UPSCALE LUXURY SUITE. LAGOONVIEW TERRACE #G102
â• LARGE 1,150 SQ FT. WATER VIEWS OF LAGOON. COVERED LANAI
â• TWO MINUTE WALK TO OCEAN. 5 MINUTE WALK TO RESTAURANTS BARS
â• NO STAIRS! CENTRAL AIR CONDITIONING ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kamuela
Luxury, oceanfront condo at the exclusive Mauna Lani Terrace - centrally located within walking distance of the private Beach Club, the Auberge Mauna Lani Hotel and fitness center, historic walking paths, the Terrace amenity center and ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
Beautiful and spacious ground floor condominium that features fully upgraded interiors, privacy and complementary access to the Auberge Mauna Lani Sports & Tennis Club. This gorgeous two bedroom has interior decor that features custom carved ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
Mauna Lani Terrace E201 offers a nice ocean view out over the ponds. This one bedroom villa has been nicely upgraded to include granite counters and stone floors. The living area has a sleeper sofa with two chairs that faces a LCD television ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kamuela
Beautiful oceanfront condo located only steps away from the beach and the Mauna Lani Resort! J201 is a primely located, newly furnished condo with 2 bedrooms and 2.5 bathrooms. Its perfect location in the exclusive "J" building offers up close ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
Mauna Lani Terrace Unit D201 is perfectly situated between the Mauna Lani Bay Hotel and the Mauna Lani Beach Club in the highly desired vacation destination of Mauna Lani Resort. You will be close to shopping, dining, championship golf, and, ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Waimea
Sophisticated, contemporary three bedroom condominium located at the point where the ancient fish ponds meet the Pacific. Enjoy the wide, unobstructed view from your lanai with your morning coffee and your sunset cocktail.
Recently ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
Recently renovated, this oceanfront penthouse with three bedrooms and three full baths showcases panoramic Pacific Ocean views and offers a stylish, serene setting for your next Big Island vacation. Mauna Lani Terrace A303 boasts an elegant new ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
This vacation rental is professionally managed by ResorticaHawaii
Mauna Lani Terrace L102. Includes Mauna Lani Beach Club Pass
Please scroll down for detailed information
PLEASE NOTE: There are stairs to that lead down to the...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Mauna Lani Resort
C202 is a 2-bedroom, 2.5 bath oceanfront condo only steps away from the beach at the renowned Mauna Lani Resort. The condo is spacious and has a large outdoor lanai - perfect for sunset dinners or relaxing to the sounds of the nearby surf. The ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
D103 is a spacious 2 bedroom, 2.5-bathroom condo only a short walk away from the beaches at Mauna Lani. Located within the highly prized Mauna Lani Terrace, the condo is close to all the best sights on the coast and has beautiful lagoon and ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
One-bedroom penthouse with beautiful ocean views at the acclaimed Mauna Lani Terrace. This spacious couples getaway features beautiful tile flooring throughout, high vaulted ceilings and a large outdoor lanai for gorgeous sunset views over Mauna...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kamuela
