Метка: почтовые индексы база

База индексов почтовых отделений Укрпочты

Для одного из сайтов клиентов была поставлена задача создать базу с индексами почтовых отделений Укрпочты для каждого региона Украины и всей страны в целом.

Так как сроки были сжатые — решили сделать решение «на коленке» и вот что получилось:

<?
ini_set("max_execution_time", "6000");
global $wpdb;
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

set_regions();
function set_regions()
{
	global $wpdb;
	$table_name = $wpdb->prefix . "ukrposhta_regions";
	$sql = "CREATE TABLE `$table_name` (
	  `REGION_ID` int(11) NOT NULL,
	  `REGION_UA` text
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	COMMIT;";
	dbDelta( $sql );
	// get regions data
	// get_regions_by_region_ua
	// receive data:
	// ["REGION_ID"]=>
	//   string(3) "262"
	//   ["REGION_UA"]=>
	//   string(18) "Вінницька"
	//   ["REGION_EN"]=>
	//   string(9) "Vinnytska"
	//   ["REGION_KATOTTG"]=>
	//   string(17) "05000000000010236"
	//   ["REGION_KOATUU"]=>
	//   string(9) "500000000"
	//   ["REGION_RU"]=>
	//   NULL
	
	// $region->REGION_UA - region name
	// $region->REGION_ID - region ID
	$data = wp_remote_get('https://www.ukrposhta.ua/address-classifier-ws/get_regions_by_region_ua',
	[
		'headers' => [ 
			'Authorization' => 'Bearer КОД_АПИ_ДЛЯ_ПОДКЛЮЧЕНИЯ',
			'Accept'=> 'application/json'
		]
	]);
	if(wp_remote_retrieve_response_code($data)==200)
	{
		$entries = json_decode($data['body']);
		$delete = $wpdb->query("TRUNCATE TABLE $table_name");
		foreach($entries->Entries->Entry as $entry)
		{
			$wpdb->insert( 
				$table_name, 
				[
					'REGION_UA' => $entry->REGION_UA, 
					'REGION_ID' => $entry->REGION_ID
				]
			);
		}
		echo 'regions scrapper - finish<br />';
		set_districts();
	}
	else
	{
		$data = json_decode($data['body']);
		var_dump($data->fault->code);
		var_dump($data->fault->message);
		die('error');
	}
}
// set_districts();
function set_districts()
{
	global $wpdb;
	$table_name = $wpdb->prefix . "ukrposhta_districts";
	$table_regions_name = $wpdb->prefix . "ukrposhta_regions";
	$sql = "CREATE TABLE `$table_name` (
	  `REGION_ID` int(11) NOT NULL,
	  `DISTRICT_ID` int(11) NOT NULL,
	  `DISTRICT_UA` text
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	COMMIT;";
	dbDelta( $sql );
	
	$result = $wpdb->get_results ( "SELECT * FROM $table_regions_name ORDER BY `REGION_ID`;");
	$delete = $wpdb->query("TRUNCATE TABLE $table_name");
	foreach($result as $region)
	{
		// get districts data
		// $district->DISTRICT_UA name of district
		// $district->DISTRICT_ID - district ID
		// sleep(1);
		$data = wp_remote_get('https://www.ukrposhta.ua/address-classifier-ws/get_districts_by_region_id_and_district_ua?region_id='.
		$region->REGION_ID,
		[
			'headers' => [ 
				'Authorization' => 'Bearer КОД_АПИ_ДЛЯ_ПОДКЛЮЧЕНИЯ',
				'Accept'=> 'application/json'
			]
		]);
		if(wp_remote_retrieve_response_code($data)==200)
		{
			$data = json_decode($data['body']);
			foreach($data->Entries->Entry as $entry)
			{
				$wpdb->insert( 
					$table_name, 
					[
						'REGION_ID' => $region->REGION_ID,
						'DISTRICT_UA' => $entry->DISTRICT_UA, 
						'DISTRICT_ID' => $entry->DISTRICT_ID
					]
				);
			}
			echo 'districts scrapper for region '.$region->REGION_UA.' - finish<br />';
		}
		else
		{
			$data = json_decode($data['body']);
			var_dump($data->fault->code);
			var_dump($data->fault->message);
			die('error '.$region->REGION_UA);
		}
	}
	set_cities();
}
// set_cities();
function set_cities()
{
	global $wpdb;
	$table_name = $wpdb->prefix . "ukrposhta_cities";
	$table_districts_name = $wpdb->prefix . "ukrposhta_districts";
	$sql = "CREATE TABLE `$table_name` (
	  `REGION_ID` int(11) NOT NULL,
	  `DISTRICT_ID` int(11) NOT NULL,
	  `CITY_UA` text,
	  `CITY_ID` int(11) NOT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	COMMIT;";
	dbDelta( $sql );
	
	$result = $wpdb->get_results ( "SELECT * FROM $table_districts_name ORDER BY `DISTRICT_ID`;");
	$delete = $wpdb->query("TRUNCATE TABLE $table_name");
	foreach($result as $district)
	{
		// get cities data
		// $city->CITY_UA - city name
		// $city->CITY_ID - city ID
		$data = wp_remote_get('https://www.ukrposhta.ua/address-classifier-ws/get_city_by_region_id_and_district_id_and_city_ua?district_id='.$district->DISTRICT_ID.'&region_id='.$district->REGION_ID,
		[
			'headers' => [ 
				'Authorization' => 'Bearer КОД_АПИ_ДЛЯ_ПОДКЛЮЧЕНИЯ',
				'Accept'=> 'application/json'
			]
		]);
		if(wp_remote_retrieve_response_code($data)==200)
		{
			$data = json_decode($data['body']);
			foreach($data->Entries->Entry as $entry)
			{
				$wpdb->insert( 
					$table_name, 
					[
						'REGION_ID' => $district->REGION_ID,
						'DISTRICT_ID' => $district->DISTRICT_ID,
						'CITY_UA' => $entry->CITY_UA,
						'CITY_ID' => $entry->CITY_ID
					]
				);
			}
			echo 'cities scrapper for district '.$district->DISTRICT_UA.' - finish<br />';
		}
		else
		{
			$data = json_decode($data['body']);
			var_dump($data->fault->code);
			var_dump($data->fault->message);
			die('error '.$district->DISTRICT_UA);
		}
	}
	set_postoffices()
}
function set_postoffices()
{
	global $wpdb;
	$table_name = $wpdb->prefix . "ukrposhta_post_offices";
	$table_districts_name = $wpdb->prefix . "ukrposhta_cities";
	$sql = "CREATE TABLE `$table_name` (
	  `REGION_UA` text,
	  `DISTRICT_UA` text,
	  `CITY_UA` text,
	  `POSTINDEX` text
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	COMMIT;";
	dbDelta( $sql );
	
	$value = 500*54;
	$result = $wpdb->get_results ( "SELECT * FROM $table_districts_name ORDER BY `CITY_ID` LIMIT $value, 500;");
	$delete = $wpdb->query("TRUNCATE TABLE $table_name");
	foreach($result as $city)
	{
		// get cities data
		// $city->CITY_UA - city name
		// $city->CITY_ID - city ID
		$data = wp_remote_get('https://www.ukrposhta.ua/address-classifier-ws/get_postoffices_by_city_id?city_id='.$city->CITY_ID.'&district_id='.$city->DISTRICT_ID.'&region_id='.$city->REGION_ID,
		[
			'headers' => [ 
				'Authorization' => 'Bearer КОД_АПИ_ДЛЯ_ПОДКЛЮЧЕНИЯ',
				'Accept'=> 'application/json'
			]
		]);
		if(wp_remote_retrieve_response_code($data)==200)
		{
			$data = json_decode($data['body']);
			if(isset($data->Entries->Entry))
			{
				$temp_index = '';	
				foreach($data->Entries->Entry as $entry)
				{
					if($temp_index!=$entry->POSTINDEX)
					{
						$wpdb->insert( 
							$table_name, 
							[
								'REGION_UA' => $entry->REGION_UA,
								'DISTRICT_UA' => $entry->DISTRICT_UA,
								'CITY_UA' => $entry->CITY_UA,
								'POSTINDEX' => $entry->POSTINDEX
							]
						);
					}
					$temp_index = $entry->POSTINDEX;
				}
				echo 'postoffices scrapper for city '.$entry->CITY_UA.' - finish<br />';
			}
			else
			{
				echo $city->CITY_UA.'- no post offices<br />';
			}
		}
		else
		{
			$data = json_decode($data['body']);
			var_dump($data->fault->code);
			var_dump($data->fault->message);
			die('error '.$district->DISTRICT_UA);
		}
	}
}
die();
?>

Реализация текущая для CMS WordPress. Однако, ее можно легко использовать где угодно, заменив <?
ini_set("max_execution_time", "6000");
global $wpdb;
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

set_regions();
function set_regions()
{
	global $wpdb;
	$table_name = $wpdb->prefix . "ukrposhta_regions";
	$sql = "CREATE TABLE `$table_name` (
	  `REGION_ID` int(11) NOT NULL,
	  `REGION_UA` text
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	COMMIT;";
	dbDelta( $sql );
	// get regions data
	// get_regions_by_region_ua
	// receive data:
	// ["REGION_ID"]=>
	//   string(3) "262"
	//   ["REGION_UA"]=>
	//   string(18) "Вінницька"
	//   ["REGION_EN"]=>
	//   string(9) "Vinnytska"
	//   ["REGION_KATOTTG"]=>
	//   string(17) "05000000000010236"
	//   ["REGION_KOATUU"]=>
	//   string(9) "500000000"
	//   ["REGION_RU"]=>
	//   NULL
	
	// $region->REGION_UA - region name
	// $region->REGION_ID - region ID
	$data = wp_remote_get('https://www.ukrposhta.ua/address-classifier-ws/get_regions_by_region_ua',
	[
		'headers' => [ 
			'Authorization' => 'Bearer КОД_АПИ_ДЛЯ_ПОДКЛЮЧЕНИЯ',
			'Accept'=> 'application/json'
		]
	]);
	if(wp_remote_retrieve_response_code($data)==200)
	{
		$entries = json_decode($data['body']);
		$delete = $wpdb->query("TRUNCATE TABLE $table_name");
		foreach($entries->Entries->Entry as $entry)
		{
			$wpdb->insert( 
				$table_name, 
				[
					'REGION_UA' => $entry->REGION_UA, 
					'REGION_ID' => $entry->REGION_ID
				]
			);
		}
		echo 'regions scrapper - finish<br />';
		set_districts();
	}
	else
	{
		$data = json_decode($data['body']);
		var_dump($data->fault->code);
		var_dump($data->fault->message);
		die('error');
	}
}
// set_districts();
function set_districts()
{
	global $wpdb;
	$table_name = $wpdb->prefix . "ukrposhta_districts";
	$table_regions_name = $wpdb->prefix . "ukrposhta_regions";
	$sql = "CREATE TABLE `$table_name` (
	  `REGION_ID` int(11) NOT NULL,
	  `DISTRICT_ID` int(11) NOT NULL,
	  `DISTRICT_UA` text
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	COMMIT;";
	dbDelta( $sql );
	
	$result = $wpdb->get_results ( "SELECT * FROM $table_regions_name ORDER BY `REGION_ID`;");
	$delete = $wpdb->query("TRUNCATE TABLE $table_name");
	foreach($result as $region)
	{
		// get districts data
		// $district->DISTRICT_UA name of district
		// $district->DISTRICT_ID - district ID
		// sleep(1);
		$data = wp_remote_get('https://www.ukrposhta.ua/address-classifier-ws/get_districts_by_region_id_and_district_ua?region_id='.
		$region->REGION_ID,
		[
			'headers' => [ 
				'Authorization' => 'Bearer КОД_АПИ_ДЛЯ_ПОДКЛЮЧЕНИЯ',
				'Accept'=> 'application/json'
			]
		]);
		if(wp_remote_retrieve_response_code($data)==200)
		{
			$data = json_decode($data['body']);
			foreach($data->Entries->Entry as $entry)
			{
				$wpdb->insert( 
					$table_name, 
					[
						'REGION_ID' => $region->REGION_ID,
						'DISTRICT_UA' => $entry->DISTRICT_UA, 
						'DISTRICT_ID' => $entry->DISTRICT_ID
					]
				);
			}
			echo 'districts scrapper for region '.$region->REGION_UA.' - finish<br />';
		}
		else
		{
			$data = json_decode($data['body']);
			var_dump($data->fault->code);
			var_dump($data->fault->message);
			die('error '.$region->REGION_UA);
		}
	}
	set_cities();
}
// set_cities();
function set_cities()
{
	global $wpdb;
	$table_name = $wpdb->prefix . "ukrposhta_cities";
	$table_districts_name = $wpdb->prefix . "ukrposhta_districts";
	$sql = "CREATE TABLE `$table_name` (
	  `REGION_ID` int(11) NOT NULL,
	  `DISTRICT_ID` int(11) NOT NULL,
	  `CITY_UA` text,
	  `CITY_ID` int(11) NOT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	COMMIT;";
	dbDelta( $sql );
	
	$result = $wpdb->get_results ( "SELECT * FROM $table_districts_name ORDER BY `DISTRICT_ID`;");
	$delete = $wpdb->query("TRUNCATE TABLE $table_name");
	foreach($result as $district)
	{
		// get cities data
		// $city->CITY_UA - city name
		// $city->CITY_ID - city ID
		$data = wp_remote_get('https://www.ukrposhta.ua/address-classifier-ws/get_city_by_region_id_and_district_id_and_city_ua?district_id='.$district->DISTRICT_ID.'&region_id='.$district->REGION_ID,
		[
			'headers' => [ 
				'Authorization' => 'Bearer КОД_АПИ_ДЛЯ_ПОДКЛЮЧЕНИЯ',
				'Accept'=> 'application/json'
			]
		]);
		if(wp_remote_retrieve_response_code($data)==200)
		{
			$data = json_decode($data['body']);
			foreach($data->Entries->Entry as $entry)
			{
				$wpdb->insert( 
					$table_name, 
					[
						'REGION_ID' => $district->REGION_ID,
						'DISTRICT_ID' => $district->DISTRICT_ID,
						'CITY_UA' => $entry->CITY_UA,
						'CITY_ID' => $entry->CITY_ID
					]
				);
			}
			echo 'cities scrapper for district '.$district->DISTRICT_UA.' - finish<br />';
		}
		else
		{
			$data = json_decode($data['body']);
			var_dump($data->fault->code);
			var_dump($data->fault->message);
			die('error '.$district->DISTRICT_UA);
		}
	}
	set_postoffices()
}
function set_postoffices()
{
	global $wpdb;
	$table_name = $wpdb->prefix . "ukrposhta_post_offices";
	$table_districts_name = $wpdb->prefix . "ukrposhta_cities";
	$sql = "CREATE TABLE `$table_name` (
	  `REGION_UA` text,
	  `DISTRICT_UA` text,
	  `CITY_UA` text,
	  `POSTINDEX` text
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	COMMIT;";
	dbDelta( $sql );
	
	$value = 500*54;
	$result = $wpdb->get_results ( "SELECT * FROM $table_districts_name ORDER BY `CITY_ID` LIMIT $value, 500;");
	$delete = $wpdb->query("TRUNCATE TABLE $table_name");
	foreach($result as $city)
	{
		// get cities data
		// $city->CITY_UA - city name
		// $city->CITY_ID - city ID
		$data = wp_remote_get('https://www.ukrposhta.ua/address-classifier-ws/get_postoffices_by_city_id?city_id='.$city->CITY_ID.'&district_id='.$city->DISTRICT_ID.'&region_id='.$city->REGION_ID,
		[
			'headers' => [ 
				'Authorization' => 'Bearer КОД_АПИ_ДЛЯ_ПОДКЛЮЧЕНИЯ',
				'Accept'=> 'application/json'
			]
		]);
		if(wp_remote_retrieve_response_code($data)==200)
		{
			$data = json_decode($data['body']);
			if(isset($data->Entries->Entry))
			{
				$temp_index = '';	
				foreach($data->Entries->Entry as $entry)
				{
					if($temp_index!=$entry->POSTINDEX)
					{
						$wpdb->insert( 
							$table_name, 
							[
								'REGION_UA' => $entry->REGION_UA,
								'DISTRICT_UA' => $entry->DISTRICT_UA,
								'CITY_UA' => $entry->CITY_UA,
								'POSTINDEX' => $entry->POSTINDEX
							]
						);
					}
					$temp_index = $entry->POSTINDEX;
				}
				echo 'postoffices scrapper for city '.$entry->CITY_UA.' - finish<br />';
			}
			else
			{
				echo $city->CITY_UA.'- no post offices<br />';
			}
		}
		else
		{
			$data = json_decode($data['body']);
			var_dump($data->fault->code);
			var_dump($data->fault->message);
			die('error '.$district->DISTRICT_UA);
		}
	}
}
die();
?>

Реализация текущая для CMS WordPress. Однако, ее можно легко использовать где угодно, заменив wp_remote_get на команды cURL и заменив запросы к БД и ее таблицам на соответствующие команды, которые будут работать в конкретном вашем случае.

Таблицы у нас 4:

  • данные с областями
  • данные с районами
  • данные с городами
  • данные с индексами почтовых отделений.

Также, если кому нужна уже собранная база — напишите — вышлем базу со всеми данными.