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()); }