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

[Chapman, Martin]

Mark,

Just out of curiosity, you mention PostgreSQL tuning at the bottom of
your email.  How will changing that setting help performance?  What do
you set it at when you run your database.

Thanks,

Martin Chapman
SANZ Inc.
Software Developer
http://www.earthwhere.com
W - 303.495.6326
C  - 303.898.0397

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Mark
Cave-Ayland
Sent: Tuesday, December 05, 2006 5:10 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Fast access to PostGIS from c/c++

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.


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