[postgis-users] X() function working for POINTs and MULTIPOINTs

[Bernhard Reimar Hoefle]

Hi!
I wanted to mix POINT and MULTIPOINTs in one geometry column. I know that it is
not the most elegant way but for my purpose the best solution concerning
performance and disk storage.

I wanted to get the coordinates (x,y,z) for each row.

The PostGIS x() function does not support MULTIPOINTs and the geometryN() does
not support POINTs.

select x('MULTIPOINT(1 1 1,2 2 2)'::GEOMETRY);
ERROR:  Argument to X() must be a point
ERROR:  Argument to X() must be a point

select x(geometryn('POINT(2 2 2)'::GEOMETRY,1));
 x
---

(1 row)

Hence, a simple SQL statement to extract coordinates from mixed types is not
possible.

Therefore I modified the X() function to X(geometry,int) which works for both
types.

Maybe it's interesting for someone working with the same 'dirty' solution.

Added lines in lwgeom_ogc.c:
##############################

Datum LWGEOM_xx_point(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(LWGEOM_xx_point);
Datum LWGEOM_xx_point(PG_FUNCTION_ARGS)
{
	PG_LWGEOM *geom;
	LWPOINT *point = NULL;
	POINT2D p;
	int idx = PG_GETARG_INT32(1);
	LWCOLLECTION *coll;
	LWGEOM *subgeom;

	geom = (PG_LWGEOM *)PG_DETOAST_DATUM(PG_GETARG_DATUM(0));

	if ( TYPE_GETTYPE(geom->type) == POINTTYPE )
	{
		point = lwgeom_getpoint(SERIALIZED_FORM(geom), 0);
		getPoint2d_p(point->point, 0, &p);
		PG_FREE_IF_COPY(geom, 0);
		PG_RETURN_FLOAT8(p.x);
	}
	else if ( TYPE_GETTYPE(geom->type) == MULTIPOINTTYPE )
	{
		idx -= 1; /* index is 1-based */
		coll = (LWCOLLECTION *)lwgeom_deserialize(SERIALIZED_FORM(geom));

		if ( idx < 0 ) PG_RETURN_NULL();
		if ( idx >= coll->ngeoms ) PG_RETURN_NULL();

		subgeom = coll->geoms[idx];

		/* Construct an LWPOINT */
		point = lwgeom_as_lwpoint(subgeom);
		getPoint2d_p(point->point, 0, &p);
		lwgeom_release((LWGEOM *)coll);
		PG_FREE_IF_COPY(geom, 0);
		PG_RETURN_FLOAT8(p.x);
	}
	else
		lwerror("First argument to x(geometry,integer) must be a point or multipoint
object");
}

Added lines in lwpostgis.sql:
#############################

CREATE OR REPLACE FUNCTION X(geometry, integer)
	RETURNS float8
	AS '$libdir/liblwgeom.so.1.1','LWGEOM_xx_point'
	LANGUAGE 'C' IMMUTABLE STRICT; -- WITH (isstrict);


The same procedure can be done for Y(), Z().
It was my first attempt in programming with C. I hope the C code is ok?

Better solutions and suggestions are welcome.

Bernhard