Some Code Differences Between WebDemo and KD Test Google Map Site
In our production environment, I created a table in our database to store geocodes for each country capitals rather than constructing xml data file on the fly. The system will first go to our database table and check if there exists a record for the country capital. If so, merge it with counting records; otherwise, it jumps out onto the internet and retrieves the geocode from Yahoo geocoding service, finishes merging process and then insert back to database table.
Here is complete version of the codes:
private void MergeDataAll(XmlTextWriter objX, SqlDataReader dr)
{
MapDataAccess da = new MapDataAccess(Global.CONNSTRING);
SqlDataReader drG;
GoogleAddress ga = new GoogleAddress();
MapData md = new MapData();
string strC = "";
while(dr.Read())
{
strC = dr["ADR1_COUNTRY"].ToString();
drG = da.GetCCGeocode(strC,MapData.Capital(strC).Trim());
drG.Read();
if ((drG.HasRows)&&(drG["Lat"].ToString() != "")&&(drG["Lng"].ToString()!= ""))
{
objX.WriteStartElement("marker");
objX.WriteAttributeString("lat", drG["Lat"].ToString());
objX.WriteAttributeString("lng",drG["Lng"].ToString());
objX.WriteAttributeString("country",strC);
objX.WriteAttributeString("title", strC);
objX.WriteAttributeString("message", dr["CountryTotal"].ToString());
objX.WriteEndElement();
}
else
{
md.SetGeocodesEach(strC, MapData.Capital(strC).Trim());
md.GetGeocodes(ga);
if ((ga.Latitude != 0) && (ga.Longitude != 0))
{
objX.WriteStartElement("marker");
objX.WriteAttributeString("lat", ga.Latitude.ToString());
objX.WriteAttributeString("lng",ga.Longitude.ToString());
objX.WriteAttributeString("country",strC);
objX.WriteAttributeString("title", strC);
objX.WriteAttributeString("message", dr["CountryTotal"].ToString());
objX.WriteEndElement();
//Insert lat and lng into database.
da.closeConn();
if (MapData.Capital(strC).Trim() != "")
da.InsertCCGeocode(strC,MapData.Capital(strC).Trim(),ga.Latitude.ToString(),ga.Longitude.ToString());
}
}
drG.Close();
}
}
Our table is called CCGeocode and there are only four fields: Country, Capital, Lat and Lng. Here is the code snippet that makes use of Microsoft applicationblocks to insert:
public bool InsertCCGeocode(string strCountry, string strCity, string strLat, string strLng)
{
string queryString="Insert Into CCGeocode";
queryString += " (Country, Capital, Lat, Lng)";
queryString += " values ('"+strCountry+"', '"+strCity+"', '" +strLat+"', '"+strLng+"')";
bool success=false;
SqlCommand myCommand=new SqlCommand(queryString, conn);
conn.Open();
int rows = myCommand.ExecuteNonQuery();
if (rows > 0)
{
success=true;
}
conn.Close();
return success;
}
To download a WebDemo project, please visit:
http://www.codeproject.com/
To see an active Google Map:
http://test.kd.iu.edu/GoogleMap/AlumStatesMap.aspx
Here is complete version of the codes:
private void MergeDataAll(XmlTextWriter objX, SqlDataReader dr)
{
MapDataAccess da = new MapDataAccess(Global.CONNSTRING);
SqlDataReader drG;
GoogleAddress ga = new GoogleAddress();
MapData md = new MapData();
string strC = "";
while(dr.Read())
{
strC = dr["ADR1_COUNTRY"].ToString();
drG = da.GetCCGeocode(strC,MapData.Capital(strC).Trim());
drG.Read();
if ((drG.HasRows)&&(drG["Lat"].ToString() != "")&&(drG["Lng"].ToString()!= ""))
{
objX.WriteStartElement("marker");
objX.WriteAttributeString("lat", drG["Lat"].ToString());
objX.WriteAttributeString("lng",drG["Lng"].ToString());
objX.WriteAttributeString("country",strC);
objX.WriteAttributeString("title", strC);
objX.WriteAttributeString("message", dr["CountryTotal"].ToString());
objX.WriteEndElement();
}
else
{
md.SetGeocodesEach(strC, MapData.Capital(strC).Trim());
md.GetGeocodes(ga);
if ((ga.Latitude != 0) && (ga.Longitude != 0))
{
objX.WriteStartElement("marker");
objX.WriteAttributeString("lat", ga.Latitude.ToString());
objX.WriteAttributeString("lng",ga.Longitude.ToString());
objX.WriteAttributeString("country",strC);
objX.WriteAttributeString("title", strC);
objX.WriteAttributeString("message", dr["CountryTotal"].ToString());
objX.WriteEndElement();
//Insert lat and lng into database.
da.closeConn();
if (MapData.Capital(strC).Trim() != "")
da.InsertCCGeocode(strC,MapData.Capital(strC).Trim(),ga.Latitude.ToString(),ga.Longitude.ToString());
}
}
drG.Close();
}
}
Our table is called CCGeocode and there are only four fields: Country, Capital, Lat and Lng. Here is the code snippet that makes use of Microsoft applicationblocks to insert:
public bool InsertCCGeocode(string strCountry, string strCity, string strLat, string strLng)
{
string queryString="Insert Into CCGeocode";
queryString += " (Country, Capital, Lat, Lng)";
queryString += " values ('"+strCountry+"', '"+strCity+"', '" +strLat+"', '"+strLng+"')";
bool success=false;
SqlCommand myCommand=new SqlCommand(queryString, conn);
conn.Open();
int rows = myCommand.ExecuteNonQuery();
if (rows > 0)
{
success=true;
}
conn.Close();
return success;
}
To download a WebDemo project, please visit:
http://www.codeproject.com/
To see an active Google Map:
http://test.kd.iu.edu/GoogleMap/AlumStatesMap.aspx
posted by Bo Fan at 12:28 PM
4 comments
