[postgis-users] Using libpq, the COPY command and binary insertion of geometries

[Ryan Proulx]

Hello all,

This is my first time posting to the list so we'll see how helpful it
is. Forgive me if these questions have been answered previously, I did
look, but without a search for the archived postings I cannot be certain
I was thorough enough. The closest topic I found was a single post from
Paul asking if anyone wanted support for binary geometries that no one
replied to in June of 2001. Anyway, on to my questions.

I am trying to insert spatial data into PostGIS and I am having success
with both inserting WKT geometries using individual SQL INSERT
statements and using the PostgreSQL COPY syntax as outlined in the
PostgreSQL documentation.

I am concerned with performance however, and I would like to switch
representations of the geometries as little as possible thus inserting a
WKB string rather that a WKT string is preferable, (correct me if I am
wrong but I assume the PostGIS geometries are stored as binary blobs and
not as text in the database, but not as WKB).

Is there any way that I can write the geometries to PostGIS without
first providing the WKT representation? Perhaps something on the order
of a GeometryFromBinary(<WKB string>) function instead of a geometry
from text, which would take a WKB argument instead of a well know text
one? Or alternatively tell me how to format the blobs from WKB and the
SRID to a GEOMETRY blob in PostGIS so that I can write it myself? I am
willing to write in either hex or binary encoded strings, but preferably
not in plain text strings due to size and performance.

Furthermore, is insertion of geometries supposed to be compatible with
the PostgreSQL COPY command? I can perform bulk insertions if I specify
the SRID with the WKT geometry appended (i.e. 'SRID=26371;POINT(1234
4321)'), but once again I need to convert the geometry to WKT first. I
would like to be able to insert WKB directly. Is this even possible? I
have not been successful so far.

If I try insertions with a WKB string I get errors like 'ERROR:  couldnt
parse objects in GEOMETRY'. I assume that is because of the extra SRID
that I don't know how to account for. I also tried with hex encoded
strings, attempting to reinsert results of the AsBinary() function to no
avail. Again I think I was foiled by the SRID.

If anything has jumped out at you about what I am doing wrong here or if
anyone has had success inserting WKB geometries or using WKB geometries
with the COPY command for bulk insertion, please let me know.

Thanks in advance.

Regards,
ryan