[postgis-devel] Transform() PROJ4 cache patch

[strk at refractions.net]

Great work Mark.

I tested your code against 7.2.1, but it doesn't compile:

gcc -DUSE_VERSION=72 -DPOSTGIS_LIB_VERSION='"1.1.0CVS"' -DPOSTGIS_SCRIPTS_VERSION='"0.3.1"' -DPOSTGIS_BUILD_DATE='"2005-11-01 10:37:45"' -I/usr/home/strk/extra/proj-4.4.5//include -DUSE_PROJ  -I/pgroot-7.2.1/include/postgresql/server -DAUTOCACHE_BBOX=1  -DUSE_STATS -Wall -g -O2 -fPIC -fexceptions -DUSE_GEOS    -c -o lwgeom_transform.o lwgeom_transform.c
lwgeom_transform.c:84: warning: excess elements in struct initializer
lwgeom_transform.c:84: warning: (near initialization for `PROJ4SRSCacheContextMethods')
lwgeom_transform.c:89: warning: excess elements in struct initializer
lwgeom_transform.c:89: warning: (near initialization for `PROJ4SRSCacheContextMethods')
lwgeom_transform.c: In function `AddToPROJ4SRSCache':
lwgeom_transform.c:196: warning: implicit declaration of function `SPI_execute'
lwgeom_transform.c: In function `transform':
lwgeom_transform.c:493: `MessageContext' undeclared (first use in this function)
lwgeom_transform.c:493: (Each undeclared identifier is reported only once
lwgeom_transform.c:493: for each function it appears in.)


--strk;

On Fri, Oct 28, 2005 at 08:00:28AM +0100, Mark Cave-Ayland wrote:
> Hi everyone,
> 
> After looking at the thread here
> (http://postgis.refractions.net/pipermail/postgis-users/2005-October/009771.
> html) regarding the slow performance of the transform() function, I've had a
> go at implementing a caching version of transform() which attempts to
> eliminate both the overhead of looking up entries in the spatial_ref_sys
> table and creating/destroying reprojection objects with pj_init() and
> pj_free().
> 
> The key point with maintaining a cache is knowing when to initialise and
> destroy the cache to ensure that any entries are invalidated after the
> current query. After reading about the hierarchical memory contexts used by
> PostgreSQL in src/backend/utils/mmgr/README, it occurred to me that it could
> be possible to create a child memory context for MessageContext that would
> receive notifications upon the start and end of each query which could make
> it possible to implement a cache suitable for an IMMUTABLE function.
> 
> While implementing an entire MemoryContext implementation seemed a lot of
> work, I decided to simply use the Init() and Delete() functions as
> notifications, and store the cached items within each individual backend. 
> 
> Whenever transform() is called, the patch checks to see if our child memory
> context exists; if it doesn't then the cache memory context is added as a
> child to MessageContext and the cache initialised for this query. Both the
> input (geometry) and output (constant) proj4texts are looked up in the cache
> to see if they exist; if they do then it is possible to return a PJ *
> pointer ready for use immediately. Otherwise the code uses SPI to lookup the
> proj4text from spatial_ref_sys, calls make_project(), and adds the PJ *
> pointer to the cache using the srid as the key.
> 
> When the current query has finished processing, the Delete() method of the
> cache memory context is automatically called which first pj_free()s any
> existing PJ * pointers followed by the cache itself before the context is
> deleted.
> 
> 
> Performance here with PostgreSQL 8.0/PostGIS 1.0.3 is very good:
> 
> BEFORE
> 
> 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
> 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
> 
> 
> AFTER TRANSFORM CACHE PATCH APPLIED
> 
> infomapper_dev=# select count(transform(geom, 4326)) from osgb_point;
>  count
> --------
>  425582
> (1 row)
> 
> Time: 5564.839 ms
> 
> 
> I haven't applied this to CVS as this is my first effort delving into
> MemoryContexts and I would consider it highly experimental. I would
> therefore be grateful if people experiencing the slow transform() problem
> could test and post any results back to the list.
> 
> 
> 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.


> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel