MySQL Spatial Data Simple operation

In the project to do, you draw the area graph, and store it up, there is the need for updates, the storage file is not, think of the database now support the spatial database.

MySQL is now used to continue to use MySQL to store and manage spatial data. Here are some simple records to make.

Create Spatial data

CREATE TABLE' T_pot ' (' ID ')int( One) not NULLauto_increment, ' name 'varchar(255)DEFAULT NULL, ' pot ' pointDEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=5 DEFAULTCHARSET=UTF8;

It's a table built with point data.

CREATE TABLE' T_polygon ' (' ID ')int( One) not NULLauto_increment, ' name 'varchar(255)DEFAULT NULL, ' PGN ' polygonDEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=9 DEFAULTCHARSET=UTF8;

A table with polygon data is built above

Spatial data types in MySQL: Point, Linestring, polygon, geometry, multipoint, multilinestring, Multipolygon, geometrycollection

Spatial data manipulation

Here are some of the data query, analysis and other operational statements

Insert some sample data first

INSERT  into' T_polygon 'VALUES('1','ABCD', Geomfromtext ('POLYGON ((0 0, 2 0, 2 2, 0 2, 0 0))'));INSERT  into' T_polygon 'VALUES('2','AEGF', Geomfromtext ('POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0))'));INSERT  into' T_polygon 'VALUES('3','CEGFBD', Geomfromtext ('POLYGON ((0 2, 0 4, 4 4, 4 0, 2 0, 2 2, 0 2))'));INSERT  into' T_polygon 'VALUES('4','Ahjik', Geomfromtext ('POLYGON ((0 0, 0 6, 6 6, 6 0, 0 0), (2 2, 4 4, 4 2, 2 2))'));INSERT  into' T_polygon 'VALUES('5','DGK', Geomfromtext ('POLYGON ((2 2, 4 4, 4 2, 2 2))'));INSERT  into' T_polygon 'VALUES('6','gkj', Geomfromtext ('POLYGON ((4 4, 4 2, 6 6, 4 4))'));INSERT  into' T_polygon 'VALUES('7','ADF', Geomfromtext ('POLYGON ((0 0, 2 2, 4 0, 0 0))'));INSERT  into' T_polygon 'VALUES('8','LDK', Geomfromtext ('POLYGON ((1 1, 2 2, 4 2, 1 1))'));INSERT  into' T_pot 'VALUES('1','A', Geomfromtext ('Point (0 0)'));INSERT  into' T_pot 'VALUES('2','B', Geomfromtext ('Point (0 2)'));INSERT  into' T_pot 'VALUES('3','C', Geomfromtext ('Point (2 0)'));INSERT  into' T_pot 'VALUES('4','D', Geomfromtext ('Point (2 2)'));

Some spatial object methods

--contains, Mbrcontains, the first parameter is a contained feature, the second is a contained feature, the first contains a second return 1, otherwise returns 0SELECTPy. ' Name ', p. ' Name ', Mbrcontains (Py.pgn,p.pot) fromT_polygon py,t_pot p;--is contained, Mbrwithin, as opposed to one of the above parameters, returns the same as the resultSELECTPy. ' Name ', p. ' Name ', Mbrwithin (P.POT,PY.PGN) fromT_polygon py,t_pot p;--disjoint, Mbrdisjoint, parameter has no precedence, disjoint returns 1, otherwise returns 1SELECTPy. ' Name ', p. ' Name ', Mbrdisjoint (P.POT,PY.PGN) fromT_polygon py,t_pot p;--equal, mbrequal, two elements are equal, return 1 equal, otherwise return 0SELECTPy. ' Name ', p. ' Name ', Mbrequal (P.POT,PY.PGN) fromT_polygon py,t_pot p;--intersect, Mbrequal, two features intersect, INTERSECT returns 1, otherwise returns 0SELECTPy. ' Name ', p. ' Name ', Mbrintersects (P.POT,PY.PGN) fromT_polygon py,t_pot p;--overlap, mbroverlaps, two features overlap, overlap returns 1, otherwise 0 is returnedSELECTPy. ' Name ', p. ' Name ', Mbroverlaps (P.POT,PY.PGN) fromT_polygon py,t_pot p;--Tangent , mbrequal, whether two elements are tangent, tangent returns 1, or 0 is returnedSELECTPy. ' Name ', p. ' Name ', Mbrequal (P.POT,PY.PGN) fromT_polygon Py,t_pot p;

Insert INTO ' T_polygon ' VALUES (' 1 ', ' ABCD ', Geomfromtext (' Polygon ((0 0, 2 0, 2 2, 0 2, 0 0)) '); insert INTO ' T_polygon ' V Alues (' 2 ', ' AEGF ', Geomfromtext (' POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0) ') '); INSERT into ' T_polygon ' VALUES (' 3 ', ' CEGFBD ', Geo Mfromtext (' POLYGON ((0 2, 0 4, 4 4, 4 0, 2 0, 2 2, 0 2) '); INSERT into ' T_polygon ' VALUES (' 4 ', ' Ahjik ', Geomfromtext (' POL Ygon ((0 0, 0 6, 6 6, 6 0, 0 0), (2 2, 4 4, 4 2, 2 2))); INSERT into ' T_polygon ' VALUES (' 5 ', ' DGK ', Geomfromtext (' Polygon ((2 2, 4 4, 4 2, 2 2)); INSERT into ' T_polygon ' VALUES (' 6 ', ' gkj ', Geomfromtext (' Polygon ((4 4, 4 2, 6 6, 4 4) ')); Inser T into ' T_polygon ' values (' 7 ', ' ADF ', Geomfromtext (' Polygon ((0 0, 2 2, 4 0, 0 0) ') '), and INSERT into ' T_polygon ' values (' 8 ', ' LDK ', Geomfromtext (' POLYGON ((1 1, 2 2, 4 2, 1 1)) ');

MySQL Spatial Data Simple operation