Get all points from a polygon

[BareFeetWare]

Hi all,

Given an arbitrary geometry, how can I most efficiently get the data points of each polygon?

I've tried iterating though nested loops in application code of GeometryN and PointN, with this query:

select X(PointN(ExteriorRing(GeometryN(geometry, ?)), ?)) as longitude
, Y(PointN(ExteriorRing(GeometryN(geometry, ?)), ?)) as latitude
from "Council" where id = ?

but it's quite slow.

I have tried joining with a sequence table that just contains id from 1 to an arbitrarily high number, greater than the number of data points I expect:

select X(PointN(ExteriorRing(geometry), "Sequence".id)) as longitude
, Y(PointN(ExteriorRing(geometry), "Sequence".id)) as latitude
from "Council"
join "Sequence" on "Sequence".id <= NumPoints(ExteriorRing(geometry))
order by "Sequence".id

It works and is faster than program code iteration, but seems like an artificial way to do it.

What I really need is some function that would explode the points for a given ExteriorRing, then I could just:

select AllPoints(ExteriorRing(GeometryN(geometry, ?)), ?)) as longitude
, Y(PointN(ExteriorRing(GeometryN(geometry, ?)), ?)) as latitude
from "Council" where id = ?

to get back one row for each data point.

Thanks,
Tom
BareFeetWare

--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
--
iPhone/iPad/iPod and Mac software development, specialising in databases
deve...@barefeetware.com

[a.furieri]

> I've tried iterating though nested loops in application code of GeometryN and PointN, with this query:
>
> select  X(PointN(ExteriorRing(GeometryN(geometry, ?)), ?)) as longitude
> ,       Y(PointN(ExteriorRing(GeometryN(geometry, ?)), ?)) as latitude
> from "Council" where id = ?
>
> but it's quite slow.
>

for sure this one is awfully slow ;-)

the OCG-SFS model is fully oriented toward
complex Geometries, and support for single-vertex
access isn't realistically feasible in pure SQL.

you are required to use some geometry-oriented
C library to perform such a task; i.e. you
can use the SpatiaLite's own C APIs (or
alternatively the GDAL APIs) to access single
vertices in an efficient way.

bye Sandro

[BareFeetWare]

Hi Sandro,

Thanks for the quick reply.

On 13/06/2011, at 6:57 PM, a.furieri wrote:

> for sure this one is awfully slow ;-)

OK, so it's not just me then ;-)

> the OCG-SFS model is fully oriented toward complex Geometries, and support for single-vertex access isn't realistically feasible in pure SQL.

Ultimately, after manipulating geometries in spatialite, I need to be able to plot the resulting polygons in my UI. My platform is iOS (iPhone/iPad) using Objective C, Cocoa and mapKit. I don't know of any libraries that convert spatialite geometry blobs into MapKit polygons, so I'm left with the only avenue of extracting each point from a spatialite polygon and constructing the polygon in MapKit. Tell me if there's a better way.

> you are required to use some geometry-oriented C library to perform such a task; i.e. you can use the SpatiaLite's own C APIs (or alternatively the GDAL APIs) to access single vertices in an efficient way.

Can you please give me some sample code or a function to do this? How can I get a point N from polygon M in a geometry returned from spatialite?

Thanks,
Tom
BareFeetWare

[a.furieri]

Hi Tom,

> Ultimately, after manipulating geometries in spatialite, I need to be able to plot the resulting polygons in my UI.
> My platform is iOS (iPhone/iPad) using Objective C, Cocoa and mapKit.
> I don't know of any libraries that convert spatialite geometry blobs into MapKit polygons,
>

I've never used Cocoa and/or MapKit, but I strongly suspect
it's more or less the same as Windows own GDI,
Linux GTK, Java Swing or any other GUI framework
such as wxWidgets or QT.
And as a general rule any developer can build
GUI specific objects simply writing few C/C++
lines.
This usually requires allocating some array,
then converting "map" coordinates into "screen"
coords ... a quite trivial task ;-)

> Can you please give me some sample code or a function to do this? How can I get a point N from polygon M in a geometry returned from spatialite?
>

just a simple code snippet:
====================
gaiaPolygonPtr polyg;
gaiaGeomCollPtr geom =
gaiaFromSpatiaLiteBlobWkb (p_blob, n_bytes);
polyg = geom->FirstPolygon;
while (polyg != NULL)
{
int iv;
int ir;
double x;
double y;
gaiaRingPtr ring = polyg->Exterior;
for (iv = 0; iv < ring->Points; iv++)
{
/* ExteriorRing: [X,Y] Nth vertex */
gaiaGetPoint (src->Coords, iv, &x, &y);
}
for (ir = 0; ir < polyg->NumInteriors; ir++)
{
ring = polyg->Interiors + ir;
for (iv = 0; iv < ring->Points; iv++)
{
/* any InteriorRing: [X,y] Nth vertex */
gaiaGetPoint (src->Coords, iv, &x, &y);
}
}
polyg = polyg->Next;
}
====================

you can easily get some thorougher example by
reading (and studying) the SpatiaLite's own sources
it's OPEN SOURCE after all, isn't ? ;-)
e.g. look at the gaiaCloneGeomColl() implementation

bye Sandro

[BareFeetWare]

Sandro,

Thanks again for your quick a this time detailed reply. You gave me the final piece to my puzzle. The speed of extracting the point data from the geometry has gone down from a minute to a few hundredths of a second. Fantastic!

I'm a bit of a raw C n00b, having been sheltered by Objective-C for so long. But I managed to adapt your sample code to work for my purpose. I had to replace src with ring etc. Please let me know if you see any glaring errors. Otherwise, I hope it's of use to others. Here it is:

NSString* querySql = @"select geometry from \"Council\" where id = ?";

NSArray* arguments = [NSArray arrayWithObject:rowID];

NSArray* resultDictArray = [[self sqlite] executeQuery:querySql arguments:arguments];

NSData* blobData = [[resultDictArray objectAtIndex:0] objectForKey:@"geometry"];

NSMutableArray* exteriorPolygons = [NSMutableArray array];

if (blobData != nil)

{

gaiaPolygonPtr polyg;

gaiaGeomCollPtr geom = gaiaFromSpatiaLiteBlobWkb ([blobData bytes], [blobData length]);

polyg = geom->FirstPolygon;

while (polyg != NULL)

{

int pointN;

int ringN;

double x;

double y;

gaiaRingPtr ring = polyg->Exterior;

MKMapPoint points[ring->Points];

for (pointN = 0; pointN < ring->Points; pointN++)

{

/* ExteriorRing: [X,Y] Nth point */

gaiaGetPoint (ring->Coords, pointN, &x, &y);

CLLocationCoordinate2D coord = {y, x};

points[pointN] = MKMapPointForCoordinate(coord);

}

NSMutableArray* interiorPolygons = [NSMutableArray array];

for (ringN = 0; ringN < polyg->NumInteriors; ringN++)

{

ring = polyg->Interiors + ringN;

MKMapPoint interiorPoints[ring->Points];

for (pointN = 0; pointN < ring->Points; pointN++)

{

/* any InteriorRing: [X,y] Nth point */

gaiaGetPoint (ring->Coords, pointN, &x, &y);

CLLocationCoordinate2D coord = {y, x};

interiorPoints[pointN] = MKMapPointForCoordinate(coord);

}

MKPolygon* interiorPolygon = [MKPolygon polygonWithPoints:interiorPoints count:ring->Points];

[interiorPolygons addObject:interiorPolygon];

}

MKPolygon* polygon = [MKPolygon polygonWithPoints:points count:ring->Points interiorPolygons:interiorPolygons];

[exteriorPolygons addObject:polygon];

polyg = polyg->Next;

}

}

Thanks again.

Tom