从数据库查询创建Google地图标记

Question

I have a database of approx 4000 addresses that I would like to have listed as markers on a google map... My end goal is to have two separate queries to display two types of markers, but for now I would like to know how to combine the results from my php to the locations variable in my javascript for the map. Here is the PHP which successfully echos the latitude and logitude...

$address = pg_query($conn, "
SELECT 
  incident.address,
  incident.city,
  incident.state_1
FROM 
  fpscdb001_ws_001.incident
WHERE
  incident.initial_symptom = 'Chrome Upgrade' AND
  incident.is_installed != 'true';");

  if (!$address) {
          echo "Query failed
";
          exit;
        }
while ($markers = pg_fetch_row($address)){
  $request_url = "http://maps.googleapis.com/maps/api/geocode/xml?address=".$markers[0]."&sensor=true";
  $xml = simplexml_load_file($request_url) or die("url not loading");
  $status = $xml->status;
  if ($status=="OK") {
  $Lat = $xml->result->geometry->location->lat;
  $Lon = $xml->result->geometry->location->lng;
  $LatLng = "$Lat,$Lon";
  }
echo "<td>$LatLng</td>";

  }

Here is the javascript... the sample code has lat and lng listed, I assume this is where I need to execute the php.

  function initMap() {

    var map = new google.maps.Map(document.getElementById('map'), {
      zoom: 5,
      center: {lat: 39.350033, lng: -94.6500523}
    });

    // Do not actually need labels for our purposes, but the site_id is best if they are required.
    var labels = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';



    // Add some markers to the map.
    // Note: The code uses the JavaScript Array.prototype.map() method to
    // create an array of markers based on a given "locations" array.
    // The map() method here has nothing to do with the Google Maps API.
    var markers = locations.map(function(location, i) {
      return new google.maps.Marker({
        position: location,
        label: labels[i % labels.length]
      });
    });

    // Find a way to set lat and lng locations from database.
    var markerCluster = new MarkerClusterer(map, markers,
        {imagePath: 'https://developers.google.com/maps/documentation/javascript/examples/markerclusterer/m'});
  }


  var locations = //some code to populate from database

I have an api key and have included the clustering javascript.

Answer 1

This should do it:

PHP:

$arr = array();
while ($markers = pg_fetch_row($address)){
  ....
  $Lat = $xml->result->geometry->location->lat;
  $Lon = $xml->result->geometry->location->lng;
  $arr[] = array("lat" => $Lat, "lng" => $Lng);
}
echo json_encode($arr);

As you can see, I'm creating an array and then converting in into a JSON formatted string. The JSON will have this format [{lat:Number, lng:Number},....] Exactly as the expected format for the JavaScript locations array.

JavaScript:

var xhr = new XMLHttpRequest();
xhr.onreadystatechange = function() {
    if (xhr.readyState == 4) {
        var arr = JSON.parse(xhr.responseText);
        if(Array.isArray(arr)){
            showMarkers(arr);
        }
    }
}
xhr.open('GET', 'link/to/php/script', true);
xhr.send();

function showMarkers(locations){
    var markers = locations.map(function(location, i) {
      return new google.maps.Marker({
        position: location,
        label: labels[i % labels.length]
      });
    });

    var markerCluster = new MarkerClusterer(map, markers, {imagePath: 'https://developers.google.com/maps/documentation/javascript/examples/markerclusterer/m'});
}

If you're using jQuery you can simplify the AJAX call.

The onreadystatechange as the initMap function is executed asynchronously, you'll have to make sure the map is ready before you make the AJAX call.