[postgis-devel] Transform() PROJ4 cache patch

['strk']

Thanks for timing Mark.
It seems there's no much to gain from invoking transform_geometry
directly in terms of performance.
Still, I'd keep it as users might want to perform reprojection
even w/out a spatial_ref_sys table.
There's no work involved in keeping it, so what's the point of
removing it ?

--strk;

On Fri, Nov 04, 2005 at 11:24:42AM -0000, Mark Cave-Ayland wrote:
> 
> > -----Original Message-----
> > From: 'strk' [mailto:strk at keybit.net] 
> > Sent: 04 November 2005 09:43
> > To: Mark Cave-Ayland (External)
> > Subject: Re: [postgis-devel] Transform() PROJ4 cache patch
> 
> (cut)
> 
> > CASE:
> > 	- transformation applied to a single geometry
> > 	- argument SRID projections are not in the cache
> > Which is faster ?
> > 
> > --strk;
> 
> 
> Hi strk,
> 
> Here are some results from testing here on different numbers of points (1,
> 5, 10, 100, 1000, 10000, 100000):
> 
> 
> CONSTANT TRANSFORMATIONS (i.e. not on a column):
> 
> transform_geometry() ~ 0.5ms faster than transform_cache() for all cases
> 
> 
> COLUMN TRANSFORMATIONS
> 
> infomapper_dev=# select count(*) FROM (SELECT 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 LIMIT 1) AS foo;
>  count
> -------
>      1
> (1 row)
> 
> Time: 0.978 ms
> infomapper_dev=# select count(*) FROM (SELECT transform_cache(geom, 4326)
> FROM osgb_point LIMIT 1) AS foo;                     count
> -------
>      1
> (1 row)
> 
> Time: 1.459 ms
> 
> 
> infomapper_dev=# select count(*) FROM (SELECT 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 LIMIT 5) AS foo;
>  count
> -------
>      5
> (1 row)
> 
> Time: 1.298 ms
> infomapper_dev=# select count(*) FROM (SELECT transform_cache(geom, 4326)
> FROM osgb_point LIMIT 5) AS foo;                     count
> -------
>      5
> (1 row)
> 
> Time: 1.565 ms
> 
> 
> infomapper_dev=# select count(*) FROM (SELECT 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 LIMIT 10) AS foo;
>  count
> -------
>     10
> (1 row)
> 
> Time: 1.680 ms
> infomapper_dev=# select count(*) FROM (SELECT transform_cache(geom, 4326)
> FROM osgb_point LIMIT 10) AS foo;                    count
> -------
>     10
> (1 row)
> 
> Time: 1.583 ms
> 
> 
> infomapper_dev=# select count(*) FROM (SELECT 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 LIMIT 100) AS foo;
>  count
> -------
>    100
> (1 row)
> 
> Time: 8.428 ms
> infomapper_dev=# select count(*) FROM (SELECT transform_cache(geom, 4326)
> FROM osgb_point LIMIT 100) AS foo;                   count
> -------
>    100
> (1 row)
> 
> Time: 3.094 ms
> 
> 
> infomapper_dev=# select count(*) FROM (SELECT 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 LIMIT 1000) AS foo;
>  count
> -------
>   1000
> (1 row)
> 
> Time: 76.267 ms
> infomapper_dev=# select count(*) FROM (SELECT transform_cache(geom, 4326)
> FROM osgb_point LIMIT 1000) AS foo;                  count
> -------
>   1000
> (1 row)
> 
> Time: 17.709 ms
> 
> 
> infomapper_dev=# select count(*) FROM (SELECT 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 LIMIT 10000) AS foo;
>  count
> -------
>  10000
> (1 row)
> 
> Time: 753.626 ms
> infomapper_dev=# select count(*) FROM (SELECT transform_cache(geom, 4326)
> FROM osgb_point LIMIT 10000) AS foo;                 count
> -------
>  10000
> (1 row)
> 
> Time: 164.406 ms
> 
> 
> infomapper_dev=# select count(*) FROM (SELECT 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 LIMIT 100000) AS foo;
>  count
> --------
>  100000
> (1 row)
> 
> Time: 7532.049 ms
> infomapper_dev=# select count(*) FROM (SELECT transform_cache(geom, 4326)
> FROM osgb_point LIMIT 100000) AS foo;                count
> --------
>  100000
> (1 row)
> 
> Time: 1640.356 ms
> 
> 
> So it looks like the break even point is somewhere between 5 and 10 points
> to make a saving. Since it is only in the region of 0.5ms for such a small
> number of points, IMHO, I can't see the point of maintaining two separate
> code paths.
> 
> I'm also more confident about that patch as it's been running on our dev
> server for a week now without any problems, plus I see that they do a
> similar thing for caching connections in contrib/dblink but using a
> longer-term context.
> 
> BTW shouldn't we move this back onto postgis-devel?
> 
> 
> 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.
>