/** * geocodes all missing records in a DB table and then stores the values * in the DB record * * only works if your DB table has the necessary fields * helpful when calculating a batch of addresses and save the latitude/longitude automatically */ public function calculateCoordinatesForAllRecordsInTable($tableName, $latitudeField = 'latitude', $longitudeField = 'longitude', $streetField = 'street', $zipField = 'zip', $cityField = 'city', $countryField = 'country', $addWhereClause = '') { // fetch all records without latitude/longitude $records = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows('*', $tableName, 'deleted=0 AND (' . $latitudeField . ' IS NULL OR ' . $latitudeField . '=0 OR ' . $latitudeField . '=0.00000000000 OR ' . $longitudeField . ' IS NULL OR ' . $longitudeField . '=0 OR ' . $longitudeField . '=0.00000000000)' . $addWhereClause, '', '', '500'); if (class_exists('\\t3lib_div')) { \t3lib_div::loadTCA($tableName); } else { TYPO3\CMS\Core\Utility\GeneralUtility::loadTCA(); } if (count($records) > 0) { foreach ($records as $record) { $country = $record[$countryField]; // resolve the label for the country if ($GLOBALS['TCA'][$tableName]['columns'][$countryField]['config']['type'] == 'select') { foreach ($GLOBALS['TCA'][$tableName]['columns'][$countryField]['config']['items'] as $itm) { if ($itm[1] == $country) { if (is_object($GLOBALS['TSFE'])) { $country = $GLOBALS['TSFE']->sL($itm[0]); } else { $country = $GLOBALS['LANG']->sL($itm[0]); } } } } // do the geocoding $coords = $this->getCoordinatesForAddress($record[$streetField], $record[$zipField], $record[$cityField], $country); if ($coords) { // update the record to fill in the latitude and longitude values in the DB $GLOBALS['TYPO3_DB']->exec_UPDATEquery($tableName, 'uid=' . intval($record['uid']), array($latitudeField => $coords['latitude'], $longitudeField => $coords['longitude'])); } } } return count($records); }