<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:v =
"urn:schemas-microsoft-com:vml" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word" xmlns:st1 =
"urn:schemas-microsoft-com:office:smarttags"><HEAD><TITLE>ESRI Shapes from database</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR><!--[if !mso]>
<STYLE>v\:* {
BEHAVIOR: url(#default#VML)
}
o\:* {
BEHAVIOR: url(#default#VML)
}
w\:* {
BEHAVIOR: url(#default#VML)
}
..shape {
BEHAVIOR: url(#default#VML)
}
</STYLE>
<![endif]--><o:SmartTagType name="place"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><o:SmartTagType
name="City"
namespaceuri="urn:schemas-microsoft-com:office:smarttags"></o:SmartTagType><!--[if !mso]>
<STYLE>st1\:* {
BEHAVIOR: url(#default#ieooui)
}
</STYLE>
<![endif]-->
<STYLE>@font-face {
font-family: Tahoma;
}
@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
P {
FONT-SIZE: 12pt; MARGIN-LEFT: 0in; MARGIN-RIGHT: 0in; FONT-FAMILY: "Times New Roman"; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto
}
SPAN.EmailStyle18 {
COLOR: navy; FONT-FAMILY: Arial; mso-style-type: personal
}
SPAN.EmailStyle20 {
COLOR: navy; FONT-FAMILY: Arial; mso-style-type: personal-reply
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2>Lee,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2>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</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2><A
href="http://www.captain.at/howto-java-convert-binary-data.php">http://www.captain.at/howto-java-convert-binary-data.php</A> but
I'm not sure if you would be allowed rights to create a new language on your
PostgreSQL server.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2>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)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2><A
href="http://en.wikipedia.org/wiki/IEEE_floating-point_standard">http://en.wikipedia.org/wiki/IEEE_floating-point_standard</A></FONT> </SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2>Sorry I wasn't more helpful,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=358182913-18102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Lee
Keel<BR><B>Sent:</B> Wednesday, October 17, 2007 3:39 PM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> RE: [postgis-users] ESRI Shapes from
database<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV class=Section1>
<DIV
style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0in; BORDER-TOP: medium none; PADDING-LEFT: 4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: blue 1.5pt solid; PADDING-TOP: 0in; BORDER-BOTTOM: medium none">
<DIV>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">
<HR tabIndex=-1 align=center width="100%" SIZE=2>
</SPAN></FONT></DIV>
<P class=MsoNormal><B><FONT face=Tahoma size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN></FONT></B><FONT
face=Tahoma size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B><SPAN
style="FONT-WEIGHT: bold">On Behalf Of </SPAN></B>Obe, Regina<BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> Wednesday, October 17, 2007 11:16
AM<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> PostGIS Users
Discussion<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B> RE:
[postgis-users] ESRI Shapes from database</SPAN></FONT><o:p></o:p></P></DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Lee,</SPAN></FONT><o:p></o:p></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">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.</SPAN></FONT><o:p></o:p></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">For example these casts
work okay</SPAN></FONT><o:p></o:p></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">SELECT
(encode(E'\\002The'::bytea, 'hex'))::float<BR>SELECT
(encode(substring(E'\\002The'::bytea from 1 for 8),
'hex'))::float</SPAN></FONT><o:p></o:p></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">SELECT
(encode(substring(E'\\002The'::bytea from 2 for 8),
'hex'))::float</SPAN></FONT><o:p></o:p></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">--Now if I try to go
past the size of the bytea observe - this casts to
empty</SPAN></FONT><o:p></o:p></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">SELECT
(encode(substring(E'\\002The'::bytea from 5 for 8),
'hex'))</SPAN></FONT><o:p></o:p></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">And if I then try to
cast that empty to float</SPAN></FONT><o:p></o:p></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">SELECT
(encode(substring(E'\\002The'::bytea from 5 for 8),
'hex'))::float</SPAN></FONT><o:p></o:p></P>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">I get the error
</SPAN></FONT><o:p></o:p></P>
<DIV>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">ERROR: invalid
input syntax for type double precision: ""</SPAN></FONT><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Hope that
helps,</SPAN></FONT><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><st1:City w:st="on"><st1:place w:st="on"><FONT face=Arial
color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Regina</SPAN></FONT></st1:place></st1:City><o:p></o:p></P></DIV>
<DIV>
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p> </o:p></SPAN></FONT></P></DIV>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">
<HR tabIndex=-1 align=center width="100%" SIZE=2>
</SPAN></FONT></DIV>
<P class=MsoNormal><B><I><FONT face=Arial color=navy size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: navy; FONT-STYLE: italic; FONT-FAMILY: Arial">[Lee
Keel] <o:p></o:p></SPAN></FONT></I></B></P>
<P class=MsoNormal><st1:City w:st="on"><st1:place w:st="on"><FONT face=Arial
color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Regina</SPAN></FONT></st1:place></st1:City><FONT
face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">,<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">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. <o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">I am getting a hex
string back from by bytea:<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">SELECT
(encode(substring(esri_shape from cnt for 8), 'hex')) =
'E4A9F1517F0E2B41'<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">So in theory this
<B><SPAN style="FONT-WEIGHT: bold">should</SPAN></B>
work:<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">SELECT
(encode(substring(esri_shape from cnt for 8),
'hex'))::float<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">But instead I get
'invalid syntax for a double precision'. In my C# code, this hex value
('</SPAN></FONT><FONT face="Courier New" color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: 'Courier New'">E4A9F1517F0E2B41'</SPAN></FONT><FONT
face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">) converts to
886591.660046872 using the microsoft bit converter. The bit converter
converts the given hex into the following bit
array:<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 30.75pt"><FONT face="Courier New"
color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: 'Courier New'"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 30.75pt"><FONT face="Courier New"
color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: 'Courier New'">[0]:
228<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: 'Courier New'">
[1]: 169<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: 'Courier New'">
[2]: 241<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: 'Courier New'">
[3]: 81<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: 'Courier New'">
[4]: 127<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: 'Courier New'">
[5]: 14<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: 'Courier New'">
[6]: 43<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face="Courier New" color=blue size=1><SPAN
style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: 'Courier New'">
[7]: 65<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">I would appreciate any
help you can continue to provide on this.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Thanks
again,<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">Lee<o:p></o:p></SPAN></FONT></P></DIV></DIV>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.</BODY></HTML>