[postgis-users] Querying geometries with different srid

[Farrukh Najmi]

Doing some RTFM I see on the posgis page here: 
http://workshops.opengeo.org/postgis-intro/projection.html

"

Note

Be careful of getting too happy with using *ST_Transform* for on-the-fly 
conversion. Spatial indexes are built using SRID of the stored 
geometries. If comparison are done in a different SRID, spatial indexes 
are (often) not used. It is best practice to choose *one SRID* for all 
the tables in your database. Only use the transformation function when 
you are reading or writing data to external applications.

"

So unless I here otherwise I think above suggests that what I called 
option #1 below is the best practice to follow.

On 01/06/2011 06:00 PM, Farrukh Najmi wrote:
> Hello,
>
> My application needs to support storing of geometry instances with 
> different SRID values in the same table and then be able to query it 
> using spatial operations and a filter geometry which could be in any 
> SRID.
>
> One possible option (Option #1) is to use ST_TRANSFORM function to 
> transform all geometry instances on WRITE to the same internal SRID 
> (say 4326) and transform the filter geometry used by the query to the 
> same SRID used internally (say 4326) before doing the spatial operation.
>
> The other option (Option #2) is to store all geometry instances with 
> their original SRID values on WRITE and use ST_TRANSFORM function in 
> the query to transform all internal geometry instances to match the 
> same SRID as that of the filter geometry used by the query.
>
> It seems option #1 is less accurately preserving the original data but 
> is much more efficient on query while option #2 stores original data 
> without any change but may have serious performance issues due to the 
> transform on query.
>
> What do people advice based upon their experience with postgis for my 
> requirement and possible solution.
>
> Thank you for your help.
>


-- 
Regards,
Farrukh Najmi

Web: http://www.wellfleetsoftware.com


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110106/8c1e2809/attachment.html>