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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
