[postgis-users] ESRI Shapes from database

[Obe, Regina]

Lee,
 
Okay now I think I understand the problem more clearly.  I guess I
should have earlier from your description.  Sadly I think you do have to
do the memcpy approach in C or similar thing in PLPerl or PLJava and
then bind that as a function. Unfortunately I know nothing about that
stuff.
 
I think PLPerl ,PLJava, PLPython would be a little easier (granted
slower) since you can write the code directly in Postgresql.  Then write
something like this
http://www.captain.at/howto-java-convert-binary-data.php but I'm not
sure if you would be allowed rights to create a new language on your
PostgreSQL server.
 
The problem is that the encode and decode functions are simply doing
base math between the two representations where as your bytea is storing
the physical bytes that define the double - precision (and the double
precision struct is not a straightforward byte to byte translation).  To
implement this in PostgreSQL without direct access to the memory of the
double precision - I suspect you'd have to get deep into bitwise math to
do some pretty hairy stuff  like described below for the
double-precision 64 bit which even if you get it right would be pretty
slow if done in Pg. (e.g. first bit for sign, next 11 bits for exponent,
last 52 for fraction)
 
http://en.wikipedia.org/wiki/IEEE_floating-point_standard 
 
 
As far as your C# stuff goes,  you said you used StringBuilder, have you
tried using StringWriter?  I haven't myself so I'm speaking from a lofty
level of ignorance.  I know StringWriter writes to a StringBuilder so
would seem more round about, but I wonder if it would get around your
memory problem since I think it behaves more like an IO Stream so maybe
it writes to a stringbuilder at the end.
 
Sorry I wasn't more helpful,
Regina
 
 

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Lee
Keel
Sent: Wednesday, October 17, 2007 3:39 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] ESRI Shapes from database


________________________________


From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
Regina
Sent: Wednesday, October 17, 2007 11:16 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] ESRI Shapes from database

 

Lee,

 

Well here is an example I grabbed from somewhere.  But if I exceed the
size of the bytearray - I get one of the errors you describe.  That was
why I thought maybe your offsets are wrong and part of your array has
terminators embedded in it.

 

For example these casts work okay

SELECT (encode(E'\\002The'::bytea, 'hex'))::float
SELECT (encode(substring(E'\\002The'::bytea from 1 for 8),
'hex'))::float

SELECT (encode(substring(E'\\002The'::bytea from 2 for 8),
'hex'))::float

 

--Now if I try to go past the size of the bytea observe - this casts to
empty

SELECT (encode(substring(E'\\002The'::bytea from 5 for 8), 'hex'))

 

And if I then try to cast that empty to float

SELECT (encode(substring(E'\\002The'::bytea from 5 for 8),
'hex'))::float

 

I get the error 

ERROR:  invalid input syntax for type double precision: ""

 

Hope that helps,

Regina

 

________________________________

[Lee Keel] 

Regina,

 

Let me start by saying THANK YOU for all of your help on this.  I have
been trying the sample like you have above and I see what it is doing,
but I can't seem to get it to work with a value that I have.  

 

I am getting a hex string back from by bytea:

SELECT (encode(substring(esri_shape from cnt for 8), 'hex')) =
'E4A9F1517F0E2B41'

 

So in theory this should work:

SELECT (encode(substring(esri_shape from cnt for 8), 'hex'))::float

 

But instead I get 'invalid syntax for a double precision'.  In my C#
code, this hex value ('E4A9F1517F0E2B41') converts to 886591.660046872
using the microsoft bit converter.  The bit converter converts the given
hex into the following bit array:

 

[0]: 228

    [1]: 169

    [2]: 241

    [3]: 81

    [4]: 127

    [5]: 14

    [6]: 43

    [7]: 65

 

I would appreciate any help you can continue to provide on this.

 

Thanks again,

Lee

This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they are
addressed. If you have received this email in error please notify the
sender. This message contains confidential information and is intended
only for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail.



-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071018/851fd2a9/attachment.html>