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 (`headline` like '%Na Hale O Keauhou%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Na Hale B-3 is a spacious 3-bedroom, 2-1/2 bath townhome in one of Kona's premier resort developments, Na Hale O Keauhou. Building B has a great location in the complex for great ocean & mountain views, and stunning Kona sunsets. This ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua Kona
A beautifully landscaped, quiet, and one of the newest properties in Kona. The townhome is in an upscale gated community. It is a 3 bedroom and 2.5 bath townhouse with a garage. The condo has 1700 sq. ft. not including 2 large lanais. It is ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua-Kona
Best ocean view townhome in rhe resort:
Sunset Views Year Round
180 Degree Ocean Views
Walk to Kahalu'u Beach Park- the best snorkling beach in Kona
Adjacent to Restaurants, Cinema, Grocery Store
• Beautiful Ocean-View Gated Complex• Large...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua Kona
Enjoy Panoramic Ocean Views from your private lanais. Sunset views 365 days a year. Your only decision, whether to use the upstairs lanai with chaise lounge and cosy sitting area perfect for morning coffee or wine and cheese at sunset, or the ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua Kona
Enjoy expansive ocean and golf course views from both upper and lower lanais and famous Kona sunsets right in front the upper lanai. Designer furnishings, Pacific Rim antiques and tropical décor. Original local artist works. The fully equipped ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
Best location at Na Hale O Keauhou and added to the website on December 18, 2014. • Beautiful Ocean-View Gated Complex• Large & Luxurious 2 & 3-Bedroom Townhomes• Resort Pool, Spa, Clubhouse & Fitness Facilty• Pavilion with Kitchen & ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua Kona
This beautiful two-bedroom townhouse offers spectacular ocean, golf course, coastline, and pool views from the living room, both Lanais, and the master bedroom. A delightful two-bedroom, 2.5 bathroom unit offering large spacious bedrooms, a ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
HI> Kailua Kona
Na Hale O Keauhou K-1 is a spacious 1704 square foot 2-story luxury air-conditioned Townhome with Ocean Views. This delightful, well-appointed townhome is located on the western edge of the Private and Gated Na Hale O Keauhou Resort. Newly ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua Kona
Panoramic, 180 degree Ocean Views from Both Levels of the Townhome
Spacious 3 Bedroom, 3 Bath Townhome
Covered Lanais
Fully Equipped Gourmet Kitchen
Beautifully Furnished by Hawaii's Top Interior Designer
600 Count Linens
Well Appointed ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua
H-5 is a spacious 2-bedroom, 2-1/2 bath, 2-level townhome in one of Kona's premier resort developments, Na Hale O Keauhou. Building H is a fantastic location in the complex for great ocean & mountain views, and stunning Kona sunsets. This ...
- 2 bedroom
- 3 bathroom
- Sleeps 5
HI> Kailua Kona
