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 = 'poipu' OR postalCode = 'poipu' OR region like '%|poipu|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Beauty, comfort, quality, and value are what best describes this spacious one bedroom condominium in sunny Poipu! This is a real gem with stunning ocean and mountain views from the lanai that backs up onto Prince Kuhio Park, a lush, quiet open ...
1 bedroom
1 bathroom
Sleeps 2
HI> Poipu
POIPU-- Kauai-- Vacation Home Rental-- Ho'ona Road-- Baby Beach Bungalow 2 is steps away from a crescent-shaped white sand beach called Baby Beach and is located in a family-friendly neighborhood on Ho'ona Road.
Across a quiet lane and ...
2 bedroom
2 bathroom
Sleeps 4
HI> Poipu
This exquisite Villa has it all! Located in The Villas at Poipu Kai boutique development. With 1900 s.f. living area, this three bedroom three bath view unit features an expansive covered lanai, private elevator access, central air, central ...
3 bedroom
3 bathroom
Sleeps 6
HI> Poipu
Highlight your day by watching the beautiful sunrise out of the Pacific while you are still in bed. Better than that, your vacation day could start with drinking Hawaii coffee, listen to Hawaiian music, enjoy the sunrise at the exact rising ...
4 bedroom
6 bathroom
Sleeps 10
HI> Poipu
The LoveRentals portfolio is comprised of curated, professionally managed vacation rental homes around the world. We only select professional property managers that meet our high standards. Book with peace of mind knowing that you will have the ...
4 bedroom
6 bathroom
Sleeps 10
HI> Poipu
Come enjoy our fully renovated condo - we've meticulously renovated the kitchen, bedroom, bathroom, and the tile floors! And, we have AIR CONDITIONING! We are one of a few units in Kuhio Shores (and in Poipu) that has installed A/C! This makes ...
1 bedroom
1 bathroom
Sleeps 2
HI> Poipu
Step back in time to old Hawaii. Yet, updated for your comfort. Located on a quiet street just a short block to Brennecke’s Beach and the adjacent Poipu Beach Park.
This spacious, comfortable home has a large living area for all to gather. ...
3 bedroom
3 bathroom
Sleeps 6
HI> Poipu
1 Bedroom amazing ocean view very close to the beach.
Location is everything! Romantic, epic views in this beautifully furnished top floor (3rd floor easy walk) 1 bedroom condo. Located in building 18 at the Kiahuna Plantation Resort, ...
1 bedroom
1 bathroom
Sleeps 4
HI> Poipu
Kuhio Shores 104 is on the ground floor of this amazing ocean front property. It is very special, perfect for Honeymoon, anniversaries, birthday celebrations, and is also a place where you can completely relax! We are also close to all of the ...
1 bedroom
1 bathroom
Sleeps 2
HI> Poipu
Sansei Hale... TVNCU #5031
Sansei Hale (Third Generation House) is a luxury Hawaiiana vacation home. It overlooks the beautiful and private Waikomo Stream as it meets the ocean in secluded Turtle Cove, Poipu. Sansei Hale has three stream- and ...
3 bedroom
3 bathroom
Sleeps 6
HI> Poipu
EFFECTIVE 7/8/21: Vaccinated travelers can avoid any quarantine period. Non-vaccinated travelers arriving in Hawaii with proof of a negative test result from a valid NAAT COVID-19 testing partner no more than 72-hours prior to arrival can avoid...
3 bedroom
4 bathroom
Sleeps 6
HI> Poipu
SEE and HEAR the BREAKING WAVES on sandy Brennecke's Beach from both floors and both decks. This is a SANDY BEACH FRONT location, set back 300 feet across a well-tended lawn.
Manualoha 106 is a beautiful two-level condominium home that offers ...
2 bedroom
3 bathroom
Sleeps 4
HI> Poipu
H.T.I.D.#GE/TA-019-916-8000-01
BRAND NEW ONE BEDROOM ULTRA DELUXE SUITE.....KUHIO SHORES #113..... Kuhio Shores #113 is located right in the middle of the building on the Ocean Side of Kuhio Shores.
THIS IS ONLY CONDO AT KUHIO SHORES WHERE ...
1 bedroom
1 bathroom
Sleeps 4
HI> Poipu
May we welcome you to Alihi Lani at Poipu. Our beautiful, condominiums located in the heart of Kauai's sunny south shore! Alihi Lani. or 'Beautiful Horizon Views', aptly describes the magnificent ocean panorama of Hawaiian sunsets, majestic ...
3 bedroom
2 bathroom
Sleeps 6
HI> Poipu
Our comfortable, fourth-floor, south shore condo is right ON the ocean so you will SEE --and HEAR-- the sound of the surf as soon as you open your front door. The condo, with stylish, contemporary furniture, boasts a comfortable king-size bed. ...
1 bedroom
1 bathroom
Sleeps 2
HI> poipu
