[postgis-users] spatial indices on two geometry or rast columns

[Vishal Mehta]

Thanks Bryce,

That's useful.

I am not planning to cearch using utm coordinates. The main reason I would create a utm geom, was so that I could calculate areas, distances in sq km easily without having to include nested ST_TRANSFORM statements. In this case would it be useful to have another index on the utm?

Cheers
Vishal

From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bryce L Nordgren
Sent: Thursday, August 11, 2011 1:38 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] spatial indices on two geometry or rast columns



postgis-users-bounces at postgis.refractions.net<mailto:postgis-users-bounces at postgis.refractions.net> wrote on 08/11/2011 07:16:22 PM:

> I'd like to transform these layers to UTM (EPSG 32643). I was
> thinking of creating a new geometry column for each table and
> ALTER TABLE wards ADD column geom_utm geometry;
> UPDATE wards SET geom_utm = ST_TRANSFORM(geom,32643);
>
> Should I, (and can I) create another gist index on the transformed geometry?

If you're planning on searching using utm coordinates, I'd make another index. If the utm version is just there for reference, I wouldn't bother.

> The same qn applies on a raster table I have called srtm_table -
> should I and can I add
> -          a new 'rast' column called rast_utm with transformed SRID
> in similar fashion above, and then add a gist index on the rast_utm column?
>
> CREATE INDEX "srtm_table_rast_gist_idx" ON "public"."srtm_table"
> USING GIST (st_convexhull(rast_utm));

This isn't really a different question than the above, since st_convexhull(raster) produces a geometry. :) I'd say the same thing: if you're going to search using utm coordinates, make a utm index; otherwise, no.


Bryce
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110811/50818dd9/attachment.html>