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

[Paul Ramsey]

Fluff the geometries with multi() in your query:

select x(geometryn(multi(geom),1)) from geotable

P.

On Mar 20, 2006, at 8:13 AM, Bernhard Reimar Hoefle wrote:

> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users