[postgis-users] 3D index in postgis

[Kevin Neufeld]

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
>