MapServer using MySQL data

In open source databases, the best support for spatial data is that Postgresql/postgis,postgresql is another well-known database in the open source database that supports basic spatial data types such as point, line, polygon, box, PATH, etc. As this article mainly explains the MySQL problem, here does not unfold. PostGIS is the spatial expansion of PostgreSQL, which enables PostgreSQL to support spatial data to a higher level: for example, the computation and analysis of spatial data objects. Although Postgresql/postgis support for spatial data is more comprehensive, but domestic use is not much, the use of the most is MySQL, so we mainly explain in this article MySQL storage space data utilization problem.

MySQL supports spatial data types starting from 4.1, may be due to historical reasons, or many technicians do not understand GIS, many spatial location data stores do not use proprietary spatial data type fields, but instead use two float type storage, representing longitude and latitude, latitude data is typically from GPS sensor; Of course, there are some data that directly utilize the MySQL spatial extension, the geometry type in the spatial data table to store the spatial data. So how do these two kinds of spatial information stored in the MySQL database be used?

This article combines MapServer, OGR and MySQL to access and render the spatial data stored in the MySQL database.

1. The need for server-side rendering of maps

So what do we do when we need to render the spatial data stored in the MySQL database and display it on the map? Because of the general features (non-map basemap) rendering, the amount of data is not very large, so many of the practice is to remove the latitude and longitude fields, splicing into GeoJSON or KML and other spatial data Interchange format, and then sent to the client for rendering, the client may be a browser, the browser's computing power is limited, There are two bottlenecks in this approach:

1. 随着数据量的增大，网络传输需要的时间变长；2. 客户端计算能力有限，如果计算量过大，会导致浏览器假死，造成很不好的体验。

If we give the rendered task to the server side, the rendered picture results back to the client, then the above bottlenecks can be broken, and the server-side computing power can be extended by increasing the server, theoretically unlimited.

If the database server software uses MapServer, we can directly render the spatial data stored in MySQL through MapServer, MapServer support for MySQL data source is due to the ability to get gdal/ogr, MapServer data that cannot be supported are often read and rendered through GDAL/OGR. There are two types of situations:

1. 如果 MySQL 中的空间数据是存储在 geometry 字段中，那么可以直接读取；2. 如果 MySQL 中的空间数据是通过两个字段分别代表 经纬度的形式存储的，那么需要构造虚拟图层。

2. Data storage in the form of geometry

MySQL support for spatial data is more intuitive, and its spatial data type directly corresponds to the specification of OpenGIS, and the types of fields that can accommodate spatial data include: GEOMETRY, point, LINESTRING, POLYGON, MULTIPOINT, Multilinestring, Multipolygon, and geometrycollection, some of them only support the storage of a geographic figure (Geometry,point,linestring,polygon), where GEOMETRY can store points, lines, and polygons, and Point,linestring,polygon can store only the corresponding figure types, and the rest is literally understandable and can store multiple point-and-line figures.

In this storage case, we can directly configure the mapfile read, the mapfile example is as follows (part, the complete mapfile configuration also has the Map object, the Web object and so on, see my previous article).

LAYER NAME"Mysql_spatial_layer"TYPE POLYGON STATUSDEFAULTConnectionType OGR CONNECTION"mysql:dbname,user=root,password=mysql,port=3306"DATA"SELECT Geom_col_name,property_col_name from table_name"Labelitem"Property_col_name"  CLASSNAME"Class_name"STYLE COLOR -  -  -OUTLINECOLOR199 199 199    ENDLABEL COLOR0 0 0FONT Sans TYPE TrueType SIZE8POSITIONAUTOPartialsFALSEOUTLINECOLOR255 255 255    END  ENDEND # layer

Here are a few lines to note:

"MySQL:dbname,user=root,password=mysql,port=3306"DATA"SELECT geom_col_name,property_col_name from table_name"

CONNECTIONTYPEThe type is OGR , this is the configuration MapServer using OGR provides the spatial data resolution capability, configure the parameters of the CONNETION connection MySQL, DATA specify the SQL query statement. This allows you to request spatial data stored in MySQL by requesting an OGC service published by MapServer.

3. The data is stored in the form x, y

By default, OGR also cannot render coordinate data stored in X, y form, for this form of data, OGR provides the Virtual Format, the corresponding parsing engine is OGR VRT, in general, through an XML configuration file to map the data in the relational database to OGR empty Data structures. VRT can be used not only to parse tables that store spatial information in the form of normal attribute fields, but also to correlate data and coordinate system information, to fuse multiple layers into a single data source, and so on, where detailed information can be seen here: http://www.gdal.org/drv_vrt.html.

To store the latitude and longitude information for the two common attribute fields in MySQL, we can create a Virtual File with the extension. OVF, one instance as follows:

<ogrvrtdatasource>    <ogrvrtlayer name="Vrt_layer_name">        <srcdatasource>Mysql:dbname,user=user_name,password=passwd,host=ip_addr,port=3306,tables=table_name</srcdatasource>        <srcsql>SELECT Longitude_col_name, Latitude_col_name, property_col_name from table_name</srcsql>        <geometrytype>Wkbpoint</geometrytype>        <geometryfield encoding="Pointfromcolumns" x="Longitude_col_name"  y="Latitude_col_name"/>    </ogrvrtlayer></ogrvrtdatasource>

Virtual FileThe root element is usually OGRVRTDataSource a child of one or more layer elements, layer types include Ogrvrtlayer, Ogrvrtwarpedlayer, or Ogrvrtunionlayer, and the latter two are supported at Gdal/ogr 1.10.0. This does not unfold, at the same time, under the layer element can have what sub-configuration elements, want to know more can see here: http://www.gdal.org/drv_vrt.html. Here I mainly explain the parameters involved in this example:

• SrcDataSourceConfigure connection information for connection to MySQL, SrcSQL configure SQL query statements that select latitude and longitude and attribute information;
• GeometryTypeSpecifies the type of spatial data contained in the table, including: "Wkbnone", "Wkbunknown", "Wkbpoint", "wkblinestring", "Wkbpolygon", "Wkbmultipoint", " Wkbmultilinestring "," Wkbmultipolygon ", or" wkbgeometrycollection ", if not specified, the default value is" Wkbunknown ", allowing any type of geo-geometric figures;
• GeometryFieldSpecifies the field name for x, y coordinates, encoding allowable values include "WKT", "WKB" or "pointfromcolumns", specifying the source format of the coordinates.

A VRT file that maps the spatial information stored in MySQL normal form to the OGR spatial data structure is written, and the next step is to specify the VRT file in the corresponding layer in the Mapfile configuration file, as shown in the following example:

layer NAME  "Layer_name"  STATUS default  TYPE point connectiontype OGR CONNECTION  " VRT_FILE_NAME.OVF " DATA  CLASS        NAME  "MyClass"  STYLE SYMBOL  ' Circle '  SIZE 15  COLOR 0  255  0  end  end
      end   

Where the DATA parameter specifies the vrt_layer_name required and consistent () specified in the VRT file OGRVRTLayer , the CONNECTION parameter specifies the VRT file path address, and note that the SYMBOL value contained in the STYLE ' circle ' is pre-set and is set in the MAP object The Symbolset parameter specifies a predefined style in the symbol file, which is omitted here in order to highlight the focus.

4. Summary

This article mainly introduces two forms of storing spatial data in MySQL, explains the benefits of rendering spatial data on the server side, and combines MapServer to explain how to combine OGR to read and render spatial data stored in MySQL.

OK, just write here, what's the problem, you can leave a message under the article or send me an e-mail.

MapServer using MySQL data