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 '%studio%') AND sleeps>=1 order by rating desc LIMIT 0, 50
We are located on the west side of the Big Island of Hawaii, directly above Kailua-Kona, on the side of Mt Hualalai, in Holualoa, with an awesome ocean and coastline view! Please read our reviews because the view from the cottage lanai is better...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Holualoa
Aloha and welcome to our tropical paradise in beautiful Kauai, Hawaii!
Indulge in an extraordinary couples' getaway at our top floor ocean and beachfront studio condo in Kapa'a. Just 150 steps from our condo's front door, the beach ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kapaa
Aloha and welcome to our tropical paradise in beautiful Kauai, Hawaii!
Indulge in an extraordinary couples' getaway at our top-floor ocean view studio condo in Kapa'a. Just 160 steps from our condo's front door, the beach awaits! ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kapaa
This second floor garden view studio at Aina Nalu Resort provides an excellent base for your vacation. The studio has a queen bed, dining area, efficiency kitchen, vaulted ceilings and a bathroom. The kitchen features: conduction cooktop (one ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Unit 162.
Update: Fall of 2022 we installed a Grohe hand-held shower head so that parents could more easily wash their children. In the summer of 2021 we added new artwork. In 2019 we installed a new 55” Samsung LED LCD Smart TV! It’s ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Peaceful, well appointed room with private entrance ,down a flight of stairs, overlooking our back yard garden of fruit trees and red ginger blooms. Private bath with shower, queen bed, walk in closet, kitchenette, outdoor seating; Wake up and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
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
Aloha! Welcome to Unit #764 at the beautiful and world-famous Ka'anapali Beach and the Whaler! Ka'anapali Beach is a destination featured on the Travel Channel and rated as one of the best beaches in the USA. The Whaler is a popular luxurious ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Peace and tranquility await your arrival at Konea 615. One of the absolute best studios on offer at Honua Kai, this luxurious gem provides full ocean views from its 6th-floor vantage point above the palm canopy as well as shelter from the ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Located at the Honua Kai Resort in the Konea Tower. Our studio has a fabulous 6th floor ocean view on Kaanapali Beach. This spacious 6th floor studio is an elegant place for a total Maui experience. Also, our exterior painting has been ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Lahaina
*** NO RESORT FEES! ***
The Mahana is a quiet resort and Unit 802 is high above the palm trees, for an unobstructed Ocean view overlooking neighboring islands of Molokai and Lanai. Perfect for whale watching or relaxing on the daybed ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
We finished a complete remodel of the kitchen and the living room in December 2021.
Stop!! You found it!! The best studio condo at the Royal Kahana based on the view, condition and price. If you've stayed in other studio's at the Royal ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kahana
We are hosting a beautiful studio with a private entry that is attached to our ocean sunset view home located in Makakilo, Hawaii. Room Six Studio offers the following amenities: Compact private lanai in the back yard, Full access to our ocean ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kapolei
Aloha from Midway Vacations! Our team of local hospitality experts is dedicated to turning your Hawaiian getaway into a reality. When you stay at one of our properties, you can count on a professionally cleaned and meticulously maintained ...
- 2 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
~ Book today and fall in love with "The Hula Hideaway" ~
Voted Best Hawaii AirBnB by Travelers Worldwide in 2023
You will feel like you have gone back in time to the true Hawaiian experience in this condo just 10 FT from the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Location, location, location! Our elegantly appointed studio is a block from Waikiki Beach, and at the center of shopping, restaurants, and nightlife. We offer FREE parking, high speed WiFi, and a well-equipped kitchen with essentials for ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
HONUA KAI RESORT AND SPA SUITE 215 welcomes you with so much ALOHA....
This spacious studio unit with full bath can accommodate up to 4 guests.
Included in our fabulous price are many owner-supplied extras such as:
• Beach...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Mahinani's Farm offers a unique experience for visitors to stay in a peaceful location tucked directly between the mountains and the ocean.
Our Tiny Studio Cabin is surrounded by soft rolling hills, giant royal palms, pines, coconut trees and...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Anahola
Cheerful and bright, this studio is located In a quiet neighborhood overlooking Hilo. The studio occupies the first floor of 2 story home. It has a private entrance and parking on site. A full private bathroom is adjacent to the studio. Guests ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Hilo
The Kona Clark's luxury studio is located in the heart of Kailua Kona in a quiet neighborhood just 1/2 mile up from the famous Honl's Ocean Beach Park on Alii Drive. The studio at the Clark's residence has panoramic ocean views along with views...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
One of the best ocean view, nearest the beach, largest studios at Maui Eldorado; 702 square feet. Second floor privacy, security, and view. Completely upgraded in 2018. Cal king bed. Complete kitchen & every convenience. 42' TV, Blue-Ray...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Island Colony 3105 - 30-Night Minimum
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Welcome to Island Colony, your home away from home in ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Honolulu
Enjoy the relaxation and fun of eating in your luxury villa with GORGEOUS VIEWS, featuring WHALE sightings and magnificent sunsets from your private lanai in this OCEANFRONT VILLA! This reservation is not only during Spring Break, but also ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Ocean view penthouse condo. This studio is located in the Pokai bay Marinas. this condo has a well equipped kitchen and boosts beautiful coast line views. The building is equipped with fenced parking , modern day security entrance system. and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Waianae
After a full day of enjoying all that Maui has to offer, come home to your air conditioned oasis in the heart of the Kaanapali resort. Maui Eldorado J115 has been totally rebuilt to reflect the island lifestyle that you deserve.
A ground ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
* Best Views and the Best Value in Waikiki! This unit is completely and beautifully redone with quality and function, 'Pride of ownership for our Guests to Enjoy'.
* This unit has a 30 day minimum requirement.
* This Spacious ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
Welcome to our tastefully renovated and decorated Kihei Akahi studio D-301, that we affectionately call “Our Pearl”.
BOOK with CONFIDENCE
Kihei Akahi is a Hotel-zoned property and is not subject to...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
WHY STAY ANYWHERE IN HILO BUT CLOSE TO BEACHES AND OCEAN WHERE THE WEATHER IS BEST!!!
Lovely beach studio located a minute walking distance to ocean with small swimming cove and tide pools.
The studio is AC comfortable which ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Hilo
“This is a timeshare. While the amenities and features will be the same, you may not get the exact unit shown in the photos.”
Units are assigned at check-in. Some units categorized as "Ocean view" may have obstructed view to the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapolei
This ocean view condo (with beautiful view of Black Rock and Maui Mountains) has been completely renovated. The kitchen, living area and bathroom have been upgraded with the finest materials. Kitchen has been renovated into a U-shape design with...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
You are sure to enjoy a memorable vacation in this fully furnished, spacious retreat in an ocean view studio premium villa at The Westin Ka'anapali Resort Villas. Maui's boundless beauty and activity begin and end here!
The Studio Premium...
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Unit 321 is in the Konea Tower of Honua Kai.
The Ocean View Suite on the 3rd floor overlooks the Adult ( quiet) pool and steps away from Dukes Restaurant and Bar
You have full access to the resort and your key gives you access to the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Everything you might need to enjoy an exciting or relaxing vacation right by the ocean at the beautiful and world famous, "Kealakekua Bay'.
This private location is perfect for adventuring around our quaint neighborhood, with quiet walks, ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Captain Cook
Large beach front studio (460 sq, ft,) located at the Waikiki Shore, the only condo building in Waikiki that is directly on Waikiki Beach.
This unit is located in a building that is within the designated Resort Zone, and therefore eligible ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Ekahi Ocean Studio is a studio perfectly located in the lower village of Wailea Ekahi (#8C), a lush-landscaped paradise, just steps from the oceanfront pool and to world-renowned Keawakapu beach. This studio is perfect for couples seeking a ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Unit 1064 is the ABSOLUTE nicest studio at The Whaler !!! Unit 1064 has been completely remodeled and professionally decorated just a few years ago...
Unit 1064 has EXPANSIVE & PANORAMIC views of the OCEAN, the island of Molokai, the...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
The studio has a TV with cable, internet connection, air conditioner, a full kitchen with an oven and stove, a refrigerator, microwave, toaster, blender, coffee maker, plates, pans, and other eating utensils. The studio is very clean, very ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Haleiwa
Marriott's Ko Olina Beach Club is located on the western shore of Oahu, far removed from the world yet just a short drive to Honolulu's exciting and vibrant activities and nightlife. Stay at this luxury resort for the price of an ordinary ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapolei
WYNDHAM ROYAL GARDEN RESORT
440 Olohana St.
Honolulu, HI 96815
• THE PERFECT LOCATION FOR WAIKIKI
• GOLD CROWN RESORT
• NEAR FAMOUS SHOPPING
• 10-MINUTE WALK TO THE BEACH
• RELAX ON YOUR PERSONAL LANAI ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Honolulu
Popolana” – Unit 2 – is our one and only oceanfront studio condominium. Just 100′ off the water, this lovely unit has warm tropical decor with bamboo walls and rattan furniture, a queen-size bed, granite counter tops, hardwood floors and...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Hana
Please choose a Friday through Sunday check-in for a seven night stay. Quotes are based on resort/Island views. Please ask you agent if you are requesting an alternate view.
Thank you for your interest in booking with Dash Realty Group,...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapolei
2024 CHRISTMAS WEEKS AVAILABLE
12/21-28 1BR 2 Bath Garden / Mountain View Category
12/22-29 1BR 2 Bath Ocean View
12/22-29 2BR 3 Bath Ocean Front
12/22-29 Studio Ocean View
Please email me first before booking. I only have full ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Beautiful premium condo resort on Ka'anapali Beach. Studio condominium is 480 sq ft with king size bed and a sofa sleeper and small kitchen with full refrigerator, microwave, dishwasher and 2 burner cooktop. In-room amenities include Jacuzzi ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Property TMK # 2-6-005-022-0091
Come and Experience Kauai's Sunny South Shore and Beautiful Beaches. Towering Eucalyptus Trees form the Famous Arch known as The Tunnel of Trees as it Welcomes our Visitors and Marks the Official Entrance ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
Beautiful Premium One Bedroom Suite is 750 sq. ft. with a king size bed in a separate bedroom, a sleeper sofa in the living area, a full size kitchen and dining table and a large bathroom with double sinks, walk in shower and jacuzzi tub .In ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
30 day minimum *Inquire for monthly rate*
The Hawaii Studio is located Laniakea/Turtle /Papailoa Beach (North Shore's most exclusive beach).Only 5min walk or 2min by bike to the beach. Strategic located near Haleiwa Town (avoid North Shore ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Haleiwa
The Marriott Ko Olina Beach Club Resort offers daily and mid-week availability. ( 3- 5 Night Minimum ) Rates are based on weekly stays in USD and will vary by week, view and season / holiday. Daily rates will increase for 3 - 7 night stays. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapolei
Beachy style, chic studio renovated to 5-star standard with partial ocean view. Centrally located at Marine Surf Waikiki on 21st floor. Just steps from the beach, world-class shopping, restaurants, and attractions. One block from Royal ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
AVAILABLE FROM 3/29-4/5 2024 (one week) One-bedroom Island view studio 480 SQUARE FEET* • SLEEPS UP TO 4 ADULTS In addition, it features these amenities: Master bedroom with king-sized Westin Heavenly® Bed and flat-panel TV Westin Heavenly®...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
This condo is located at the Ilikai Marina, on the 13th floor. Enjoy this clean & comfortable studio at an unbeatable location in Waikiki; centrally located between Ala Moana & Waikiki! You'll be close to the beach, shopping and dining! ...
- 2 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Honolulu
