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, 20
Overlooking the beautiful North Shore Kauai coastline, this popular Kauai resort features one of the best ocean views. From your Princeville condo, it is only a short walk to a secluded Kauai beach.
Sealodge at Princeville Resort offers ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Princeville
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
Property is currently for sale. The property manager will contact you to make arrangements to schedule showings at a convenient time.
Enjoy tranquil ocean views and soothing trade winds from this lovely Princeville vacation rental condo ...
- 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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
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
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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Princeville
Overlooking the beautiful North Shore Kauai coastline, this popular Kauai resort features one of the best ocean views. From your Princeville condo, it is only a short walk to a secluded Kauai beach.
Sealodge at Princeville Resort offers ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
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
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. You’ll enjoy gorgeous ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Princeville
Overlooking the beautiful North Shore Kauai coastline, this popular Kauai resort features one of the best ocean views. From your Princeville condo, it is only a short walk to a secluded Kauai beach.
Sealodge at Princeville Resort offers ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
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
Overlooking the beautiful North Shore Kauai coastline, this popular Kauai resort features one of the best ocean views. From your Princeville condo, it is only a short walk to a secluded Kauai beach.
Sealodge at Princeville Resort offers ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
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 ...
- 0 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
