select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%Sealodge%') AND sleeps>=1 order by rating desc LIMIT 0, 15
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
HI> 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 amazing...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> 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 amazing...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> 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
HI> Princeville
Enhanced COVID cleaning and disinfecting procedures for every check-in.
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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Princeville
Enhanced COVID cleaning and disinfecting procedures for every check-in.
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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> 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 picture ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Princeville
Enhanced COVID cleaning and disinfecting procedures for every check-in.
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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> 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 amazing...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> 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 more ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Princeville
Enhanced COVID cleaning and disinfecting procedures for every check-in.
Sealodge D1: With a private oceanfront lanai and spacious expanse of lawn in front, you'll enjoy sitting outside admiring the view and the passing chickens and nene in ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> 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
HI> Princeville
Sealodge H4 is a beautifully remodeled ground floor condo. The living room, lanai, and bedroom all boast spectacular oceanfront views. The kitchen has been remodeled and includes granite counters, bright and open white cabinetry, and modern ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> 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 - twin bed ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> 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 the ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Princeville