pg.ddx.io postgis-users@lists.osgeo.org mailing list archive (PostGIS users)
help / color / mirror / Atom feed[postgis-users] X() function working for POINTs and MULTIPOINTs
6+ messages / 3 participants
[nested] [flat]
* [postgis-users] X() function working for POINTs and MULTIPOINTs
@ 2006-03-20 16:13 Bernhard Reimar Hoefle <Bernhard.Hoefle@uibk.ac.at>
0 siblings, 2 replies; 6+ messages in thread
From: Bernhard Reimar Hoefle @ 2006-03-20 16:13 UTC (permalink / raw)
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* [postgis-users] X() function working for POINTs and MULTIPOINTs
@ 2006-03-20 20:54 Paul Ramsey <pramsey@refractions.net>
parent: Bernhard Reimar Hoefle <Bernhard.Hoefle@uibk.ac.at>
1 sibling, 0 replies; 6+ messages in thread
From: Paul Ramsey @ 2006-03-20 20:54 UTC (permalink / raw)
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* [postgis-users] Re: X() function working for POINTs and MULTIPOINTs
@ 2006-03-22 16:57 Bernhard Reimar Hoefle <Bernhard.Hoefle@uibk.ac.at>
0 siblings, 0 replies; 6+ messages in thread
From: Bernhard Reimar Hoefle @ 2006-03-22 16:57 UTC (permalink / raw)
Nice hint!
But it takes approximately the twice amount of time in comparison to the
modified C function. And if you have Millions of point geometries...the double
can be a long time ;-)
ems=> explain analyze select x(geom_last) from tdtascanpoint order by
idscanpoint limit 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..3.06 rows=100 width=33) (actual time=0.033..0.568 rows=100
loops=1)
-> Index Scan using tdtascanpoint_pkey on tdtascanpoint
(cost=0.00..60274.22 rows=1970979 width=33) (actual time=0.029..0.292 rows=100
loops=1)
Total runtime: 0.771 ms
(3 rows)
ems=> explain analyze select x(geom_last,1) from tdtascanpoint order by
idscanpoint limit 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..3.06 rows=100 width=33) (actual time=0.054..0.562 rows=100
loops=1)
-> Index Scan using tdtascanpoint_pkey on tdtascanpoint
(cost=0.00..60274.22 rows=1970979 width=33) (actual time=0.050..0.300 rows=100
loops=1)
Total runtime: 0.744 ms
(3 rows)
ems=> explain analyze select x(geometryn(multi(geom_last),1)) from tdtascanpoint
order by idscanpoint limit 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..3.56 rows=100 width=33) (actual time=0.048..1.015 rows=100
loops=1)
-> Index Scan using tdtascanpoint_pkey on tdtascanpoint
(cost=0.00..70129.11 rows=1970979 width=33) (actual time=0.043..0.709 rows=100
loops=1)
Total runtime: 1.204 ms
(3 rows)
> Fluff the geometries with multi() in your query:
>
> select x(geometryn(multi(geom),1)) from geotable
>
> P.
^ permalink raw reply [nested|flat] 6+ messages in thread
* [postgis-users] X() function working for POINTs and MULTIPOINTs
@ 2006-03-24 14:45 Markus Schaber <schabi@logix-tt.com>
parent: Bernhard Reimar Hoefle <Bernhard.Hoefle@uibk.ac.at>
1 sibling, 0 replies; 6+ messages in thread
From: Markus Schaber @ 2006-03-24 14:45 UTC (permalink / raw)
Hi, Bernhard,
Bernhard Reimar Hoefle wrote:
> 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
You might want to write your own set returning plpgsql function. This
way, it is even possible that select your_x('MULTIPOINT(...)') returns
two rows, 1 and 2.
HTH,
Schabi
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 6+ messages in thread
* [postgis-users] X() function working for POINTs and MULTIPOINTs
@ 2006-03-25 09:27 Bernhard Reimar Hoefle <Bernhard.Hoefle@uibk.ac.at>
0 siblings, 1 reply; 6+ messages in thread
From: Bernhard Reimar Hoefle @ 2006-03-25 09:27 UTC (permalink / raw)
> Hi, Bernhard,
>
> Bernhard Reimar Hoefle wrote:
>
> > 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
>
> You might want to write your own set returning plpgsql function. This
> way, it is even possible that select your_x('MULTIPOINT(...)') returns
> two rows, 1 and 2.
>
> HTH,
> Schabi
Hi Markus!
I was thinking of plpgsql functions. How would such a function look like? I
don't know how to write a plpgsql function which works like the dump()
function. I just know to write a function like SELECT * from
your_x('MULTIPOINT(...)')
But my problem is performance and the C functions are quite faster than the
procedural scripts.
Indeed, I do need a function like
SELECT id, your_x('MULTIPOINT(1 1 1, 2 2 2)') from table1;
returning
id|x
-----
1|1
1|2
I was thinking of adapting the C code of the dump() function for my purpose.
What do the PostGIS insiders think is the fastest solution for my problem?
The easiest solution is a combined usage of geometryn() and x(), or writing a
plpgsql script which checks how many geometries are in the MULTIPOINT object
and loops over the geometries with returning the individual values.
Bernhard H.
^ permalink raw reply [nested|flat] 6+ messages in thread
* [postgis-users] X() function working for POINTs and MULTIPOINTs
@ 2006-03-27 10:35 Markus Schaber <schabi@logix-tt.com>
parent: Bernhard Reimar Hoefle <Bernhard.Hoefle@uibk.ac.at>
0 siblings, 0 replies; 6+ messages in thread
From: Markus Schaber @ 2006-03-27 10:35 UTC (permalink / raw)
Hi, Bernhard,
Bernhard Reimar Hoefle wrote:
> I was thinking of plpgsql functions. How would such a function look like? I
> don't know how to write a plpgsql function which works like the dump()
> function. I just know to write a function like SELECT * from
> your_x('MULTIPOINT(...)')
It even does work without writing your own, using the built-in
generate_series set-returning function:
navteq=# select foo,asText(bar) from test;
foo | astext
-----+-------------------------
42 | MULTIPOINT(1 2,3 4,5 6)
23 | MULTIPOINT(7 8)
(2 rows)
navteq=# select foo,x(geometryn(bar,generate_series(1,npoints(bar))))
FROM test;
foo | x
-----+---
42 | 1
42 | 3
42 | 5
23 | 7
(4 rows)
An appropriate set-returning function would be along the lines of:
CREATE OR REPLACE FUNCTION generate_x (geom geometry)
RETURNS SETOF double precision AS
'DECLARE
index integer;
BEGIN
FOR index IN 1 .. npoints(geom) LOOP
RETURN NEXT X(geometryn(geom,index));
END LOOP;
END
' LANGUAGE 'plpgsql' IMMUTABLE STRICT;
However it does not work for some obscure reason:
navteq=# select foo,generate_x(bar) from test;
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "generate_x" line 5 at return next
The problem is that I can call other set-valued functions in the same
context:
navteq=# select foo,dump(bar) from test;
foo | dump
-----+--------------------------------------------------
42 | ({1},0101000000000000000000F03F0000000000000040)
42 | ({2},010100000000000000000008400000000000001040)
42 | ({3},010100000000000000000014400000000000001840)
23 | ({1},01010000000000000000001C400000000000002040)
(4 rows)
And the generate_series above does work, too.
I'll try to investigate this.
> But my problem is performance and the C functions are quite faster than the
> procedural scripts.
Yes, that may be the case for you.
HTH,
Schabi
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-03-27 10:35 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-20 16:13 [postgis-users] X() function working for POINTs and MULTIPOINTs Bernhard Reimar Hoefle <Bernhard.Hoefle@uibk.ac.at>
2006-03-20 20:54 ` Paul Ramsey <pramsey@refractions.net>
2006-03-24 14:45 ` Markus Schaber <schabi@logix-tt.com>
2006-03-22 16:57 [postgis-users] Re: X() function working for POINTs and MULTIPOINTs Bernhard Reimar Hoefle <Bernhard.Hoefle@uibk.ac.at>
2006-03-25 09:27 [postgis-users] X() function working for POINTs and MULTIPOINTs Bernhard Reimar Hoefle <Bernhard.Hoefle@uibk.ac.at>
2006-03-27 10:35 ` Markus Schaber <schabi@logix-tt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox