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, 30
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
Enjoy tranquil ocean views and soothing trade winds from this lovely Princeville vacation rental condo at Sealodge II. Newly remodeled and beautifully furnished in a bright and beachy style, this top-floor unit offers the peace of no upstairs ...
- 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
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
Very private top floor ocean front location. Sealodge E10 is the perfect choice for a honeymoon. All of the north shore's great beaches and attractions are conveniently close, and there's a beach path onsite, but you'll be perfectly happy to ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Princeville
Enhanced COVID cleaning and disinfecting procedures for every check-in.
Sealodge A6 is a corner unit that offers guests breathtaking, unobstructed second floor oceanfront views from the living areas, lanai and bedroom. Wake up in a ...
- 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
Sealodge G4 is the perfect Sealodge Kauai condo for honeymoons, anniversaries, or other romantic occasions. With its expansive views of the sea, waves breaking, and lush green coastline visible from the large bedroom picture window, you won't ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Princeville
Enhanced COVID cleaning and disinfecting procedures for every check-in.
Sealodge G8: This top floor 2br/2ba condo at Sealodge Kauai offers incredible oceanfront views from every the EXTRA window added in the living room to provide even ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Private, top floor apartment with OCEAN FRONT views at Sealodge Kauai! Sealodge F9 is the only top floor condo in the building! Perfect for your honeymoon or other romantic getaway, it offers privacy, panoramic and unobstructed oceanfront ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Princeville
Enhanced COVID cleaning and disinfecting procedures for every check-in.
Sealodge J9 offers dramatic and uninterrupted oceanfront views, top floor privacy, and your own furnished oceanfront lanai. Living room and both bedrooms have big ...
- 2 bedroom
- 1 bathroom
- Sleeps 5
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
Our Kauai vacation rentals at Sealodge have tons of personality, charm, and ABSOLUTELY AMAZING oceanfront views. Sealodge E6 gives you direct oceanfront views all the way down the coast to the Kilauea lighthouse. It's the best view in all of ...
- 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Princeville
Enhanced COVID cleaning and disinfecting procedures for every check-in.
Sealodge J1: This Sealodge Kauai condo is private, quiet and serene with oceanfront views all the way down the Anini Reef to the Kilauea lighthouse. On the edge of ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Princeville
Enhanced COVID cleaning and disinfecting procedures for every check-in.
Sealodge E8: This affordable one bedroom Sealodge Kauai condo offers an amazing oceanfront view. Relax on the punee (that’s a window seat in the living room - ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Princeville
2024 Traveler Award winning rental, Sealodge D7 is beautifully remodeled with Incredible oceanfront views the length of the Anini reef. From the living room you can watch the fishermen on Anini, spot humpback whales, watch exotic tropical birds....
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Sealodge E7 - cozy retreat for two with the most amazing view all the way down the coast to the lighthouse at Kilauea. This oceanfront retreat in the quiet Sealodge Princeville complex lets you see and hear the ocean, plus free wifi if it's a ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Princeville
Enhanced COVID cleaning and disinfecting procedures for every check-in.
Sealodge C7 enjoys top floor privacy with endless oceanfront views at Sealodge Kauai. Inside you'll find charming vibrant decor and many updates and upgrades. You ...
- 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
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
