How to handle the original coordinate?

Data clean for visualizaiton

Posted by resuly on November 10, 2016


The coordinate from Visum is based on real distance.

There are many different coordinates for various uses. In our case, the dataset has consisted of many big numbers base on x and y coordinate axis. They are not longitude and latitude value for geographic use, so we can’t use this data directly for Google Maps or Baidu Maps.


Essential Knowledge

Universal Transverse Mercator System:


The idea of UTM is making a projection for the earth and then get the x,y coordinate. (Kinds of (0,0) all located in African.) More information, please visit this reference:

The following reference shows the difference among GCJ-02, WGS-84, and UTM. In this article, you can also find these coordinates system information that main map companies use.

Baidu Maps issue: UTM to Baidu Coordinate

Processing Steps

The original data is close to Universal Transverse Mercator system. So I decided to convert the dataset into UTM first.

Firstly, we have to get the UTM coordinate of the zone 1. The following tool provides an online point choosing system:



We can find the zone 1 is (278369,3468927) in UTM format. So let’s handle the rest of data based on the initial point in Excel.


The problem is how to convert all UTMs to the WGS-84. I tried a converted library in PHP but failed, because the final result was not correct. JScoord is a powerful convert library which wrote in Javascript.


The function we want:


Simplify the code to just one line(it’s helpful for generating codes in Ecxel):

new UTMRef(x, y, "R", 51).toLatLng().toString();

Change the output format in the JScoord (line 37):


function LatLngToString() {
  return '<tr><td>''</td><td>'+this.lng.toFixed(6)+'</td></tr>';

Generate all x&y for codes in Excel:


Establish the HTML page for the coordinate table:


<!DOCTYPE html>
<html lang="en">
<meta charset="UTF-8">
<script type="text/javascript" src="./jscoord-1.1.1.js"></script>
<script type="text/javascript">

// Main codes
document.write(new UTMRef(278306,3468927, "R", 51).toLatLng().toString());
document.write(new UTMRef(278305.774151,3469549.551763,'R', 51).toLatLng().toString());
document.write(new UTMRef(277062.047957,3469484.170427,'R', 51).toLatLng().toString());


Final result


The result is a HTML table so that you can paste it into Excel sheet directly.

Let’s check the first point in Google Maps (It’s correct):


We get the coordinate of the Google version now. Also, you can transfer it into Baidu version too.

The final results on map:

  • Population


  • Trips


  • Heat Map


Further Works

Visualization of the usage from public bike stations in Souzhou with a timeline.

Thanks for reading.

Please inform the errors to me from what you found.