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,

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,

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,

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,