Koneksi Php, MySQL, dan Google Maps API

Source Code Aplikasi GIS

Kembali dengan tutorial google maps. Untuk kali ini, pembahasan akan mengarah pada bagaimana kita menampilkan suatu peta ke dalam google maps dalam bentuk marker yang diload dari dalam database. Selain itu, kita tidak perlu terlalu banyak melakukan “hard coding” tetapi cukup menggunakan class/library yang sudah ada di aplikasi DrasticTools. Untuk mendownload sample dan gallery tentang google maps yang ada pada DrasticTools, silakan Anda dapat kunjungi http://www.drasticdata.nl. Dan yang lebih keren lagi, aplikasi ini dirilis dibawah GPL. Jadi ya silakan pembaca blog saya, dapat mengeksplor lebih jauh lagi digabungkan dengan teknik-teknik menawan Jquery atau Ajax. Langsung saja, saat ini persiapan kita adalah :

1. Buat folder baru di folder utama web misal /htdocs/maps1/ atau /www/maps1/.

2. Siapkan data untuk MySQL dengan format bebas, asalkan ada kolom “Coords” untuk inisialisasi koordinat Longitude dan Latitude. Sehingga, google bisa membaca record-record di kolom Coords dan ditampilkan sesuai dengan koordinatnya di peta google maps. Untuk nama database yang saya pakai adalah “drasticdata” dengan nama tabel “country_map”. Isi dari SQL-nya misalnya untuk daerah Bandung adalah sebagai berikut:

Struktur tabel:

DROP TABLE IF EXISTS `country_map`;
CREATE TABLE `country_map` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `Code` CHAR(3) COLLATE utf8_unicode_ci NOT NULL,
  `nama_kec` CHAR(52) COLLATE utf8_unicode_ci NOT NULL,
  `Luas_Area` FLOAT(10,2) NOT NULL DEFAULT '0.00',
  `Jumlah_Penduduk` INT(11) NOT NULL DEFAULT '0',
  `Kepadatan` INT(11) NOT NULL DEFAULT '0',
  `Pelanggan_MA` CHAR(45) COLLATE utf8_unicode_ci NOT NULL,
  `Coords` CHAR(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '-6.907, 107.609',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=273 ;

Pengisian Data:

INSERT INTO `drasticdata`.`country_map` (`id`, `Code`, `nama_kec`, `Luas_Area`,`Jumlah_Penduduk`, `Kepadatan`, `Pelanggan_MA`, `Coords`)
VALUES
('1',	'AAN',	'Astana Anyar',	'2.7',	'73241', '27126',	'1899',	'-6.8612,107.5963'),
('2',	'BTN'	,'Batu Nunggal',	'4.76',	'117753',	'24738',	'1732',	'-6.9274,107.6457'),
('3',   'BKL',	'Bojong Loa Kaler',	'3.01',	'106867',	'35504',	'2485',	'-6.9297, 107.5990'),
('4',	'CBK',	'Cibeunying Kidul',	'4.08',	'100927',	'24737',	'1732',	'-6.9008, 107.6534'),
('5',	'ADR',	'Andir',	'3.93',	'93708',	'23844'	,'1669'	,'-6.9064, 107.5801'),
('6',	'SJD',	'Sukajadi',	'5.26',	'95455',	'18147',	'1270',	'-6.8918, 107.5971'),
('7',	'RGL',	'Regol',	'4.5',	'96159',	'21369',	'1496',	'-6.9366, 107.6217'),
('8',	'BBK',  'Babakan Ciparay',	'7.16',	'108725',	'15185',	'1063',	'-6.9310, 107.5846'),
('9',	'BKL',	'Bandung Kulon',	'6.48',	'120733',	'18632',	'1304',	'-6.9302, 107.5767'),
('10',	'KCN',	'Kiara Condong',	'5.59',	'115305',	'20627',	'1444',	'-6.9258, 107.6540'),
('11',	'CBL',	'Coblong',	'7.35',	'107946',	'14687',	'1028',	'-6.8848, 107.6267'),
('12',	'SMB',	'Sumur Bandung',	'3.44',	'39535',	'11493',	'804',	'-6.9120, 107.6266'),
('13',	'CCD',	'Cicendo',	'8.03',	'93456',	'11638',	'815',	'-6.9052, 107.6046'),
('14',	'BKD',	'Bojong Loa Kidul',	'5.13',	'70492',	'13741',	'962',	'-6.9447, 107.6086'),
('15',	'LEN',  'Lengkong',	'5.93',	'70969',	'11968',	'838',	'-6.9318, 107.6275'),
('16',	'CBY',  'Cibeunying Kaler',	'4.4',	'65711',	'14934',	'1045',	'-6.8900, 107.6456'),
('17',	'BWT',	'Bandung Wetan',	'3.33',	'34890',	'10477',	'733',	'-6.9041, 107.6281'),
('18',	'ATP',	'Antapani',	'4.05',	'67529',	'16674',	'1167',	'-6.9222, 107.6661'),
('19',	'BKD',	'Bandung Kidul',	'5.55',	'45956',	'8280',	'580',	'-6.9525, 107.6384'),
('20',	'ARC',	'Arcamanik',	'7.36','60990',	'8287',	'580',	'-6.9135, 107.6767'),
('21',	'PAN',	'Panyileukan',	'5.51',	'38694',	'7023',	'492',	'-6.9343, 107.7002'),
('22',	'CID',	'Cidadap',	'8.29',	'52137',	'6289',	'440',	'-6.8616, 107.6234'),
('23',	'RAN',	'Rancasari',	'15.78',	'82633',	'5237',	'367',	'-6.9408, 107.6915'),
('24',	'CIB',	'Cibiru',	'10.56',	'57365',	'5432',	'380',	'-6.9165, 107.7165'),
('25',	'MAN',	'Mandala Jati',	'7.5',	'59193',	'7892',	'552',	'-6.9084, 107.6644'),
('26',	'UJB',	'Ujung Berung',	'10.11',	'63742',	'6305',	'441',	'-6.9200, 107.6942'),
('27',	'SKS',	'Suka Sari',	'6.2',	'34518',	'5567',	'390',	'-6.8612, 107.5963'),
('28',	'GDB',	'Gede Bage',	'11.57',	'27328',	'2362',	'165',	'-6.9408, 107.6915'),
('29',	'CMB',	'Cinambo',	'12.23',	'19964',	'1632',	'114',	'-6.653, 107.955'),
('30',	'BHB',	'Buah Batu',	'13.35',	'82722',	'6196',	'434',	'-6.920, 107.623');

3. Buatlah file bernama config.php untuk koneksi ke databasenya.

<?php
$server="localhost";
$user="root";
$pw="";
$db="drasticdata";
$table="country_map";
 
$GApiKey="silakan Anda generate untuk Google API Key-nya";
?>

4. Buatlah file bernama tampil_peta.php untuk menampilkan peta utama.

<?php
include ("config.php");
include ("class_library.php");
class mysrc extends drasticsrcmysql {
	protected function select(){
		$res = mysql_query("SELECT * FROM $this->table WHERE daerah='bandung'" . $this->orderbystr, $this->conn) or die(mysql_error());
		return ($res);
	}
}
$src = new mysrc($server, $user, $pw, $db, "country_map");
?>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<link rel="stylesheet" type="text/css" href="css/grid_default.css"/>
<link rel="stylesheet" type="text/css" href="css/map_default.css"/>
<title>Mobile Advertising Market Spread</title>
</head>
<html>
<body>
 
<script type="text/javascript" src="js/mootools-1.2-core.js"></script>
<script type="text/javascript" src="js/mootools-1.2-more.js"></script>
<script type="text/javascript" src="js/drasticGrid.js"></script>
<script type="text/javascript" src="http://maps.google.com/maps?file=api&v=2&key=<?php echo $GApiKey ?>"></script>
<script type="text/javascript" src="js/markermanager.js"></script>
<script type="text/javascript" src="js/drasticMap.js"></script>
 
<div id="map1" style="width: 740px; height: 300px;"></div>
<div id="grid1"></div>
<script type="text/javascript">
var themap = new drasticMap('map1', {
	pathimg: "img/",
	displaycol: "nama_kec",
	onClick: function(id){themap.DefaultOnClick(id); thegrid.DefaultOnClick(id)}
});
var thegrid = new drasticGrid('grid1', {
	pathimg: "img/",
	pagelength: 10,
	onClick: function(id){themap.DefaultOnClick(id); thegrid.DefaultOnClick(id)}
});
</script>
 
</body></html>

5. Jangan lupa buatlah file class_library.php untuk menyimpan file-file class yang ada di DrasticTools.

<?php
class drasticSrcMySQL {
	// these options should be set via the options argument on the constructor!
	public $add_allowed 	= true;		// may the user add records? Default is true.
	public $delete_allowed  = true;		// may the user delete records? Default is true.
	public $editablecols;				// array of columnnames to be editable. Defaults to all columns except the id column
	public $defaultcols;				// array of columnnames and values; only records that satisfy these conditions will be selected;
										// added records will have these values as default
	public $sortcol;					// name of column to sort on initially. Defaults to the id column.
	public $sort;						// sort ascending (a) or descending (d)? Default is a.
	public $SQLCharset		= "utf8";	// character set of the strings in the table	 
	public $HTMLCharset		= "UTF-8";	// character set for xhttprequest
 
	// General variables
	public $orderbystr, $wherestr, $addstr;
	public $idname;
	public $idcolnr;
	public $result;
	public $num_rows;
	public $num_fields;
	public $cols;
	public $cols_numeric;
	private $max;
 
	function __construct($server, $user, $pw, $db, $table, $options = null) {
		if (!isset($_REQUEST["op"])) return;
		if ($options) {
			if (isset($options["add_allowed"])) $this->add_allowed = $options["add_allowed"];
			if (isset($options["delete_allowed"])) $this->delete_allowed = $options["delete_allowed"];
			if (isset($options["editablecols"])) $this->editablecols = $options["editablecols"];
			if (isset($options["defaultcols"])) $this->defaultcols= $options["defaultcols"];
			if (isset($options["sortcol"])) $this->sortcol= $options["sortcol"];
			if (isset($options["sort"])) $this->sort= $options["sort"];
			if (isset($options["SQLCharset"])) $this->SQLCharset= $options["SQLCharset"];
			if (isset($options["HTMLCharset"])) $this->HTMLCharset= $options["HTMLCharset"];						
		}
		/* Optionally retrieve parameters from the addparams parameter. * Uncomment the line below and change "myparameter" to the name of your parameter * If you pass multiple parameters copy the line multiple times * * $myparameter = $_REQUEST["myparameter"]; */		
 
		$this->conn = mysql_connect($server, $user, $pw) or die(mysql_error());
		mysql_select_db($db) or die (mysql_error());
		$this->table = $table;
		$res = mysql_query("SET NAMES '" . $this->SQLCharset . "'", $this->conn);
 
		// Initialize the name of the id field, column names and numeric columns:
		$idresult = $this->metadata();
		$primary_found = false;
		for($i=0; $i < mysql_num_fields($idresult); $i++)  {
			$fld = mysql_fetch_field($idresult, $i);
			if ($primary_found == false) {
				if ($fld->primary_key == 1) { 
					$this->idname = $fld->name;
					$this->idcolnr = $i;
					$primary_found = true;
				}
				elseif ($fld->unique_key == 1) {
					$this->idname = $fld->name;
					$this->idcolnr = $i;
				}
			}
			$this->cols[] = $fld->name;
			if ($fld->numeric == 1) $this->cols_numeric[] = $fld->name;
		}
		if (!isset($this->idname)) die("Could not find primary or unique key");
		// Initialize editablecols if not done yet:
		if (!isset($this->editablecols)) {
			mysql_field_seek($idresult, 0);
			for($i=0; $i < mysql_num_fields($idresult); $i++)  {
				$fldname = mysql_fetch_field($idresult)->name;
				if ($fldname != $this->idname) $this->editablecols[] = $fldname;
			}
		}
		mysql_free_result($idresult);
 
		// Initialize Field types:
		$this->fldtypes = array();
		$colresult = mysql_query("SHOW COLUMNS FROM " . $this->table, $this->conn) or die(mysql_error());
		for($i=0; $i < mysql_num_rows($colresult); $i++)  {
			list($fldname, $fldtype, $fldnull, $fldkey, $flddefault, $fldextra) = mysql_fetch_row($colresult);	
			$this->fldtypes[$fldname] = $fldtype;
		}
		mysql_free_result($colresult);
 
		// Calculate the WHERE string and the string for the ADD operation, if the defaultcols option is set.
		$this->wherestr = "";
		$this->addstr = " () VALUES () ";
		if ($this->defaultcols){
			foreach ($this->defaultcols as $key => $value) $assignment[] = $key . " = '" . $value . "'";
			$wherestr1 = implode(" AND ", $assignment);
			$this->wherestr = sprintf(" WHERE %s ", $wherestr1);
 
			$addstr1 = implode(", ", array_keys($this->defaultcols));
			$addstr2 = implode(", ", array_map(array ($this, "addquotes"), array_values($this->defaultcols)));
			$this->addstr = sprintf(" (%s) VALUES (%s) ", $addstr1, $addstr2);
		}
 
		// Do the sorting:
		if (isset($_REQUEST["sortcol"])) $this->sortcol = mysql_real_escape_string($_REQUEST["sortcol"]);
		if (isset($_REQUEST["sort"])) $this->sort = mysql_real_escape_string($_REQUEST["sort"]);
		if (!$this->sortcol) $this->sortcol = $this->idname;
		if (!$this->sort) $this->sort = "a";
		$this->orderbystr = " ORDER BY " . $this->sortcol . ($this->sort == "d"?" DESC":"");		  
 
		header( "Expires: Mon, 26 Jul 1997 05:00:00 GMT" );  // disable IE caching
		header( "Last-Modified: " . gmdate( "D, d M Y H:i:s" ) . " GMT" ); 
		header( "Cache-Control: no-cache, must-revalidate" ); 
		header( "Pragma: no-cache" );
		if (isset($_REQUEST["op"])) 	$op		= $_REQUEST["op"]; else $op = "";		
		if (isset($_REQUEST["id"])) 	$id		= $_REQUEST["id"];
		if (isset($_REQUEST["col"])) 	$col	= $_REQUEST["col"];
		if (isset($_REQUEST["value"])) 	$value	= $_REQUEST["value"];
		if ($op != "vb" && $op != "vc") header("Content-Type: text/html; charset=".$this->HTMLCharset);
 
		switch ($op) {
			case ("a") : if ($this->add_allowed) exit($this->add());
			case ("d") : if ($this->delete_allowed) exit($this->delete($id));
			case ("u") : exit($this->update($id, $col, rawurldecode($value)));
		}
 
		// Get the table in memory
		$this->result = $this->select();
		$this->num_rows = mysql_num_rows($this->result);
		$this->num_fields = mysql_num_fields($this->result);
 
		if ($op == "v") exit($this->view());
		if ($op == "vm") exit($this->view_meta());
		if ($op == "vb") exit($this->view_bar());
		if ($op == "vc") exit($this->view_circle());
		if ($op == "vl") exit($this->view_label());
		if ($op == "vrn") exit($this->view_rownr());
	}
	function __destruct() {
		if ($this->result) mysql_free_result($this->result);
		if ($this->conn) mysql_close($this->conn);
	}
 
	private function view_meta(){
		$result[0] = $this->num_rows;
		$result[1] = $this->num_fields;
		$result[2] = $this->idname;
		$result[3] = $this->idcolnr;
		$result[4] = $this->cols;	
		$result[5] = $this->cols_numeric;
		$result[6] = $this->add_allowed;
		$result[7] = $this->delete_allowed;
		$result[8] = $this->editablecols;
		$result[9] = $this->defaultcols;
		$result[10] = $this->sortcol;
		$result[11] = $this->sort;
		$result[12] = $this->fldtypes;
		return(json_encode($result));
	}	
 
	//
	// These protected functions can be overruled if you want to redefine your datasource
	//
	protected function select(){
		$res = mysql_query("SELECT * FROM $this->table" . $this->wherestr . $this->orderbystr, $this->conn) or die(mysql_error());
		return ($res);
	}	
	protected function add(){
		mysql_query("INSERT INTO $this->table" . $this->addstr, $this->conn) or die(mysql_error());
		if (mysql_affected_rows($this->conn) == 1) return(true); else return(false);
	}
	// Override this function if you want to use (join) query on multiple tables:
	protected function metadata(){
		$res = mysql_query("SELECT * FROM $this->table LIMIT 1", $this->conn);
		return ($res);
	}
 
	//
	// Private functions only visible within the class
	//
	private function exists($id, $fld = "") {
		$res = $this -> select();
		// check field
		if ($fld != "") {
			$found = false;
			while (($field = mysql_fetch_field($res)) != null) {
				if ($field->name == $fld) {
					$found = true;
					break;
				}
			}
			if (!$found) return(false);
		} 
		// check id
		for ($i=0; $i < mysql_num_rows($res); $i++)  {
			$row = mysql_fetch_array($res);
			if ($row[$this->idcolnr] == $id) return(true);
		}
		return(false);
	}	
	private function delete($id){
		if (!$this->exists($id)) return(false);
		mysql_query("DELETE FROM $this->table WHERE $this->idname='$id'", $this->conn) or die(mysql_error());
		if (mysql_affected_rows($this->conn) == 1) return(json_encode(true)); else return(json_encode(false));
	}
	private function update($id, $fld, $value){
		if ((in_array($fld, $this->editablecols)) && $this->exists($id, $fld)) {
			mysql_query("UPDATE $this->table SET $fld='$value' WHERE $this->idname='$id'", $this->conn) or die(mysql_error());
			if (mysql_affected_rows($this->conn) == 1) { 
				return("1");
			} else {
				$res = mysql_query("SELECT $fld FROM $this->table WHERE $this->idname='$id'", $this->conn) or die(mysql_error());
				$row = mysql_fetch_array($res);
				if ($row[0] == $value) 
					return("1");
				else
					return("0");
			}
		}
		return("0");
	}
	private function view_rownr(){
		if (isset($_REQUEST["id"])){
			mysql_data_seek($this->result, 0);
			for ($i = 0; $i < $this->num_rows; $i++) {
				$value = mysql_fetch_array($this->result);
				if ($value[$this->idcolnr] == $_REQUEST["id"]) {
					return(json_encode($i));
				}
			}
		}
		return(json_encode(-1));
	}	
	private function view(){
		if ($this->num_rows == 0) return(json_encode(array(null, null)));
		if (isset($_REQUEST["cols"])) $cols = explode(",", $_REQUEST["cols"], $this->num_fields);
		if (isset($_REQUEST["id"])){
			$res = mysql_query("SELECT * FROM $this->table WHERE ".$this->idname." = '".$_REQUEST["id"]."'", $this->conn);
			$value = mysql_fetch_array($res);
			for ($j = 0; $j < ((isset($cols))?(count($cols)):($this->num_fields)); $j++) {
				$row[$j] = $value[((isset($cols))?($cols[$j]):($j))];
			}
			$arr[0] = $row;
			$sqlidarr[0] = $value[$this->idname];
			mysql_free_result($res);
		}
		else {
			if (isset($_REQUEST["start"])) $start = $_REQUEST["start"]; else $start = 0;
			if (isset($_REQUEST["end"])) $end = $_REQUEST["end"]; else $end= $this->num_rows;
			if ($start < 0) $start=0;
			if ($end > $this->num_rows) $end = $this->num_rows;
			if ($start < $this->num_rows) {		
				mysql_data_seek($this->result, $start);
				for ($i = 0; $i < ($end-$start); $i++) {
					$value = mysql_fetch_array($this->result);
					for ($j = 0; $j < ((isset($cols))?(count($cols)):($this->num_fields)); $j++) {
						$row[$j] = $value[((isset($cols))?($cols[$j]):($j))];
						//echo $row[$j];
					}
					$arr[$i] = $row;
					$sqlidarr[$i] = $value[$this->idname];
				}
			}
		}
		$result[0] = $sqlidarr;
		$result[1] = $arr;
		return(json_encode($result));
	}
	private function view_bar(){
		$id = $_REQUEST["id"];
		$colname = $_REQUEST["colname"];
		$w = $_REQUEST["w"];
		$h = $_REQUEST["h"];
		if (!isset($this->max)) $this->max = $this->MaxNumber($colname);	
		$res = mysql_query("SELECT $colname FROM $this->table WHERE $this->idname='$id'", $this->conn) or die(mysql_error());
		$data = mysql_fetch_array($res);
		$value = $data[0];
		mysql_free_result($res);
 
		$im = imagecreatetruecolor ($w, $h) or die("Cannot Initialize new GD image stream");
		imagealphablending($im, FALSE);
		imagesavealpha($im, TRUE);
 
		$bg = imagecolorallocatealpha($im, 255, 255, 255, 127);
		imagefill($im, 0, 0, $bg);
 
		$clr = imagecolorallocatealpha($im, 0, 0, 255, 60);
		$height = (int) ceil($h * ($value / $this->max));
		imagefilledrectangle ($im, 0, $h-$height, $w, $h, $clr);
 
		Header("Content-type: image/png");
		Imagepng($im);		
		ImageDestroy($im);
	}
	private function view_circle(){
		$id = $_REQUEST["id"];
		$colname = $_REQUEST["colname"];
		$w = $_REQUEST["w"];
		if (!isset($this->max)) $this->max = $this->MaxNumber($colname);	
		$res = mysql_query("SELECT $colname FROM $this->table WHERE $this->idname='$id'", $this->conn) or die(mysql_error());
		$data = mysql_fetch_array($res);
		$value = $data[0];
		mysql_free_result($res);
 
		$maxr = $w / 2;
		$maxopp = pi() * pow($maxr, 2);
		$opp = $maxopp * ($value / $this->max);
		$r = sqrt($opp / pi()); 
		$width = $r*2;
 
		$im = imagecreatetruecolor ($w, $w) or die("Cannot Initialize new GD image stream");
		imagealphablending($im, FALSE);
		imagesavealpha($im, TRUE);
 
		$bg = imagecolorallocatealpha($im, 255, 255, 255, 127);
		imagefill($im, 0, 0, $bg);
 
		$clr = imagecolorallocatealpha($im, 0, 0, 255, 60);
		imagefilledarc ($im, $maxr, $maxr, $width, $width, 0, 360, $clr, IMG_ARC_PIE);
 
		Header("Content-type: image/png");
		Imagepng($im);		
		ImageDestroy($im);
	}	
	private function MaxNumber($colname) {
		$max = 0;
		mysql_data_seek($this->result, 0);
		for ($i=0; $i < $this->num_rows; $i++) {
			$row = mysql_fetch_array($this->result);
			$max = max($max, $row[$colname]);
		}
		return($max);
	}
	function addquotes($str) {
    	return ("'".$str."'");
	} 
}
?>

Nah, langsung saja habis itu, kita buka browser kesayangan kita misal di google chrome dengan alamat http://localhost/maps1. Selamat Mencoba! Untuk tampilannya menyusul ya.

DOWNLOAD FILE LENGKAP DRASTICTOOLS DISINI


Firmansyah Wahyudiarto merupakan salah satu enthusiast di bidang teknologi informasi dan manajemen bisnis kontemporer. Saat ini bekerja di salah satu perusahaan swasta telekomunikasi Indonesia. Memiliki pengalaman dalam bidang pengembangan teknologi serta optimalisasi berbasis Internet diantaranya Search Engine Marketing, Web Development & Optimization, Geographic Information System, Computer Networking, serta pendalaman ilmu bisnis yaitu Strategic Management Business.

One Comment

  1. enda says:

    numpang nanya,
    kalo mau menampilkan koordinat2 tadi menjadi bentuk shape/polygon bagaimana ya ?

    Terimakasih

Leave a Comment