[postgis-users] Spatial index and coordinate transform ?

[Kevin Neufeld]

Smith Roman wrote:
> select gid, road_name, town, AsText(the_geom) as geo, 
> ST_Distance(ST_Transform(the_geom, 26331), 
> ST_Transform(setsrid(makepoint(3.3315343, 6.5593127), 4326), 26331)) as dist
>   from lagos84
> where expand(ST_Transform(setsrid(makepoint(3.3315343, 6.5593127), 
> 4326), 26331), 5) && ST_Transform(the_geom, 26331)
> order by dist
> limit all;
> 
> My question is this:
> 
> 1. As a result of the coordinate transformation, did the query above 
> still use the spatial index ?
>    Since the spatial index was built with the data in wgs 84 (lon & lat) 
> 

No, the above query would not use any spatial index created on just the geometry column (the_geom).


> 2. is the d spatial index dependent on coordinate system ?

No, the PostGIS implemenation of GiST has no knowledge of the coordinate system your spatial data is in.

> 
> 3. what happens during a coordinate transformation ( ST_Transform( ) ) ? 
> is the spatial index rebuilt ?

Again, no.  ST_Transform is just a runtime function call (just like ST_Area, ST_StartPoint, etc.) and has no knowledge 
of indexes created on the supplied geometry.

> 
> I know spatial indexes are built based on mbb's so i am just curious.
> 
> I would also appreciate it if one has better query to meet this requirement.
> 

The way I see it, your options are:
1. Add a functional index on the_geom using ST_Transform.

CREATE INDEX lagos84_geom_utm_26331_idx ON lagos84 USING GIST ( ST_Transform(the_geom, 26331) );

If you use the function call "ST_Transform(the_geom, 26331)" exactly in your WHERE clause (as you've done in your 
query), the functional index will get invoked.

2.  If you often perform queries on transformed geometries, you may want to consider either creating a new table with 
your transformed geometry or adding a second geometry column to your lagos84 table with the transformed geometry.

In either case, index the new utm geometry column as normal and use it as you normally would.  This will drop all the 
excessive calls you are making to ST_Transform, speeding up your queries.

Of course, there's a trade off here, because now you have denormalized data and have to worry about keeping the data in 
sync.  Triggers placed on your lagos84 table will help.

Hope this helps,
Kevin