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 = 'big island hawaii' OR postalCode = 'big island hawaii' OR region like '%|big island hawaii|%') order by rating desc LIMIT 0, 15
Hale Iki (ha-lay ee-kee) is a centrally located private home (with A/C) at the 650' elevation, making for sunny days and cool nights. We're ...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Kailua-Kona
This lovely, spacious (300 sq. ft.) studio with large covered lanai and tropical garden view is one of five studios at Bears' Place Guest House ...
-
1
bedroom
-
1
bathroom
-
Sleeps 3
HI>
Kailua Kona
Set on a breathtaking cliff along the Hamakua Coast on Hawaii’s Big Island, this former macadamia nut plantation has been transformed into a ...
-
6
bedroom
-
7
bathroom
-
Sleeps 18
HI>
Nīnole
Welcome to WhaleSpirit Sanctuary, a sacred vacation and rejuvenation destination. This eco-friendly sanctuary is located within minutes of some ...
-
1
bedroom
-
1
bathroom
-
Sleeps 2
HI>
Captain Cook
SPACIOUS 1200sq. ft. 1bedroom/1bath Villa is beautiful, fully furnished and in a QUIET location in the Resort. This resort has everything you ...
-
1
bedroom
-
1
bathroom
-
Sleeps 6
HI>
Kona
Enjoy being literally on the ocean in this beautiful remodeled top floor corner condo unit . Superb coastline and 180 degree ocean views. ...
-
2
bedroom
-
2
bathroom
-
Sleeps 4
HI>
Kailua-Kona
This unique 3 bedroom, 3 bath, two-story new luxury home is located on a secluded, end-of-the-road 5 acre property. There are 3050 sq. ft. under...
-
3
bedroom
-
3
bathroom
-
Sleeps 6
HI>
Honomu
We can help you with KAYAKS! As a means to provide protection to the marine wildlife, only permitted Kayaking is allowed in Kealakekua Bay. As ...
-
3
bedroom
-
3
bathroom
-
Sleeps 10
HI>
Captain Cook
Our villa provides 2 Bedrooms and 2.5 Baths, totalling over 1400 square feet plus 2 Lanais.
It is perfect for families or couples vacationing ...
-
2
bedroom
-
3
bathroom
-
Sleeps 5
HI>
Waikoloa Village
This spacious, well appointed apartment is located in Kailua-Kona, Big Island, on one acre of land, on a private road, and overlooks 15 acres of ...
-
1
bedroom
-
1
bathroom
-
Sleeps 2
HI>
Kailua-Kona
This studio cottage sits on 5 acres of orchard, gulch and gardens with the Kohala coast as a backdrop. The owners farm the back 20 acres with ...
-
1
bedroom
-
1
bathroom
-
Sleeps 2
HI>
Kapaau
For the tropical getaway of a lifetime, come stay at this phenomenal Kailua-Kona vacation rental apartment, ideally situated in a quiet ...
-
1
bedroom
-
1
bathroom
-
Sleeps 2
HI>
Kailua Kona
Welcome to KaMilo 111, where your Big Island Vacation truly begins! Enter the door to this beautifully furnished home and you will immediately ...
-
3
bedroom
-
3
bathroom
-
Sleeps 6
HI>
Kamuela
Picture yourself in paradise, a spacious elevator-accessible top floor 1 bedroom 2 bath condo with expansive living/dining area. Step out onto a ...
-
1
bedroom
-
2
bathroom
-
Sleeps 3
HI>
Kailua-Kona
Our condo is located in Waikoloa Beach Resort area not Waikoloa Village.
Our beautiful two-bedroom, two bath penthouse condominium is in a ...
-
2
bedroom
-
2
bathroom
-
Sleeps 5
HI>
Waikoloa Beach Resort
