So, a recent post on Reddit highlighted a Mitch Hedberg joke.
“La Quinta” is Spanish for “next to Denny’s.”
Thinking about this, I realized we could use GIS to find the number of La Quintas that are next to Denny’s. Last night after the kids were asleep, I sat down with a beer (Sierra Nevada) and figured out how I could get the data and perform the calculation.
First, I visited La Quinta’s website and their interactive map of hotel locations. Using Firebug, I found “hotelMarkers.js” which contains the locations of the chain’s hotels in JSON. Using a regular expression, I converted the hotel data into CSV.
Next, I went to Denny’s website and their map. Denny’s uses Where2GetIt to provide their restaurant locations. They provide a web service to return an XML document containing the restaurants near the user’s location (via GeoIP) or by a specified address. Their web service also has a hard limit of 1,000 results returned per call. Again, I used Firebug to get the URL of the service, then changed the URL to search for locations near Washington, DC and Salt Lake City, Utah, with the result limit set to 1000 and the radius set to 10000 (miles, presumably). From this, I was able to get an “east” and “west” set of results for the whole country. These results were in XML, so I wrote a quick Python script to convert the XML to CSV.
#!/usr/bin/env python import sys from xml.dom.minidom import parse dom = parse(sys.argv) tags = ("uid", "address1", "address2", "city", "state", "country", "latitude", "longitude") print ",".join(tags) for poi in dom.getElementsByTagName("poi"): Â Â Â values =  Â Â Â for tag in tags: Â Â Â Â Â Â Â if len( poi.getElementsByTagName(tag).childNodes ) == 1: Â Â Â Â Â Â Â Â Â Â Â values.append( poi.getElementsByTagName(tag).firstChild.nodeValue ) Â Â Â Â Â Â Â else: Â Â Â Â Â Â Â Â Â Â Â values.append("") Â Â Â print ",".join( map(lambda x: '"'+str(x)+'"', values) )
I then loaded the CSVs into PostgreSQL. First, I needed to remove the duplicates from my two Denny’s CSVs.
CREATE TABLE dennys2 (LIKE dennys); INSERT INTO dennys2 SELECT DISTINCT * FROM dennys; DROP TABLE dennys; ALTER TABLE dennys2 RENAME TO dennys;
Then I added a Geometry column to both tables.
SELECT AddGeometryColumn('laquinta', 'shape', 4326, 'POINT', 2); UPDATE laquinta SET shape = ST_SetSRID(ST_MakePoint(longitude, latitude),4326); SELECT AddGeometryColumn('dennys', 'shape', 4326, 'POINT', 2); UPDATE dennys SET shape = ST_SetSRID(ST_MakePoint(longitude, latitude),4326);
From there, finding all of the La Quinta hotels that live up to their name quite easy.
SELECT d.city, d.state, d.shape <-> l.shape as distance, Â ST_MakeLine(d.shape, l.shape) as shape FROM dennys d, laquinta l WHERE (d.shape <-> l.shape) < 0.001 -- 'bout 100m ORDER BY 3;
Here are the 29 cities that are home to La Quinta – Denny’s combos:
- Mobile, AL
- Phoenix, AZ
- Bakersfield, Irvine, & Tulare, CA
- Golden, CO
- Orlando & Pensacola, FL
- Augusta & Savannah, GA
- Lenexa, KS
- Metairie, LA
- Amarillo, Austin, Brenham, College Station, Corpus Christi, Dallas, El Paso (2!), Galveston, Irving, Killeen, Laredo, Lubbock, McAllen, San Antonio, & The Woodlands, TX.
You might have noticed El Paso, Texas, has two La Quinta – Denny’s combos. Both happen to also be on the same street, just a few miles apart.
I love that the second one even shares a post for both of their highway-scale signage.
So out of the 833 La Quintas and 1,675 Denny’s, there are 29 that are very close (if not adjacent) to one another. So, only 3.4% of the La Quintas out there live up to Mitch Hedberg’s expectations.
GIS: coming up with solutions for the problems no one asked!
Update: Chris in the comments made a good point about projection. So here’s the data reprojected into US National Atlas Equal Area and then limited to 150 meters distance between points.
SELECT d.city, d.state, ST_Transform(d.shape,2163) <-> ST_Transform(l.shape,2163) as distance FROM dennys d, laquinta l WHERE (ST_Transform(d.shape,2163) <-> ST_Transform(l.shape,2163)) < 150 ORDER BY 3;
This yields 49 pairs (or 5.8% of all La Quintas):
- Alabama: Huntsville, Huntsville, Mobile
- Arizona: Phoenix, Tempe, Tucson
- California: Bakersfield, Bakersfield, Irvine, South San Francisco, Tulare
- Colorado: Golden
- Florida: Cocoa Beach, Orlando, Pensacola, St Petersburg
- Georgia: Augusta, Savannah
- Illinois: Schaumburg
- Indiana: Greenwood
- Kansas: Lenexa
- Louisiana: Metairie
- New Mexico: Albuquerque
- Oregon: Salem
- Texas: Amarillo, Austin, Brenham, College Station, Corpus Christi, Dallas, Dallas, El Paso, El Paso, Galveston, Irving, Killeen, Laredo, Live Oak, Lubbock, McAllen, San Antonio, San Antonio, San Antonio, Victoria, The Woodlands
- Utah: Midvale
- Virginia: Virginia Beach
- Washington: Auburn, Seatac
This modification to the query introduced a new oddity: Huntsville, AL has a Denny’s that is between two La Quintas, which is why it’s on the revised list twice.
Update: I uploaded a dump of the data to Github, if you want to explore the data on your own.
That was awesome!
Great use of GIS!
I noticed all of your detected location were southern-ish, which made me wonder if it’s an error associated with using the distance of .001.
Using the vincenty formula, You can determine a possibly more fine tuned (its an approximation still) estimates for the needed lat/lon. Below are 3 cities measurements for both north and east.
If you used .00133 you’ll get a lot more false positives (locations, especially southern that are more than 100 meters apart), put you’d better capture the locations in the north that are within 100 meter.
100 meters north = 0.0008994202020318198
100 meters east = 0.001330026833983311
100 meters north = 0.0008999416454642528
100 meters east = 0.0012533425193907988
100 meters north = 0.000902801077771187
100 meters east = 0.0009870957371589384
Missed one in Fort Collins, CO. Probably a bit farther than 100m away. Check out this bing map, at the top center is a Denny’s and bottom is a La Quinta http://binged.it/MyKQPs
This is awesome! I’m trying to do what you did, step-by-step, as knowing how to do this will prove useful to work. I just can’t figure out how you parsed the La Quinta locations. Can you please give me a hint or two?