Level 54
Have many addresses are you looking to process?
Sep 30, 2020
18
Level 7
BULK update query of lat /long from postcodes
Hi,
So I have a large database of addresses which I would like to update the latitude and longitude form the postscodes which is the only field I have.
so, using the BULK lookup postcodes from here: https://postcodes.io/
I believe I would need to create a page to output JSON object of an array of postcodes?!
then post that using axios?!
then UPDATE my latitude and longitude fields accordingly?
can anyone help me a bit here?
thanks
Level 7
I have multiple postcodes, I just want to update latitude and longitude from the postcode
Level 54
@boyjarv understood you have some to convert, what number?100? 1000? 10,000?
just trying to get a scale of the work, as if its a few hundred, it'll be a simpler process than a few million.
Level 7
so at my url, I have
{ "postcodes" : [ "BN12 5NE", "BA21 3JD", "SK4 3JT", "BT6 8EF", "BT62 2EZ", "BT1 3DE", "BT38 7AE"] }
here is my blade file:
{
"postcodes" : [
@foreach($postcodes as $postcode)
"{{$postcode->postcode}}",
@endforeach
]
}
here is my controller:
public function postcodes() {
$postcodes = Venue::select('postcode')->distinct()->get();
return view('venues.postcodes', compact(
'postcodes'
));
}
surely I can just POST that JSON to: api.postcodes.io/postcodes
Level 122
Here, a class that calls postcodes.io
<?php
namespace App\Utility;
use App\Postcode;
use Illuminate\Support\Facades\Log;
use \Exception;
class PostcodeLookup
{
/**
* Lookups up postcodes using postcodes.io
* Status from https://status.ideal-postcodes.co.uk/
* returns 0,0 if service fails, and writes message to logfile.
* Uses database to cache previous lookups
*
* @param string $postcode a valid UK postcode
* @return array with lat and lon as keys values as string
*/
public function lookup(string $postcode): array
{
$postcode = strtoupper(str_replace(' ','',$postcode));
$result = cache()->remember('postcode'.$postcode, now()->addMinute(1), function () use($postcode) {
$dbresult = Postcode::where('postcode', $postcode)->select(['postcode', 'latitude', 'longitude'])->first();
if($dbresult) {
return $dbresult->toArray();
}
return null;
});
if($result){
if($result['longitude'] == 0) {
Log::warning("Postcode coordinates returned are 52,0 for `{$postcode}`");
}
return ['lat' => $result['latitude'], 'lon' => $result['longitude']];
}
try {
$result = $this->strategies($postcode);
} catch (Exception $e) {
Log::Error("Postcode Lookup failed for `{$postcode}`");
Postcode::create(['postcode' => $postcode, 'latitude' => 52, 'longitude' => 0]);
return ['lat' => 52, 'lon' => 0];
}
Postcode::create([
'postcode' => $postcode,
'latitude' => $result['lat'],
'longitude' => $result['lon'],
]);
return $result;
}
/**
* uses various strategies to resolve postcode to latlong
* returns the first result
*
* @param string $postcode The postcode string to find
* @return array lat,long
*/
public function strategies(string $postcode):array
{
if($result = $this->directLookup($postcode)) {
return $result;
}
if ($result = $this->terminatedLookup($postcode)) {
return $result;
}
if ($result = $this->partialLookup($postcode)) {
return $result;
}
Log::warning("No viable postcode lookup strategies for '$postcode'");
throw new Exception('No Viable Postcode Strategy');
}
/**
* performs exact match lookup for the postcode
*
* @param string $postcode
* @return array
*/
public function directLookup(string $postcode): ?array
{
try {
$result = json_decode(file_get_contents("https://api.postcodes.io/postcodes/$postcode"));
return ['lat' => (string) $result->result->latitude, 'lon' => (string) $result->result->longitude];
} catch (\Throwable $th) {
return null;
}
}
/**
* performs a search to see if the postcode was previously terminated
*
* @param string $postcode
* @return array
*/
public function terminatedLookup(string $postcode): ?array
{
try {
$result = json_decode(file_get_contents("https://api.postcodes.io/terminated_postcodes/$postcode"));
Log::info("Resolved failed postcode by looking in terminated codes for `$postcode`");
return ['lat' => (string) $result->result->latitude, 'lon' => (string) $result->result->longitude];
} catch (\Throwable $th) {
return null;
}
}
public function partialLookup($postcode)
{
$original = $postcode;
while(strlen($postcode)>3){
try {
$result = json_decode(file_get_contents("https://api.postcodes.io/postcodes/$postcode/autocomplete"));
} catch (\Throwable $th) {
return null;
}
$codes = $result->result;
if($codes) {
$resolve = $codes[0]; //unintelligently grabs the first from the stack
Log::info("Using partial postcode lookup. Aliased $original to $resolve ");
return $this->directLookup($resolve);
}
$postcode = substr($postcode, 0, -1);
}
return null;
}
}
You will see I use a table to store previous lookups so that the postcode can be resolved quickly if it has been checked before.
I'm sure you could adapt this to your needs
Level 7
Thanks but you give me that and I don't know where to start to loop over all my postcodes and then update fields in my database?!
Level 7
I need to post to this API: api.postcodes.io/postcodes
an object like this:
{
"postcodes" : ["BN12 5NE", "BN158AR", "NE30 1DP"]
}
Level 7
so I changed my function to this:
public function postcodes() {
$postcodes = Venue::select('postcode')->distinct()->get();
return response()->json($postcodes);
}
but now I'm getting an array of objects?!:
[{"postcode":"BN12 5NE"},{"postcode":"BA21 3JD"},{"postcode":"SK4 3JT"},{"postcode":"BT6 8EF"},{"postcode":"BT62 2EZ"},{"postcode":"BT1 3DE"},{"postcode":"BT38 7AE"},{"postcode":"BT8 6GQ"},{"postcode":"BT21 0AH"},{"postcode":"BT6 9PE"},{"postcode":"BT36 7QN"},{"postcode":"BT36 5HL"},{"postcode":"BT6 9SA"},{"postcode":"BT10 0LL"},]
grr please help
Level 122
Instantiate the class then pass the postcodes one by one to the lookup() function. The function returns an array of coordinates.
It copes with postcodes that are terminated, and estimates the location of ones that are not known by gradually reducing the number of characters in the postcode.
If you don't know how to initiate a class or iterate over an array then you have bigger problems than looking up postcodes.
Level 7
I don't have postcode model like in your method
Level 122
oh well, never mind
Level 7
anyone else?
Level 54
@boyjarv don't pass the postcodes to the view. You need to do this within the controller.
an absolutely basic (don't shoot me @snapey) way to do this is as follows
public function postcodes()
{
// gets Venues which don't have latitude set.
$venues = Venue::select('postcode')->whereNull('latitude')->distinct()->limit(100)->get();
foreach ($venues as $venue) {
$response = Http::post('api.postcodes.io/postcodes', ['postcodes' => [$venue->postcode]]);
// @todo - add some checks for successful response.
$results = $response->json()['result'];
// updates venues which have matching postcode
$venue->where('postcode', $venue->postcode)
->update([
'latitude' => $results[0]['result']['latitude'],
'longitude' => $results[0]['result']['longitude']
]);
}
return view('venues.postcodes', compact(
'postcodes'
));
}
this does 100 at a time and updates your venue table with the result.
if you have loads, you should build a script that batches these as a background task. You'd definitely benefit from using @snapey's PostcodeLookup class. Mine is rough and ready.
Level 122
I think you will be surprised how many lookups fail. That code was built from crap customer data.
If you want a simple lookup with no caching and no fallback strategy, just call the directLookup() function
Level 7
Level 7
one small thing:
Class 'App\Http\Controllers\Http' not found
do I need to run
npm i http
Level 54
@boyjarv you need to tell laravel where that class lives:
add
use Illuminate\Support\Facades\Http;
to the top of your Venue controller
Level 7
I have, still getting the error
Please or to participate in this conversation.