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 (`headline` like '%Sealodge%') AND sleeps>=1 order by rating desc LIMIT 0, 50
This premium Sealodge vacation rental has one of the most spectacular views in all of Princeville, Kauai! It is perched on an oceanfront cliff with expansive views of the Pacific Ocean, Kauai north shore coastline, Anini reef and Kilauea ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Princeville
This idyllic getaway has the absolute best view of any other condo in the complex! It's a perfect place for family vacations, weddings and honeymoons. Spend your evenings lounging on the Lanai, watching the sunset along the Anini reef or relax ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
This Sealodge unit is located on the beautiful ocean cliffs in Princeville. You'll enjoy viewing the ocean from your living room, bedroom, and lanai. Just a short hike will take you down to one of Hawaii's most beautiful and secluded beaches. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Princeville
Enjoy tranquil ocean views and soothing trade winds from this lovely Princeville vacation rental condo at Sealodge II. Tastefully furnished in a bright and beachy style, this top-floor unit offers the peace of no upstairs neighbors, along with ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
On a green ocean bluff in the middle of the blue Pacific Ocean, on the north shore of Hawaii's northernmost island -- "The Garden Isle" -- is where you'll find Sea Lodge -- a small condominium complex perfect for your next vacation ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Ocean Front with breathtaking views from every room. Our one bedroom, one bath, well equipped condo sits on the bluff of Kauai's beautiful North Shore.
Completely remodeled from floor to ceiling, including a new walk-in shower, new ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Princeville
This charming corner unit at Sealodge sits atop the cliffs on the North Shore of Kauai. A gangway leads you from the parking lot level to the unit's 2nd floor which overlooks a protected canyon greenbelt. Relax with a Mai Tai on the lanai above ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Princeville
Welcome to Sealodge J8 by Gather. This cozy oceanfront abode is perched atop the cliffs overlooking the beautiful Pacific Ocean. With stunning panoramic ocean views, this is the perfect home for a relaxing getaway. Watch the colors of the ...
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Princeville
Welcome to your dream retreat on the stunning island of Kauai! This luxurious 1-bedroom oceanfront home offers an unrivaled escape, surrounded by breathtaking natural beauty and mesmerizing views.
As you step inside, you'll be immediately ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Princeville
Sealodge is one of north shores most secluded bluffs. One of the only units w/ private entrance you'll walk into views from every room as sounds of the ocean fill each room. Completely remodeled corner unit, this 2 bed 2 bath open concept ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
