1

Re: PHP - unpacking Multipolygons from MySQL

Hi

Has anyone used PHP to parse the Polygon/Multipolygon geometry data held in MySql?

I have managed to parse it as text using AsText (a bit long winded) but was keen to try using AsBinary per http://dev.mysql.com/doc/refman/5.0/en/ … ormat.html -

<?php
// $wkb is the value retrieved from MySQL using AsBinary(g)
$geometry = unpack(corder/Ltype/dlatitude/dlongitude, $wkb);
if ($geometry['type'] == 1) {
    $latitude = $geometry['latitude'];
    $longitude = $geometry['longitude'];
}
?>

however this does is only good for dimension type 1 (lines and multilines) and does not appear to work for type 2 (polygons and multipolygons)

TIA

Cheers

Nick

(ePlanning Project Manager) Aberdeenshire Council

2 (edited by davec 15-04-2008 14:15:29)

Re: PHP - unpacking Multipolygons from MySQL

Hi Nick,

Not that familiar with spatial extensions in MySQL, but in PostGIS (Postgresql spatial extensions) there is the OGC function GeometryN( geometry, N), where <geometry> is the WKT/B string and <N> is the N'th geometry for GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING or MULTIPOLYGON types (or NULL if not of those). (Such that N is 0 .. N -1 geometries).

I would hazard a guess that if you want the geometry of the third polygon you could get it via a query such as;

SELECT GeometryN( the_geometry, 2) AS wkb FROM my_table WHERE some_clause

Out of interest, are you trying to get the centroid of the polygon or one point within the polygon.

EDIT: Had a quick look at MySQL 5.1 and GeometryN() is supported.

Regards,

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

3

Re: PHP - unpacking Multipolygons from MySQL

Hi Dave

What I am trying to get are all the points for Multipolygon.

I can get other bits of information from the inbuilt MySQL functions e.g the Dimension type

SELECT dimension($geom) FROM $table

As far as I understand the GeometryN function is that this works where you have a geometry collection (e.g. Point(1,1), Point(2,1), Linestring(1 2, 2 3)) - using GeometryN will select the Nth geometry so if select the second it would return Point(2,1).

I don't think that the above would therefore apply to a Multipolygon(((....)), ((....))). Or maybe I am missing the point? Any thoughts?

I guess I could carry on parsing the data using AsText($geom) and then using explode to break it up but the unpack solution looks a lot neater if only I can get my head round how to unpack. If I apply the example given it won't work (error states that I am trying to divide Zero).

If I use unpack ('V', $wkb) it returns a single value but it makes no sense.

BTW I am using version 4.4.2 of MySQL - that is what the hosting company provides.

Cheers

Nick

(ePlanning Project Manager) Aberdeenshire Council

4 (edited by davec 16-04-2008 15:10:19)

Re: PHP - unpacking Multipolygons from MySQL

Hi Nick,

I hope the following may help, if I've understood what you are trying to do correctly. I have a table which has a geometry column of type MULTIPOLYGON and I wish to list all of the discreet points in the polygon(s) - given that, by definition, a multi polygon can have  0 .. N-1 polygons. (I may be incorrect in my last post as my example only works from an indice of 1).  Please note that this example is using PostGIS, but it should be OGC compliant. If not, I think that the general principle should be sound.

First, I obtain the Nth polygon of interest as text.

SELECT AsText( GeometryN( polygon_geom, 1)) FROM spatial_search_geometry

This gives me a string in this form (Coordinate system is OSGB - SRID 27700);

POLYGON((280593.054673555 254679.164020664,280554.160845461 254662.495237195,280515.267017367
254645.826453727,280531.935800836 254573.595058695,280562.495237195 254448.57918268,280593.054673555 254270.77882568,280556.938976039 254065.197162898,280518.045147945 253745.712146414,280476.373189273 253759.602799305,280429.144969445 253779.049713352,280423.588708289 253876.284283586,280398.585533086 253917.956242258,280454.148144648 253981.853245555,280512.488886789 254101.312860414,280518.045147945 254231.884997586,280495.82010332 254368.013395914,280468.038797539 254423.576007477,280495.82010332 254454.135443836,280501.376364477 254490.251141352,280429.144969445 254604.154495055,280493.041972742 254643.048323148,280479.151319852 254690.276542977,280495.82010332 254795.845504945,280473.595058695 254848.62998593,280493.041972742 254948.642686742,280462.482536383 254998.649037148,280448.591883492 255087.549215648,280484.707581008 255223.677613977,280556.938976039 255426.48114618,280593.054673555 255423.703015602,280645.839154539 255420.924885023,280709.736157836 255429.259276758,280729.183071883 255387.587318086,280729.183071883 255323.690314789,280706.958027258 255276.462094961,280684.732982633 255254.237050336,280651.395415695 255232.012005711,280656.951676852 255195.896308195,280665.286068586 255126.443043742,280665.286068586 255076.436693336,280665.286068586 255029.208473508,280620.835979336 254951.42081732,280584.72028182 254934.752033852,280531.935800836 254940.308295008,280593.054673555 254679.164020664))

For this example, I saved the output of the SQL to a file in order to save on time. I now need to remove the outer characters of the string
and this is achieved using the split() function. Next, I produce an array split on the commas and then iterate over that and split again on whitespace.

 

   /* Read File */
  $wkt = file_get_contents ( "polygon.txt" );
  
  /* Use Regex to do a quick parse on the WKT string. */
  $tmp = split( 'POLYGON', $wkt ) ;
  $tmp = split( '\(\(', $tmp [ 1 ] ) ;
  $tmp = split( '\)\)', $tmp [ 1 ] ) ;

  /* Now explode this string on commas (if any) 
     String is X11 Y11 X12 Y12 ... , X21 Y21 X22 Y22 ... , ... */
  $polygons = explode ( ',', $tmp [ 0 ] );
  
  /* Loop over polygons and explode (in this case on white space 
     Each element will therefore be a list of points */
  foreach ( $polygons as $polygon ) {
    $point = explode ( ' ', $polygon );
    print "X=" . $point [ 0 ] . ",Y=" . $point[ 1 ] . "\n";
  }

Here is a sample of the output;

D:\sysadmin\scripts\php\ogc_parsing>php parse_polygon.php
X=280593.054673555,Y=254679.164020664
X=280554.160845461,Y=254662.495237195
X=280515.267017367,Y=254645.826453727
X=280531.935800836,Y=254573.595058695
X=280562.495237195,Y=254448.57918268
X=280593.054673555,Y=254270.77882568
X=280556.938976039,Y=254065.197162898
X=280518.045147945,Y=253745.712146414
X=280476.373189273,Y=253759.602799305
X=280429.144969445,Y=253779.049713352
X=280423.588708289,Y=253876.284283586
X=280398.585533086,Y=253917.956242258
X=280454.148144648,Y=253981.853245555

I hope this helps, if not I had fun trying! :)

EDIT: Oops! :rolleyes: just noticed in your first post you have already parsed as text. In that case what advantages (apart from not having to handle potentially huge strings do you see in parsing the binary form)
Regards,

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

5

Re: PHP - unpacking Multipolygons from MySQL

Hi Dave

Many thanks for your excellent suggestion.

The code I am using is something like

<?php
//Set variables
//Top left corner of map - 6 figure OS grid
$eastjust = 123456;
$northjust = 789789;
$var = 0.002; //value used for conversion for a specific map according 
to the scale
//create image
$im = imagecreatefrompng("somemap.png");

mysql_connect($dbhost, $dbuname, $dbpass) or die('Could not connect: ' . 
mysql_error());
@mysql_select_db($dbname) or die("Unable to select database");
$query = "SELECT AsText($geom) from $tablename";
$result = mysql_query($query);
if($result){ 
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$polygons = explode("((",$row[0]);
$number = count($polygons)-1;
for ( $n = 1; $n < count($polygons); $n++ )
{
$polygon = str_replace(")),","",$polygons[1]);
$polygon = str_replace(")))","",$polygon);
$polygon = str_replace("(","",$polygon);

    $converted_points = array();
    $points_str        = explode(",",$polygon);
    $number_points = count($points_str);
    $i = 0;
    while($i<$number_points)
    {
    $points = explode(" ",$points_str[$i]);
      $lon  = $points[0];
    $lat  = $points[1];   
        $converted_points[] = ($lon -   $eastjust )*$var;
        $converted_points[] = ( $northjust - $lat)*$var;
    $i++;
    }

imagepolygon($im,$converted_points,$number_points,$colour);
  }
 }
}

mysql_free_result($result);
mysql_close();

header("Content-type: image/png");
imagepng($im);
imagedestroy($im);
?>

So as you can see, the query I am using is to parse the geometry data to text and then extract the data from there. This however looks long winded to me - hence the thought that I could use unpack to extract the data which should make for less lines of code and possibly less load/faster :P

If nobody can offer a neater solution or advice on faster processing, then I will carry on with the code I am using

Cheers

Nick

(ePlanning Project Manager) Aberdeenshire Council

6

Re: PHP - unpacking Multipolygons from MySQL

Hi Nick,

I can see what you are trying to do now. A couple of questions. Could you define "faster processing"? How much faster than your current solution are you trying to achieve? What are the sizes of polygons you work with? If you are trying to send polygon data to a map, it would be helpful to know what mapping system that is. I'm curious to know why the polygon cannot be dumped out of the database (as a shapefile?) and loaded dynamically into the mapping software.

A fast WKB parser sounds like a nice problem to work on anyway. :)

Regards,

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

7

Re: PHP - unpacking Multipolygons from MySQL

Hi Dave

I am not using any mapping software, purely writing the mapping from scratch. A simple example is http://www.nesbrec.org/shp_index.php?layername=areas, which takes the data from the MySql database and dynamically draws the polygons and imagemap. The imagemap is to provide simple interactivity  using Javascript (if you hover over a polygon in the above example you are accessing the data from the database) but this can be extended to enable interrogation of the MySql database.

As you can see, it loads quite slowly. Basically I am trying different ways to generate the maps to see what works best (e.g. using .mif/.mid data directly, even tried saving as transparent overlays)

The above example will form the basis of existing mapping from our search page http://www.nesbrec.org/thesaurus2.phpwhich consumes NBN web services to retrieve the details. The map is then displayed with data taken dynamically from the MySql database e.g. http://www.nesbrec.org/species_map.php? … 20vulgaris. This display uses the most current data we have as the MySql database is updated nightly using Navicat. If you click on a square on the map, a new map is redrawn showing the data centred on that square.

So what I am planning is to display the Shapefile data and the species data, both of which are stored in the MySql database. The philosophy is that I can use the MySql features to do more complex queries  on the data e.g. area of the polygon, overlaps etc. Using MySql, searching using the inbuilt spatial index capability should also speed up queries.

In terms of size, one .shp file is over 60MB although I am planning to reduce the size. In general terms, I think that if there are ways of speeding up processing and display of data, it is easier on the server and nicer for the users if they don't have to wait ages for the display to come up.

As to why I don't use any Mapping software - quite simply I like to have the freedom to develop the display to meet user needs and not be constrained by the system. Also if I am honest, I like to see what I can achieve just using PHP in combination with MySql :)

As you say it woudl be cool to have a fast WKB parser as part of a range of modules to facilitate the use of NBN web services.

Cheers

Nick

(ePlanning Project Manager) Aberdeenshire Council

8

Re: PHP - unpacking Multipolygons from MySQL

Hi Nick,

I'd still be tempted to optimise the text parsing first to be honest. However, the WKB format looks more efficient in terms of a data structure, a lot less 'empty' data than text for very big geometries. I'll have a play with the unpack() function (thanks Perl!) at some point. I think the reason the example WKB code fails for types other than lines is that the structure of WKB is different for each geometry. Thus, there would need to be a different unpack format for each one. This could be selected by reading the 'type' byte first.

For example this is a polygon with two rings [OpenGIS Simple Features Specification p. 3-27].

[B=1][T=3][NR=2][NP=3][X1][Y1][X2][Y2][X3][Y3][NP=3][X1][Y1][X2][Y2][X3][Y3]

Where B is NDR little-endian, T is type 3 (polygon), NR=number of rings and NP=number of points.

Cheers,

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

9

Re: PHP - unpacking Multipolygons from MySQL

Hi Dave

Hmmm... I had a look and I think that your initial suggestion for GeometryCollection is possibly the correct starting point. I have just had a look at the SQL Geometry Type Hierarchy and Multipolygons are a subset of a Geometry Collection. So parsing it is not like a Point, LineString or Polygon. This will take a bit of thinking through but all your suggestions add up

I am sure that this can be cracked :|

Cheers

Nick

(ePlanning Project Manager) Aberdeenshire Council

10

Re: PHP - unpacking Multipolygons from MySQL

Hi Dave

It took me a while to figure this out and you were right, I had to use GeometryN and then figured out how to unpack (see below - also posted at http://dev.mysql.com/doc/refman/5.0/en/ … ormat.html). Incidentally, if you don't have Multipolygons, the unpack would still work and is very simple to use :)

function shpdetails($tablename,$geom, $ID)
{
//connect to mysql;
//open database
@mysql_select_db($dbname) or die("Unable to select database");

//Step 1. Find the number of elements to a collection e.g. Multipolygon
$query1 = "SELECT NumGeometries($geom)from $tablename Where.....";
$result1 = mysql_query($query1);
$row1 = mysql_fetch_row($result1);
$N = $row1[0]; //number of polygons in the Multipolygon


//Step 2 - Break up multipolygons into individual polygons - loop through each
$n=1;
while ($n<=$N){
$query = "SELECT AsBinary(GeometryN($geom,$n))from $tablename Where ....."; 
$result = mysql_query($query);
if($result){  
  while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
  $geometry = unpack("corder/Ltype/d*", $row[0]);

   $i=2;//ignore order and type
   while($i<count($geometry)){
   echo $geometry[$i]."<br>";
   $i++;
   }
  }
 //end of if ($result)
 }
$n++;
//end of loop for each polygon
}
mysql_free_result($result);
//close Mysql connection
//end of function
}

Hope this helps others

Cheers

Nick

(ePlanning Project Manager) Aberdeenshire Council