PHP, JavaScript and Google Maps API in MOPSI

1. PHP, JavaScript and Google Maps API in MOPSI Karol Waga 15.02.2011

2. Table of contents • PHP • JavaScript • Google Maps API

3. PHP – server side • designed for web development to produce dynamic web pages • interpreted by a web server • available as a processor for most modern web servers and as a standalone interpreter on most operating systems

4. Use of PHP in MOPSI • Retrieving routes and photos • Handling data sent by mobile devices • Recommendation, clustering, and search algorithms • Running scripts (for example, route segmentation and route reduction)

5. Getting routes • session_start(); • include("../inc/general.php"); • include("getRoutes.php"); • $queryStr = $_GET['q']; • $text = explode(";", $queryStr); • $q = $text[0]; // user • $dateArr = $text[1]; // date • $maxDistance = $text[2]; • $timeThreshold = $text[3]; • $date_array = explode("-",$dateArr); • $startday = $date_array[0]; • $startmonth = $date_array[1]; • $startyear = $date_array[2]; • $endday = $date_array[3]; • $endmonth = $date_array[4]; • $endyear = $date_array[5]; • $start_day = '\''.$startyear."-".$startmonth."-".$startday.'\''; • $end_day = '\''.$endyear."-".$endmonth."-".$endday.'\''; • $inputprefix1 = "reduction/input_" . session_id(); • $inputprefix2 = "measured_routes/"; • getRoutes($q, $maxDistance, $timeThreshold, $start_day, $end_day, $inputprefix1,$inputprefix2);

6. Retrieve route from database • $path = str_replace("inc/module_routeCollections","",dirname(__FILE__)); • $database = new database(); • $lnk = $database->connectToDb(); • mysql_set_charset("utf8", $lnk); • $minDistance = 10; • $message = ""; • $routeInfo = ""; • $common = new common(); • $isResults = false; • $cacheAddresses = false; • if ($timeThreshold >= DEFAULT_TIME_SEGMENTATION_THRESHOLD && • $maxDistance >= DEFAULT_DISTANCE_SEGMENTATION_THRESHOLD) • { • $cacheAddresses = true; • } • $maxDistance = $maxDistance/1000; // convert meters to kilometers • // Select all points for user in selected time period • $sql = "SELECT `Latitude`, `Longitude`, `Timestamp`, `Address`, `ID` FROM `Tracking` " . • "WHERE `Staff_ID` = $userId AND `Date` BETWEEN $start_day AND $end_day ORDER BY Timestamp Desc "; • $rs = mysql_query ( $sql, $lnk); • if ( mysql_num_rows( $rs ) > 0 ) • { • $isResults = true; • $rs_arr = preprocessQueryResult($rs); • } • else { • $rs_arr = array(); • }

7. Preparing input for scripts • session_start(); • $routeNumber = $_POST["routeNumber"]; • $segmentsNo = $_POST["segmentsNo"]; • $isFiltered = ($_POST["filtering"]=="true"); • $inputprefix = "reduction/input_" . session_id(); • $routeFile = "./" . $inputprefix . ".txt"; • $fp= @fopen($routeFile, "r"); • $id = 0; • $routeString = ""; • if($fp) • { • while( $str = fgets( $fp ) ) • { • if(strpos($str, "*") !== false) // find the route segment flag • { • $id ++; • }else if($id == $routeNumber) // write the selected route • { • $routeString .= $str; • }else if($id > $routeNumber) // ignore rest of the route • { • break; • } • } • fclose($fp); • $prefix = "routeSegmentation"; • $originalRoute = $prefix."/route_filt/originalRoute.txt"; • $filteredRoute = $prefix."/filteredRoute.txt"; • $file= @fopen($originalRoute, "w"); • if ($file) • { • flock($file, 2); • fwrite($file, $routeString); • flock($file, 3); • } • fclose($file); • }

8. Calling external scripts • if ($isFiltered) • { • $rs = shell_exec($prefix."/route_filt/route_filt ".$originalRoute." ".$filteredRoute); • $rs = shell_exec($prefix."/routeseg ".$filteredRoute." ".$prefix."/segmentedRoute.txt ".$segmentsNo); • } • else • $rs = shell_exec($prefix."/routeseg ".$originalRoute." ".$prefix."/segmentedRoute.txt ".$segmentsNo);

9. Keywords clustering • function clusterServices($lnk) { • $services = mysql_query("SELECT * FROM `search_cache` WHERE `Municipality`='iitti'", $lnk); • if ( (!$services) || (($num_rows = mysql_num_rows($services)) == 0) ) { die('Invalid query: ' . mysql_error()); } • $i=0; • while($result = mysql_fetch_assoc($services)) • { • $serv_id = $result['SERV_ID']; • $keyword_ids = mysql_query("SELECT * FROM `search_link` WHERE `SERV_ID`='$serv_id'", $lnk); • while ($rs = mysql_fetch_assoc($keyword_ids)) • {$kw_id = $rs['KW_ID']; • $actual_keyword = mysql_query("SELECT * FROM `search_kw` WHERE `KW_ID`='$kw_id'", $lnk); • $keyword = mysql_fetch_assoc($actual_keyword); • $keyword = $keyword['Keyword']; • if (!isOnTheList($kw_id,$list)) • {$list[$i] = $kw_id;$list2[$i++] = $keyword;}} • } • $k = 0; • $table = array(array()); • for ($i = 0; $i < count($list); $i++) • { if (isInTable($table,$list[$i])) { continue; } • $idx = 0; • $table[$k][$idx++] = $list[$i]; • $neighbors = searchNeighborKeywords($list[$i],$lnk); • for ($j = 0; $j < count($neighbors); $j++) • { • if (isInVector($list,$neighbors[$j])) • $table[$k][$idx++] = $neighbors[$j]; • } • $k++; } • for($i=0; $i < count($table); $i++) • { for($j=0; $j < count($table[$i]); $j++) • { • $table = mergeClusters($table,$i,$j); • $table[$i][$j] = ucfirst($list2[array_search($table[$i][$j],$list)]);//convert number (keyword_id) to name (keyword) • } • sort($table[$i]); } • $table2 = sortCells($table); • return $table2; • }

10. Changes to clustering algorithm

11. Bus search • function findNearestStop($lat, $lng, $city) • { • global $lnk, $stop_services_selection, $stop_id; • $stop_id = array(); • $city_id_selection = mysql_query("SELECT * FROM `bus_places_codes` WHERE `place_name` = '$city'", $lnk); • $city_id = mysql_fetch_assoc($city_id_selection); • $city_id = $city_id['place_id']; • $bus_stops_selection = mysql_query("SELECT * FROM `bus_stations` WHERE `city_id` = '$city_id'", $lnk);$i = 0; • while($result = mysql_fetch_assoc($bus_stops_selection)) { • if ($result['lat']!=0) • { • $stop_id[$i] = $result['id']; $stop_name[$i] = $result['name']; • $stop_lat[$i] = $result['lat']; • $stop_lng[$i] = $result['lon']; • $stop_distance[$i] = getDistance($result['lat'],$result['lon'], $lat, $lng); • $i++; • } • } • $shortest_distance = 9999999; • $closest_stop_index = -1; • for ($i=0; $i<count($stop_distance); $i++) • { if ($stop_distance[$i]<$shortest_distance) • {$shortest_distance = $stop_distance[$i];$closest_stop_index = $i;} • } $stop_services_selection = mysql_query("SELECT * FROM `bus_stops` WHERE `station_id` = '$stop_id[$closest_stop_index]' ORDER BY `departure_time`", $lnk); • $stop_name = $stop_name[$closest_stop_index]; • $stop_id = $stop_id[$closest_stop_index]; • print $stop_name."|".$shortest_distance."|".$stop_id."|".$stop_lat[$closest_stop_index]."|".$stop_lng[$closest_stop_index]."\n".mysql_num_rows($stop_services_selection)."\n"; • return $stop_services_selection; • }

12. Nearest bus stop search • User can find timetable from the nearest bus stop

13. JavaScript – client side • designed for web development to produce dynamic web pages • interpreted by a web browser • available as a processor for most modern web servers and as a standalone interpreter on most operating systems

14. Use of JavaScript in MOPSI • presenting results retrieved from server • clustering of routes, photos and users • jQuery library for better visual outlook • with Google Maps API

15. Displaying photo results • function xmlPhotoParse( xml ) { • var point = new GLatLng(parseFloat(markers[i].getAttribute("lat")), • parseFloat(markers[i].getAttribute("lon"))); • pointList.push(point); • titleStr= markers[i].getAttribute("title"); • urlStr = markers[i].getAttribute("url"); • addressStr = markers[i].getAttribute("address"); • dateStr = markers[i].getAttribute("date"); • photoid = markers[i].getAttribute("id"); • author = markers[i].getAttribute("author"); • distStr = formatMeters( dist ); // the nearest place • info = "<table cellspacing='0' rowspacing='0'><tr><td align='center' colspan='2'><b>"+titleStr+"</b></td></tr><tr><td>Address:</td><td>"+addressStr+"</td></tr>" • +"<tr><td>Date:</td><td>"+dateStr+"</td></tr><tr><td>Distance:</td><td>"+distStr • +'</td></tr><tr><td colspan="2" align="center"><a href="'+urlStr+'" TARGET="_blank"><img border="0" src="http://cs.joensuu.fi/paikka/mobile_photo/thumb-'+ photoid +'" /></a></td></tr>' • +"<tr><td colspan='2' align='center'>"+author+"</td></tr></table>"; • var picNum = m+1; // get the icon num • if(picNum > 20) • picNum = 'empty' • // use the yellow bubbles for photos search results • picNum = "yellow" + picNum; • var marker = createResultMarker(point, info, picNum, 5, ""); • marker.top = 3; // set the local result bubble on the top • map.addOverlay(marker); • markerList.push(marker); • var markerNum = markerList.length-1; • resultStr += 'onmouseover="showEdit(this,' + markerNum + ')" onmouseout="closeEdit(this,' + markerNum + ')">' • + '<td class="icon" width="30" border="0" align="center">' • + '<a href="javascript:myclick(' + markerNum + ')">' + '<img border="0" src="' + './Icon/'+ picNum +'.png" >' + '<\/a>' + '</td>' • + '<td width="140" border="0" >' + '<div class="jinfo">' + info + '</div>' + '<a href="javascript:myclickRoute(' + markerNum + ')">' + 'Check route' + '<\/a>' + '</td>' • + '<td width="100" border="0" align="center"><div class="jaction">' • + '<div id="editpart" class="edit' + markerNum + '" style="display:none"> <a href="javascript:void(0);" onclick="photoAdd(' + markerNum +',\''+photoid+'\');"> <span class="jaction" style="font-weight:bold;">Connect</span> </a> </div>' • + '<a href="'+urlStr+'" TARGET="_blank"> <img class="imgstyle" src="http://cs.joensuu.fi/paikka/mobile_photo/thumb-'+ photoid +'" /></a><br>'+ author + '</div></td>' • + '</tr>';

16. Displaying photo results

17. Clustering of photos • function gridClustering(){ • var maxLat = mapBounding.maxLat; • var maxLng = mapBounding.maxLng; • var minLat = mapBounding.minLat; • var minLng = mapBounding.minLng; • var deltaLat = mapBounding.deltaLat; • var deltaLng = mapBounding.deltaLng; • var len = markers.length; • var clusters = new Array(); • var photoClusters = new Array(); • var lat, lng; • var k, total; • var numArray, rowNum, columnNum; • numArray = calcClusterParam(); • rowNum = numArray[0]; • columnNum = numArray[1]; • total = rowNum * columnNum; • clusters = new Array(); • //grid-based clustering, assign points to the given cluster • for(var i=0; i < len; i++) • { • lat = markers[i].realCoords.lat(); • lng = markers[i].realCoords.lng(); • k = decideClusterNum(lat, lng, maxLat, maxLng, deltaLat, deltaLng, rowNum, columnNum); • if(k == -1) • { • continue; • } • if(clusters['"'+k+'"'] == null) • { • clusters['"'+k+'"'] = new photoCluster(k); • clusters['"'+k+'"'].group.push(i); • clusters.push(clusters['"'+k+'"']); • }else • {clusters['"'+k+'"'].group.push(i); } • } • photoClusters = mergeClusters(clusters, columnNum); • return photoClusters;}

18. Clustering of photos

19. jQuery • $("#dialogPhoto").dialog({ • autoOpen: false, • height: 500, • width: 350, • modal: true, • resizable: true, • buttons: { • 'Cancel': function() { • $(this).dialog('close'); • },}, • close: function() { • } • });

20. jQuery

21. Google Maps API • launched in 2005 by Google • allow developers to integrate Google Maps into their websites • initially contained only JavaScript API • extended with web services for performing geocoding, and generating driving directions • free for commercial use providing that the site on which it is being used is publicly accessible and does not charge for access (other sites can purchase Google Maps API Premier) • used by over 350000, including MOPSI website

22. Use of Google Maps API in MOPSI • displaying routes, photos and users • displaying search results • segmented route visualization • bus routes visualization • geocoding, reverse geocoding

23. Displaying search results • info = "<table cellspacing='0' rowspacing='0'><tr><td align='center' colspan='2'><b>"+titleStr • +"</b></td></tr><tr><td>Address:</td><td>"+addressStr+"</td></tr>" • +"</td></tr><tr><td>Phone:</td><td>"+telStr+"</td></tr>" • +"</td></tr><tr><td>Link:</td><td>"+urlStr+"</td></tr>" • +"<tr><td>Distance:</td><td>"+distStr • +"</td></tr></table>"; • var picNum = m+1; // get the icon num • if(picNum > 20) • picNum = 'empty'; • // use the green bubbles for local search results • picNum = "green" + picNum; • var marker = createResultMarker(point, info, picNum, 9, service_photo); • marker.top = 2; // set the local result bubble on the top • map.addOverlay(marker);

24. Displaying search results

25. Segmented route visualization • function animateRoute(route, icon, timeout, isPolyline) • { if (isAnimationOn)return 0; • if (!isPolyline) • { • route = ajaxPost("../route/getSpecificRoute.php","routeNumber="+route); • if (document.getElementById("custom").checked) • { • timeout = parseFloat(document.getElementById("animationSpeed").value); • if (isNaN(timeout)) • timeout=0.5; • } • else • { • var startTime = coordinates[2]; • var timeInterval = point_data[step+1].split(" "); • timeout = timeInterval[2]-startTime; • }; • timeout*=1000; • } • //marker creation • var point; • var infoMarker; • var infoTimeMarker; • if (isPolyline) • point = route.getVertex(0); • else//isString • { • var point_data = route.split("\n"); • var coordinates = point_data[0].split(" "); • point = new GLatLng(coordinates[0],coordinates[1]); • if (!isPolyline) • if (isAnalysisMode) • { • infoMarker = new createAnimationLabeledTextMarker(point,formatSpeed(point_stats_array[0].split("")[6]),"speed"); • infoTimeMarker = new createAnimationLabeledTextMarker(point,formatSeconds(point_stats_array[0].split(" ")[1]),"time"); • map.addOverlay(infoMarker); • map.addOverlay(infoTimeMarker); • } • } • var marker = createAnimationMarker(point,icon); • map.addOverlay(marker); • if (infoMarker==undefined)infoMarker = ""; • moveToStep(marker, infoMarker, infoTimeMarker, route, icon, isPolyline, timeout, 0); • isAnimationOn = true; • }

26. Segmented route visualization

27. Bus routes visualization • function setDirections(aPoints, strRoute, locale, numText, timeLabels){ • document.getElementById("statistics„).innerHTML += numText; • map.clearOverlays(); • gdir = new GDirections(map); • gdir.loadFromWaypoints(aPoints[0], {"locale": locale}); • GEvent.addListener(gdir, "load", onGDirectionsLoad); • document.getElementById("busDirections").innerHTML = strRoute; • for (var i=0;i<timeLabels.length;i++) • map.addOverlay(timeLabels[i]); • window.setTimeout(function(){ • getBusRouteBounds(); • },1000); • }

28. Bus routes visualization

29. Geocoding • process of finding associated geographic coordinates (often expressed as latitude and longitude) from other geographic data, such as street addresses, or postal codes • Google Geocoding is free up to 2500 queries per day, but with numerous restrictions for example geocoding results without displaying them on a Google Map is prohibited

30. Geocoding • geocoder = new GClientGeocoder(); • var point = new GLatLng(lat, lon); • userpoint = point; • var latlng = point; • geocoder.getLocations(latlng, getCurrentAddr); • lat_des = formatLat(lat); • lon_des = formatLon(lon); • userinfo = '<b>Current location: </b><br>' + lat_des + " " + lon_des + '<br>' + city+" "+country;

31. Geocoding • function getCurrentAddr(response) • { • if (!response || response.Status.code != 200) { • alert("Status Code:" + response.Status.code); • } else { • place = response.Placemark[0]; • currentAddress = place.address; • } • }

32. Reverse geocoding • the opposite to geocoding: finding an associated textual location such as a street address, from geographic coordinates

33. Google map object • function createMap( lat, lon, canvas_id, zoom ) • { • var map = new GMap2(document.getElementById(canvas_id)); • map.setCenter(new GLatLng(lat, lon), zoom); • map.addControl(new GMapTypeControl()); //Map Type-choosing tool. • map.addControl(new GLargeMapControl()); //Zoom tool. • if(loggedInUser && loggedInUser.settings.wheel_zoom) { • map.enableScrollWheelZoom(); • map.enableContinuousZoom(); • } • return map; • }

34. Listeners on Google map • clicklistener = GEvent.addListener(map, "click", function(overlay, latlng){ • if (overlay instanceof GMarker) { • for (var j=0;j<segmentMiddleMarkerArray.length;j++) • { • if (segmentMiddleMarkerArray[j].getLatLng() == overlay.getLatLng()) • showSegmentBubble(segmentMiddleMarkerArray[j].getLatLng().lat(), segmentMiddleMarkerArray[j].getLatLng().lng(), speedStrArray[j], distanceStrArray[j], timeStrArray[j], iconArrayNonAnimated[j], j+1); • } • }});

35. GIS databases • PostgreSQL with PostGIS • Oracle with Oracle Spatial • MySQL spatial extention

36. PostgreSQL with PostGIS • open source software program that adds support for geographic objects to the PostgreSQL • geometry types for points, polygons, etc. • spatial operators for determining geospatial measurements like area, distance, length and perimeter • spatial operators for determining geospatial set operations, like union, difference, symmetric difference and buffers • spatial indexes for high speed spatial querying • index selectivity support, to provide high performance query plans for mixed spatial/non-spatial queries

37. Oracle with Oracle Spatial • separately-licensed option component of the Oracle database • managing geographic and location-data in a native type within an Oracle database • schema that prescribes the storage, syntax, and semantics of supported geometricdata types • spatial indexing system • operators, functions, and procedures for performing area-of-interest queries, spatial join queries, and other spatial analysis operations

38. MySQL spatial extenstion • supports spatial extensions to enable the generation, storage, and analysis of geographic features • before MySQL 5.0.16, these features are available for MyISAM tables only, as of MySQL 5.0.16, InnoDB, NDB, BDB, and ARCHIVE support spatial features

39. MySQL spatial extension MySQL has data types that correspond to OpenGIS classes. Some of these types hold single geometry values: • GEOMETRY • POINT • LINESTRING • POLYGON GEOMETRY can store geometry values of any type. The other single-value types (POINT, LINESTRING, and POLYGON) restrict their values to a particular geometry type. The other data types hold collections of values: • MULTIPOINT • MULTILINESTRING • MULTIPOLYGON • GEOMETRYCOLLECTION GEOMETRYCOLLECTION can store a collection of objects of any type. The other collection types (MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION) restrict collection members to those having a particular geometry type.