[postgis-users] 3D index in postgis

[a1001800]

Thanks Kevin,

It looks like ~= not invoke the index.
Do we have a way to deal with an index with third value?
For example, point (x, y) and userid

I need to do a query like "select point(x,y) from table where point in rectangle and userid=xxx"

Regards
Frank





From: Kevin Neufeld
Sent: 2008-05-20 14:52:16
To: PostGIS Users Discussion
CC: 
Subject: Re: [postgis-users] 3D index in postgis

Hi Frank,

Indexes in PostGIS are 2D. ST_Equals, as you've discovered, disregards
the Z component. (Clearly the documentation should be updated to reflect
this.)

Try this instead:
SELECT a.id, AsText(a.map_geom) AS geom
FROM maps a, (SELECT 'POINT(3 3 1)'::geometry AS geom) b
WHERE a.map_geom && b.geom -- invokes the 2D index operator
AND a.map_geom ~= b.geom; -- performs vertex-by-vertex comparison,
including Z


Note that in your query, ST_Equals() doesn't actually use any indexes at
all. Your query is likely performing a sequential scan through your
table looking for 'POINT(3 3)'.


Cheers,
Kevin



a1001800 wrote:
> Hi,
> Please see the sql SELECT id, AsText(map_geom) AS geom FROM maps where
> ST_Equals(map_geom,GeomFromText('POINT(3 3 1)', -1));
> And it matches point(3 3 1), point(3 3 2), point(3 3 3) ...
> The geo field and index is below:
> SELECT AddGeometryColumn( 'maps', 'map_geom', -1, 'GEOMETRY', 3);
> CREATE INDEX geomk ON maps USING GIST (map_geom);
> So it looks like that the index is only working in 2D
> How can I build a index on the 3rd coordinate？
> Thanks
> Frank
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080520/bec79595/attachment.html>