<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><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></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=789372212-17102007><FONT face=Arial
color=#0000ff size=2>Lee,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=789372212-17102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=789372212-17102007><FONT face=Arial
color=#0000ff size=2>Just wondering what your example bytea looks like that you
are trying to convert that is giving you problems. Normally I would think
what you are doing below should work (and I have tried some simple examples and
they work - but I don't work with bytea so what do I know.)
</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=789372212-17102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=789372212-17102007><FONT face=Arial
color=#0000ff size=2>So perhaps whatever your bytea is returning is out of range
of float and is not actually what you think it is like you have
a terminator byte in there or something causing the
problems.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=789372212-17102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT size=2><FONT color=#0000ff><SPAN
class=789372212-17102007>Other thought is if you could get the string
representation of your xcoord without casting to float, since in the next
line your float is going to be internally recast as a string to do
this <FONT color=#000000><FONT face="Times New Roman" size=3> </FONT><FONT
face=Arial size=2>sb := sb || '(' || xCoord || ' ' || yCoord;
</FONT></FONT></SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT size=2><FONT color=#0000ff><SPAN
class=789372212-17102007><FONT color=#000000><FONT face=Arial
size=2>anyway.</FONT></FONT></SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT size=2><FONT color=#000000><SPAN
class=789372212-17102007></SPAN></FONT></FONT></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT size=2><FONT color=#000000><SPAN
class=789372212-17102007>Hope that helps,</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT size=2><FONT color=#000000><SPAN
class=789372212-17102007>Regina</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><SPAN class=789372212-17102007><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> Tuesday, October 16, 2007 10:49 AM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] ESRI
Shapes from database<BR></FONT><BR></DIV>
<DIV></DIV>
<P align=left><FONT face=Arial size=2>I am trying to get ESRI</FONT> <FONT
face=Arial size=2>multipolygon</FONT> <FONT face=Arial size=2>shapes into
PostGIS without going to a shape file first. I have several reasons that I
can't go to shape files first,</FONT> <FONT face=Arial size=2>but I have the
shape data in a Sql Server 2000 database.</FONT><FONT face=Arial size=2> I
was parsing the ESRI shape in C# code and then pushing the string to the
database. The problem that I am having doing this is that I get
an</FONT><FONT face=Arial size=2> 'Out of memory' error on the</FONT><FONT
face=Arial size=2> string for the text of some of my shapes. I have traced
that down to a limit by MS on the size of a string and the size of stringbuilder
(2GB).</FONT><FONT face=Arial size=2> (FYI my machine has 8GB, so it is
not my machine that was running out of memory.) So, my next thought was a
stored procedure where I just passed the binary in and parsed it on the
server.</FONT><FONT face=Arial size=2> I have listed the stored procedure
that I am using to convert the ESRI shape into a WKT so that I can then convert
to WKB. My problem is that I can find a way to</FONT> <FONT face=Arial
size=2>convert parts of a bytea into a float.</FONT></P>
<P align=left><FONT face=Arial size=2>My function:</FONT></P><BR>
<P align=left><FONT face=Arial size=2>CREATE OR REPLACE FUNCTION
esribytea2text(bytea)</FONT></P>
<P align=left><FONT face=Arial size=2> RETURNS text AS</FONT></P>
<P align=left><FONT face=Arial size=2>$BODY$</FONT></P>
<P align=left><FONT face=Arial size=2>DECLARE</FONT></P>
<P align=left> <FONT face=Arial
size=2>esri_shape alias for $1;</FONT></P>
<P align=left> </P>
<P align=left> <FONT face=Arial
size=2>xCoord numeric;</FONT></P>
<P align=left> <FONT face=Arial
size=2>yCoord numeric;</FONT></P>
<P align=left> <FONT face=Arial
size=2>num_parts int;</FONT></P>
<P align=left> <FONT face=Arial
size=2>num_points int ;</FONT></P>
<P align=left> <FONT face=Arial
size=2>first_pt_idx int ;</FONT></P>
<P align=left> <FONT face=Arial
size=2>next_pt_idx int := 0 ;</FONT></P>
<P align=left> <FONT face=Arial
size=2>idx int := 48;</FONT></P>
<P align=left> <FONT face=Arial
size=2>pt_idx int ;</FONT></P>
<P align=left>
</P>
<P align=left> <FONT face=Arial
size=2>first_pt varchar;</FONT></P>
<P align=left> <FONT face=Arial
size=2>sb text ;</FONT></P>
<P align=left> <FONT face=Arial
size=2>isFirstPt bool := true ;</FONT></P>
<P align=left><FONT face=Arial size=2>BEGIN</FONT></P>
<P align=left> <FONT face=Arial
size=2>num_parts := get_byte(esri_shape::bytea, 36)::int ;</FONT></P>
<P align=left> <FONT face=Arial
size=2>num_points := get_byte(esri_shape::bytea, 40)::int ;</FONT></P>
<P align=left> <FONT face=Arial
size=2>first_pt_idx := 44 + (4 * num_parts) ;</FONT></P>
<P align=left> <FONT face=Arial
size=2>pt_idx := get_byte(esri_shape::bytea, idx)::int ;</FONT></P>
<P align=left> <FONT face=Arial
size=2>FOR cnt IN first_pt_idx..length(esri_shape)-16 BY 16 LOOP</FONT></P>
<P align=left>
<FONT face=Arial size=2>--xCoord :=
decode(substring(esri_shape from cnt for 8),
float);</FONT>
<FONT face=Arial
size=2>--PROBLEM</FONT></P>
<P align=left>
<FONT face=Arial size=2>xCoord :=
(select
encode(esri_shape::bytea,'hex')::float);</FONT>
<FONT face=Arial
size=2>--PROBLEM</FONT></P>
<P align=left>
<FONT face=Arial size=2>yCoord :=
get_byte(esri_shape, cnt + 8)::float;</FONT><FONT face=Arial size=2></FONT>
<FONT face=Arial
size=2>--PROBLEM</FONT></P>
<P align=left>
</P>
<P align=left>
<FONT face=Arial size=2>insert into
tempdata(func, txtdata) values('esribytea2text:x', xCoord);</FONT></P>
<P align=left>
<FONT face=Arial size=2>insert into
tempdata(func, txtdata) values('esribytea2text:y', yCoord);</FONT></P>
<P align=left>
<FONT face=Arial size=2>IF
char_length(sb) = 0 THEN</FONT></P>
<P align=left>
<FONT face=Arial size=2>sb := sb ||
'(' || xCoord || ' ' || yCoord;</FONT></P>
<P align=left>
<FONT face=Arial
size=2>ELSE</FONT></P>
<P align=left>
<FONT face=Arial size=2>IF isFirstPt
THEN</FONT></P>
<P align=left>
<FONT face=Arial size=2>sb := sb ||
', (' || xCoord || ' ' || yCoord;</FONT></P>
<P align=left>
<FONT face=Arial
size=2>ELSE</FONT></P>
<P align=left>
<FONT face=Arial size=2>sb := sb ||
', ' || xCoord || ' ' || yCoord;</FONT></P>
<P align=left>
<FONT face=Arial size=2>END
IF;</FONT></P>
<P align=left>
<FONT face=Arial size=2>END
IF;</FONT></P>
<P align=left>
<FONT face=Arial size=2>IF isFirstPt
THEN</FONT></P>
<P align=left>
<FONT face=Arial size=2>isFirstPt :=
false ;</FONT></P>
<P align=left>
<FONT face=Arial size=2>first_pt
:= xCoord || ' ' || yCoord; </FONT></P>
<P align=left>
<FONT face=Arial size=2>END
IF;</FONT></P>
<P align=left>
<FONT face=Arial size=2>IF (num_parts
> 1 AND pt_idx = next_pt_idx AND idx < first_pt_idx) THEN</FONT></P>
<P align=left>
<FONT face=Arial size=2>IF
(char_length(sb)>0 AND char_length(first_pt)>0) THEN</FONT></P>
<P align=left>
<FONT face=Arial size=2>sb := sb ||
', ' + first_pt + ')';</FONT></P>
<P align=left>
<FONT face=Arial size=2>isFirstPt :=
true ;</FONT></P>
<P align=left>
<FONT face=Arial size=2>END
IF;</FONT></P>
<P align=left>
<FONT face=Arial size=2>idx := idx +
4 ;</FONT></P>
<P align=left>
<FONT face=Arial size=2>pt_idx :=
get_byte(esri_shape::bytea, idx)::int ;</FONT></P>
<P align=left>
<FONT face=Arial size=2>END
IF;</FONT></P>
<P align=left>
<FONT face=Arial size=2>next_pt_idx
:= next_pt_idx + 1 ;</FONT></P>
<P align=left> <FONT face=Arial
size=2>END LOOP;</FONT></P>
<P align=left> <FONT face=Arial
size=2>IF (char_length(sb)>0 AND char_length(first_pt)>0) THEN</FONT></P>
<P align=left>
<FONT face=Arial size=2>sb := sb ||
', ' || first_pt || ')';</FONT></P>
<P align=left>
<FONT face=Arial size=2>isFirstPt :=
true ;</FONT></P>
<P align=left> <FONT face=Arial
size=2>END IF;</FONT></P>
<P align=left> <FONT face=Arial
size=2>insert into tempdata(func, txtdata) values('esribytea2text',
sb);</FONT></P>
<P align=left> <FONT face=Arial
size=2>return 'MULTIPOLYGON((' || sb || '))';</FONT></P>
<P align=left><FONT face=Arial size=2>END;</FONT></P>
<P align=left><FONT face=Arial size=2>$BODY$</FONT></P>
<P align=left><FONT face=Arial size=2> LANGUAGE 'plpgsql'
VOLATILE;</FONT></P>
<P align=left><FONT face=Arial size=2>ALTER FUNCTION esribytea2text(bytea) OWNER
TO postgres;</FONT></P><BR><BR>
<P align=left><FONT face=Arial size=2>The lines that are marked with '--PROBLEM'
are where my problem exists. What I am trying to do here is take 8 bytes
from the bytea and cast them to a float. The errors that I am getting
are:</FONT></P>
<P align=left><FONT face=Arial size=2>For:<BR></FONT><FONT face=Arial
size=2>xCoord := substring(arrByte from cnt for 8)::float8;</FONT></P>
<P align=left><FONT face=Arial size=2>Error:</FONT></P>
<P align=left><FONT face=Arial size=2>ERROR: 42846: cannot cast type bytea to
double precision</FONT></P>
<P align=left><FONT face=Arial size=2>For:</FONT></P>
<P align=left><FONT face=Arial size=2>xCoord :=</FONT> <FONT face=Arial
size=2>(</FONT><FONT face=Arial size=2>select
encode(esri_shape::bytea,'escape')::float</FONT><FONT face=Arial
size=2>)</FONT></P>
<P align=left><FONT face=Arial size=2>Error:</FONT></P>
<P align=left><FONT face=Arial size=2>invalid input syntax for type double
precision</FONT></P>
<P align=left><FONT face=Arial size=2>For:</FONT></P>
<P align=left><FONT face=Arial size=2>yCoord := get_byte(esri_shape, cnt +
8)::float;</FONT></P>
<P align=left><FONT face=Arial size=2>Error:</FONT></P>
<P align=left><FONT face=Arial size=2>I</FONT><FONT face=Arial size=2>t only
returns the single byte (as I would expect), not</FONT> <FONT face=Arial
size=2>all 8 bytes for the float</FONT></P>
<P align=left><FONT face=Arial size=2>The table '</FONT><FONT face=Arial
size=2>tempdata</FONT><FONT face=Arial size=2>' is just a table used for dumping
results</FONT><FONT face=Arial size=2> (Schema Below)</FONT><FONT face=Arial
size=2>. The reason that I had to do this was because I was using get_byte
and thought it was working, but</FONT> <FONT face=Arial size=2>it really
wasn't. </FONT> </P>
<P align=left><FONT face=Arial size=2>CREATE TABLE tempdata</FONT></P>
<P align=left><FONT face=Arial size=2>(</FONT></P>
<P align=left><FONT face=Arial size=2> id serial NOT NULL,</FONT></P>
<P align=left><FONT face=Arial size=2> func text,</FONT></P>
<P align=left><FONT face=Arial size=2> txtdata text</FONT></P>
<P align=left><FONT face=Arial size=2>) </FONT></P>
<P align=left><FONT face=Arial size=2>WITHOUT OIDS;</FONT></P>
<P align=left><FONT face=Arial size=2>ALTER TABLE tempdata OWNER TO
postgres;</FONT></P><BR>
<P align=left><FONT face=Arial size=2>I am very close to getting this working
but have run into this last road block. I would appreciate</FONT> <FONT
face=Arial size=2>any help that anyone can provide as I do have a client waiting
on a solution.</FONT></P>
<P align=left><FONT face=Arial size=2>Thanks in advance,<BR>Lee
Keel</FONT></P><BR><BR>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>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
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.
</STRONG></P></BODY></HTML>