[postgis-users] Fast access to PostGIS from c/c++

Mark Cave-Ayland mark.cave-ayland at ilande.co.uk
Tue Dec 5 04:09:32 PST 2006


On Tue, 2006-12-05 at 12:25 +0100, Boehm, Andreas wrote:
> Hi!
> 
> Evaluating the possibilities of using PostGIS instead of ArcSDE, I'm
> looking for a fast solution to insert and read points and polygons to
> PostGIS from my c++ code.
> 
> I have tried several approaches with libpqxx. The only way to insert
> points is executing a statement with WNT like "INSERT INTO points VALUES
> (1, GeometryFromText('POINT (10 20)', -1) )". Either the tabelwriter
> functionality (internally using COPY) nor predefined statements ("INSERT
> INTO points VALUES ($1, GeometryFromText('POINT ($2 $3)', -1)" do work.

Hi Andi,

AFAICT using prepared statements in this way won't work because
GeometryFromText() takes a string argument and you are trying to
substitute values within the string parameter, rather than the parameter
itself.

In theory, COPY should be fastest method of inserting geometries since
it requires that the client should already have converted the geometry
to EWKB format ready for insertion. Documentation on the (E)WKB formats
can be found within the PostGIS source download.

If all your geometries are of one type, e.g. points, the second fastest
method would probably be to use a prepared statement using the
MakePoint() function and put as many points as possible within a single
transaction, e.g.

PREPARE myplan(int, double, double) AS INSERT INTO points VALUES ($1,
MakePoint($2, $3));
BEGIN;
..
..
EXECUTE myplan(1, 20, 30);
..
..
COMMIT;

Since committing a transaction incurs an extra write to the transaction
log, then placing as many points (if not all) within the same
transaction will greatly speed your insertion speed.

Finally, don't forget your basic PostgreSQL tuning - by default
out-of-the-box PostgreSQL only uses 8Mb of memory, so you'll need to
alter the postgresql.conf parameters for
shared_buffers/effective_cache_size to suit the amount of RAM in your
server and restart PostgreSQL.


Kind regards,

Mark.





More information about the postgis-users mailing list