select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 AND (city = 'Puako' OR postalCode = 'Puako' OR region like '%|Puako|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Stately and serene, THE HOME OF THE HULA MOON is one of the most beautiful contemporary oceanfront properties in PUAKO. Each bedroom has a perfect ocean view. Located near the end of the road, you will enjoy year-round sunsets and privacy....
4 bedroom
5 bathroom
Sleeps 10
HI> Puako
Kulalani at Mauna Lani #2004
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Discover luxury living at its finest when you stay at this Kamuela retreat, a...
2 bedroom
3 bathroom
Sleeps 5
HI> Puako
* 3 Bedroom, 3 Bath two story unit.
* Beautifully decorated with Tommy Bahama furnishings.
* Living room has a TV, DVD and CD player.
* Master bedroom (upstairs) has a king bed, TV and DVD.
* The second bedroom (upstairs) has a queen size bed ...
3 bedroom
3 bathroom
Sleeps 6
HI> Puako
Hale Nana Kohola (Whale Watch House) Located in the heart of Puako, offers a tropical retreat that showcases the best of Hawaii and is perfect for a romantic getaway or a family vacation. Escape to the sunny Kohala Coast on the Island of Hawaii....
4 bedroom
7 bathroom
Sleeps 10
HI> Puako
2 bedroom house with separate guest quarters, sleeps 6 in two queen and two twin beds (can be joined to form a king, to accommodate a third couple). All bedrooms have en suite bathrooms, TV/DVD, and separate entrances/lanai from the exterior....
3 bedroom
4 bathroom
Sleeps 6
HI> Puako
Welcome to our Paradise Home come and relax in luxurious surroundings at our tropical condo, which has been freshly painted, and new air conditioner installed and kitchen renovation, new King Mattress. ( Winter 2016). New appliances, carpet ...
3 bedroom
2 bathroom
Sleeps 6
HI> Puako
Hali'i Kai is just minutes away from beautiful sandy beaches, world class golf, shopping, restaurants and a host of vacation amenities. One of the wonderful features of Hali'i Kai is it's oceanfront pool area - The Ocean Club. The Club has a ...
2 bedroom
2 bathroom
Sleeps 6
HI> Puako
The kitchen and main floor have recently been renovated with fresh paint, new floors, designer furnishings, quartz counters, and stainless appliances.
This well appointed condo is located in the gated Golf Villas community of Mauna Lani and ...
2 bedroom
2 bathroom
Sleeps 8
HI> Puako
Plumeria Cottage offers a vacation of ease indoors and out. Compact yet perfectly proportioned in the vintage beach style, it is a delightful combination of convenience and charm. And, with a roomy, sunny lanai and large shaded grassy yard, it ...
1 bedroom
1 bathroom
Sleeps 3
HI> Puako
Situated in one of Hawaii's oceanfront communities on the sunny Kohala Coast, The Puako Hylton Beach Villa offers the ultimate luxury retreat. With a new swimming pool being added December 1, 2016, this exotic Balinese-style home offers a ...
4 bedroom
5 bathroom
Sleeps 10
HI> Puako
Listed rates are based on two guests. An additional $25/nt per guest will be added. Please enclose the total number of guest when booking..... Mango Cottage is an original beach house located at the private and quiet end of Puako Beach Dr. The ...
1 bedroom
1 bathroom
Sleeps 3
HI> Puako
Very important information : https://www.usatoday.com/story/travel/2020/07/01/hawaii-quarantine-do-you-need-coronavirus-test-before-you-go/5355303002/
Please read the above before booking.
Aloha!!
*****
We are always working to enhance our ...
2 bedroom
2 bathroom
Sleeps 6
HI> Puako
Kolea 15G -- Enhanced Cleaning -- Contactless Check In & Out -- Fast WiFi
ALOHA! Book with peace of mind: COVID-19 related cancellation policy: in case of pre-travel testing program cancellation, resulting in mandatory and non-avoidable ...
2 bedroom
2 bathroom
Sleeps 4
HI> Puako
This home, along with the considerable benefits offered thru Mauna Lani resort and hotel exclusively to owners and their guests, make this a very special opportunity for your holiday visit.
The Mauna Lani Golf Villas are one of the most sought ...
3 bedroom
3 bathroom
Sleeps 6
HI> Puako
We would like you to enjoy our third and top floor view Condo with elevator as much as we do. The wrap around Lanai is 412 sq ft of the total area! We are on the Kona Coast 20 minutes drive north of the Kona airport. Only a 10-15 minute ...
1 bedroom
1 bathroom
Sleeps 2
HI> Puako
