[postgis-users] Query 3D points ignores z values....

[Abd Muhsen]

Hello Mark,

Unfortunately, it didn't work!! I tried many times with different OGC 
operators with no luck. Nothing considers the Z coordinate. Give it a try 
and see:

CREATE TABLE table3d (
id integer not null,
name varchar(50)
);

INSERT INTO table3d VALUES(1, 'pnt1', geomfromEWKT('POINT(12 4 6)'));
INSERT INTO table3d VALUES(2, 'pnt2', geomfromEWKT('POINT(44 41 46)'));
INSERT INTO table3d VALUES(3, 'pnt3', geomfromEWKT('POINT(29 7 31)'));

-- Show all
select id, name, asEWKT(pnt3d) from table3d;

-- Test 3D Query
SELECT id, name, asEWKT(pnt3d) FROM table3d WHERE pnt3d && 'BOX3D(0 0
50, 100 100 100)'::box3d AND intersects(pnt3d, 
Envelope(Makebox3d(makepoint(0,0,50), makepoint(100,100,100) )));

I wonder how hard it is to customize these operators. In the mean time, I 
will add a new colunm (say Z) to the table, and build a btree index on it. 
So, my query will look like this:

SELECT id, name, asEWKT(pnt3d) FROM table3d
WHERE
pnt3d && 'BOX3D(0 0 50, 100 100 100)'::box3d AND
Z >= 50 AND Z<= 100 ;

What do you think?!!

In all cases, thank you so much for your effort and thoughts, it is all 
appreciated ;)

Abdel M.


>From: Mark Cave-Ayland <mark.cave-ayland at ilande.co.uk>
>Reply-To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>Subject: RE: [postgis-users] Query 3D points ignores z values....
>Date: Wed, 04 Apr 2007 19:23:48 +0100
>
>On Wed, 2007-04-04 at 20:03 +0200, Abd Muhsen wrote:
> > Hi there,
> >
> > Can anybody help me with this please?!! I cann't understand why the
> > following query doesn't return correct result:
> >
> > SELECT  id, name, asEWKT(pnt3d) FROM table3d
> > WHERE pnt3d && 'BOX3D(0 0 50, 100 100 100)'::box3d;
> >
> > It is supposed to return all the 3D points within a cube. However, what 
>is
> > returned from this query is all the points which are inside the 2D box 
>(0 0,
> > 100 100), which is not right... am I missing something or what? Please 
>help.
> >
> > Cheers,
> >
> > Abdel m.
>
>
>Hi Abdel,
>
>The PostGIS indexable operators (like &&) which operate on the bounding
>boxes are currently only 2D only. I believe that someone mentioned that
>the OGC operators (from GEOS) are 2.5D, so you may be able to get the
>result you want by including an additional OGC operator with an AND
>clause to provide the additional filtering you need, e.g.
>
>SELECT id, name, asEWKT(pnt3d) FROM table3d WHERE pnt3d && 'BOX3D(0 0
>50, 100 100 100)'::box3d AND intersects(pnt3d, envelope('BOX3D(0 0 50,
>100 100 100)'));
>
>
>Kind regards,
>
>Mark.
>
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users