When you want to map multiple items with the same location in Fusion Table, only one item is displayed and all the others are ignored. There is several workaround to this major limitation, and the most common is to change slightly your coordinate (longitude / latitude) so your point will appear close to each other on the map (tip from the google fusion team itself).
When working with large data set, identifying and manually correcting all records sharing the same location can become time consuming. So I've been looking how to deal with this in Google refine and ends up with this straight forward process.
0. if you don't have google refine, download it, unzip the package, launch it and create a new project with your data.
1. Create record based on the location sharing the same coordinate in fusion table (you can work with city, country or by creating couple long/lat). View the tutorial to create record and switch to record mode (see difference between a record and a row).
2. Update the latitude or longitude (or both!), based on your latitude column, create a new column with the expression (select your column > Edit cells > Transform):
value+(toNumber(row.index - row.record.fromRowIndex)*0.01)
where
- value refer to the cell value, current longitude or latitude. Make sure it is formatted as number.
- row.index - row.record.fromRowIndex is the index of the record within the record (I found this function in the google refine discussion group),
- toNumber will change the index to a number so we can do mathematical operation on it,
- *0.01 to make the change to at the second decimal. You can play with this parameter to space more or less your marker (0.02 will double the space, this is pretty good for city level, 0.1 will be better if you work at country level).
3. Export your project and load it in fusion table to check the new markers.
