接之前一篇文章,我们使用mysql来处理geometry相关数据。用到了laravel-mysql-spatial来处理geometry数据。今天正好有时间整理了laravel-mysql-spatial完整的使用方法。机器翻译为主哈,使用通俗易懂的文字加以描述。原文在这里 https://github.com/grimzy/laravel-mysql-spatial

开始之前必须要了解的概念: SRID (空间引用识别号, 坐标系)
地球并不是平的,也不是正圆形,而是一个非标准的椭圆,在不同的经纬度位置,计算两点的距离没有一个完美的公式(即快速、有准确)。
所以坐标系应运而生,SRID是指数据的坐标系,例如: 在PostGIS中可以查到支持的SRID。查询与北京、中国有关的坐标系。
postgres=# select from spatial_ref_sys where srtext ~ 'beijing';
postgres=# select from spatial_ref_sys where srtext ~ 'china';

数据可以在不同的坐标系之间转换,在进行GIS计算时,一定要在同一个坐标系中进行。

Laravel 包可轻松使用MySQL 空间数据类型和 MySQL 空间函数。
请检查 MySQL 版本的文档。MySQL 的空间数据扩展在 MySQL 5.5 中添加,但许多空间函数在 5.6 和 5.7 中已更改。
版本

  • 1.x.x: MySQL 5.6 (也支持 MySQL 5.5, 但不是所有空间分析函数)
  • 2.x.x: MySQL 5.7
  • 3.x.x: MySQL 8.0,支持 SRID(当前分支)
    此包还适用于 MariaDB。有关兼容性,请参阅 MySQL/MariaDB空间支持矩阵。

安装

使用composer添加包:

$ composer require grimzy/laravel-mysql-spatial
对于 MySQL 5.7:
$ composer require grimzy/laravel-mysql-spatial:^2.0
对于 MySQL 5.6 和 5.5:
$ composer require grimzy/laravel-mysql-spatial:^1.0
对于 5.5 之前或不使用自动发现中的 Laravel 版本,请注册service provider:config/app.php
'providers' => [
  /*
   * Package Service Providers...
   */
  GrimzyLaravelMysqlSpatialSpatialServiceProvider::class,
],

快速入门

创建迁移

从命令行:
php artisan make:migration create_places_table

然后通过添加至少一个空间数据字段来编辑刚刚创建的迁移。对于 5.5 之前的 Laravel 版本,您可以使用此包提供的Blueprint (GrimzyLaravelMysqlSpatialSchemaBlueprint):

use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;

// For Laravel < 5.5
// use GrimzyLaravelMysqlSpatialSchemaBlueprint;

class CreatePlacesTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('places', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('name')->unique();
            // Add a Point spatial data field named location
            $table->point('location')->nullable();
            // Add a Polygon spatial data field named area
            $table->polygon('area')->nullable();
            $table->timestamps();
        });
  
        // Or create the spatial fields with an SRID (e.g. 4326 WGS84 spheroid)
  
        // Schema::create('places', function(Blueprint $table)
        // {
        //     $table->increments('id');
        //     $table->string('name')->unique();
        //     // Add a Point spatial data field named location with SRID 4326
        //     $table->point('location', 4326)->nullable();
        //     // Add a Polygon spatial data field named area with SRID 4326
        //     $table->polygon('area', 4326)->nullable();
        //     $table->timestamps();
        // });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('places');
    }
}

运行迁移:

php artisan migrate

创建模型

从命令行:
php artisan make:model Place

然后编辑刚刚创建的模型。必须使用 和 定义调用的数组,该数组的名称为在迁移中创建的 MySQL 空间数据字段的名称:SpatialTrait $spatialFields (Trait语法 应该懂一点)

namespace App;

use IlluminateDatabaseEloquentModel;
use GrimzyLaravelMysqlSpatialEloquentSpatialTrait;

/**
 * @property GrimzyLaravelMysqlSpatialTypesPoint   $location
 * @property GrimzyLaravelMysqlSpatialTypesPolygon $area
 */
class Place extends Model
{
    use SpatialTrait;

    protected $fillable = [
        'name'
    ];

    protected $spatialFields = [
        'location',
        'area'
    ];
}

保存模型(保存数据)

use GrimzyLaravelMysqlSpatialTypesPoint;
use GrimzyLaravelMysqlSpatialTypesPolygon;
use GrimzyLaravelMysqlSpatialTypesLineString;

$place1 = new Place();
$place1->name = 'Empire State Building';

// saving a point
$place1->location = new Point(40.7484404, -73.9878441);    // (lat, lng)
$place1->save();

// saving a polygon
$place1->area = new Polygon([new LineString([
    new Point(40.74894149554006, -73.98615270853043),
    new Point(40.74848633046773, -73.98648262023926),
    new Point(40.747925497790725, -73.9851602911949),
    new Point(40.74837050671544, -73.98482501506805),
    new Point(40.74894149554006, -73.98615270853043)
])]);
$place1->save();

或者,如果数据库字段是使用特定的 SRID 创建的:

use GrimzyLaravelMysqlSpatialTypesPoint;
use GrimzyLaravelMysqlSpatialTypesPolygon;
use GrimzyLaravelMysqlSpatialTypesLineString;

$place1 = new Place();
$place1->name = 'Empire State Building';

// saving a point with SRID 4326 (WGS84 spheroid)
$place1->location = new Point(40.7484404, -73.9878441, 4326);    // (lat, lng, srid)
$place1->save();

// saving a polygon with SRID 4326 (WGS84 spheroid)
$place1->area = new Polygon([new LineString([
    new Point(40.74894149554006, -73.98615270853043),
    new Point(40.74848633046773, -73.98648262023926),
    new Point(40.747925497790725, -73.9851602911949),
    new Point(40.74837050671544, -73.98482501506805),
    new Point(40.74894149554006, -73.98615270853043)
])], 4326);
$place1->save();

Note: When saving collection Geometries (, , , , and ), only the top-most geometry should have an SRID set in the constructor.LineStringPolygonMultiPointMultiLineStringGeometryCollection
In the example above, when creating a , we only set the SRID on the and use the default for the and the objects.new Polygon()PolygonLineStringPoint

模型检索(读取数据)


$place2 = Place::first();
$lat = $place2->location->getLat(); // 40.7484404
$lng = $place2->location->getLng(); // -73.9878441

Geometry classes

可用的几何类

  • GrimzyLaravelMysqlSpatialTypes OpenGIS 类
  • Point($lat, $lng, $srid = 0) Point
  • MultiPoint(Point[], $srid = 0) MultiPoint
  • LineString(Point[], $srid = 0) LineString
  • MultiLineString(LineString[], $srid = 0) MultiLineString
  • Polygon(LineString[], $srid = 0) (外部和内部边界) Polygon
  • MultiPolygon(Polygon[], $srid = 0) MultiPolygon
  • GeometryCollection(Geometry[], $srid = 0) GeometryCollection
    查看类图。

使用几何类
Using Geometry classes
In order for your Eloquent Model to handle the Geometry classes, it must use the trait and define a property as an array of MySQL Spatial Data Type column names (example in Quickstart).GrimzyLaravelMysqlSpatialEloquentSpatialTraitprotected$spatialFields

IteratorAggregate and ArrayAccess
The collection Geometries (, , , , and ) implement IteratorAggregate and ArrayAccess; making it easy to perform Iterator and Array operations. For example:LineStringPolygonMultiPointMultiLineStringGeometryCollection
$polygon = $multipolygon[10]; // ArrayAccess


// IteratorAggregate
for($polygon as $i => $linestring) {
echo (string) $linestring;
}

laravel-mysql-spatial提供的帮助

从可视化文本或者和转换成可视化文本(WKT)

// fromWKT($wkt, $srid = 0)
$point = Point::fromWKT('POINT(2 1)');
$point->toWKT();    // POINT(2 1)

$polygon = Polygon::fromWKT('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))');
$polygon->toWKT();    // POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))

从字符串或者转换成字符串

// fromString($wkt, $srid = 0)
$point = new Point(1, 2);    // lat, lng
(string)$point            // lng, lat: 2 1

$polygon = Polygon::fromString('(0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1)');
(string)$polygon;    // (0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1)

从JSON或者转换成JSON (Geojson)
几何类实现Json 序列化,并帮助序列化为 GeoJSON:IlluminateContractsSupportJsonable

$point = new Point(40.7484404, -73.9878441);

json_encode($point); // or $point->toJson();

// {
//   "type": "Feature",
//   "properties": {},
//   "geometry": {
//     "type": "Point",
//     "coordinates": [
//       -73.9878441,
//       40.7484404
//     ]
//   }
// }

若要将 GeoJSON 字符串去功能化到几何类中,可以使用 :Geometry::fromJson($json_string)

$location = Geometry::fromJson('{"type":"Point","coordinates":[3.4,1.2]}');
$location instanceof Point::class; // true
$location->getLat(); // 1.2
$location->getLng()); // 3.4

范围:空间分析功能(空间计算函数)

空间分析函数使用 Eloquent Local Scopes.实现。
可用函数:

  • distance($geometryColumn, $geometry, $distance)
  • distanceExcludingSelf($geometryColumn, $geometry, $distance)
  • distanceSphere($geometryColumn, $geometry, $distance)
  • distanceSphereExcludingSelf($geometryColumn, $geometry, $distance)
  • comparison($geometryColumn, $geometry, $relationship)
  • within($geometryColumn, $polygon)
  • crosses($geometryColumn, $geometry)
  • contains($geometryColumn, $geometry)
  • disjoint($geometryColumn, $geometry)
  • equals($geometryColumn, $geometry)
  • intersects($geometryColumn, $geometry)
  • overlaps($geometryColumn, $geometry)
  • doesTouch($geometryColumn, $geometry)
  • orderBySpatial($geometryColumn, $geometry, $orderFunction, $direction = 'asc')
  • orderByDistance($geometryColumn, $geometry, $direction = 'asc')
  • orderByDistanceSphere($geometryColumn, $geometry, $direction = 'asc')

请注意,MySQL 空间分析函数的行为和可用性在每个 MySQL 版本(参见文档 )中有所不同。

迁移

对于 5.5 之前的 Laravel 版本,您可以使用此包提供的Blueprint: 。GrimzyLaravelMysqlSpatialSchemaBlueprint

use IlluminateDatabaseMigrationsMigration;
use GrimzyLaravelMysqlSpatialSchemaBlueprint;

class CreatePlacesTable extends Migration {
    // ...
}

可用的MySQL 空间类型迁移blueprints:

$table->geometry(string $column_name, int $srid = 0)
$table->point(string $column_name, int $srid = 0)
$table->lineString(string $column_name, int $srid = 0)
$table->polygon(string $column_name, int $srid = 0)
$table->multiPoint(string $column_name, int $srid = 0)
$table->multiLineString(string $column_name, int $srid = 0)
$table->multiPolygon(string $column_name, int $srid = 0)
$table->geometryCollection(string $column_name, int $srid = 0)

空间索引

您可以使用 和 蓝图在迁移中添加或删除空间索引。spatialIndexdropSpatialIndex

$table->spatialIndex('column_name')
$table->dropSpatialIndex(['column_name'])或$table->dropSpatialIndex('index_name')

有关MySQL 文档中的空间索引的注释:
对于MyISAM和(截至 MySQL 5.7.5)表,MySQL 可以使用类似于创建常规索引的语法创建空间索引,但使用 关键字。必须声明空间索引中的列。InnoDBSPATIALNOT NULL
此外,请阅读Laravel 5.6文档中有关索引长度的重要说明。
例如,作为快速入门的后续;从命令行生成新的迁移:

php artisan make:migration update_places_table

然后编辑您刚刚创建的迁移文件:

use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateSupportFacadesSchema;

class UpdatePlacesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        // MySQL < 5.7.5: table has to be MyISAM
        // DB::statement('ALTER TABLE places ENGINE = MyISAM');

        Schema::table('places', function (Blueprint $table) {
            // Make sure point is not nullable
            $table->point('location')->change();
          
            // Add a spatial index on the location field
            $table->spatialIndex('location');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('places', function (Blueprint $table) {
            $table->dropSpatialIndex(['location']); // either an array of column names or the index name
        });

        // DB::statement('ALTER TABLE places ENGINE = InnoDB');

        Schema::table('places', function (Blueprint $table) {
            $table->point('location')->nullable()->change();
        });
    }
}

测试


$ composer test
// or
$ composer test:unit
$ composer test:integration

集成测试需要运行的 MySQL 数据库。如果安装了 Docker,可以轻松地启动一个:

$ make start_db # starts MySQL 8.0
// or
$ make start_db V=5.7 # starts MySQL 5.7

Tags: mysql, geometry

Related Posts:

Leave a Comment