MySQL support for GIS

I had nothing to worry about this afternoon. I accidentally reviewed the MySQL manual. I used to check the content of the manual very well, the focus is on some basic details of SQL statements, MySQL performance parameters, and storage control management. However, today I accidentally discovered an important feature of MySQL, that is, MySQL also has the storage function for spatial data related to GIS, which suddenly aroused my interest, so I also focused on the relevant content of the manual. From the manual perspective, MySQL provided support for GIS objects very early, but I did not find it.

PostgreSQL is widely used in open-source GIS because of postgis extension. As another giant in open-source databases, MySQL has never abandoned the important database application field of GIS. MySQL has introduced a series of spatial extensions since mysql4.1, enabling it to have certain spatial processing capabilities.
MySQL complies with the OGC OpenGIS geometry model and supports the following spatial data objects:
Geometry (non-instantiable)

• Point (instantiable)
• Curve (non-instantiable)
  • Linestring (instantiable)
    • Line
    • Linearring
• Surface (non-instantiable)
  • Polygon (instantiable)
• Geometrycollection (instantiable)
  • Multipoint (instantiable)
  • Multicurve (non-instantiable)
    • Multilinestring (instantiable)
  • MultiSurface (non-instantiable)
    • Multipolygon (instantiable)

Wtk is the main way to import spatial data into MySQL spatial data tables. Wtb is a binary form of wtk and can also be identified by MySQL.

Create a MySQL space data table
Currently, only MyISAM engine data tables support the storage of geospatial data. Therefore, you must declare the data tables when creating them.

Create Database Geodatabase;
Use Geodatabase;
Create Table Test (
Id int primary key auto_increment,
Name varchar (128) not null,
PNT point,
Line linestring,
PGN Polygon
) Engine = MyISAM;

Insert a piece of space data using the following SQL

Insert into 'test' values (
Null,
'A test string ',
Pointfromtext ('point (15 20 )'),
Linestringfromtext ('linestring (0 0, 10 10, 20 25, 50 60 )'),
Polygonfromtext ('polygon (0 0, 10, 10, 0 10, 0 0), (5, 7, 7, 7, 5, 5 ))')
);

Here, you can also use the geomfromtext function to convert WKT to the internal geometric format of the database. The geomfromwkb function is used to convert WKB.

Use the following SQL statement to obtain spatial data from a data table:

Select ID, name, astext (PNT), astext (line), astext (PGN) from 'test ';

The functions of the astext function are the opposite of those of the geomfromtext function, that is, to convert data from an internal format to WKT; the corresponding asbinary function can be converted to WKB.

The built-in GIS functions of MySQL provide powerful spatial analysis capabilities for MySQL databases. As the fastest open source database, MySQL can be widely used in the GIS field.

 

I personally think that it is very meaningful to fully explore the features of MySQL in GIS space data storage through PHP scripts or C # program development with distinctive storage methods, if I have time, I will try again and write any progress on my blog.