Exterior Lands, Airbnb Costs, and Rockset’s Geospatial Queries


Airbnb Costs Round Main Occasions

Operational analytics on real-time information streams requires with the ability to slice and cube it alongside all of the axes that matter to individuals, together with time and house. We will see how essential it’s to investigate information spatially by an app that’s all about location: Airbnb. Main occasions in San Francisco trigger enormous influxes of individuals, and Airbnb costs enhance accordingly. Nonetheless, these value will increase are extremely localized round these occasions. Airbnb publishes pricing information for the previous and future, and we are able to use this information to see how costs spike round main occasions nicely earlier than they occur.

We’ll have a look at three main occasions. The primary is Exterior Lands Music and Artwork Pageant, which introduced over 90,000 individuals to Golden Gate Park in August. We’ll additionally have a look at costs round Oracle OpenWorld and Dreamforce, two massive conferences at Moscone Heart. We ran the queries utilizing Rockset’s new geospatial features.


For all three occasions, there’s a noticeable enhance within the common value of Airbnbs inside a one kilometer radius of the occasion. Within the case of Exterior Lands, the imply value spiked by over 30%!

Behind the Scenes

In an effort to make geospatial queries quick, we reimagined Rockset’s search index. Rockset is constructed on three forms of indexes– columnar storage, row storage, and a search index. We retailer every of the indexes in RocksDB, an ordered key-value retailer. The search index permits queries for all paperwork with a specific worth, or a variety of values, to run shortly. For every worth a discipline takes on, the search index shops a sorted listing of doc IDs which have that worth. This permits a question like this one to run shortly:

SELECT * FROM individuals WHERE identify="Ben"

All we have to do is search for the important thing “identify.Ben” within the search index.

Once we launched the geography sort to the IValue framework, we wanted to increase the capabilities of the search index. Typical geospatial queries aren’t often looking for precisely one level, however for some compact area of factors, like all factors inside a given distance, or inside a polygon. To serve this want, we repurposed the search index to work otherwise for geographies. First, we partition the floor of the earth right into a hierarchical grid of roughly sq. cells utilizing the S2 library.

image (1)

For every level in a set, we add an entry within the search index for every cell which comprises it. Since these cells type a hierarchy, a single level is contained by many cells- its quick mum or dad, and all of that cell’s ancestors. This will increase house utilization, however pays off with higher question efficiency. Within the determine above, a degree in cell A within the determine may also be added to cells B, C, and D, as a result of every of those cells comprises cell A.

image (2)

To search out all factors in a given area, we discover a set of cells which covers that area. Whereas each cell within the area (on this case Florida) is within the set of cells which covers it, a few of the cells fall partly outdoors the goal area. To make sure our outcomes are precise, we examine if these candidate factors are contained by the area after retrieving them from storage, and discard these which aren’t. Due to the index, we by no means have to look at any factors outdoors this set of cells, vastly lowering the question time for selective queries.

How To Do It Your self

First, obtain and extract calendar.csv.gz and listings.csv.gz from Airbnb in your location and time of curiosity (I used the info for August in San Francisco). Then create a Rockset account if you happen to don’t have already got one, and add every CSV to a separate Rockset assortment.

image (3)

Create a set and add calendar.csv. Specify that the format is CSV/TSV, and the default format choices ought to be right.

image (4)

Create one other assortment and add listings.csv, however this time you’ll have to specify a change. Earlier than the geography sort and geospatial queries, you needed to do the mathematics to compute distances between latitude/longitude factors your self (as we did when analyzing SF automobile break-ins). With geographies, we are able to specify a change which mixes the latitude and longitude discipline into one object, and tells Rockset to create an geospatial index on it. The fields are initially strings, so we first forged them to floats, then convert them to a geography with the next transformation:

ST_GEOGPOINT(CAST(:longitude AS float), CAST(:latitude AS float)) 

Observe that the longitude comes first.

Screen Shot 2019-09-19 at 3.35.35 PM

As soon as your information has been ingested and listed, you’ll be able to run this question to get the every day common value close to Moscone Heart:

image (6)

Once more, for simple copy and paste:

SELECT c.date,
  AVG(CAST(change(REPLACE(c.value, '$'), ',') as FLOAT)) average_price
FROM commons.AirbnbCalendar c
JOIN commons.AirbnbListings l on c.listing_id = l.id
WHERE c.date < '2019-12-01'
  AND ST_DISTANCE(ST_GEOGPOINT(-122.400658, 37.784035), l.geography) < 1000
GROUP BY c.date
ORDER BY date;

We will additionally have a look at how the costs between Airbnb’s very near Golden Gate Park, and additional away. I created this visualization utilizing Rockset’s Tableau integration. Costs for the weekend of Exterior Lands are in orange. Costs in blue are the common over all of August.

image (9)

Once more, for simple copy and paste:

    CAST(change(REPLACE(c.value, '$'), ',') as FLOAT) value,
        ST_GEOGPOINT(-122.491341, 37.768761),
    ) distance,
        c.date >= '2019-08-09'
        AND c.date <= '2019-08-11'
    ), 'Throughout Exterior Lands', 'August Common') AS during_outside_lands
FROM commons.AirbnbCalendar c
JOIN commons.AirbnbListings l on c.listing_id = l.id
WHERE c.date >= '2019-08-01'
    AND c.date <= '2019-08-30'
        ST_GEOGPOINT(-122.491341, 37.768761),
    ) < 1300

As you’ll be able to see, you’ll pay a considerable premium to get an Airbnb close to Golden Gate Park in the course of the weekend of the Exterior Lands. Nonetheless, if you happen to can accept a spot just a little additional away, the costs look extra like typical Airbnb costs. With Rockset, you’ll be able to go from deeply nested information with latitude and longitude fields to quick, expressive geospatial queries in below an hour.


Leave a Reply