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 (city = '96777' OR postalCode = '96777' OR region like '%|96777|%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Aloha! This is truly a special and unique part of the world. Condo #17, Hale Maluhia (House of Peace), sits OCEANFRONT on the Sea Mountain Resort at Punalu'u Black Sand Beach. It is a spacious separate one-bedroom unit, built in the 'Kama'aina' ...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
Amazing Ocean and Mountain Views from this beautifully renovated condo at the Colony One ~ Sea Mountain Resort in the Ka’u region at the Punalu`u famous black sand beach.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
What you will love about our vacation home
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
Aloha and welcome back to the trip to Hawaii you have been dreaming about! If you are looking to really get away from it all, visit one of the least populated spots in the part of Hawaii that is most like "old Hawaii". The Ka'u district is the ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Pahala
Ideally located on the unspoiled southeastern coastline of Hawaii's Big Island, the Turtle Paradise is a spacious Polynesian-style condo for your dream vacation in Hawaii.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
This is a true one-bedroom unit, not a small studio unit that is (often) described as a one-bedroom unit.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
Dramatic ocean and lava rock shoreline views, walk to black sand beach
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
Turtle Hut Description for Condo
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
Aloha! You have found 'Hale Sweet Hale' an OCEANFRONT Hawaiian-style bungalow nestled on the Southeast coast of the 'Big Island' of Hawaii. This full one bedroom condo has been completely remodeled. The entry level bedroom has a king bed with ...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
Colony One at Sea Mountain resort is one of the most serene resorts on the Big Island. Perfect for those who want to get the true Hawaiian experience. Colony One at Sea Mountain Condos are located on the South end of the Big Island only 60 ...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
Colony One at Sea Mountain resort is one of the most serene resorts on the Big Island. Perfect for those who want to get the true Hawaiian experience. Colony One at Sea Mountain Condos are located on the South end of the Big Island only 60 ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Pahala
Colony One at Sea Mountain resort is one of the most serene resorts on the Big Island. Perfect for those who want to get the true Hawaiian experience. Colony One at Sea Mountain Condos are located on the South end of the Big Island only 60 ...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
This is a 2 bedroom unit with the BEST location in the entire resort. This condo has ocean views from both the lower level and the upper bedroom level. Upstairs features a private balcony, where the downstairs offers sliders out to a covered...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Pahala
Aloha! This is truly a special and unique part of the world.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
Colony One at Sea Mountain resort is one of the most serene resorts on the Big Island. Perfect for those who want to get the true Hawaiian experience. Colony One at Sea Mountain Condos are located on the South end of the Big Island only 60 ...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
This studio unit is on the ocean side of the complex and features beautiful ocean views. King Bed in sleeping area as well as a pullout sleeper sofa....allowing for an occupancy of up to 4 guests.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
#50, ‘Olu ‘Olu Condo with full kitchenette, marble shower walk-in shower, private lanai, short walk to Punalu’u Black Sand Beach
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
The Hawaiian Retreat “Where the Aloha Spirit Lives” * Connection around complex can be a challenge, but our condo has new high speed internet in the unit *Conveniently located halfway between KONA and HILO for full service tourist ...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Pahala
