[postgis-users] Re: Transform Performance

[Charlie Savage]

Interesting, I see a similar thing.  To first recap, this query takes 1 
second:

create temp table t1 as
select ogc_fid, wkb_geometry as wkb_geometry
from my_table;


And this query, with a transform, takes 34 seconds:

create temp table t3 as
select ogc_fid, Transform(wkb_geometry, 4326) as wkb_geometry
from my_table;


Similar to you, calling transform_geometry directly is more than 3 times 
faster, approximately 10 seconds:

create temp table t2 as
select ogc_fid,
transform_geometry(wkb_geometry, '+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) as wkb_geometry
from my_table;


While this query takes about 1.2 seconds (get_proj4_from_srid is 
specified as IMMUTABLE, see below):

create temp table t3 as
select ogc_fid, get_proj4_from_srid(4326)
from my_table;

And this query 1.1 seconds:

create temp table t4 as
select ogc_fid, SRID(wkb_geometry)
from my_table;

Unexpectedly, combining the calls together takes 9 seconds:

create temp table t5 as
select ogc_fid, get_proj4_from_srid(SRID(wkb_geometry))
from my_table;

And combining everything together takes 17 seconds:

create temp table t8 as
select ogc_fid,
   transform_geometry(wkb_geometry,
     get_proj4_from_srid(SRID(wkb_geometry)), get_proj4_from_srid(4326), 
4326)
from my_table;

It certainly seems like 1/2 the time is spent in the transform method 
itself, although its not all obvious to me why (it doesn't do much 
besides the method calls listed above).

strk - the definition of get_proj4_from_srid() shows it is IMMUTABLE:

CREATE OR REPLACE FUNCTION public.get_proj4_from_srid(int4)
   RETURNS text AS
$BODY$
BEGIN
	RETURN proj4text::text FROM spatial_ref_sys WHERE srid= $1;
END;
$BODY$
   LANGUAGE 'plpgsql' IMMUTABLE STRICT;


Thanks,


Charlie


Mark Cave-Ayland wrote:
>> -----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.