Exercises

Exercise 1

• Create a geospatial index (2dsphere) for the states collection

  • Get information on states collection’s indexes

    {'_id_': {'key': [('_id', 1)], 'ns': 'geo.states', 'v': 2}, 
    'loc_2dsphere': 
    {'2dsphereIndexVersion': 3, 'key': [('loc', '2dsphere')], 'ns': 'geo.states', 'v': 2}}

• Remove the geospatial index from the states collection

  • Get information on states collection’s indexes

    {'_id_': {'v': 2, 'key': [('_id', 1)], 'ns': ‘geo.states'}}

Exercise 2

• Use the restaurant collection in the geo database

1. Create a valid query and print the query plan using executionStats•Find documents between '10200‘ and '10280‘ in zipcode

2. Create an index to zipcode (Hint: Embedded Document)

3. Execute #1, again.

• No Index

    "executionStats" : {
   …
                  "executionTimeMillis" : 11,
                  "totalKeysExamined" : 0,
                  "totalDocsExamined" : 25359,
                  "nReturned": 21,
  …

• Indexed

   "executionStats" : {
  …
                  "executionTimeMillis" : 0,
                  "totalKeysExamined" : 21,
                  "totalDocsExamined" : 21,
                  "nReturned": 21,
  …

• Use the restaurant collection in the geo database

1. Create a query and print the query plan using executionStats•Find documents that have at least the grade is ‘B’ in the grades.grade field.

2. Create an index to grade (Hint: Indexing on Array)

3. Execute #1, again.

• No Index

   "executionStats" : {
  …
                  "executionTimeMillis" : 17,
                  "totalKeysExamined" : 0,
                  "totalDocsExamined" : 25359,
                  "nReturned": 8280,
  …

• Indexed

   "executionStats" : {
  …
                  "executionTimeMillis" : 13,
                  "totalKeysExamined" : 8280,
                  "totalDocsExamined" : 8280,
                  "nReturned": 8280,
  …

Exercise 3

• Find international airports in the state that has the above zip code (10044)

  • Sorting the name in ascending order

  • Print state’s name and airport name each.

    • Hint: states and small_zip collections

    [{'name': 'Albany County'}, 
    {'name': 'Greater Buffalo Intl'}, 
    {'name': 'Greater Rochester International'}, 
    {'name': 'John F Kennedy Intl'}, 
    {'name': 'La Guardia'}, 
    {'name': 'Massena Intl'}, 
    {'name': 'Niagara Falls Intl'}, 
    {'name': 'Ogdensburg Intl'}, 
    {'name': 'Stewart Intl'}, 
    {'name': 'Syracuse Hancock Intl'}, 
    {'name': 'Watertown International'}] 

• Find Korean restaurants that are located at less 2km from international airports.

  • Warn: should create 2dsphere index to address for restaurants collection.

• Find states that are intersect to the flight path (trajectory).

  • Print names including source and destination states.

• Make Geojson format whose type is LineString.

  • Find the two coordinates (LAX, DTW) Result

• Result

  [{'name': 'Arizona'},
  {'name': 'California'},
  {'name': 'Colorado'},
  {'name': 'Illinois'},
  {'name': 'Iowa'},
  {'name': 'Kansas'},
  {'name': 'Michigan'},
  {'name': 'Nebraska'}, 
  'name': 'Nevada'},
  {'name': 'Utah'}]

Exercise 4

• Using the restaurants collection.

• Find restaurants whose names include "Kimchi".

• Print only name, zipcodeand address

  { "zipcode" : 11231, "address" : "Smith Street 478" }
  { "zipcode" : 11238, "address" : "Washington Avenue 766" }
  { "zipcode" : 10036, "address" : "West 48 Street 18-20" }

• Using the restaurants and states collections.

• Find restaurants that have "NolbuRestaurant＂(restaurants collection)

• Print the state (states collection)

  New York

• Using restaurants.json

• Among the "Korean" restaurants, find a restaurant that has "Ramen"but "Izakaya“.

• Then, find a restaurant near above restaurant in 150m.

  { '_id' : ObjectId('5c8626739f44c9f6768035da'),
    'address' : {'building' : '8', 'coord': [-73.9916305, 40.7247106],
                 'street': 'Extra Pl', 'zipcode': '10003'},
    'borough': 'Manhattan',
    'cuisine': 'Japanese',
    'grades': [{'date': datetime.datetime(2015, 1, 20, 0, 0),
                 'grade': 'Not Yet Graded',
                 'score': 21}],
    'name': 'Ko Ep, Llc',
    'restaurant_id': '50015854'}