public static function batchGeocode($max_records_to_geocode) { $count = 0; $cached_count = 0; $hcps = DB::table(static::$table)->where('geo_cache_id', null)->take(5000)->get(); $hcps_not_geocoded = []; foreach ($hcps as $hcp) { Log::debug("HCP ID:" . $hcp->hcp_id); list($geo, $cached, $status) = GeoCache::findByAddressOrCreateWithNewnessIndicator(Hcp::getGeocodeAddress($hcp)); if ($status == 'OK' || $status == 'LOCALLY_CACHED') { HcpImport::update($hcp->hcp_id, ['lat' => $geo->lat, 'lng' => $geo->lng, 'geo_cache_id' => $geo->id, 'geo_address' => $geo->address]); Log::debug("Geocode OK:", ['hcp_id' => $hcp->hcp_id, 'status' => $status]); } else { Log::error("Geocode Error:", ['hcp_id' => $hcp->hcp_id, 'address' => Hcp::getGeocodeAddress($hcp), 'status' => $status]); $hcps_not_geocoded[] = $hcp; } if (!$cached) { Log::debug("{$count} new geocodes"); $count++; } else { Log::debug("{$cached_count} cached geocodes"); $cached_count++; } if ($count > $max_records_to_geocode) { break; } } return $hcps_not_geocoded; }
/** * @return \Illuminate\View\View */ public function import() { $import_path = "/var/www/vhosts/dev1/docroot/import/"; // it appears there are some unexpected commas in the data so csv is difficult to fully parse //$import_file = "bup_providers_data.csv"; // using pipe delimeted works better $import_file = "bup_providers_data-pipe.txt"; $terminator = "|"; $file = $import_path . $import_file; $import_statement = "LOAD DATA INFILE '{$file}' INTO TABLE " . HcpImport::getTable() . " FIELDS TERMINATED BY '{$terminator}' OPTIONALLY ENCLOSED BY '\"'\nLINES TERMINATED BY '\n' IGNORE 2 LINES (first_name,m_name,last_name,suffix,address_line1,address_line2,city,state,zip_code,phone)"; // import statement: // LOAD DATA INFILE '/var/www/vhosts/dev1/docroot/import/bup_providers_data-pipe.txt' INTO TABLE hcp_import FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' LINES TERMINATED BY '\n' IGNORE 2 LINES (first_name,m_name,last_name,suffix,address_line1,address_line2,city,state,zip_code,phone) // fails from PHP due to some driver/buffering issue, haven't found a solution yet. // Field order from SAMSHA csv // First Name,Middle Name,Last Name,Suffix,Address Line 1,Address Line2,City,State,Zip Code,Phone DB::statement($import_statement); $view_data = ['file' => $file, 'sql' => $import_statement]; return view('data.import', ['data' => $view_data]); }