Vehicle Year, Make, and Model data in SQL, NoSQL data format since year 2001
Year, Make, and Model data of nearly all motor vehicle manufactured between 2001 and 2015, in sql, json, and csv format.
Accurate motor vehicle make & model data since year 2001. This data set includes Car, Motorcycle, Truck, and UTV manufactures and their corresponding models. The data is database agnostic and is user-friendly as same set of data is ported to mysql, json, and csv format. Json and csv data sets are flattened while mysql data set being normalized to 3 tables. Currently there are 19,722 models and increasing.
None
$ git clone https://github.com/arthurkao/vehicle-make-model-data.git $ cd ./vehicle-make-model-data
Replace myDBName with db name to your liking. Three tables, makes, make_years, and models , will be created with proper foreign key constraint(s).
$ mysql -uroot myDBName < mysql_data.sql
Replace myDBName with db name and myCollectionName with collection name to your liking.
$ mongoimport -d myDBName -c myCollectionName --jsonArray --file json_data.json
Open csv_data.csv in your favorite csv editor.
mysql> SELECT my.year, ma.name AS make, mo.name AS model FROM models mo JOIN make_years my on mo.makeyear_id = my.id JOIN makes ma on ma.id = my.make_id WHERE ma.name = 'BMW' and my.year = 2015 ORDER BY model;+------+------+-----------------------------+
| year | make | model |
+------+------+-----------------------------+
| 2015 | BMW | 118I |
| 2015 | BMW | 220I |
| 2015 | BMW | 228I |
| 2015 | BMW | 228I XDRIVE |
| 2015 | BMW | 320I |
| 2015 | BMW | 320I XDRIVE |
| 2015 | BMW | 328D |
| 2015 | BMW | 328D XDRIVE |
| 2015 | BMW | 328I |
| 2015 | BMW | 328I GT XDRIVE |
...
| 2015 | BMW | M3 |
| 2015 | BMW | M4 |
| 2015 | BMW | M5 |
| 2015 | BMW | M6 |
| 2015 | BMW | M6 GRAN COUPE |
| 2015 | BMW | X1 |
| 2015 | BMW | X3 |
| 2015 | BMW | X4 |
| 2015 | BMW | X5 |
| 2015 | BMW | X6 |
| 2015 | BMW | Z4 |
+------+------+-----------------------------+
77 rows in set (0.00 sec)
Replace collectionName with the collection name set during setup
> db.collectionName.find({ year: 2015, make: "BMW" });{ "_id" : ObjectId("5559d73bd4ad885f71d607c1"), "year" : 2015, "make" : "BMW", "model" : "118I" }
{ "_id" : ObjectId("5559d73bd4ad885f71d607c2"), "year" : 2015, "make" : "BMW", "model" : "220I" }
{ "_id" : ObjectId("5559d73bd4ad885f71d607c3"), "year" : 2015, "make" : "BMW", "model" : "228I" }
{ "_id" : ObjectId("5559d73bd4ad885f71d607c4"), "year" : 2015, "make" : "BMW", "model" : "228I XDRIVE" }
{ "_id" : ObjectId("5559d73bd4ad885f71d607c5"), "year" : 2015, "make" : "BMW", "model" : "320I" }
{ "_id" : ObjectId("5559d73bd4ad885f71d607c6"), "year" : 2015, "make" : "BMW", "model" : "320I XDRIVE" }
{ "_id" : ObjectId("5559d73bd4ad885f71d607c7"), "year" : 2015, "make" : "BMW", "model" : "328D" }
{ "_id" : ObjectId("5559d73bd4ad885f71d607c8"), "year" : 2015, "make" : "BMW", "model" : "328D XDRIVE" }
{ "_id" : ObjectId("5559d73bd4ad885f71d607c9"), "year" : 2015, "make" : "BMW", "model" : "328I" }
{ "_id" : ObjectId("5559d73bd4ad885f71d607ca"), "year" : 2015, "make" : "BMW", "model" : "328I GT XDRIVE" }
...
Type "it" for more
...
mysql> SELECT my.year, ma.name AS make, mo.name AS model FROM models mo JOIN make_years my on mo.makeyear_id = my.id JOIN makes ma on ma.id = my.make_id WHERE ma.name = 'BMW' and mo.name LIKE 'R1200GS%' ORDER BY my.year, model;+------+------+-------------------+
| year | make | model |
+------+------+-------------------+
| 2004 | BMW | R1200GS |
| 2005 | BMW | R1200GS |
| 2006 | BMW | R1200GS |
| 2006 | BMW | R1200GS ADVENTURE |
| 2006 | BMW | R1200GS HP2 |
| 2007 | BMW | R1200GS |
| 2007 | BMW | R1200GS ADVENTURE |
| 2007 | BMW | R1200GS HP2 |
| 2008 | BMW | R1200GS |
| 2008 | BMW | R1200GS ADVENTURE |
| 2009 | BMW | R1200GS |
| 2009 | BMW | R1200GS ADVENTURE |
| 2010 | BMW | R1200GS |
| 2010 | BMW | R1200GS ADVENTURE |
| 2011 | BMW | R1200GS |
| 2011 | BMW | R1200GS ADVENTURE |
| 2012 | BMW | R1200GS |
| 2012 | BMW | R1200GS ADVENTURE |
| 2013 | BMW | R1200GS |
| 2013 | BMW | R1200GS ADVENTURE |
| 2014 | BMW | R1200GS |
| 2014 | BMW | R1200GS ADVENTURE |
+------+------+-------------------+
22 rows in set (0.00 sec)
Replace collectionName with the collection name set during setup
> db.collectionName.find({ model: /^R1200GS/ });{ "_id" : ObjectId("5559d73bd4ad885f71d5cc7f"), "year" : 2004, "make" : "BMW", "model" : "R1200GS" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5d1ce"), "year" : 2005, "make" : "BMW", "model" : "R1200GS" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5d743"), "year" : 2006, "make" : "BMW", "model" : "R1200GS" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5d744"), "year" : 2006, "make" : "BMW", "model" : "R1200GS ADVENTURE" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5d745"), "year" : 2006, "make" : "BMW", "model" : "R1200GS HP2" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5dccb"), "year" : 2007, "make" : "BMW", "model" : "R1200GS" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5dccc"), "year" : 2007, "make" : "BMW", "model" : "R1200GS ADVENTURE" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5dccd"), "year" : 2007, "make" : "BMW", "model" : "R1200GS HP2" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5e27f"), "year" : 2008, "make" : "BMW", "model" : "R1200GS" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5e280"), "year" : 2008, "make" : "BMW", "model" : "R1200GS ADVENTURE" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5e853"), "year" : 2009, "make" : "BMW", "model" : "R1200GS" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5e854"), "year" : 2009, "make" : "BMW", "model" : "R1200GS ADVENTURE" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5eded"), "year" : 2010, "make" : "BMW", "model" : "R1200GS" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5edee"), "year" : 2010, "make" : "BMW", "model" : "R1200GS ADVENTURE" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5f309"), "year" : 2011, "make" : "BMW", "model" : "R1200GS" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5f30a"), "year" : 2011, "make" : "BMW", "model" : "R1200GS ADVENTURE" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5f831"), "year" : 2012, "make" : "BMW", "model" : "R1200GS" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5f832"), "year" : 2012, "make" : "BMW", "model" : "R1200GS ADVENTURE" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5fd68"), "year" : 2013, "make" : "BMW", "model" : "R1200GS" }
{ "_id" : ObjectId("5559d73bd4ad885f71d5fd69"), "year" : 2013, "make" : "BMW", "model" : "R1200GS ADVENTURE" }
Type "it" for more
mysql> SELECT count(id) AS num_of_active_manufacture FROM make_years WHERE year = 2015;+---------------------------+
| num_of_active_manufacture |
+---------------------------+
| 56 |
+---------------------------+
1 row in set (0.00 sec)
Replace collectionName with the collection name set during setup
> db.collectionName.aggregate([ {$match: {year: 2015}}, {$group: { _id: "$make" }}, {$group: { _id: null, count: {$sum: 1} }} ]){ "_id" : null, "count" : 56 }
MIT