[postgis-users] Spatial index and coordinate transform ?

[Kevin Neufeld]

Hi Roman,

Denormalization is a database concept referring to data redundancies.

http://database-programmer.blogspot.com/2008/04/denormalization-patterns.html
http://en.wikipedia.org/wiki/Denormalization

My suggestion, although making queries faster, duplicates the geometry field to geometries in a different projection. 
The redundant copy can always be derived from the originals.  But now you have to worry about keeping your data in sync. 
  Whenever you modify the original geometries (add new ones, remove old ones, alter existing ones), you have to do the 
same thing to your geometry copies.

The same problem occurs with attribute fields like area or length that represent the area or length of some geometry 
field.  Such fields are redundant and can always be calculated with ST_Area() and ST_Length().  Again, whenever the 
original geometries are modified, the extraneous fields have to get updated as well.

This is where triggers can help you.  Applied to a table with a geometry column, you can write them to automatically 
update those redundant columns by recomputing area, length, or a reprojected geometry.

http://www.postgresql.org/docs/8.4/static/triggers.html

Hope that helps,
Kevin

Smith Roman wrote:
> HI,
> 
> Thanks for the wonderful insight. I think creating a functional index is 
> my best option. I would like you to explain in more detail what you mean 
> about the problem of denormalized data and synchronization and how 
> triggers can help.
> 
> Cheers,
> 
> Roman.
> 
>