[postgis-users] Spatial index and coordinate transform ?
kneufeld at refractions.net
Tue Aug 11 08:39:01 PDT 2009
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,
More information about the postgis-users