StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POcron job - geocode address field to lat & long in mySQL database
text
Body
copied!<p>Has anyone done this, or have an idea?</p> <p>I have a real estate script that for whatever reasons (google mapping limits, script itself) only gets the coordinates for half of the listings. I want to run a script that of the lat & long field are empty the script will use the address, city, state to populate the lat & long fields.</p> <p>THoughts?</p> <p>Edit:</p> <p>Here's a code from google, that does allow you to geocode for a database: <a href="http://code.google.com/apis/maps/articles/phpsqlgeocode.html" rel="nofollow noreferrer">http://code.google.com/apis/maps/articles/phpsqlgeocode.html</a></p> <pre><code><?php require("phpsqlgeocode_dbinfo.php"); define("MAPS_HOST", "maps.google.com"); define("KEY", "abcdefg"); // Opens a connection to a MySQL server $connection = mysql_connect("localhost", $username, $password); if (!$connection) { die("Not connected : " . mysql_error()); } // Set the active MySQL database $db_selected = mysql_select_db($database, $connection); if (!$db_selected) { die("Can\'t use db : " . mysql_error()); } // Select all the rows in the markers table $query = "SELECT * FROM markers WHERE 1"; $result = mysql_query($query); if (!$result) { die("Invalid query: " . mysql_error()); } // Initialize delay in geocode speed $delay = 0; $base_url = "http://" . MAPS_HOST . "/maps/geo?output=xml" . "&key=" . KEY; // Iterate through the rows, geocoding each address while ($row = @mysql_fetch_assoc($result)) { $geocode_pending = true; while ($geocode_pending) { $address = $row["address"]; $id = $row["id"]; $request_url = $base_url . "&q=" . urlencode($address); $xml = simplexml_load_file($request_url) or die("url not loading"); $status = $xml->Response->Status->code; if (strcmp($status, "200") == 0) { // Successful geocode $geocode_pending = false; $coordinates = $xml->Response->Placemark->Point->coordinates; $coordinatesSplit = split(",", $coordinates); // Format: Longitude, Latitude, Altitude $lat = $coordinatesSplit[1]; $lng = $coordinatesSplit[0]; $query = sprintf("UPDATE markers " . " SET lat = '%s', lng = '%s' " . " WHERE id = '%s' LIMIT 1;", mysql_real_escape_string($lat), mysql_real_escape_string($lng), mysql_real_escape_string($id)); $update_result = mysql_query($query); if (!$update_result) { die("Invalid query: " . mysql_error()); } } else if (strcmp($status, "620") == 0) { // sent geocodes too fast $delay += 100000; } else { // failure to geocode $geocode_pending = false; echo "Address " . $address . " failed to geocoded. "; echo "Received status " . $status . " \n"; } usleep($delay); } } ?> </code></pre> <p>I'm not sure where to start getting this to work with my database and my lat & long rows (declat and declong) </p> <p>Any help would be appreciated. Thanks!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload