03 SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Import csv files into SQL database\n",
    "```\n",
    "jeremydai ~ $  cd /Users/jeremydai/Dropbox/Udacity_Project/OpenStreetMap\n",
    "jeremydai OpenStreetMap $ sqlite3\n",
    "SQLite version 3.13.0 2016-05-18 10:57:30\n",
    "Enter \".help\" for usage hints.\n",
    "Connected to a transient in-memory database.\n",
    "Use \".open FILENAME\" to reopen on a persistent database.\n",
    "sqlite> .open houston.db\n",
    "sqlite> .read data_wrangling_schema.sql\n",
    "sqlite> .tables\n",
    "nodes       nodes_tags  ways        ways_nodes  ways_tags \n",
    "sqlite> .mode csv\n",
    "sqlite> .import nodes.csv nodes\n",
    "sqlite> .import nodes_tags.csv nodes_tags\n",
    "sqlite> .import ways.csv ways\n",
    "sqlite> .import ways_nodes.csv ways_nodes\n",
    "sqlite> .import ways_tags.csv ways_tags\n",
    "\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "## Database Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "db = sqlite3.connect(\"/Users/jeremydai/Dropbox/Udacity_Project/OpenStreetMap/houston.db\")\n",
    "c = db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3486397"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Number of Nodes\n",
    "c.execute(\"select count(*) from nodes;\")\n",
    "c.fetchall()[0][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "441328"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Number of Ways\n",
    "c.execute(\"select count(*) from ways;\")\n",
    "c.fetchall()[0][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1978\n"
     ]
    }
   ],
   "source": [
    "# Number of unique users\n",
    "query = '''\n",
    "select count(distinct user.uid) as n\n",
    "from (select uid from nodes UNION ALL select uid from ways) as user\n",
    "'''\n",
    "c.execute(query)\n",
    "n_users=c.fetchall()[0][0]\n",
    "print n_users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "u.uid count\n",
      "(147510, 556989)\n",
      "(672878, 536682)\n",
      "(1110270, 485675)\n",
      "(496606, 203246)\n",
      "(3119079, 193609)\n",
      "(119881, 137301)\n",
      "(9065, 115728)\n",
      "(243003, 86031)\n",
      "(475877, 81047)\n",
      "(37392, 58765)\n",
      "Top 10 users contribute 2455073 items of nodes and ways\n",
      "They, 0.51 % of total users, contribute 62 % of nodes and ways\n"
     ]
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT u.uid, count(*) as n\n",
    "FROM (select uid from nodes UNION ALL select uid from ways) as u\n",
    "GROUP BY uid\n",
    "ORDER BY n DESC\n",
    "LIMIT 10;\n",
    "'''\n",
    "c.execute(query)\n",
    "print 'u.uid', 'count'\n",
    "contri=0\n",
    "for row in c:\n",
    "    print row\n",
    "    contri+=row[1]\n",
    "\n",
    "print 'Top 10 users contribute', contri,'items of nodes and ways'\n",
    "\n",
    "query = '''\n",
    "SELECT count(*) as n\n",
    "FROM (select uid from nodes UNION ALL select uid from ways) as u\n",
    ";\n",
    "'''\n",
    "c.execute(query)\n",
    "total=c.fetchall()[0][0]\n",
    "\n",
    "print 'They,',round(float(1000)/n_users,2), '% of total users, contribute', contri*100/total,'% of nodes and ways'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "#contributions per person histogram\n",
    "query = '''\n",
    "SELECT count(*) as n\n",
    "FROM (select uid from nodes UNION ALL select uid from ways) as u\n",
    "GROUP BY uid\n",
    "'''\n",
    "c.execute(query)\n",
    "\n",
    "contri_list=[]\n",
    "for row in c:\n",
    "    contri_list.append(row[0])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x110cb4050>"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXIAAAD3CAYAAAAALt/WAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAIABJREFUeJzt3Xl0W9dh5/EvNhIEAZIQCYqkqH25Wi1ZlhzJlhPZiRLX\niZc0k7R106ZO3NRt09PmtGfG7XSZnmma6Zy4mcRTJ3Ga3UnbNLGyj+PGjjfJrm3JWi1eidpJLdz3\nFcv8AUqGaYoEJZAPj/x9zuEhgQc8/IBD/vB48d59nlQqhYiIuJfX6QAiInJtVOQiIi6nIhcRcTkV\nuYiIy6nIRURczj/dD9jc3D3ubjLRaIj29r7pinNV3JAR3JFTGXNDGXMjnzPGYhHPlZbl3Ra53+9z\nOsKE3JAR3JFTGXNDGXPDDRnHkndFLiIik6MiFxFxORW5iIjLqchFRFxORS4i4nIqchERl1ORi4i4\nnIpcRMTlJjyy0xjjBR4B1gODwP3W2vqM5b8J/CmQAL5qrf3CFGUVEZExZHOI/j1A0Fq71RizBXgI\nuDtj+WeANUAP8Lox5l+tte25j5o/nnjxFN09A5O6z/YN86YmjIjMetkMrWwDngCw1r4EbBq1/ABQ\nCgQBD6BTDomITKNstshLgM6MywljjN9aGx+5fAjYA/QCj1trO8ZbWTQamnA+g1gskkUsB9W3EgkH\nJ3UXp55T3r+WKGOuKGNuuCHjaNkUeReQ+cy8l0rcGHMd8F5gMemhlceMMR+01v77lVY20cxisViE\n5ubuLGI5a7JDK048Jze8lsqYG8qYG/mccbw3mGyGVnYBdwCMjJEfzFjWCfQD/dbaBNAERK86qYiI\nTFo2W+Q7gR3GmN2kx8DvM8bcC4SttY8aY74EvGCMGQKOA1+fsrQiIvIWExa5tTYJPDDq6rqM5V8E\nvpjjXCIikiUdECQi4nIqchERl1ORi4i4nIpcRMTlVOQiIi6nIhcRcTkVuYiIy6nIRURcTkUuIuJy\nKnIREZdTkYuIuJyKXETE5VTkIiIupyIXEXE5FbmIiMupyEVEXE5FLiLicipyERGXm/BUb8YYL/AI\nsB4YBO631taPLKsC/jXj5huAB0dO/yYiItMgm5Mv3wMErbVbjTFbgIeAuwGstReA7QDGmK3Ap4Av\nT01UEREZSzZDK9uAJwCstS8Bm0bfwBjjAR4Gft9am8hpQhERGVc2W+QlQGfG5YQxxm+tjWdcdydw\n2FprJ1pZNBrC7/eNe5tYLJJFLAfVtxIJByd1F6eeU96/lihjrihjbrgh42jZFHkXkPnMvKNKHODD\nwOeyecD29r5xl8diEZqbu7NZlaO6ewYmdXsnnpMbXktlzA1lzI18zjjeG0w2Qyu7gDsARsbID45x\nm03A7qsJJyIi1yabLfKdwA5jzG7AA9xnjLkXCFtrHzXGxIAua21qKoOKiMjYJixya20SeGDU1XUZ\ny5tJ73YoIiIO0AFBIiIupyIXEXE5FbmIiMupyEVEXE5FLiLicipyERGXU5GLiLicilxExOVU5CIi\nLqciFxFxORW5iIjLqchFRFxORS4i4nIqchERl1ORi4i4nIpcRMTlVOQiIi6nIhcRcbkJT/VmjPEC\njwDrgUHgfmttfcbyzcA/kj6f5wXgw9bayZ1iXkRErlo2W+T3AEFr7VbgQeChSwuMMR7gy8B91tpt\nwBPAwqkIKiIiY5twixy4VNBYa18yxmzKWLYCaAU+aYxZC/zUWmvHW1k0GsLv9437gLFYJItYDqpv\nJRIOTuouTj2nvH8tUcZcUcbccEPG0bIp8hKgM+Nywhjjt9bGgQrgJuATQD3wE2PMq9bap6+0svb2\nvnEfLBaL0NzcnUUsZ3X3TG70yInn5IbXUhlzQxlzI58zjvcGk83QSheQuQbvSIlDemu83lp7xFo7\nTHrLfdPoFYiIyNTJpsh3AXcAGGO2AAczlp0AwsaYZSOXbwEO5zShiIiMK5uhlZ3ADmPMbtJ7ptxn\njLkXCFtrHzXGfAz4zsgHn7uttT+dwrwiIjLKhEVurU0CD4y6ui5j+dPAjTnOJSIiWdIBQSIiLqci\nFxFxORW5iIjLqchFRFxORS4i4nIqchERl1ORi4i4nIpcRMTlVOQiIi6nIhcRcTkVuYiIy6nIRURc\nTkUuIuJyKnIREZdTkYuIuJyKXETE5VTkIiIuN+EZgowxXuARYD0wCNxvra3PWP5J4H6geeSq37PW\n2inIKiIiY8jmnJ33AEFr7daRky8/BNydsfwG4LettXumIqCIiIwvm6GVbcATANbal4BNo5bfAPy5\nMeYFY8yf5zifiIhMIJst8hKgM+Nywhjjt9bGRy7/K/BPQBew0xjzPmvtT660smg0hN/vG/cBY7FI\nFrEcVN9KJByc1F2cek55/1qijLmijLnhhoyjZVPkXUDmM/NeKnFjjAf4P9bazpHLPwWuB65Y5O3t\nfeM+WCwWobm5O4tYzuruGZjU7Z14Tm54LZUxN5QxN/I543hvMNkMrewC7gAYGSM/mLGsBDhkjAmP\nlPptgMbKRUSmUTZb5DuBHcaY3YAHuM8Ycy8QttY+aoz5C+CXpPdoecpa+7OpiysiIqNNWOTW2iTw\nwKir6zKWfwv4Vo5ziYhIlnRAkIiIy6nIRURcTkUuIuJyKnIREZdTkYuIuJyKXETE5VTkIiIupyIX\nEXE5FbmIiMupyEVEXE5FLiLicipyERGXU5GLiLicilxExOVU5CIiLqciFxFxORW5iIjLqchFRFxu\nwlO9GWO8wCPAetLn5bzfWls/xu0eBdqstQ/mPKWIiFxRNlvk9wBBa+1W4EHgodE3MMb8HrAux9lE\nRCQLE26RA9uAJwCstS8ZYzZlLjTG3AS8DfgSsHKilUWjIfx+37i3icUiWcRyUH0rkXBwUndx6jnl\n/WuJMuaKMuaGGzKOlk2RlwCdGZcTxhi/tTZujKkG/gZ4P/ChbB6wvb1v3OWxWITm5u5sVuWo7p6B\nSd3eiefkhtdSGXNDGXMjnzOO9waTTZF3AZlr8Fpr4yM/fxCoAH4GVAEhY0ydtfbrVxdVREQmK5si\n3wXcCXzXGLMFOHhpgbX288DnAYwxvwOsVImLiEyvbIp8J7DDGLMb8AD3GWPuBcLW2kenNJ2IiExo\nwiK31iaBB0ZdXTfG7b6eo0wiIjIJOiBIRMTlVOQiIi6nIhcRcTkVuYiIy6nIRURcTkUuIuJy2exH\nLuNo7Rxg79FmAKKRQqrKQ9TGwg6nEpHZREV+lRLJFAfqWzh0so1UKn3d+dY+Xj/VjllQxuaVlXi9\nHmdDisisoCK/Sq8caeLo2Q6Kg362rq2iojRIW/cgL79+EXumg46eQbZvmEdhwfgzPYqIXCuNkV+F\nC629HD3bQWlxAXdtW0xNRTEFAR9Vc0L8ypaFLJgb5mJbP8/saySZTDkdV0RmOBX5JCWSSZ7Z2wDA\nljVzCfjf/BIG/F7esaHmcpm/fOQiqZTKXESmjop8kp56tYHWzgGWzith7pzQmLfxeDzcvK6aaKSQ\no2c7sWc6pjmliMwmKvJJGBpO8KNdpygs8HGDiY1724Dfy60b5xEs8PFqXRMnz3dNU0oRmW1U5JOw\nxzbTNxhn7ZJyggUTf04cLgqw7bpqkin44g8P0TcQn/A+IiKTpSKfhOf2nwNg1aI5Wd+npqKYtYvn\n0NwxwDd/XqfxchHJORV5li629WHPdrByQRml4cJJ3XfD8gqWzivh5SNNl98MRERyRUWepecPnAfg\n7etrJn1fr9fD7921hlChn+/84hiNzT25jicis9iERW6M8RpjvmiMedEY84wxZtmo5R8wxrxijHnZ\nGPPHUxfVOYlkkl0Hz1Mc9E/4IeeVVJQW8dH3rmI4nuQLPzzM4HAixylFZLbKZov8HiBord0KPAg8\ndGmBMcYH/C/gXcBW4A+MMRVTEdRJh0+20dk7xJbVVQT8V3+k5sYVMd65sZZzLb188wmr8XIRyYls\ninwb8ASAtfYlYNOlBdbaBLDKWtsJlAM+YGgKcjpqX30rAJtXVV7zuj5021IWV5fw4uEL/PTF09e8\nPhGRbOZaKQE6My4njDF+a20cwFobN8b8KvBPwE+B3vFWFo2G8E+wVRuLRbKINT1SqRSHTrQSCQXY\nsn4ePp8X6luJhIOTWk/mc/rbj2/lTz//HI8/d4IVi8q5+SrG3a/mcfOVMuaGMuaGGzKOlk2RdwGZ\nz8x7qcQvsdY+boz5AfB14LeBr11pZe3tfeM+WCwWobm5O4tY0+PMxW5aOgfYsnoubW1vvEd19wxM\naj2jn9Mn3r+Ov39sD5/59h4G+ge5bmnuR6Ty7bUcizLmhjLmRj5nHO8NJpuhlV3AHQDGmC3AwUsL\njDElxphnjTGF1tok6a3x5LXFzS8HjqeHVa5bVp7T9c6vDPOJX12H1wMPf/8ge2xTTtcvIrNHNkW+\nExgwxuwGPgt80hhzrzHm49baLuDbwHPGmBeAFPDY1MWdfvuPt+D1eFi7OLdFDrBm0Rw++aH1+P1e\nvvCDw/zHq2f1AaiITNqEQysjW9oPjLq6LmP5o8CjOc6VF7r6hjjR2MXy2lLCRYEpeQyzIMqf/doG\nPve9A/zLL45Rd7qdj753FcXBqXk8EZl5dEDQOA4ebyUFrF82tXtULp1Xyt9+9EZWLijjtWMt/Pcv\n/yfPvNZIIjmjRqlEZIqoyMdx8MTI+PjS3A+rjBaNFPJnv349H3jHEgaHEnzz55a//srL/HJvA/2D\nmmxLRK5Mp3q7glQqxZHT7UQjhdRUFE/LY3q9Ht67dRHb1lXzgxdO8sKB83zryaN895nj3LAixg0r\nYqxZPIeCgHtOH/fMvsZxl0fCwTftAbR9w7ypjiQy46jIr+BcSy/dfcNsWTMXj2d6T6JcGi7kI7ev\n5O5ti3l+/zme3X+O3YcusPvQBfw+L8vmlWAWRFm5oIwlNSXXdLSpiLifivwK6kbO6rNyQdSxDGXh\nQu68eTHvvWkRp853s8c2cfhkG/ZMB3VnOvgh6RNYLKyKsGhuJP29KkJ1eTFe7/S++YiIc1TkV1B3\nph2AlQudK/JLvB4PS2pKWFJTwgdvhZ7+YY6dTZe5PdPO8cZO6hveOPi2IOBlbjRE7dwIpUUBYtEi\nKkqDREIBQsEAAd+bPxpJpVL0DyXoGximdyBO/0Cc3oFhevrf+OrtH6anP57+eWCYRDJFKpUilYLk\nyC6ToUI/oaCfsnAhsbIi5kaL6Owdorw0SFGhftVEpor+usaQTKWwZzqYU1JIrHRyh+LnykRjywDV\nFSGqK0LEE0nauwaZUxLk1IUuzlzs4WJ7H2ebcjtdbkHAS7goQKysCL/Pi9eTPj+px5M+gKB/MF30\n9Y2dHGvofNN9I6EANRXFzKsoprqiGJ/+YxDJGRX5GM4199LTP8xNS6umfXz8avh9XmLRojd9UJhK\npQgUFVBX30JzRz8tnf30DsTp7U9vTY8WLPARCgYoDvopCvopDgYIB/0UFwUIj3wVBHxZvcEkkin6\nBobp7BmitWuAls4Bmtr6sWc6sGc6KAh4WTg3wuLqEoqLJ3eSDhF5KxX5GI6MDKuYBWUOJ7l6Ho+H\naCTIstpSltWWTutj+7weIqECIqECaivDACSTKVo6+zlzsYeT57s51pDeag8dPM+iqgjLa0snfeYl\nEUlTkY+h7nS6yFc5+EHndMhm6zpXvF4PldEQldEQG02MprZ+Tp5PDwO9fqqd10+1EysL4vN42Lyq\nMquTW4tImv5aRkmmUhw920FFaZCKsiKn48xIXo+HqvIQVeUh3rm5gMMnWqhv6OR8ax9f+391fOep\nY9y4spJb1tewtKbEFcNbIk5SkY/S2NxL70CcDVN8WL6k+XxeFleXsLi6hJ7+YeLx9Gn1nj+Q/qqp\nKOaW66rZuraKklCB03FF8pKKfJSjZ9P7j6+Y797xcbcKFwXYvmEed928mNdPt/H8/vO8dqyZf3u6\nnu89c5yVC6NsXBFj4/IKjaeLZFCRj3KsIV3ky11Y5KPHvEcf/u4WXm962uC1i8vp7hvipcMX2X34\nAodPtnH4ZBuP/dyytLaUjctjbFheQdWckNORRRylIs+QSqU41tBJSSjA3KjGx/NBJFTAjs3z2bF5\nPi2d/bx2tIU9R5s51tBBfUMn3/1lPXOjRaxfVsH6ZRUsry3F79NccDK7qMgztHQO0N49yA0mpg/Y\n8lBFadHlUu/qHWL/8Rb217dy4HgLT75ylidfOUvA72VeRTG1lcXUVIQJFrx1HhpNzCUzjYo8w+Vh\nlVr3DavMNiXFBdxyXQ23XFfDU3vPcrGtn7NNPTQ09XDqQjenLnTjAWLRImpjxdRWhiktLtAbtMxI\nKvIMR8+mDytfMT/3B9BM5z7bbnY1r5PP66WmopiaimJuXFVJR88QDc3pUm9q76epvZ+9R1uIhAIs\nrSlh7eI5VJRq6ExmjgmL3BjjBR4B1gODwP3W2vqM5b8B/AkQJ31i5j8YOT2c6xxr6KCwwMf8kaMR\nxX3SR7QWEo0Usm5JOQNDcRqbe2lo7qWxuYd99a3sr3+RlQujbFtXzUYTo9BF87uLjCWbLfJ7gKC1\ndqsxZgvwEHA3gDGmCPg7YJ21ts8Y8y/A+4AfTVXgqdLVN8T51j7WLIri8+rDspkiWOBn6bxSls4r\nZTie5NSFblo7+jlyup0jp9sp/oWf7dfP45031FKmXRrFpbIp8m3AEwDW2peMMZsylg0CN1lr+zLW\n57793eDyNLBu3O1QshPwe1leW8rvvm81F9v7eOHAeZ7dd46fvnian798hndsmMf7ti7UPuriOtkU\neQmQOSdpwhjjt9bGR4ZQLgIYY/4ICAP/Md7KotEQ/gnOaBOLRbKIlVsNL54G4Ma1NRM/fn0rkbAz\n09tOlhtyTnfGPfXpc7EumldGbVUJ9nQ7e20TT+1p4Nl9jWxYUckNKyvf2I2xvpXbty6a1oxXw4m/\nm8lSxqmRTZF3AZnPzGutvXw24JEx9P8NrAA+YK196xypGdrb+8ZbTCwWobm5O4tYubX/aDM+r4do\nyJ/V47vhQBs3HBCUDxkXVBZTW7GI+oZO9h9v4dUjF6k71caNqyupjYWJhIOO/E5OhlN/N5OhjNdm\nvDeYbAaDdwF3AIyMkR8ctfxLQBC4J2OIxVUGhxKcudjNoqqIPviapbxeDysWlHHPLUtYvShK78Aw\nT+9p5MVDFxiOJ5yOJzKubLbIdwI7jDG7AQ9wnzHmXtLDKK8CHwOeB542xgB8zlq7c4ryTonj5zpJ\nJFMaHxcCfi+bVlaydF4pLxw4z7GGTpo7B6idE7o8t7pIvpmwyEfGwR8YdXVdxs+u38Xj0mnJlk/z\nCRgkf0UjhdyxdQGvHW3h9VPtfOqxPXz8ztVcvzzmdDSRt9ABQbwx46GO6JRMPm9663xBVQm/eOUM\nD3//IBtNjLWL50x4X00DINNp1hd5PJHk+LlOaiqKCRcFnI4jeWhpbRk+L/xybyN7bTNDwwmuX16h\nw/0lb7h+WORanW3qYWg4yQoNq8g4ykuC3P62BURCAQ6daOOVI02kUuPuoCUybWZ9kV8eVtEHnTKB\ncFGA29+2gLJwAXVnOni1rlllLnlBRX55fFxb5DKxokI/775xPqXhAo6cbufA8VanI4nM7iJPjpxI\nYk5JoWbDk6wFC/zs2DSfcFGA/fWtHDnd7nQkmeVmdZE3NvfS0z/MygVRp6OIy4SCfnZsrqWo0Mer\nR5poaO5xOpLMYrO6yC9tSa1aqCKXyYuECrh14zy8Xg/P7ztPe/eg05FklprVRV43UuTaIperVVFa\nxM3rqhhOJHl6TwP9g/GJ7ySSY7O2yBPJJPZsO5VlRZSX5v8MgZK/FlWXsH5ZOb0DcZ55rZFEwpXn\nVREXm7VFfuZiD/2DCVZqWEVy4Lql5SyqjtDcMcCLhy9qt0SZVrO2yOs0Pi455PF4uGltFRWlQU6c\n6+JnL512OpLMIrO2yI9cHh/XgUCSG36fl1s3ziNU6OfxZ09w8IT2MZfpMSuLPJ5IcrShg5qKYp3W\nS3KqqNDP9o01+HxevvTDw1xsc+UU/eIys7LIT5zrYmg4ySrtrSJToKK0iI/cbugbjPPw4we1J4tM\nuVlZ5Jf+5V29WEUuU+PmddW8a1Mt51p6+eefvE5SH37KFJqVRb6/vhW/z8vqhRPPKy1ytT506zJW\nLijjtWMt/GTXKafjyAw264q8tXOAhuYeVi4so7BA5+eUqeP3eXngnrWUlwT5wQsn2WObnI4kM9SE\nJ5YwxniBR4D1wCBwv7W2ftRtQsB/AB+z1ta9dS3548DIsMr6pRUOJ5HZoCRUwB99YB2ffmwvX/7x\n65SXBllUVeJ0LJlhstkivwcIWmu3Ag8CD2UuNMZsAp4DluY+Xu4dqG8B0gdwiEyHBXMjfPyu1QzH\nk3z+ewc0J4vkXDanetsGPAFgrX1ppLgzFQLvB76VzQNGoyH8/vGHNGKxSDarmrTB4QRHznQwf26E\n1csrr35F9a1Ewu44rN8NOWdixtG/w++ORegdSvLVHx/m4ccP8uk/3JbzUwtO1d9NLinj1MimyEuA\nzozLCWOM31obB7DW7gIwxmT1gO3t4+9XG4tFaG7uzmpdk3XgeAtDwwnWLIpe82N09wzkKNXUiYSD\neZ9zpmYc6/fr5tWVnGzs4Jd7G/mbL+3mT39tPYEJNmqyNZV/N7mijNdmvDeYbIZWuoDMNXgvlbjb\n7K+/ND6uYRWZfh6Ph9981wo2mRhHz3bwxR8eJq4JtiQHsinyXcAdAMaYLcDBKU00ReKJJK/aJiKh\nAMt0WjdxiNfr4XfvXMOqhVFeO9bCoz9+XWUu1yyboZWdwA5jzG7AA9xnjLkXCFtrH53SdDn0+qk2\nuvuGeefGWnzeWbfXpUyzZ/Y1jrt8w/IK2roGeLWuieaOfm65rprbNtZOUzqZaSYscmttEnhg1NVv\n2cXQWrs9R5mmxEuHLwKwZe1ch5OIQMDv5bYbanl6TwOnL3QTTyS5eW21jm2QqzIrNk37B+PsPdpM\nZbSIJdXah1fyw6Uyr6koprG5l3/4zl66eoecjiUuNCuK/LVjzQzFk2xdU4XH43E6jshlAb+X2zbO\nY+m8Ek5d6OZ/fuMVTp7vcjqWuMysKPLLwyprNKwi+cfrTZ+U4v23LKata5BPP7aHZ15r1FmGJGsz\nvshbOwc4fKqNxdUlzI2GnI4jMiaPx8OdNy/mkx9aT2HAxzd/bnn4+wfp7NFRoDKxGV/kT75yllQK\nbts4z+koIhNau6Sc/3HfjaxcUMa++hb+8p//k+cPnNM0uDKuGV3kPf3DPLf/HNFIIW9brWEVcYfy\n0iB/9hvX8+F3r2A4keRrP6vjU9/cw/HGzonvLLPSjC7yX77WyOBwgh2b5uP3zeinKjOM1+Phto21\n/P3vbuHGVZWcPN/Fp761h89/7wBnLubnIeTinGwOCHKl4XiCp149S1Ghn3dsqHE6jshVmVMS5IG7\n13Lbxg6+/+xx9tW3sK++hbWL57Bj83zWLJ6DV3tizXoztsif3XeOrr5h7tiykKLCGfs0ZZZYMb+M\nB39zI4dPtfHjXac4dLKNQyfbqJoTYsemWu7avtzpiOKgGdlwHT2D7Hz+BEWFfnZsnu90HJGsTHRY\n/yVb11axYn4Z7d2DvHzkIt968iiPP3eCG0yMrWuqWD6/TFvps8yMLPJ/+cUx+gcT/NZ7DKXFBU7H\nEcm58tIg5aVB5sWKsWc6OH6ui+f2n+e5/ecpDvpZUlPCouoSysIFbzoIbvsG7b01E824Ij9wvIVX\n6ppYWlOisXGZ8YoK/WxYXsHNG+ZRf6aNE+e6OHOhh4Mn2jh4oo1wUYDaymJqY2HmztFxFDPVjCry\ntq4BvvGExef18JHbV+rfS5k1vB4P1eXFVJcX87bVSc429XDmYg/nWnqpO91B3ekOAj4vh0+0sWJ+\nGcvnl7JwbkR7c80QM6bIeweG+ex399PePciHbl1GbWXY6UgijvD7vCyuLmFxdQmJZIqm9j4amnpp\naO65vNcLQEHAy9KaUpbUlLBgboQFc8PEyoq0AeRCM6LIB4biPPz9gzS29PKuTbW850Z9wCkC4PO+\nsaW+eVUlvf3DXGzvp6m9j6b2fo6cbufI6fbLtw/4vERLCiktLqA0XMC2dTXUlIeYUxpUwecx1xf5\n2aYevvCDQ1xo62PTykp+/Z3LNcOhyBUUFwVYUhRgSU16OufBoQRt3QO0dQ3S1jVAW/cgze39NLX3\nA/BqXTOQnqWxak6I6vIQldEiYqVFxMrSX9FIIV6v/uac5Noi7xuI8/TeBn68+xTD8STv3jyf/7J9\nqbYaRCahsMB3eYv9kkQiSVffMJ29Q8yJFHKhtY9zrb1caOvjbFPPW9bh93koLy0iVhakvCRIWbiQ\nsnAB0UjhyM+FhEOB6Xxas86ERW6M8QKPAOuBQeB+a219xvI7gb8G4sBXrbVfnqKsxBNJjjd2sq++\nhef2n6N/MEFx0M/v372WDcsrpuphRWYVn89LNFJINFIIQElxASsWlJFKpegdiNPTN0x3/zA9fUN0\n9w3T0z9MR/cgF9v6rrxOr4eySCHBAh/FwQDFQT/FRSPfgwFCQT8Ffh8FAS8FAR+F/vT39JeXAr+P\nwoAXv8+Lz+fB6/HoP+8M2WyR3wMErbVbR06+/BBwN4AxJgB8FtgM9AK7jDE/stZezHXQ442dfPa7\n++kbjANQEgpwxzsWcuv1tYSCrv3HQsQ1PB4P4aIA4aIAVWMsH4on6BuI0zcQp38w/b1v8I2fh+JJ\nunuHaIz3XnsW0m84Pp8Hv9dzueD93vR3n9eL3+e5fF36Zy8+b/q7P+O2l+/j81ASCTI4MIzf58U/\nctvM2/hH1nFpff6M9fm86WXxRJLhRJJ4PP19cChJd98QvQPD3GAqqZqC3UCzacBtwBMA1tqXjDGb\nMpatAuqtte0AxpgXgLcD/57roF6vh6ryEAurIqxbUs7qhVEKAjq/oUi+KPD7KAj7KAsXjrk8Eg7S\n3TNAMpVieDjJ4HCCoXiCwaEkQ/EEiUSKeDKZ/p5IEk+kSCSSxJNvXE4mR75So74nUwwNpxhIJkim\nUiSSKVImsHo0AAADi0lEQVSXv0/zCzGO7r5hfv2duZ9OIZsiLwEy589MGGP81tr4GMu6gdLxVhaL\nRSb8fygWi4x53Y3X5cdRabePkU9ExCnZHA3QBWQ2l3ekxMdaFgE6cpRNRESykE2R7wLuABgZIz+Y\nsewIsNwYM8cYU0B6WOXFnKcUEZEr8kx0gteMvVauI/0Zw33ARiBsrX00Y68VL+m9Vv5paiOLiEim\nCYtcRETym2bMERFxORW5iIjLqchFRFwubw+JNMa8H/igtfZep7NcMtF0BfnEGPM24B+stdudzjLa\nyBHBXwUWAYXA31lrf+RoqFGMMT7gy4ABUsAD1tpDzqYamzGmEtgD7LDW1jmdZyzGmL2kd1cGOGmt\nvc/JPGMxxvw5cBdQADxirf2Kw5Gylpdb5MaYzwGfJv/yXZ6uAHiQ9HQFeccY81+BfwaCTme5gg8D\nrdbaW4Dbgf/rcJ6x3Algrb0Z+EvgU87GGdvIm+KXgH6ns1yJMSYIeKy120e+8rHEtwM3ATcD7wBc\nNRd2vhXlJbuB33c6xBjeNF0BsGn8mzvmOPCrTocYx78DfzXys4f0hGt5xVr7A+DjIxcXkr8Hun0G\n+CJwzukg41gPhIwxTxpjnh45HiXfvIf0MTI7gR8DP3E2zuQ4WuTGmI8ZYw6N+tpsrf030v/O5psx\npytwKsyVWGu/Dww7neNKrLU91tpuY0wE+B7pLd68Y62NG2O+ATwMfNvpPKMZY34HaLbW/tzpLBPo\nI/2G8x7gAeDbefh3U0F6w+yDvJHRNdMrOvpijoxBuWYcivGnK5BJMMbMJ73184i19jtO57kSa+1H\njDH/DfhPY8xqa+21T92XOx8FUsaYdwEbgG8aY+6y1l5wONdoR0lPrpcCjhpjWoFq4Kyzsd6kFaiz\n1g4B1hgzAMSAJmdjZSff3hXz3S7SY6ffHWO6AsmSMWYu8CTwCWvtU07nGYsx5reAWmvtp0lvUSZH\nvvKGtfbtl342xjxD+gPZfCtxSL/hrAP+wBhTQ/o/2/PORnqLF4A/Nsb8I+k3mWLS5e4KKvLJ2Qns\nMMbs5o3pCmTy/gKIAn9ljLk0Vv4r1tp8+sDuceBrxpjngADwJ3mWz02+Anx9ZJrrFPDRfPtP1lr7\nE2PM24GXSQ85/6G1NuFwrKzpEH0REZfL171WREQkSypyERGXU5GLiLicilxExOVU5CIiLqciFxFx\nORW5iIjL/X/dsqKLgHmKNwAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x110bc9e50>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "sns.distplot(np.log10(contri_list),kde=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'place_of_worship', 2194)\n",
      "(u'school', 799)\n",
      "(u'fountain', 722)\n",
      "(u'restaurant', 700)\n",
      "(u'fast_food', 636)\n",
      "(u'fire_station', 349)\n",
      "(u'fuel', 281)\n",
      "(u'pharmacy', 176)\n",
      "(u'bank', 172)\n",
      "(u'police', 160)\n"
     ]
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT value, COUNT(*) as num\n",
    "FROM nodes_tags\n",
    "WHERE key='amenity'\n",
    "GROUP BY value\n",
    "ORDER BY num DESC\n",
    "LIMIT 10;\n",
    "'''\n",
    "c.execute(query)\n",
    "for row in c:\n",
    "    print row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'christian', 2128)\n",
      "(u'buddhist', 16)\n",
      "(u'jewish', 12)\n"
     ]
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT a.value, COUNT(*) as num\n",
    "FROM nodes_tags as a, \n",
    "    (SELECT DISTINCT(id) FROM nodes_tags WHERE value='place_of_worship') as b\n",
    "WHERE a.id=b.id\n",
    "AND a.key='religion'\n",
    "GROUP BY a.value\n",
    "ORDER BY num DESC\n",
    "LIMIT 3;\n",
    "'''\n",
    "c.execute(query)\n",
    "for row in c:\n",
    "    print row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'mexican', 73)\n",
      "(u'american', 35)\n",
      "(u'pizza', 35)\n",
      "(u'italian', 32)\n",
      "(u'chinese', 28)\n",
      "(u'seafood', 21)\n",
      "(u'burger', 19)\n",
      "(u'barbecue', 16)\n",
      "(u'sandwich', 13)\n",
      "(u'thai', 10)\n"
     ]
    }
   ],
   "source": [
    "query = '''\n",
    "SELECT a.value, COUNT(*) as num\n",
    "FROM nodes_tags as a,\n",
    "    (SELECT DISTINCT(id) FROM nodes_tags WHERE value='restaurant') as b\n",
    "WHERE a.id=b.id\n",
    "AND a.key='cuisine'\n",
    "GROUP BY a.value\n",
    "ORDER BY num DESC\n",
    "LIMIT 10;\n",
    "'''\n",
    "\n",
    "c.execute(query)\n",
    "for row in c:\n",
    "    print row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'highway', 37651)\n",
      "(u'power', 29894)\n",
      "(u'name', 11442)\n",
      "(u'amenity', 8094)\n",
      "(u'ele', 5367)\n",
      "(u'feature_id', 4914)\n",
      "(u'railway', 4588)\n",
      "(u'created', 4098)\n",
      "(u'state_id', 3719)\n",
      "(u'county_id', 3718)\n",
      "(u'housenumber', 2882)\n",
      "(u'street', 2814)\n",
      "(u'city', 2678)\n",
      "(u'state', 2511)\n",
      "(u'postcode', 2261)\n",
      "(u'natural', 2232)\n",
      "(u'religion', 2171)\n",
      "(u'crossing', 1965)\n",
      "(u'barrier', 1915)\n",
      "(u'created_by', 1839)\n",
      "(u'source', 1804)\n",
      "(u'shop', 1431)\n",
      "(u'building', 1413)\n",
      "(u'material', 1384)\n",
      "(u'denomination', 1329)\n",
      "(u'import_uuid', 1257)\n",
      "(u'county_name', 1188)\n",
      "(u'place', 1169)\n",
      "(u'cuisine', 965)\n",
      "(u'reviewed', 788)\n",
      "(u'noexit', 771)\n",
      "(u'tourism', 757)\n",
      "(u'ref', 693)\n",
      "(u'website', 661)\n",
      "(u'phone', 631)\n",
      "(u'leisure', 580)\n",
      "(u'traffic_signals', 524)\n",
      "(u'is_in', 514)\n",
      "(u'class', 453)\n",
      "(u'county', 453)\n",
      "(u'id', 452)\n",
      "(u'st_alpha', 452)\n",
      "(u'county_num', 451)\n",
      "(u'st_num', 451)\n",
      "(u'exit_to', 403)\n",
      "(u'feature_type', 381)\n",
      "(u'source_ref', 376)\n",
      "(u'aeroway', 346)\n",
      "(u'operator', 317)\n",
      "(u'opening_hours', 309)\n",
      "(u'emergency', 274)\n",
      "(u'foot', 241)\n",
      "(u'landuse', 234)\n",
      "(u'entrance', 223)\n",
      "(u'bicycle', 208)\n",
      "(u'direction', 198)\n",
      "(u'population', 191)\n",
      "(u'traffic_calming', 187)\n",
      "(u'level', 185)\n",
      "(u'artwork_type', 178)\n",
      "(u'artist_name', 168)\n",
      "(u'access', 165)\n",
      "(u'waterway', 156)\n",
      "(u'type', 147)\n",
      "(u'brand', 145)\n",
      "(u'edited', 137)\n",
      "(u'office', 135)\n",
      "(u'note', 133)\n",
      "(u'backrest', 98)\n",
      "(u'takeaway', 97)\n",
      "(u'drive_through', 94)\n",
      "(u'description', 93)\n",
      "(u'toll_method', 83)\n",
      "(u'transformer', 78)\n",
      "(u'en', 77)\n",
      "(u'designation', 76)\n",
      "(u'wheelchair', 76)\n",
      "(u'atm', 63)\n",
      "(u'email', 61)\n",
      "(u'historic', 60)\n",
      "(u'delivery', 58)\n",
      "(u'sport', 57)\n",
      "(u'motorcar', 56)\n",
      "(u'artwork', 54)\n",
      "(u'outdoor_seating', 54)\n",
      "(u'motorcycle', 48)\n",
      "(u'wikipedia', 47)\n",
      "(u'leaf_type', 45)\n",
      "(u'maxspeed', 45)\n",
      "(u'smoking', 45)\n",
      "(u'shelter', 41)\n",
      "(u'surface', 40)\n",
      "(u'housename', 39)\n",
      "(u'covered', 38)\n",
      "(u'capacity', 37)\n",
      "(u'internet_access', 37)\n",
      "(u'leaf_cycle', 37)\n",
      "(u'unit', 36)\n",
      "(u'transition', 35)\n",
      "(u'postal_code', 34)\n",
      "(u'wikidata', 33)\n",
      "(u'fee', 32)\n",
      "(u'indoor', 32)\n",
      "(u'credit_cards', 25)\n",
      "(u'alt_name', 24)\n",
      "(u'country', 24)\n",
      "(u'network', 24)\n",
      "(u'parking', 24)\n",
      "(u'craft', 23)\n",
      "(u'fax', 23)\n",
      "(u'suite', 21)\n",
      "(u'bitcoin', 17)\n",
      "(u'dispensing', 17)\n",
      "(u'disposal', 16)\n",
      "(u'public_transport', 15)\n",
      "(u'junction', 14)\n",
      "(u'method', 14)\n",
      "(u'voltage', 14)\n",
      "(u'advertising', 13)\n",
      "(u'frequency', 13)\n",
      "(u'crossing_ref', 12)\n",
      "(u'shelter_type', 12)\n",
      "(u'type1', 12)\n",
      "(u'vending', 12)\n",
      "(u'owner', 11)\n",
      "(u'collection_times', 10)\n",
      "(u'odbl', 10)\n",
      "(u'information', 9)\n",
      "(u'button_operated', 8)\n",
      "(u'car_wash', 8)\n",
      "(u'diesel', 8)\n",
      "(u'full', 8)\n",
      "(u'golf', 8)\n",
      "(u'side', 8)\n",
      "(u'social_facility', 8)\n",
      "(u'tee', 8)\n",
      "(u'url', 8)\n",
      "(u'bench', 7)\n",
      "(u'bicycle:rental', 7)\n",
      "(u'for', 7)\n",
      "(u'litecoin', 7)\n",
      "(u'noref', 7)\n",
      "(u'recycling_type', 7)\n",
      "(u'self_service', 7)\n",
      "(u'vehicle', 7)\n",
      "(u'bell', 6)\n",
      "(u'bus', 6)\n",
      "(u'cans', 6)\n",
      "(u'cash', 6)\n",
      "(u'category', 6)\n",
      "(u'clothes', 6)\n",
      "(u'forward', 6)\n",
      "(u'light', 6)\n",
      "(u'name_1', 6)\n",
      "(u'route_ref', 6)\n",
      "(u'street_lamp', 6)\n",
      "(u'voltage-high', 6)\n",
      "(u'wifi', 6)\n",
      "(u'bicycle:chain_tool', 5)\n",
      "(u'chademo', 5)\n",
      "(u'iata', 5)\n",
      "(u'icao', 5)\n",
      "(u'levels', 5)\n",
      "(u'nrhp', 5)\n",
      "(u'octane_91', 5)\n",
      "(u'start_date', 5)\n",
      "(u'airmark', 4)\n",
      "(u'automated', 4)\n",
      "(u'biodiesel', 4)\n",
      "(u'biogas', 4)\n",
      "(u'coins', 4)\n",
      "(u'female', 4)\n",
      "(u'ford', 4)\n",
      "(u'height', 4)\n",
      "(u'hours', 4)\n",
      "(u'kerb', 4)\n",
      "(u'keywords', 4)\n",
      "(u'left', 4)\n",
      "(u'male', 4)\n",
      "(u'mastercard', 4)\n",
      "(u'memorial', 4)\n",
      "(u'notes', 4)\n",
      "(u'old_name', 4)\n",
      "(u'paper', 4)\n",
      "(u'pipeline', 4)\n",
      "(u'plastic', 4)\n",
      "(u'restriction', 4)\n",
      "(u'ru', 4)\n",
      "(u'sound', 4)\n",
      "(u'substance', 4)\n",
      "(u'toll', 4)\n",
      "(u'visa', 4)\n",
      "(u'american_express', 3)\n",
      "(u'compensator', 3)\n",
      "(u'construction', 3)\n",
      "(u'discover_card', 3)\n",
      "(u'disused', 3)\n",
      "(u'electricity', 3)\n",
      "(u'faa', 3)\n",
      "(u'lit', 3)\n",
      "(u'message', 3)\n",
      "(u'microbrewery', 3)\n",
      "(u'mooring', 3)\n",
      "(u'motor_vehicle', 3)\n",
      "(u'octane_95', 3)\n",
      "(u'octane_98', 3)\n",
      "(u'payments', 3)\n",
      "(u'seats', 3)\n",
      "(u'smoothness', 3)\n",
      "(u'stop', 3)\n",
      "(u'supervised', 3)\n",
      "(u'tactile_paving', 3)\n",
      "(u'ufid', 3)\n",
      "(u'uk', 3)\n",
      "(u'backward', 2)\n",
      "(u'bicycle:repair', 2)\n",
      "(u'cng', 2)\n",
      "(u'collection_time', 2)\n",
      "(u'concrete', 2)\n",
      "(u'continent', 2)\n",
      "(u'demolished', 2)\n",
      "(u'denotation', 2)\n",
      "(u'destination', 2)\n",
      "(u'e10', 2)\n",
      "(u'e85', 2)\n",
      "(u'facebook', 2)\n",
      "(u'fuel:1_25', 2)\n",
      "(u'fuel:1_50', 2)\n",
      "(u'google_plus', 2)\n",
      "(u'gtl_diesel', 2)\n",
      "(u'healthcare', 2)\n",
      "(u'hgv_diesel', 2)\n",
      "(u'ko', 2)\n",
      "(u'location', 2)\n",
      "(u'lpg', 2)\n",
      "(u'manhole', 2)\n",
      "(u'octane_100', 2)\n",
      "(u'park_ride', 2)\n",
      "(u'parking_space', 2)\n",
      "(u'scrap_metal', 2)\n",
      "(u'short_name', 2)\n",
      "(u'sloped_curb', 2)\n",
      "(u'small_appliances', 2)\n",
      "(u'sou', 2)\n",
      "(u'status', 2)\n",
      "(u'substation', 2)\n",
      "(u'ta', 2)\n",
      "(u'tesla_standard', 2)\n",
      "(u'unisex', 2)\n",
      "(u'youtube', 2)\n",
      "(u'zh', 2)\n",
      "(u'admin_level', 1)\n",
      "(u'aluminium', 1)\n",
      "(u'am', 1)\n",
      "(u'ar', 1)\n",
      "(u'area', 1)\n",
      "(u'art', 1)\n",
      "(u'artist', 1)\n",
      "(u'attraction', 1)\n",
      "(u'attribution', 1)\n",
      "(u'az', 1)\n",
      "(u'batteries', 1)\n",
      "(u'be', 1)\n",
      "(u'beverage_cartons', 1)\n",
      "(u'bg', 1)\n",
      "(u'bicycle:cleaning', 1)\n",
      "(u'bicycle:diy', 1)\n",
      "(u'bicycle:pump', 1)\n",
      "(u'bicycle:retail', 1)\n",
      "(u'bicycle:second_hand', 1)\n",
      "(u'bicycle_parking', 1)\n",
      "(u'bin', 1)\n",
      "(u'bn', 1)\n",
      "(u'books', 1)\n",
      "(u'border_type', 1)\n",
      "(u'boundary', 1)\n",
      "(u'cardboard', 1)\n",
      "(u'cartons', 1)\n",
      "(u'ce', 1)\n",
      "(u'check', 1)\n",
      "(u'christmas_trees', 1)\n",
      "(u'closest_town', 1)\n",
      "(u'club', 1)\n",
      "(u'country_code', 1)\n",
      "(u'cryptocurrencies', 1)\n",
      "(u'cuisine_1', 1)\n",
      "(u'date', 1)\n",
      "(u'day_care', 1)\n",
      "(u'diaper', 1)\n",
      "(u'drinking_water', 1)\n",
      "(u'drive_in', 1)\n",
      "(u'el', 1)\n",
      "(u'electrified', 1)\n",
      "(u'electronic_purses', 1)\n",
      "(u'engine_oil', 1)\n",
      "(u'eo', 1)\n",
      "(u'es', 1)\n",
      "(u'fa', 1)\n",
      "(u'fcc_asrn_ref', 1)\n",
      "(u'floor', 1)\n",
      "(u'foil', 1)\n",
      "(u'food', 1)\n",
      "(u'food_pantry', 1)\n",
      "(u'fr', 1)\n",
      "(u'fridge_and_freezer', 1)\n",
      "(u'garden_waste', 1)\n",
      "(u'gasoline', 1)\n",
      "(u'genre', 1)\n",
      "(u'glass', 1)\n",
      "(u'glass_bottles', 1)\n",
      "(u'green_waste', 1)\n",
      "(u'gu', 1)\n",
      "(u'hairdresser', 1)\n",
      "(u'he', 1)\n",
      "(u'hi', 1)\n",
      "(u'hoops', 1)\n",
      "(u'horse', 1)\n",
      "(u'hr', 1)\n",
      "(u'hy', 1)\n",
      "(u'intermittent', 1)\n",
      "(u'ja', 1)\n",
      "(u'ka', 1)\n",
      "(u'key', 1)\n",
      "(u'keyword', 1)\n",
      "(u'kk', 1)\n",
      "(u'kn', 1)\n",
      "(u'ku', 1)\n",
      "(u'kw', 1)\n",
      "(u'la', 1)\n",
      "(u'landmark:category', 1)\n",
      "(u'lanes', 1)\n",
      "(u'lawyer', 1)\n",
      "(u'layer', 1)\n",
      "(u'lid_code', 1)\n",
      "(u'linen', 1)\n",
      "(u'lt', 1)\n",
      "(u'lv', 1)\n",
      "(u'magazines', 1)\n",
      "(u'mattress', 1)\n",
      "(u'medical_clinic', 1)\n",
      "(u'mk', 1)\n",
      "(u'ml', 1)\n",
      "(u'mobile_phones', 1)\n",
      "(u'monitoring_station', 1)\n",
      "(u'mr', 1)\n",
      "(u'my', 1)\n",
      "(u'nails', 1)\n",
      "(u'name_type', 1)\n",
      "(u'ne', 1)\n",
      "(u'newspaper', 1)\n",
      "(u'old_ref', 1)\n",
      "(u'os', 1)\n",
      "(u'pa', 1)\n",
      "(u'paper_packaging', 1)\n",
      "(u'payment', 1)\n",
      "(u'pkey', 1)\n",
      "(u'plastic_bags', 1)\n",
      "(u'plastic_bottles', 1)\n",
      "(u'playground', 1)\n",
      "(u'position', 1)\n",
      "(u'primary', 1)\n",
      "(u'printer_cartridges', 1)\n",
      "(u'radar', 1)\n",
      "(u'rai', 1)\n",
      "(u'sculpture', 1)\n",
      "(u'secondary', 1)\n",
      "(u'service', 1)\n",
      "(u'services', 1)\n",
      "(u'sheet_metal', 1)\n",
      "(u'shoes', 1)\n",
      "(u'sl', 1)\n",
      "(u'sport_1', 1)\n",
      "(u'sr', 1)\n",
      "(u'stars', 1)\n",
      "(u'state_code', 1)\n",
      "(u'street_1', 1)\n",
      "(u'street_cabinet', 1)\n",
      "(u'te', 1)\n",
      "(u'text', 1)\n",
      "(u'th', 1)\n",
      "(u'tl', 1)\n",
      "(u'trade', 1)\n",
      "(u'tv_monitor', 1)\n",
      "(u'tyres', 1)\n",
      "(u'ug', 1)\n",
      "(u'ur', 1)\n",
      "(u'uz', 1)\n",
      "(u'vi', 1)\n",
      "(u'waste_oil', 1)\n",
      "(u'white_goods', 1)\n",
      "(u'yi', 1)\n",
      "(u'zipcode', 1)\n"
     ]
    }
   ],
   "source": [
    "# What are in the nodes tags \n",
    "query = '''\n",
    "SELECT DISTINCT key, COUNT(*) as n\n",
    "FROM nodes_tags \n",
    "GROUP BY key\n",
    "ORDER BY n DESC;\n",
    "'''\n",
    "c.execute(query)\n",
    "for row in c:\n",
    "    print row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'highway', 281825)\n",
      "(u'county', 164036)\n",
      "(u'cfcc', 163022)\n",
      "(u'reviewed', 135616)\n",
      "(u'building', 110847)\n",
      "(u'street', 110226)\n",
      "(u'name_type', 94504)\n",
      "(u'source', 90340)\n",
      "(u'tlid', 71842)\n",
      "(u'zip_left', 71749)\n",
      "(u'zip_right', 67196)\n",
      "(u'separated', 65311)\n",
      "(u'oneway', 51782)\n",
      "(u'name', 47668)\n",
      "(u'service', 41148)\n",
      "(u'upload_uuid', 33226)\n",
      "(u'surface', 27063)\n",
      "(u'lanes', 24296)\n",
      "(u'name_base_1', 15167)\n",
      "(u'access', 12707)\n",
      "(u'ref', 11317)\n",
      "(u'amenity', 9385)\n",
      "(u'layer', 7781)\n",
      "(u'name_type_1', 7689)\n",
      "(u'name_1', 7396)\n",
      "(u'landuse', 7352)\n",
      "(u'bridge', 7226)\n",
      "(u'railway', 7112)\n",
      "(u'natural', 6408)\n",
      "(u'gauge', 6155)\n",
      "(u'leisure', 5932)\n",
      "(u'city', 4817)\n",
      "(u'state', 4482)\n",
      "(u'maxspeed', 3941)\n",
      "(u'old_ref', 3714)\n",
      "(u'waterway', 3459)\n",
      "(u'zip_left_1', 3354)\n",
      "(u'foot', 3221)\n",
      "(u'housenumber', 3175)\n",
      "(u'bicycle', 3020)\n",
      "(u'sport', 2582)\n",
      "(u'operator', 2576)\n",
      "(u'postcode', 2477)\n",
      "(u'footway', 2297)\n",
      "(u'note', 2242)\n",
      "(u'name_base_2', 2175)\n",
      "(u'power', 2108)\n",
      "(u'is_in', 2062)\n",
      "(u'hgv', 1858)\n",
      "(u'parking', 1852)\n",
      "(u'voltage', 1733)\n",
      "(u'frequency', 1644)\n",
      "(u'state_id', 1619)\n",
      "(u'toll', 1581)\n",
      "(u'nhs', 1435)\n",
      "(u'lit', 1417)\n",
      "(u'barrier', 1392)\n",
      "(u'zip_right_1', 1316)\n",
      "(u'sidewalk', 1294)\n",
      "(u'old_railway_operator', 1267)\n",
      "(u'name_type_2', 1245)\n",
      "(u'golf', 1221)\n",
      "(u'cables', 1205)\n",
      "(u'name_direction_prefix_1', 1194)\n",
      "(u'aeroway', 1188)\n",
      "(u'pass_route', 1159)\n",
      "(u'forward', 1145)\n",
      "(u'backward', 1107)\n",
      "(u'ele', 1082)\n",
      "(u'fixme', 1063)\n",
      "(u'circuits', 1030)\n",
      "(u'tunnel', 1008)\n",
      "(u'feature_id', 995)\n",
      "(u'cycleway', 965)\n",
      "(u'shop', 928)\n",
      "(u'loc_name', 919)\n",
      "(u'created', 901)\n",
      "(u'name_direction_suffix_1', 900)\n",
      "(u'toll_method', 896)\n",
      "(u'county_id', 886)\n",
      "(u'water', 872)\n",
      "(u'lanes:forward', 857)\n",
      "(u'hov', 836)\n",
      "(u'lanes:backward', 824)\n",
      "(u'usage', 781)\n",
      "(u'texas_trunk_system', 778)\n",
      "(u'boundary', 741)\n",
      "(u'admin_level', 696)\n",
      "(u'destination', 672)\n",
      "(u'level', 663)\n",
      "(u'mtfcc', 619)\n",
      "(u'electrified', 601)\n",
      "(u'border_type', 586)\n",
      "(u'motor_vehicle', 567)\n",
      "(u'name_2', 558)\n",
      "(u'old_name', 552)\n",
      "(u'zip_left_2', 548)\n",
      "(u'indoor', 541)\n",
      "(u'area', 524)\n",
      "(u'website', 517)\n",
      "(u'phone', 494)\n",
      "(u'content', 485)\n",
      "(u'hazmat', 470)\n",
      "(u'segregated', 455)\n",
      "(u'postal_code', 433)\n",
      "(u'levels', 431)\n",
      "(u'cuisine', 426)\n",
      "(u'height', 410)\n",
      "(u'location', 372)\n",
      "(u'residential', 352)\n",
      "(u'created_by', 345)\n",
      "(u'crossing', 344)\n",
      "(u'substation', 334)\n",
      "(u'name_base_3', 332)\n",
      "(u'source_ref', 330)\n",
      "(u'national_network', 320)\n",
      "(u'attribution', 317)\n",
      "(u'horse', 302)\n",
      "(u'country', 300)\n",
      "(u'proposed', 299)\n",
      "(u'segment', 299)\n",
      "(u'religion', 287)\n",
      "(u'place', 286)\n",
      "(u'tourism', 285)\n",
      "(u'history', 278)\n",
      "(u'rttyp', 260)\n",
      "(u'name_direction_prefix', 257)\n",
      "(u'placement', 225)\n",
      "(u'junction', 219)\n",
      "(u'fee', 215)\n",
      "(u'zip_right_2', 213)\n",
      "(u'line', 210)\n",
      "(u'zh', 206)\n",
      "(u'zh_pinyin', 206)\n",
      "(u'description', 201)\n",
      "(u'fcode', 196)\n",
      "(u'alt_name', 191)\n",
      "(u'name_direction_suffix', 191)\n",
      "(u'name_direction_suffix_2', 189)\n",
      "(u'denomination', 184)\n",
      "(u'expressway', 182)\n",
      "(u'odbl', 182)\n",
      "(u'width', 182)\n",
      "(u'noname', 178)\n",
      "(u'construction', 171)\n",
      "(u'name_direction_prefix_2', 168)\n",
      "(u'zip_left_3', 160)\n",
      "(u'wheelchair', 159)\n",
      "(u'park_ride', 143)\n",
      "(u'boat', 138)\n",
      "(u'fence_type', 138)\n",
      "(u'bus', 135)\n",
      "(u'wikipedia', 133)\n",
      "(u'opening_hours', 127)\n",
      "(u'embankment', 113)\n",
      "(u'disabled', 111)\n",
      "(u'lanes:both_ways', 108)\n",
      "(u'name_3', 104)\n",
      "(u'owner', 102)\n",
      "(u'reach_code', 101)\n",
      "(u'county_name', 95)\n",
      "(u'brand', 94)\n",
      "(u'basin', 93)\n",
      "(u'both_ways', 93)\n",
      "(u'name_type_3', 92)\n",
      "(u'scale', 85)\n",
      "(u'tracktype', 84)\n",
      "(u'colour', 83)\n",
      "(u'covered', 80)\n",
      "(u'capacity', 77)\n",
      "(u'import_uuid', 75)\n",
      "(u'classfp', 69)\n",
      "(u'cpi', 69)\n",
      "(u'funcstat', 69)\n",
      "(u'lsad', 69)\n",
      "(u'namelsad', 69)\n",
      "(u'pcicbsa', 69)\n",
      "(u'pcinecta', 69)\n",
      "(u'placefp', 69)\n",
      "(u'placens', 69)\n",
      "(u'plcidfp', 69)\n",
      "(u'statefp', 69)\n",
      "(u'zip_right_3', 67)\n",
      "(u'part', 65)\n",
      "(u'trail_visibility', 62)\n",
      "(u'scale:uphill', 61)\n",
      "(u'supervised', 61)\n",
      "(u'toll:hov', 61)\n",
      "(u'cutting', 57)\n",
      "(u'image', 57)\n",
      "(u'drive_through', 53)\n",
      "(u'scale:imba', 53)\n",
      "(u'office', 51)\n",
      "(u'parent', 51)\n",
      "(u'women', 51)\n",
      "(u'shape', 50)\n",
      "(u'sac_scale', 49)\n",
      "(u'wikidata', 48)\n",
      "(u'fut_ref', 47)\n",
      "(u'handicap', 45)\n",
      "(u'par', 45)\n",
      "(u'smoking', 45)\n",
      "(u'landcover', 40)\n",
      "(u'lane:right', 40)\n",
      "(u'wall', 40)\n",
      "(u'attraction', 39)\n",
      "(u'zip', 39)\n",
      "(u'name_base_4', 37)\n",
      "(u'takeaway', 37)\n",
      "(u'atm', 35)\n",
      "(u'tracks', 34)\n",
      "(u'zip_left_4', 34)\n",
      "(u'type', 33)\n",
      "(u'shoulder', 32)\n",
      "(u'historic', 31)\n",
      "(u'official_name', 31)\n",
      "(u'length', 30)\n",
      "(u'shelter_type', 30)\n",
      "(u'housename', 28)\n",
      "(u'smoothness', 28)\n",
      "(u'golf_cart', 27)\n",
      "(u'name_type_4', 27)\n",
      "(u'nine', 27)\n",
      "(u'room', 27)\n",
      "(u'short_name', 27)\n",
      "(u'conditional', 26)\n",
      "(u'start_date', 26)\n",
      "(u'outdoor_seating', 25)\n",
      "(u'output:electricity', 25)\n",
      "(u'wood', 25)\n",
      "(u'emergency', 24)\n",
      "(u'fax', 24)\n",
      "(u'name:2', 24)\n",
      "(u'pre_1939', 24)\n",
      "(u'name:1', 23)\n",
      "(u'feature_type', 22)\n",
      "(u'kerb', 22)\n",
      "(u'public_transport', 22)\n",
      "(u'vehicle', 22)\n",
      "(u'disused', 20)\n",
      "(u'inclusion', 20)\n",
      "(u'interpolation', 20)\n",
      "(u'stairs', 19)\n",
      "(u'bench', 18)\n",
      "(u'black', 18)\n",
      "(u'blue', 18)\n",
      "(u'designation', 18)\n",
      "(u'edited', 18)\n",
      "(u'red', 18)\n",
      "(u'white', 18)\n",
      "(u'advisory', 17)\n",
      "(u'concrete', 17)\n",
      "(u'delivery', 17)\n",
      "(u'disposal', 16)\n",
      "(u'intermittent', 16)\n",
      "(u'method', 16)\n",
      "(u'motorroad', 16)\n",
      "(u'unsigned_ref', 16)\n",
      "(u'com_id', 15)\n",
      "(u'incline', 15)\n",
      "(u'wetland', 15)\n",
      "(u'drinking_water', 14)\n",
      "(u'driveway', 14)\n",
      "(u'route_ref', 14)\n",
      "(u'bicycle_road', 13)\n",
      "(u'boundary_type', 13)\n",
      "(u'diesel', 13)\n",
      "(u'informal', 13)\n",
      "(u'email', 12)\n",
      "(u'internet_access', 12)\n",
      "(u'min_height', 12)\n",
      "(u'route', 12)\n",
      "(u'runway', 12)\n",
      "(u'ship', 11)\n",
      "(u'slope:direction', 11)\n",
      "(u'zip_right_4', 11)\n",
      "(u'crop', 10)\n",
      "(u'distance', 10)\n",
      "(u'icao', 10)\n",
      "(u'name_direction_prefix_3', 10)\n",
      "(u'water_slide', 10)\n",
      "(u'abandoned', 9)\n",
      "(u'en', 9)\n",
      "(u'fips_code', 9)\n",
      "(u'motorcar', 9)\n",
      "(u'nrhp', 9)\n",
      "(u'playground', 9)\n",
      "(u'social_facility', 9)\n",
      "(u'state_fips', 9)\n",
      "(u'deep_draft', 8)\n",
      "(u'hoops', 8)\n",
      "(u'reservoir_type', 8)\n",
      "(u'tactile_paving', 8)\n",
      "(u'unisex', 8)\n",
      "(u'craft', 7)\n",
      "(u'for', 7)\n",
      "(u'heritage', 7)\n",
      "(u'iata', 7)\n",
      "(u'left', 7)\n",
      "(u'material', 7)\n",
      "(u'maxheight', 7)\n",
      "(u'substance', 7)\n",
      "(u'country_code', 6)\n",
      "(u'dispensing', 6)\n",
      "(u'iso_3166_2', 6)\n",
      "(u'leaf_cycle', 6)\n",
      "(u'octane_91', 6)\n",
      "(u'octane_95', 6)\n",
      "(u'private', 6)\n",
      "(u'psv', 6)\n",
      "(u'state_code', 6)\n",
      "(u'bin', 5)\n",
      "(u'information', 5)\n",
      "(u'maritime', 5)\n",
      "(u'name_direction_suffix_3', 5)\n",
      "(u'pedestrians', 5)\n",
      "(u'ramp', 5)\n",
      "(u'service_times', 5)\n",
      "(u'unit', 5)\n",
      "(u'_linearid_', 4)\n",
      "(u'class', 4)\n",
      "(u'demolished', 4)\n",
      "(u'diaper', 4)\n",
      "(u'direction', 4)\n",
      "(u'leaf_type', 4)\n",
      "(u'leisure_1', 4)\n",
      "(u'minspeed', 4)\n",
      "(u'octane_98', 4)\n",
      "(u'recycling_type', 4)\n",
      "(u'right', 4)\n",
      "(u'url', 4)\n",
      "(u'both', 3)\n",
      "(u'car_wash', 3)\n",
      "(u'collection_times', 3)\n",
      "(u'comment', 3)\n",
      "(u'county_num', 3)\n",
      "(u'dock', 3)\n",
      "(u'e85', 3)\n",
      "(u'floating', 3)\n",
      "(u'full', 3)\n",
      "(u'heading', 3)\n",
      "(u'highway_1', 3)\n",
      "(u'highway_2', 3)\n",
      "(u'id', 3)\n",
      "(u'landuse_1', 3)\n",
      "(u'maxstay', 3)\n",
      "(u'motorboat', 3)\n",
      "(u'movable', 3)\n",
      "(u'multi-story', 3)\n",
      "(u'occurrence', 3)\n",
      "(u'octane_80', 3)\n",
      "(u'old', 3)\n",
      "(u'orientation', 3)\n",
      "(u'shelter', 3)\n",
      "(u'st_alpha', 3)\n",
      "(u'st_num', 3)\n",
      "(u'step_count', 3)\n",
      "(u'ufid', 3)\n",
      "(u'address', 2)\n",
      "(u'baseball', 2)\n",
      "(u'bicycle:rental', 2)\n",
      "(u'biodiesel', 2)\n",
      "(u'biogas', 2)\n",
      "(u'canoe', 2)\n",
      "(u'cans', 2)\n",
      "(u'cng', 2)\n",
      "(u'drive_in', 2)\n",
      "(u'e10', 2)\n",
      "(u'electricity', 2)\n",
      "(u'ethanol', 2)\n",
      "(u'fuel:1_25', 2)\n",
      "(u'fuel:1_50', 2)\n",
      "(u'gtl_diesel', 2)\n",
      "(u'hgv_diesel', 2)\n",
      "(u'lane:left', 2)\n",
      "(u'layer1', 2)\n",
      "(u'link', 2)\n",
      "(u'lpg', 2)\n",
      "(u'military', 2)\n",
      "(u'min_level', 2)\n",
      "(u'name1', 2)\n",
      "(u'noexit', 2)\n",
      "(u'octane_100', 2)\n",
      "(u'paper', 2)\n",
      "(u'produce', 2)\n",
      "(u'product', 2)\n",
      "(u'scrap_metal', 2)\n",
      "(u'sport_1', 2)\n",
      "(u'toilets', 2)\n",
      "(u'trees', 2)\n",
      "(u'use', 2)\n",
      "(u'waterway_1', 2)\n",
      "(u'yes', 2)\n",
      "(u'aefe', 1)\n",
      "(u'alt', 1)\n",
      "(u'aluminium', 1)\n",
      "(u'amenity_1', 1)\n",
      "(u'american_express', 1)\n",
      "(u'base_name', 1)\n",
      "(u'batteries', 1)\n",
      "(u'beverage_cartons', 1)\n",
      "(u'bicycle:repair', 1)\n",
      "(u'bicycle:retail', 1)\n",
      "(u'books', 1)\n",
      "(u'brewery', 1)\n",
      "(u'cardboard', 1)\n",
      "(u'cartons', 1)\n",
      "(u'cash', 1)\n",
      "(u'center', 1)\n",
      "(u'check_date', 1)\n",
      "(u'city_served', 1)\n",
      "(u'closest_town', 1)\n",
      "(u'clothes', 1)\n",
      "(u'coins', 1)\n",
      "(u'commercial', 1)\n",
      "(u'created:old', 1)\n",
      "(u'date', 1)\n",
      "(u'demlished', 1)\n",
      "(u'designer', 1)\n",
      "(u'discover_card', 1)\n",
      "(u'district', 1)\n",
      "(u'engine_oil', 1)\n",
      "(u'es', 1)\n",
      "(u'faa', 1)\n",
      "(u'feature_id:old', 1)\n",
      "(u'female', 1)\n",
      "(u'fence', 1)\n",
      "(u'fenced', 1)\n",
      "(u'foil', 1)\n",
      "(u'ford', 1)\n",
      "(u'fr', 1)\n",
      "(u'garden', 1)\n",
      "(u'geometry', 1)\n",
      "(u'gigh', 1)\n",
      "(u'glass', 1)\n",
      "(u'glass_bottles', 1)\n",
      "(u'grade', 1)\n",
      "(u'grass', 1)\n",
      "(u'handrail', 1)\n",
      "(u'hole', 1)\n",
      "(u'inscription', 1)\n",
      "(u'key', 1)\n",
      "(u'lane:left:capacity', 1)\n",
      "(u'magazines', 1)\n",
      "(u'male', 1)\n",
      "(u'mascot', 1)\n",
      "(u'mastercard', 1)\n",
      "(u'max_level', 1)\n",
      "(u'mobile_phones', 1)\n",
      "(u'mown', 1)\n",
      "(u'nab', 1)\n",
      "(u'name_direction_prefixss', 1)\n",
      "(u'nationalregisterofhistoricplaces_ref', 1)\n",
      "(u'neaten', 1)\n",
      "(u'network', 1)\n",
      "(u'newspaper', 1)\n",
      "(u'notes', 1)\n",
      "(u'objectid', 1)\n",
      "(u'octane_92', 1)\n",
      "(u'old_name_1', 1)\n",
      "(u'paper_packaging', 1)\n",
      "(u'park', 1)\n",
      "(u'pedestrian', 1)\n",
      "(u'plastic_bottles', 1)\n",
      "(u'power_supply', 1)\n",
      "(u'ref:to', 1)\n",
      "(u'reg_name', 1)\n",
      "(u'reservoir', 1)\n",
      "(u'rooms', 1)\n",
      "(u'rpm', 1)\n",
      "(u'sanitary_dump_station', 1)\n",
      "(u'school', 1)\n",
      "(u'seasonal', 1)\n",
      "(u'second_hand', 1)\n",
      "(u'sheet_metal', 1)\n",
      "(u'shooting', 1)\n",
      "(u'small_appliances', 1)\n",
      "(u'spirce', 1)\n",
      "(u'street_1', 1)\n",
      "(u'style', 1)\n",
      "(u'taxiway', 1)\n",
      "(u'the', 1)\n",
      "(u'theatre', 1)\n",
      "(u'tidal', 1)\n",
      "(u'tourist_bus', 1)\n",
      "(u'traffic_calming', 1)\n",
      "(u'tv_monitor', 1)\n",
      "(u'twitter', 1)\n",
      "(u'tyres', 1)\n",
      "(u'visa', 1)\n",
      "(u'waste_oil', 1)\n"
     ]
    }
   ],
   "source": [
    "# What are in the ways tags \n",
    "query = '''\n",
    "SELECT DISTINCT key, COUNT(*) as n\n",
    "FROM ways_tags \n",
    "GROUP BY key\n",
    "ORDER BY n DESC;\n",
    "'''\n",
    "c.execute(query)\n",
    "for row in c:\n",
    "    print row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'pitch', 2503)\n",
      "(u'swimming_pool', 1322)\n",
      "(u'park', 1258)\n",
      "(u'playground', 773)\n",
      "(u'sports_centre', 135)\n",
      "(u'golf_course', 85)\n",
      "(u'garden', 75)\n",
      "(u'stadium', 69)\n",
      "(u'track', 68)\n",
      "(u'picnic_table', 59)\n"
     ]
    }
   ],
   "source": [
    "#Most Popular Leisure types\n",
    "query = '''\n",
    "SELECT a.value, COUNT(*) as n\n",
    "FROM (SELECT key, value FROM nodes_tags UNION ALL SELECT key, value FROM ways_tags) as a\n",
    "WHERE a.key ='leisure'\n",
    "GROUP BY a.value\n",
    "ORDER BY n DESC\n",
    "Limit 10;\n",
    "'''\n",
    "\n",
    "c.execute(query)\n",
    "for row in c:\n",
    "    print row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'water', 3881)\n",
      "(u'tree', 2032)\n",
      "(u'wood', 1282)\n",
      "(u'sand', 465)\n",
      "(u'coastline', 429)\n",
      "(u'wetland', 185)\n",
      "(u'tree_row', 119)\n",
      "(u'scrub', 79)\n",
      "(u'bay', 56)\n",
      "(u'beach', 43)\n"
     ]
    }
   ],
   "source": [
    "#Natural?\n",
    "query = '''\n",
    "SELECT a.value, COUNT(*) as n\n",
    "FROM (SELECT key, value FROM nodes_tags UNION ALL SELECT key, value FROM ways_tags) as a\n",
    "WHERE a.key ='natural'\n",
    "GROUP BY a.value\n",
    "ORDER BY n DESC\n",
    "Limit 10;\n",
    "'''\n",
    "\n",
    "c.execute(query)\n",
    "for row in c:\n",
    "    print row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Most popular shops\n",
    "query = '''\n",
    "SELECT DISTINCT value, COUNT(*) as n\n",
    "FROM nodes_tags \n",
    "WHERE key ='shop'\n",
    "GROUP BY value\n",
    "ORDER BY n DESC\n",
    "Limit 10;\n",
    "'''\n",
    "c.execute(query)\n",
    "for row in c:\n",
    "    print row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'Jason Street', 94)\n",
      "(u'Kingwood Drive', 84)\n",
      "(u'Holly Street', 71)\n",
      "(u'Braesvalley Drive', 56)\n",
      "(u'Jackwood Street', 51)\n",
      "(u'Imogene Street', 50)\n",
      "(u'NASA Parkway', 50)\n",
      "(u'North Braeswood Boulevard', 49)\n",
      "(u'Westheimer Road', 47)\n",
      "(u'Indigo Street', 37)\n"
     ]
    }
   ],
   "source": [
    "# top street names\n",
    "query = '''\n",
    "SELECT value, COUNT(*) as num\n",
    "FROM nodes_tags\n",
    "WHERE key='street'\n",
    "GROUP BY value\n",
    "ORDER BY num DESC\n",
    "LIMIT 1;\n",
    "'''\n",
    "c.execute(query)\n",
    "for row in c:\n",
    "    print row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'Jason Street', u'house', 93)\n",
      "(u'Kingwood Drive', u'Randalls Pharmacy', 2)\n",
      "(u'Kingwood Drive', u'TGF Haircutters', 2)\n",
      "(u'Kingwood Drive', u'Amegy Bank', 1)\n",
      "(u'Kingwood Drive', u'Baskin Robbins', 1)\n",
      "(u'Kingwood Drive', u'Bike Werks', 1)\n",
      "(u'Kingwood Drive', u\"Carrabba's Italian Grill\", 1)\n",
      "(u'Kingwood Drive', u'Chase', 1)\n",
      "(u'Kingwood Drive', u'Chelsea Deli & Cafe', 1)\n",
      "(u'Kingwood Drive', u\"Chico's\", 1)\n",
      "(u'Kingwood Drive', u\"Children's Place\", 1)\n",
      "(u'Kingwood Drive', u\"Cici's Pizza\", 1)\n",
      "(u'Kingwood Drive', u'Cold Stone Creamery', 1)\n",
      "(u'Kingwood Drive', u'Cyrese & Co', 1)\n",
      "(u'Kingwood Drive', u'Darque Tan', 1)\n",
      "(u'Kingwood Drive', u\"Dickey's Barbeque Pit\", 1)\n",
      "(u'Kingwood Drive', u\"Domino's Pizza\", 1)\n",
      "(u'Kingwood Drive', u'Edward Jones Financial Office', 1)\n",
      "(u'Kingwood Drive', u'Embody Fitness Center', 1)\n",
      "(u'Kingwood Drive', u'Eye Level Kingwood-West', 1)\n",
      "(u'Kingwood Drive', u'Farmers Insurance', 1)\n",
      "(u'Kingwood Drive', u'Fouette Dancewear', 1)\n",
      "(u'Kingwood Drive', u'GameStop', 1)\n",
      "(u'Kingwood Drive', u'H-E-B', 1)\n",
      "(u'Kingwood Drive', u'H-E-B Pharmacy', 1)\n",
      "(u'Kingwood Drive', u'Hearing Aid Express', 1)\n",
      "(u'Kingwood Drive', u\"Hin's Garden\", 1)\n",
      "(u'Kingwood Drive', u'Hunan Garden Restaurant', 1)\n",
      "(u'Kingwood Drive', u'James Avery Jewelry', 1)\n",
      "(u'Kingwood Drive', u'Jos. A. Bank', 1)\n",
      "(u'Kingwood Drive', u'KW Shoe and Boot Repair', 1)\n",
      "(u'Kingwood Drive', u'Kings Crossing Dentistry', 1)\n",
      "(u'Kingwood Drive', u'Kingwood Nails', 1)\n",
      "(u'Kingwood Drive', u'Kingwood Spine and Wellness', 1)\n",
      "(u'Kingwood Drive', u'La Madeleine', 1)\n",
      "(u'Kingwood Drive', u'Loft', 1)\n",
      "(u'Kingwood Drive', u'MW Cleaners', 1)\n",
      "(u'Kingwood Drive', u'Made Ya Smile Dental', 1)\n",
      "(u'Kingwood Drive', u'Marble Slab Creamery', 1)\n",
      "(u'Kingwood Drive', u'Massage Envy', 1)\n",
      "(u'Kingwood Drive', u'Mattress 1 One', 1)\n",
      "(u'Kingwood Drive', u'MedSpring Urgent Care of Kingwood', 1)\n",
      "(u'Kingwood Drive', u'Minuti Coffee', 1)\n",
      "(u'Kingwood Drive', u'Oriental Gardens Chinese Restaurant', 1)\n",
      "(u'Kingwood Drive', u'Palais Royal', 1)\n",
      "(u'Kingwood Drive', u'Pei Wei', 1)\n",
      "(u'Kingwood Drive', u'Pet Ranch', 1)\n",
      "(u'Kingwood Drive', u'Petco', 1)\n",
      "(u'Kingwood Drive', u'Portrait Innovations', 1)\n",
      "(u'Kingwood Drive', u'PostNet', 1)\n",
      "(u'Kingwood Drive', u\"Rachael's Hallmark Shop\", 1)\n",
      "(u'Kingwood Drive', u\"Rachel's Hallmark Shop\", 1)\n",
      "(u'Kingwood Drive', u'Radio Shack', 1)\n",
      "(u'Kingwood Drive', u\"Ryan's Express Cleaners\", 1)\n",
      "(u'Kingwood Drive', u'S&A Nails', 1)\n",
      "(u'Kingwood Drive', u\"Schlotzsky's Deli\", 1)\n",
      "(u'Kingwood Drive', u'School of Rock', 1)\n",
      "(u'Kingwood Drive', u'Soma Intimates', 1)\n",
      "(u'Kingwood Drive', u\"Spec's Wine, Spirits & Finer Foods\", 1)\n",
      "(u'Kingwood Drive', u'Stein Mart', 1)\n",
      "(u'Kingwood Drive', u'Talbots', 1)\n",
      "(u'Kingwood Drive', u'The Center for Wound Care', 1)\n",
      "(u'Kingwood Drive', u'The Mint National Bank', 1)\n",
      "(u'Kingwood Drive', u'The Studio Salon', 1)\n",
      "(u'Kingwood Drive', u'The UPS Store', 1)\n",
      "(u'Kingwood Drive', u\"The Woman's Place Kingwood\", 1)\n",
      "(u'Kingwood Drive', u\"Three B's Grill\", 1)\n",
      "(u'Kingwood Drive', u'Town Center Family Dentistry', 1)\n",
      "(u'Kingwood Drive', u'Town Center Family Practice', 1)\n",
      "(u'Kingwood Drive', u'Toys & Candy On The Park', 1)\n",
      "(u'Kingwood Drive', u'UPS Store', 1)\n",
      "(u'Kingwood Drive', u'Verizon Wireless', 1)\n",
      "(u'Kingwood Drive', u'Vision Source - Kingwood', 1)\n",
      "(u'Kingwood Drive', u'Vitamins & Such', 1)\n",
      "(u'Kingwood Drive', u'Walgreens', 1)\n",
      "(u'Kingwood Drive', u'Weight Watchers', 1)\n",
      "(u'Kingwood Drive', u'Wells Fargo', 1)\n",
      "(u'Kingwood Drive', u\"Wendy's\", 1)\n",
      "(u'Kingwood Drive', u'Whataburger', 1)\n",
      "(u'Kingwood Drive', u'White House Black Market', 1)\n",
      "(u'Kingwood Drive', u'Willa Boutique', 1)\n",
      "(u'Kingwood Drive', u\"Zachary's Cajun Cafe\", 1)\n",
      "(u'Kingwood Drive', u\"francesca's\", 1)\n"
     ]
    }
   ],
   "source": [
    "#explore these streets\n",
    "query = '''\n",
    "SELECT b.value,a.value,COUNT(a.value) as num\n",
    "FROM nodes_tags as a,\n",
    "    (SELECT DISTINCT(id),value FROM nodes_tags WHERE key = 'street' and value IN \n",
    "    ('Jason Street','Kingwood Drive')) as b\n",
    "WHERE (a.key='name' or a.key='building')\n",
    "AND a.id =b.id\n",
    "GROUP BY a.value\n",
    "ORDER BY num DESC;\n",
    "\n",
    "'''\n",
    "c.execute(query)\n",
    "for row in c:\n",
    "    print row\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}