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 and affordable 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 ...
- 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
From your private covered ocean-facing lanai, enjoy sunsets, sea breeze, whale watching, and among the best ocean views on Kauai thanks to the contrasting colors of Anini reef. This cozy 1-bedroom, 1-bathroom sits atop an ocean bluff, ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Princeville
Property is currently for sale. Real estate showings are given a 24-48 hour notice. The property manager will contact you to make arrangements to schedule showings at a convenient time.
Enjoy tranquil ocean views and soothing trade winds...
- 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
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
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
This Kauai home- away- from- home will appeal to your all of your senses. This nicely appointed unit has been tastefully re-done. Gaze out at the ocean framed by palm trees from the living room, bedroom, and lanai of this comfortable condo which...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Princeville
One of the BEST VIEWS in Sealodge and located very near the bluff, you will be surrounded by ocean views at Sealodge G7. With a large additional window in the living room, you can relax on the living room punae (the lounging bed in the living ...
- 2 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
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
Summary:
Comfort, space, and style are yours to enjoy when you book Sealodge G7 for your Kauai adventure. This open concept condo is located on the second floor and provides guests with impeccable views through large windows that promote ...
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Princeville
Summary:
We welcome you to this top floor condo which captures the beauty of its surroundings and offers stunning vistas highlighting the Kilauea lighthouse as well as the Anini reef through large windows throughout. The open concept ...
- 2 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Princeville
