protected function applyLocationCsv($file)
{
$locationColumns = ['id', 'country', 'region', 'city', 'postal', 'latitude', 'longitude', 'create_time', 'update_time'];
$licationDuplicates = ['country', 'region', 'city', 'postal', 'latitude', 'longitude'];
$pointColumns = ['id', 'point'];
$pointDuplicates = ['point'];
$locationRows = [];
$pointRows = [];
if (($handle = fopen($file, 'r')) !== false) {
while (($data = fgetcsv($handle, 1000, ',')) !== false) {
if (!isset($data[0]) || intval($data[0]) === 0) {
continue;
}
$locationRows[] = [(int) $data[0], trim($data[1]), trim($data[2]), @iconv(mb_detect_encoding($data[3]), 'UTF-8', $data[3]), trim($data[4]), (double) $data[5], (double) $data[6], time(), time()];
$pointRows[] = [(int) $data[0], new Expression('GeomFromText("POINT(' . $data[5] . ' ' . $data[6] . ')")')];
if (count($locationRows) === $this->maxExecuteRows) {
$this->batchInsertDuplicate(Locations::tableName(), $locationColumns, $locationRows, $licationDuplicates)->execute();
$this->batchInsertDuplicate(LocationPoint::tableName(), $pointColumns, $pointRows, $pointDuplicates)->execute();
$locationRows = [];
$pointRows = [];
}
}
if (count($locationRows) > 0) {
$this->batchInsertDuplicate(Locations::tableName(), $locationColumns, $locationRows, $licationDuplicates)->execute();
$this->batchInsertDuplicate(LocationPoint::tableName(), $pointColumns, $pointRows, $pointDuplicates)->execute();
}
fclose($handle);
}
}
/**
* @param type $lat
* @param type $lng
* @param type $dist
* @return type
* @throws Exception
*/
public function fromPoint($lat, $lng, $dist = 30)
{
if (!is_float($lat) || !is_float($lng)) {
throw new Exception('Location coords is not valid.');
}
$this->from(Locations::tableName() . ' l');
$this->innerJoin(LocationPoint::tableName() . ' p', 'l.id=p.id');
$this->select('*, ' . GeoHelper::createDistanceCondition($lat, $lng) . ' AS distance');
$this->andWhere(GeoHelper::createPoligonCriteria($lat, $lng, $dist));
$this->orderBy('distance');
return $this;
}
public function down()
{
$this->dropTable(Locations::tableName());
}
/**
* Find location by remote address
* @param type $address
* @return type
*/
protected function resolveByAddress($addr)
{
return Locations::find()->fromBlock($addr)->limit(1)->one();
}
/**
* @return \yii\db\ActiveRelation
*/
public function getLocation()
{
return $this->hasOne(Locations::className(), ['id' => 'id']);
}
