## Saturday 31 October 2009

### Working with spatial data in binary representation in MySQL and PHP

In an earlier post I showed how to store and retrieve spatial POINT geometries in MySQL and PHP. A POINT is a geometry that represents a single location in coordinate space. In this post we will be dealing with more complex spatial data such as LINESTRING and POLYGON, in real world representing for example rivers, roads, or territories in coordinate space. A number of Shapefiles (SHP) describing territories and division of kingdoms in early medieval Europe have been imported into MySQL. The geometries are drawn directly from the SQL-database using PHP-script, building the basemaps of the historical GIS-application Regnum Francorum Online. This article will discuss how this was accomplished. A Linestring is a one-dimensional geometry represented by a sequence of points, whereas a Polygon is a planar surface representing a multisided geometry, with a single exterior boundary and zero or more interior boundaries, where each interior boundary defines a hole in the polygon. The first and last Point of the exterior boundary is the same, we say it's closed. Here we will only deal with polygons without interior boundaries.

In order to get these geometries on a computer screen map we must be able to somehow retreive the single points building these geometries and transform them from coordinates to pixels, and then use some graphics functions to draw lines and polygons. The following steps were used to achieve this task.

1. Define a minimal bounding rectangles (MBR) representing the boundaries of the map we will display on the screen and compare this geometry with the lines and polygons in the database and see if they should be selected. If this box is expressed in coordinate space we will be able to take advantage of functions comparing the spatial relationship between two geometries in MySQL. The MBR of the following Linestring reaching from one corner to the opposite, represents the map given its width, height and scale.
`SET @g = GeomFromText('LineString(\$longitude1 \$latitude1,\$longitude2 \$latitude2)')`

2. The geometries holding the coordinates for rivers or territories has a spatial column of type LINESTRING or POLYGON and has been given the name SHAPE2 on all database-tables of this kind (layers). We will Select values from this column that Intersects the MBR defining the boundaries of the map (@g). We will then retreive the data as a binary string (BLOB). The Well-Known Binary (WKB) representation for geometric values is defined by the OpenGIS specification. WKB uses one-byte unsigned integers, four-byte unsigned integers, and eight-byte double-precision numbers. For fast access of the data, we use the Unbuffered Query in MySQL.
`\$query="SELECT AsBinary(SHAPE2), color FROM \$layer WHERE MBRIntersects(SHAPE2,@g)";...\$result=mysql_unbuffered_query(\$query);`

3. In the code snippet above, we are retreiving two columns of each row in the database table. Unpack is a PHP-function that unpacks data from a binary string into an associative array according to a given format, in this case defined by the WKB. The first parameter of the geometry is the byte order (1 byte), the second, geometry type (4 bytes), and the third, number of elements (4 bytes). This sums to 9 bytes, and at this position (offset) in the binary string, we will find the number of elements of the given geometry type. The class-function `drawLine` deals with Linestrings and the function `drawRings` deals with Polygons. We will not deal any further with Multipolygon and Point geometries.
``while(\$row = mysql_fetch_row(\$result)) {\$color=\$row[1];\$g = unpack("Corder/Ltype/Lnum",\$row[0]);\$type = \$g['type'];\$num = \$g['num'];\$offset   = 9;switch(\$type) {case 1: // POINTbreak;case 2: // LINESTRING\$this->drawLine(\$offset,\$num,\$color,\$row[0]);break;case 3: // POLYGON\$this->drawRings(\$offset,\$num,\$color,\$row[0]);break;case 6: // MULTIPOLYGONbreak;}}mysql_free_result (\$result);``

4. Now we are ready to transform the coordinates to pixels on the map. We will begin with the Linestring-geometry. Offset is the position in the binary string (\$row) where we will start to read. \$numpts is the number of points in the Linestring. From the offset position in the buffer we start to unpack into the points-array (`pts`) eight-byte double-precision numbers (d) until we reach the end of the buffer (*). Next we assign an array `geom`, that will hold the resulting pixel-values of the points defining the Linestring. When reading the points-array, we know that the first number is the longitude and the second the latitude, on so forth. When reaching every second number we have a longitude/latitude pair that will be transformed using the gRef-function. The resulting pixel-values are assigned to the `geom`-array. Then we use the PHP graphics-function `imageline` to draw a line-segment between x1,y1 and x2, y2, in the given color.
``function drawLine(\$offset,\$numpts,\$color,\$row) {\$pts = unpack("@\$offset/d*", \$row);\$geom = array();\$lon=0;\$lat=0;\$odd=true;foreach (\$pts as \$value) {\$odd ? \$lon=\$value : \$lat=\$value;if (!\$odd) { \$this->gRef(\$lon, \$lat); \$geom[]= \$this->Xp(); \$geom[]= \$this->Yp();}\$odd=!\$odd;} // end foreach\$max=(\$numpts-1)*2;for (\$i=0;\$i < \$max; \$i+=2) {            imageline ( \$this->img, \$geom[\$i], \$geom[\$i+1], \$geom[\$i+2], \$geom[\$i+3], \$color);}} // end function``

5. Now we deal with the Polygons. In it's core this is the same function as the one for Linestrings, because polygons are Linestrings with the same start- and end-point.
``function drawRings(\$offset,\$numrings,\$color,\$row){ \$off=\$offset; \$x=\$numrings; while(\$x > 0) {  \$h=unpack("@\$off/Lnumpts",\$row);  \$numpts=\$h['numpts'];  \$off+=4;  \$nump=\$numpts*2;  \$pts = unpack("@\$off/d\$nump", \$row);  \$geom = array();  \$lon=0;\$lat=0;   \$odd=true;  foreach (\$pts as \$value) {   \$odd ? \$lon=\$value : \$lat=\$value;   if (!\$odd) {    \$this->gRef(\$lon, \$lat);    \$geom[]= \$this->Xp();    \$geom[]= \$this->Yp();   }   \$odd=!\$odd;  } // end foreach  imagefilledpolygon ( \$this->img, \$geom, \$numpts, \$color);  // If we wish to draw a border  imagepolygon ( \$this->img, \$geom, \$numpts,    \$this->bordercolor);  // Increase byte pointer  \$off+=(\$nump*8);  \$x--; } // end while x return(\$off);} // end function``