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 '%Oceanfront%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Dream your most desired vacation. Then come to magical Maui, one of the world’s most elite vacation destinations. How better to experience our island than by renting from an owner who has combined her own personal getaway with the conveniences...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Maalaea
Gorgeous, completely upgraded oceanfront/oceanview 2 bedroom, 2 bath. This condo is exquisite and has been meticulously renovated for the discriminating traveler. Huge, unobstructed ocean and island views from the living room, lanai and master ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
SPECTACULAR VIEWS await you at Kahoa House for your Hawaiian holiday. Early-birds and mainlanders adjusting to the time difference can enjoy a cup of Ka’u coffee in the comfortable living room, and watch the sun rise out of the Pacific ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Hilo
Make your trip to Maui more memorable with a stay at our 5 star condo at Luana Kai. Luana Kai is a beautiful resort with immaculate grounds in a park like setting, ocean front in sunny Kihei with no busy roads to cross to get to the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Enjoy breathtaking sunset views every night from this beautifully updated condo steps from Downtown Kailua-Kona village with AC in both the living room and bedroom.
Sunset in Paradise is your private paradise with gorgeous ocean views ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Our comfortable, fourth-floor, south shore condo is right ON the ocean so you will SEE --and HEAR-- the sound of the surf as soon as you open your front door. The condo, with stylish, contemporary furniture, boasts a comfortable king-size bed. ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> poipu
In Hawaii's lush tropics, discover your dream home at this stunning 2-bedroom retreat. Nestled just an 11-minute walk from the picturesque Maalaea Harbor, you'll be perfectly positioned to explore the stunning West Maui Mountains and the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
Simply put, this property sits on one of the most gorgeous ocean-front locations in East Hawaii. Unlike other ocean-front homes, you will not need to walk to the end of a yard to appreciate the Pacific waves. Here, you will feel as if you are on...
- 2 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Hilo
*** For more details and more pictures, go to our website at BIGWAVEHAWAII•COM***
The Big Wave Penthouse is arguably in the best location on the Big Island. Consider this:
• Facing west, you're guaranteed a front-row seat every...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Maui is outdoor living at its finest! Enjoy having breakfast lunch and dinner on your lanai staring at the ocean! Wake each morning to birds singing, swaying palm trees and the teal blue waters of the ocean.. there is no escaping the absolute ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Lahaina
Hale Aloha… On The Sand TVNC-4185
Anaik Martin, is the property manager, lives nearby and is available 24 hours a day, 7 days a week, (808) 652-3410. Professionally cleaned and disinfected by a licensed ...
- 6 bedroom
- 7 bathroom
- Sleeps 12
Hawaii> Anahola
Was recently completely remodeled.
Our oceanfront condo is 23ft. from the ocean and is located on the 2nd floor and offers spectacular sunsets.
Watch the surfers and the dolphins from your lanai.
A Short walk to town and over 20 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Aloha! E Komo Mai (Welcome) to our beautiful luxury Beach Villa in Ko Olina on the island of Oahu and conveniently located just 25 minutes from Honolulu! We would love to have you come stay with us!!
Our fully equipped luxury 15th-floor ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapolei
PRIME SEASON DISCOUNT DUE TO CANCELLATION: Dec 12 - 20, 2024.
25% discont. Only $675 per night.
Please be sure to read the reviews. Prior guests truly tell the story.
This beautiful multi-million dollar property is directly ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
This upscale resort offers incredible panoramic ocean views. Set on a point with coastline vistas, including Bali Hai, Pu'u Po'a features commanding 180 degree plus views of the Pacific Ocean. The spacious lanais are perfect for relaxing, whale ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
****Please note this property may not be rented for less than 30 consecutive days. Rental prices will not be reduced or adjusted based on the number of days the rental is actually used or occupied. Please inquire regarding availability for stays...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Haleiwa
Enjoy stunning ocean views from all the bedrooms and living area of the condo. Greet every morning on your bed enjoying the views and sounds from the ocean!
*** OCEANFRONT overlooking greenbelt, quiet and private, no noise from pool or ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
This nicely remodeled 3rd-floor condo offers breathtaking views of Maalaea Bay, the grand volcano Haleakala, and the sparkling Kihei lights at night from the private lanai! The primary suite features a comfy king bed and ensuite bathroom with a ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
Experience breathtaking ocean vistas on your next vacation with our deluxe Cliffs Resort Condo.
Furnishings and amenities include:
- Two queen beds in loft
- Portable Honeywell air conditioner in master bedroom
- Two ceiling ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Princeville
*Available for booking! Thankfully, our condo is not located in the wildfire affected area*
Enjoy panoramic views in this renovated oceanfront corner unit condo located on Kahana Beach at Valley Isle Resort complex! This unique corner ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Enjoy Kauai's simple pleasures and grand landscapes right from home! From the moment you arrive, you immediately sense the Aloha spirit and the other-worldliness of Kauai's North Shore. Hanalei Bay Villas' surroundings are stunning and beyond ...
- 3 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Princeville
Kona Bay Estates is a gated and patrolled oceanfront community. The Sunset Beach House offers 2700 square feet of luxurious space. It is just 500 meters from the sea. Opening walls of glass offer indoor-outdoor living with spectacular views of ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kailua-Kona
Located on a picturesque lava bluff, our top floor condo offers privacy, cooling trade winds and panoramic ocean vistas which a camera can't begin to capture.
Crashing waves, migrating whales and sunrise/sunset views are what you'll see as...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
This unit is remodeled and ready for you to call home for your stay! Walk down the stairs and onto the longest walking beach in Maui! Heading South on the beach, you are a 5 minute walk to delicious Sugar Beach Bakery and Ululani Shave Ice. Head...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Stylish Oceanfront 2nd Floor 2 Bedroom | 2 Bathroom
Lanikai is centrally located on the Island yet 'tucked away'. This elegant second floor unit has sweeping Ocean views. This luxurious condo comes with a fully-equipped kitchen area ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kapaa
Words cannot describe the incredible view from this penthouse condo. It's mesmerizing!! You'll hear the waves crash below you along the coastline and marvel at the Pacific Ocean stretching out as far as you can see. Turtles, spinner dolphins, ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> koloa
Enjoy the perpetually calm span of crystalline ocean and the gentle trade winds as you relax on the oceanfront lanai or sun deck of this beautiful home tucked onto Oahu's south shore. Launch a kayak or stand up paddle board from the private ...
- 4 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Honolulu
Welcome to Pacific Sunset Villa, home of expansive ocean views and ultimate luxury. This award winning home with 4 bedroom with ensuite bathrooms and 3 half bathrooms sits on an oceanfront setting and offers easy access to all the amenities at ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Waikoloa
This luxury condo is as close to the beautiful blue waters of the Pacific Ocean as you can get at Kanaloa at Kona. Unit 1804 is a top-floor (second floor) oceanfront condo with over 1700 square feet of living space with all the amenities of ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Experience a relaxing Hawaiian vacation in one of Maui's most serene oceanfront settings. Despite the devastating fire in Lahaina, Puamana remains a haven of tranquility and beauty, where the spirit of "Old Hawaii" lives on. Unit 9-1 is located ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Lahaina
This oceanfront, single-level townhome is located just 25 feet from the water, offering a front-row seat to stunning sunsets and surfers from the comfort of your lanai. While the Lahaina fire has impacted some parts of the community, this ...
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
3 Bedroom Oceanfront Luxury at The Hyatt Residence Club – Ka'anapali's Newest, Most Exclusive Resort.
Perched upon Ka'anapali, one of the world's most beautiful beaches is the Hyatt Residence Club Ka'anapali. Within the lush borders of ...
- 4 bedroom
- 3 bathroom
- Sleeps 11
Hawaii> Lahaina
Nov 29-Dec 5 - Rock bottom at $259/nt - How can you NOT?!
Jan 4-11 or Jan 18-25 at just $549 for OCEANFRONT CONDO! Our last winter opening until Mar 18-24 - BOOK NOW!
Are you craving PANORAMIC views from your condo & private balcony? ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Luxury Oceanfront Studio Villa at The Westin Ka'anapali Ocean Resort Villas North.
This oceanfront luxury studio villa features outstanding oceanfront views of the Pacific Ocean and sleeps up to 4 with one king sized bed and one queen ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Freshly Renovated 1 Bedroom Oceanfront Suite at Marriott's Maui Ocean Club.
When you can’t afford a mediocre Maui vacation!
Family time is limited, make the most of your vacation time with an out of this world Ocean front ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Hawaii STVR Registration Number: STVR-19-355146 ****************************** OUR CONDO HAS BEEN COMPLETELY RENOVATED !! NEW DUAL AIR CONDITIONING SYSTEMS - - KEEPS CONDO COOLER THAN MOST OTHER UNITS IN RESORT
PREMIUM OCEAN VIEWS ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
. . . . . . . . . . . . . . . . . . WELCOME TO SUNSET CENTRAL! . . . . . . . . . . . . . . . . . .
At Sunset Central YOU ARE SO CLOSE YOU CAN FEEL THE OCEAN! As soon as you walk into SUNSET CENTRAL you take a deep breath and start to ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
*Inquiry Only*
Indulge yourself in the ultimate island style luxury at the Big Island of Hawaii’s most exclusive retreat, Kauhale Pilialoha. Experience breathtaking sunsets, enjoy the natural beauty of Hawaii from the ultimate poolside ...
- 10 bedroom
- 10 bathroom
- Sleeps 30
Hawaii> Kamuela
Kana'i A Nalu 412 by Knowles Maui
• Recently Remodeled condo
• Top floor corner unit with ocean and outer island views
• Freshwater oceanfront swimming pool
• Next to Sugar Beach – the longest beach in Maui, great for ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Wailuku
2 Bedroom Oceanfront Luxury Villa at The Westin Ka'anapali Ocean Resort North!
This two bedroom, two bathroom luxury villa features over 1200 square foot of interior space and incredible ocean front views of the tranquil blue Pacific ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
Highly cleaned and sanitized for your vacation. Ask about our AlohaSafeStay program.
If you have a stay that qualifies for housekeeping services, traditional housekeeping service will be performed every 7th day. This includes refreshing...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei, Maui
Welcome to Kihei Beach, a charming six-story beachfront property nestled in North Kihei equipped with an elevator. Enjoy breathtaking views of Kaho'olawe, Molokini, and the sparkling Pacific Ocean in a relaxed, friendly setting. Right outside ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
J202 is an amazingly well-located 2-bedroom, 2.5 bathroom condo only steps away from the ocean. With a great location on the property, families will enjoy being close to the Mauna Lani's sandy beaches, as well as a private pool/BBQ area. The ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
C302 is a top floor oceanfront condo that is currently available at a reduced rate! The 2 bedroom, 2-bath top-floor villa features vaulted ceilings, gorgeous ocean views and a large covered lanai for sunset dinners. The living room features new ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
Beautifully upgraded oceanfront condo at the Mauna Lani Resort, only steps away from the beach! B301 is a newly updated 2 bedroom / 2.5 bath condo located on the top floor of the B building - offering gorgeous ocean and sunset views. This ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
Beautiful oceanfront condo only steps away from the beach! Superbly located at the exclusive Mauna Lani Terrace, this newly decorated 2 Bedroom / 2.5 Bath upgraded condo has a large outdoor lanai that is perfect for sunset dinners, or relaxing ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
Highly cleaned and sanitized for your vacation. Ask about our AlohaSafeStay program.
If you have a stay that qualifies for housekeeping services, traditional housekeeping service will be performed every 7th day. This includes refreshing...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei, Maui
Come to the charming town of Hanalei and experience the Jones House, a spacious oceanfront home on spectacular Hanalei Bay. Steps away from the beach with breathtaking views of Hanalei Bay, the Jones House is sure to delight. Swim, surf, ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Hanalei
Oceanfront! Live the Dream! Ultra Luxury "Beachfront" Ground Floor Unit A104 at Waipouli Beach Resort & Spa offering stunning full oceanfront views from every room, with watercolor sunrises, whale watching and romantic dinners on the lanai with...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Gorgeous Oceanfront Waipouli Beach Resort G302, one of Waipouli Beach Resort's most desirable Ocean Front suites, offers spectacular 180 degree unobstructed ocean and sandy beach views. You can see the ocean from every room! Fall asleep to the ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
