[postgis-users] Inserting geometry using ADO...

[strk at refractions.net]

On Wed, Nov 10, 2004 at 12:08:31PM -0500, Jason Sardano wrote:
> I did some digging and found the following articles:
> 
> http://users.bigpond.net.au/rmoonen/Jason_Godden1/BLOBs.html
> 
> http://gborg.postgresql.org/project/psqlodbc/faq/faq.php?faq_id=52
> 
> Apparently, the odbc driver can be configured to see byte arrays as a large
> object (LO). I did two tests. I configured my connection to see bytea as LO.
> The geometry column was still represented as text. I then created a table in
> Postgresql that had a column explicitly defined as a bytea. I then linked to
> this table in MSAccess and the resulting table definition was OLE Object.
> So, this leads me to conclude that the geometry type in Postgresql is
> somehow making the driver believe the column is a text/memo.
> 
> Jason

To obtain a bytea WKB representation of the geometry 
you can use:

# SELECT asBinary(the_geom)::bytea from yourtable;

Give it a try and let me know.
Also, please report postgis version.

--strk;

> 
> -----Original Message-----
> From: strk at refractions.net [mailto:strk at refractions.net] 
> Sent: Wednesday, November 10, 2004 3:36 AM
> To: Jason Sardano
> Subject: Re: [postgis-users] Inserting geometry using ADO...
> 
> On Tue, Nov 09, 2004 at 02:23:55PM -0500, Jason Sardano wrote:
> > No, I'm stating that the Postgresql ODBC Drivers see PostGIS's Geometry
> > column as a text field. For example, if you link an Access database to a
> > PostGIS table, and then look at the design of the table in Access's GUI,
> the
> > Geometry field is defined as being a text field (or a memo field if the
> > field size is greater than 254). See attached image.
> > 
> > Jason
> 
> I didn't see the image, but try to check out how does the drivers
> see bytea columns.
> --strk;
> 
> > 
> > 
> > -----Original Message-----
> > From: strk at refractions.net [mailto:strk at refractions.net] 
> > Sent: Tuesday, November 09, 2004 2:02 PM
> > To: Jason Sardano
> > Subject: Re: [postgis-users] Inserting geometry using ADO...
> > 
> > On Tue, Nov 09, 2004 at 01:50:26PM -0500, Jason Sardano wrote:
> > > Unfortunately, the Postgresql driver sees the geometry column as a text
> > > field, so the binary functionality of PostGIS is lost in this regards
> (at
> > > least I think it is). I wish PostGIS had their own OLEDB/ODBC drivers :(
> > > 
> > > Jason
> > 
> > Are you positive about your statement ?
> > You're saying that PostgreSQL OLEDB/ODBC drivers
> > are not capable of binary transport.
> > --strk;
> > 
> > > 
> > > -----Original Message-----
> > > From: strk at refractions.net [mailto:strk at refractions.net] 
> > > Sent: Tuesday, November 09, 2004 1:37 PM
> > > To: jsardano at northgeo.com; PostGIS Users Discussion
> > > Subject: Re: [postgis-users] Inserting geometry using ADO...
> > > 
> > > On Tue, Nov 09, 2004 at 12:43:27PM -0500, Jason Sardano wrote:
> > > > I am writing an extension for ESRI's ArcCatalog that will allow a user
> > to
> > > > export feature classes to PostGIS. The spatial data is exported as WKT
> > to
> > > > PostGIS correctly if and only if the WKT length is less than or equal
> to
> > > the
> > > > max varchar size attribute of my connection and the geometry column's
> > > length
> > > > (the OLE DB driver from Postgresql sees the geometry as text). If I
> > change
> > > > the max varchar size property on my connection string to a valid size
> > for
> > > > the geometry (greater than 8190), I receive a message box stating
> "can't
> > > > alloc Desc Handle yet"; so simply changing the max varchar size is not
> a
> > > > solution. I also receive an error if I try a raw sql update of the
> > > geometry.
> > > > Is there a better way to insert spatial data into PostGIS from an
> > > OLEDB/ODBC
> > > > client?
> > > 
> > > I've no experience with OLEDB/ODBC, but postgis supports binary input
> > > in WKB format.
> > > 
> > > --strk;
> > > 
> > > 
> > > > 
> > > >  
> > > > 
> > > > Thank you,
> > > > 
> > > > Jason Sardano
> > > > 
> > > >  
> > > > 
> > > > //Sample ADO Code:
> > > > 
> > > > ipRecordset->AddNew();
> > > > 
> > > > //.
> > > > 
> > > > ipRecordset->put_Collect(CComVariant(shapeFieldName),
> > > > CComVariant(ShapeToWKT(ipGeometry, -1))); //ShapeToWKT returns a BSTR
> > > > 
> > > > //.
> > > > 
> > > > ipRecordset->Update(); //the geometry is saved if it is a valid
> length,
> > > > otherwise a NULL geometry is stored.
> > > > 
> > > 
> > > > _______________________________________________
> > > > postgis-users mailing list
> > > > postgis-users at postgis.refractions.net
> > > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > > 
> 
>