[postgis-users] Issue with the geometry field in postgresql tables

[Markus Schaber]

Hi, Vincent,

"Vincent J. Troisi" <Vincent.Troisi at Colorado.EDU> wrote:

> Any examples of how one might implement functional indices on 
> transformed geometries?

[local]:schabi=# create table foo (name varchar, geom geometry);
CREATE TABLE

[local]:schabi=# CREATE INDEX foo_transformed_idx ON foo USING GIST
(transform(geom, 4326)); CREATE INDEX

[local]:schabi=# EXPLAIN SELECT geom, name FROM foo WHERE transform(geom,4326) && setsrid('BOX(0 0,10 10'::box2d,4326);

                              QUERY PLAN
---------------------------------------------------------------------------------------
 Index Scan using foo_transformed_idx on foo  (cost=0.00..2.92 rows=1 width=64)
   Index Cond: (transform(geom, 4326) && '0103000020E610000001000000050000000000000000000000000000000000000000000000000000000000000000002440000000000000244000000000000024400000000000002440000000000000000000000000000000000000000000000000'::geometry)
   Filter: (transform(geom, 4326) && '0103000020E610000001000000050000000000000000000000000000000000000000000000000000000000000000002440000000000000244000000000000024400000000000002440000000000000000000000000000000000000000000000000'::geometry)
(3 rows)


Regards,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org