Convert bytea to Float8

I am sorry to push this issue, but I have clients waiting on a solution for
this. So, does the lack of response mean that I am going to have to find
another approach? Or can this conversion be done in ppgsql\perl?

Thanks in advance,

Lee Keel

_____

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Lee Keel
Sent: Monday, October 15, 2007 8:33 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Convert bytea to Float8

Can someone please answer what I hope to be an easy question? I am trying
to convert 8 bytes of a bytea into a float8.

In C# this code looks like:

xCoord = System.BitConverter.ToDouble(arrByte, cnt) ;

But in my plpgsql function, the following gives me an error:

xCoord := substring(arrByte from cnt for 8)::float8;

The error that I get is:

ERROR: 42846: cannot cast type bytea to double precision

I have also tried get_byte, but that seems to only be returning the single
byte and not all 8bytes for the float. Thanks in advance.

Lee Keel

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.

On 10/15/07, Lee Keel <lee.keel@uai.com> wrote:

I am sorry to push this issue, but I have clients waiting on a solution for
this. So, does the lack of response mean that I am going to have to find
another approach? Or can this conversion be done in ppgsql\perl?

this is trivially done in a C procedure on the database....are you
willing to do that?

merlin

On 10/15/07, Lee Keel <lee.keel@uai.com> wrote:

I am sorry to push this issue, but I have clients waiting on a solution for
this. So, does the lack of response mean that I am going to have to find
another approach? Or can this conversion be done in ppgsql\perl?

Does something like:

select encode('12346758'::bytea,'escape')::float

work?

Since I'm not sure what format your data is stored in I'm just guessing here.

Note that you could use get_byte and cycle through the offset to get
each byte as well.

On Mon, Oct 15, 2007 at 03:16:40PM -0500, Lee Keel wrote:

I am sorry to push this issue, but I have clients waiting on a solution for
this. So, does the lack of response mean that I am going to have to find
another approach? Or can this conversion be done in ppgsql\perl?

This question probably doesn't help Lee, but what do the *recv (i.e.
float8 float8recv(internal)) functions do? Is this just used to receive
things from the client over the wire, or could they be used somehow
here.

Thanks,
Sam

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Monday, October 15, 2007 3:54 PM
To: Lee Keel
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Convert bytea to Float8

Does something like:

select encode('12346758'::bytea,'escape')::float

work?

Since I'm not sure what format your data is stored in I'm just guessing
here.

Note that you could use get_byte and cycle through the offset to get
each byte as well.

I am sorry to say that the following line did not work. I also tried the
base64 and hex encoding types and got the same error.
select encode(esri_shape::bytea,'escape')::float

I got the following error:
invalid input syntax for type double precision

The data is an ESRI blob and I am trying to convert it to a WKB to store it
in postgis. I was doing this in C# code, but I am getting a string memory
error when I get into some of my large multi-polygons. So, my solution was
to do all the byte parsing on the server. I know the standard way is to
dump to shape file and import from there, but that is too much to ask of my
client to do in this case.

Thanks,
Lee Keel

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.

-----Original Message-----
From: Merlin Moncure [mailto:mmoncure@gmail.com]
Sent: Monday, October 15, 2007 3:26 PM
To: Lee Keel
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Convert bytea to Float8

On 10/15/07, Lee Keel <lee.keel@uai.com> wrote:

I am sorry to push this issue, but I have clients waiting on a solution

for

this. So, does the lack of response mean that I am going to have to

find

another approach? Or can this conversion be done in ppgsql\perl?

this is trivially done in a C procedure on the database....are you
willing to do that?

merlin

At this point I would be willing to try anything. I am just not sure how to
do it in C and get it into the database. Can you, or anyone else, help me
out with this? I am running postgres 8.2 on a 64 bit windows vista os with
VS2005 installed, so:

1) Can I use VS2005 C++ dll?
2) Where do I set the dynamic library path? I found it commented out in the
pstgresql.conf file, but it is referencing $libdir.
3) Actually, I have not written C code in so long, I would probably even be
better off if I could get someone to write this for me.

Sorry for my lack of knowledge on this, but that is outside of my expertise
with postgres.

Thanks,
LK

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.

On 10/15/07, Lee Keel <lee.keel@uai.com> wrote:

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Monday, October 15, 2007 3:54 PM
To: Lee Keel
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Convert bytea to Float8

Does something like:

select encode('12346758'::bytea,'escape')::float

work?

Since I'm not sure what format your data is stored in I'm just guessing
here.

Note that you could use get_byte and cycle through the offset to get
each byte as well.

I am sorry to say that the following line did not work. I also tried the
base64 and hex encoding types and got the same error.
select encode(esri_shape::bytea,'escape')::float

I got the following error:
invalid input syntax for type double precision

The data is an ESRI blob and I am trying to convert it to a WKB to store it
in postgis. I was doing this in C# code, but I am getting a string memory
error when I get into some of my large multi-polygons. So, my solution was
to do all the byte parsing on the server. I know the standard way is to
dump to shape file and import from there, but that is too much to ask of my
client to do in this case.

This is sounding more and more like a question for the postgis lists.
why couldn't you load the shape file from the client side?

here is one link i found discussing loading WKB files:
http://osdir.com/ml/gis.postgis/2003-04/msg00067.html

merlin

-----Original Message-----
From: Merlin Moncure [mailto:mmoncure@gmail.com]
Sent: Monday, October 15, 2007 8:22 PM
To: Lee Keel
Cc: Scott Marlowe; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Convert bytea to Float8

This is sounding more and more like a question for the postgis lists.
why couldn't you load the shape file from the client side?

here is one link i found discussing loading WKB files:
http://osdir.com/ml/gis.postgis/2003-04/msg00067.html

merlin

Thanks merlin. I looked at that and not a lot of help. I will post to the
postgis group and see what they say. I can't load a shape file because I
don't have one and my client doesn't have permissions to write files to the
machine. Plus I don't have any ESRI products loaded on this server to
create one myself. The security at this client is EXTREMELY strict. Thanks
again for all the help.

-LK
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.