<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=US-ASCII">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2657.73">
<TITLE>ESRI Shapes from database</TITLE>
</HEAD>
<BODY>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">I am trying to get ESRI</FONT> <FONT SIZE=2 FACE="Arial">multipolygon</FONT> <FONT SIZE=2 FACE="Arial">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 SIZE=2 FACE="Arial">but I have the shape data in a Sql Server 2000 database.</FONT><FONT SIZE=2 FACE="Arial"> 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 SIZE=2 FACE="Arial"> 'Out of memory' error on the</FONT><FONT SIZE=2 FACE="Arial"> 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 SIZE=2 FACE="Arial"> (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 SIZE=2 FACE="Arial"> 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 SIZE=2 FACE="Arial">convert parts of a bytea into a float.</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">My function:</FONT></P>
<BR>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">CREATE OR REPLACE FUNCTION esribytea2text(bytea)</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial"> RETURNS text AS</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">$BODY$</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">DECLARE</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">esri_shape alias for $1;</FONT></P>
<P ALIGN=LEFT> </P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">xCoord numeric;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">yCoord numeric;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">num_parts int;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">num_points int ;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">first_pt_idx int ;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">next_pt_idx int := 0 ;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">idx int := 48;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">pt_idx int ;</FONT></P>
<P ALIGN=LEFT> </P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">first_pt varchar;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">sb text ;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">isFirstPt bool := true ;</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">BEGIN</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">num_parts := get_byte(esri_shape::bytea, 36)::int ;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">num_points := get_byte(esri_shape::bytea, 40)::int ;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">first_pt_idx := 44 + (4 * num_parts) ;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">pt_idx := get_byte(esri_shape::bytea, idx)::int ;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">FOR cnt IN first_pt_idx..length(esri_shape)-16 BY 16 LOOP</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">--xCoord := decode(substring(esri_shape from cnt for 8), float);</FONT> <FONT SIZE=2 FACE="Arial">--PROBLEM</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">xCoord := (select encode(esri_shape::bytea,'hex')::float);</FONT> <FONT SIZE=2 FACE="Arial">--PROBLEM</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">yCoord := get_byte(esri_shape, cnt + 8)::float;</FONT><FONT SIZE=2 FACE="Arial"></FONT> <FONT SIZE=2 FACE="Arial">--PROBLEM</FONT></P>
<P ALIGN=LEFT> </P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">insert into tempdata(func, txtdata) values('esribytea2text:x', xCoord);</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">insert into tempdata(func, txtdata) values('esribytea2text:y', yCoord);</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">IF char_length(sb) = 0 THEN</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">sb := sb || '(' || xCoord || ' ' || yCoord;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">ELSE</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">IF isFirstPt THEN</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">sb := sb || ', (' || xCoord || ' ' || yCoord;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">ELSE</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">sb := sb || ', ' || xCoord || ' ' || yCoord;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">END IF;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">END IF;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">IF isFirstPt THEN</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">isFirstPt := false ;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">first_pt := xCoord || ' ' || yCoord; </FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">END IF;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">IF (num_parts > 1 AND pt_idx = next_pt_idx AND idx < first_pt_idx) THEN</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">IF (char_length(sb)>0 AND char_length(first_pt)>0) THEN</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">sb := sb || ', ' + first_pt + ')';</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">isFirstPt := true ;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">END IF;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">idx := idx + 4 ;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">pt_idx := get_byte(esri_shape::bytea, idx)::int ;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">END IF;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">next_pt_idx := next_pt_idx + 1 ;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">END LOOP;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">IF (char_length(sb)>0 AND char_length(first_pt)>0) THEN</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">sb := sb || ', ' || first_pt || ')';</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">isFirstPt := true ;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">END IF;</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">insert into tempdata(func, txtdata) values('esribytea2text', sb);</FONT></P>
<P ALIGN=LEFT> <FONT SIZE=2 FACE="Arial">return 'MULTIPOLYGON((' || sb || '))';</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">END;</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">$BODY$</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial"> LANGUAGE 'plpgsql' VOLATILE;</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">ALTER FUNCTION esribytea2text(bytea) OWNER TO postgres;</FONT></P>
<BR>
<BR>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">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 SIZE=2 FACE="Arial">For:<BR>
</FONT><FONT SIZE=2 FACE="Arial">xCoord := substring(arrByte from cnt for 8)::float8;</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">Error:</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">ERROR: 42846: cannot cast type bytea to double precision</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">For:</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">xCoord :=</FONT> <FONT SIZE=2 FACE="Arial">(</FONT><FONT SIZE=2 FACE="Arial">select encode(esri_shape::bytea,'escape')::float</FONT><FONT SIZE=2 FACE="Arial">)</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">Error:</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">invalid input syntax for type double precision</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">For:</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">yCoord := get_byte(esri_shape, cnt + 8)::float;</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">Error:</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">I</FONT><FONT SIZE=2 FACE="Arial">t only returns the single byte (as I would expect), not</FONT> <FONT SIZE=2 FACE="Arial">all 8 bytes for the float</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">The table '</FONT><FONT SIZE=2 FACE="Arial">tempdata</FONT><FONT SIZE=2 FACE="Arial">' is just a table used for dumping results</FONT><FONT SIZE=2 FACE="Arial"> (Schema Below)</FONT><FONT SIZE=2 FACE="Arial">. The reason that I had to do this was because I was using get_byte and thought it was working, but</FONT> <FONT SIZE=2 FACE="Arial">it really wasn't. </FONT> </P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">CREATE TABLE tempdata</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">(</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial"> id serial NOT NULL,</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial"> func text,</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial"> txtdata text</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">) </FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">WITHOUT OIDS;</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">ALTER TABLE tempdata OWNER TO postgres;</FONT></P>
<BR>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">I am very close to getting this working but have run into this last road block. I would appreciate</FONT> <FONT SIZE=2 FACE="Arial">any help that anyone can provide as I do have a client waiting on a solution.</FONT></P>
<P ALIGN=LEFT><FONT SIZE=2 FACE="Arial">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>