Thread: 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.
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
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
entityto whom they are addressed. If you have received this email in error please notify the sender. This message
containsconfidential information and is intended only for the individual named. If you are not the named addressee you
shouldnot 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 entityto whom they are addressed. If you have received this email in error please notify the sender. This message containsconfidential information and is intended only for the individual named. If you are not the named addressee you shouldnot 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 entityto whom they are addressed. If you have received this email in error please notify the sender. This message containsconfidential information and is intended only for the individual named. If you are not the named addressee you shouldnot disseminate, distribute or copy this e-mail.