[postgis-users] Transform Performance

[Mark Cave-Ayland]

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Charlie Savage
> Sent: 26 October 2005 00:15
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] Transform Performance

(cut)

> Should it be expected that doing a transform causes such a large 
> performance degradation?
> 
> Thanks,
> 
> Charlie


Hi Charlie,

I'm seeing similar slowdowns on my development machine here (PostgreSQL
8.0/PostGIS 1.0.3). Here are my example queries:

infomapper_dev=# \timing
Timing is on.
infomapper_dev=# select count(*) from osgb_point;
 count
--------
 425588
(1 row)

Time: 723.873 ms
infomapper_dev=# select count(transform(geom, 4326)) from osgb_point;
 count
--------
 425582
(1 row)

Time: 115331.752 ms


Having a quick play with calling the transform_geometry() function directly,
I can see that a lot of the time appears to be disappearing into the
transform() plpgsql wrapper, e.g. copying the values from spatial_ref_sys
directly I get the following:


infomapper_dev=# select count(transform_geometry(geom, '+proj=tmerc
+lat_0=49 +lon_0=-2 +k=0.999601 +x_0=400000 +y_0=-100000 +ellps=airy
+units=m +no_defs'::text, '+proj=longlat +ellps=WGS84 +datum=WGS84
+no_defs'::text, 4326)) from osgb_point;
 count
--------
 425582
(1 row)

Time: 30944.905 ms


So as a starting point (without running a decent profile) there is
definitely some mileage in converting the transform() wrapper function into
C and including its functionality in transform_geometry() directly.


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com 
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.