<!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=049143415-16102007><FONT face=Arial
color=#0000ff size=2>Sorry if my questions are kind of stupid and
basic</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=049143415-16102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=049143415-16102007><FONT face=Arial
color=#0000ff size=2>So is the data in SQL Server 2000 nothing more than the
stuff in an ESRI shape file but stuffed in a database (and is that an
ESRI shape record per record or like a whole esri shape table in a SQL
Server record). Or is it some other format like WKB.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=049143415-16102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=049143415-16102007><FONT face=Arial
color=#0000ff size=2>Is your 2GB problem for an individual geometry or for a
whole shape table stuffed in a row or something?</FONT></SPAN><SPAN
class=049143415-16102007><FONT face=Arial color=#0000ff
size=2></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=049143415-16102007><FONT face=Arial
color=#0000ff size=2>How did it get into SQL Server in the first
place?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=049143415-16102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=049143415-16102007><FONT face=Arial
color=#0000ff size=2>Thanks,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=049143415-16102007><FONT face=Arial
color=#0000ff size=2>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=049143415-16102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=049143415-16102007><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>