[postgis-users] ESRI Shapes from database

[Lee Keel]

I am trying to get ESRI multipolygon shapes into PostGIS without going to a
shape file first.  I have several reasons that I can't go to shape files
first, but I have the shape data in a Sql Server 2000 database.  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 'Out of
memory' error on the 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).  (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.  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 convert parts of a bytea into a float.

My function:


CREATE OR REPLACE FUNCTION esribytea2text(bytea)
  RETURNS text AS
$BODY$
DECLARE
	esri_shape alias for $1;
	
	xCoord numeric;
	yCoord numeric;

	num_parts int;
	num_points int ;
	first_pt_idx int ;
	next_pt_idx int := 0 ;
	idx int := 48;
	pt_idx int ;
				
	first_pt varchar;
	sb text ;

	isFirstPt bool := true ;
BEGIN
	num_parts := get_byte(esri_shape::bytea, 36)::int ;
	num_points := get_byte(esri_shape::bytea, 40)::int ;
	first_pt_idx := 44 + (4 * num_parts) ;

	pt_idx := get_byte(esri_shape::bytea, idx)::int ;

	FOR cnt IN first_pt_idx..length(esri_shape)-16 BY 16 LOOP
		--xCoord := decode(substring(esri_shape from cnt for 8),
float);		--PROBLEM
		xCoord := (select encode(esri_shape::bytea,'hex')::float);
--PROBLEM
		yCoord := get_byte(esri_shape, cnt + 8)::float;
--PROBLEM
		
		insert into tempdata(func, txtdata)
values('esribytea2text:x', xCoord);
		insert into tempdata(func, txtdata)
values('esribytea2text:y', yCoord);

		IF char_length(sb) = 0 THEN
			sb := sb || '(' || xCoord || ' ' || yCoord;
		ELSE
			IF isFirstPt THEN
				sb := sb || ', (' || xCoord || ' ' ||
yCoord;
			ELSE
				sb := sb || ', ' || xCoord || ' ' || yCoord;
			END IF;
		END IF;

		IF isFirstPt THEN
			isFirstPt := false ;
			first_pt :=  xCoord || ' ' || yCoord; 
		END IF;

		IF (num_parts > 1 AND pt_idx = next_pt_idx AND idx <
first_pt_idx) THEN
			IF (char_length(sb)>0 AND char_length(first_pt)>0)
THEN
				sb := sb || ', ' + first_pt + ')';
				isFirstPt := true ;
			END IF;

			idx := idx + 4 ;
			pt_idx := get_byte(esri_shape::bytea, idx)::int ;
		END IF;

		next_pt_idx := next_pt_idx + 1 ;
	END LOOP;

	IF (char_length(sb)>0 AND char_length(first_pt)>0) THEN
		sb := sb || ', ' || first_pt || ')';
		isFirstPt := true ;
	END IF;

	insert into tempdata(func, txtdata) values('esribytea2text', sb);

	return 'MULTIPOLYGON((' || sb || '))';
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION esribytea2text(bytea) OWNER TO postgres;



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:

For:
xCoord := substring(arrByte from cnt for 8)::float8;
Error:
ERROR: 42846: cannot cast type bytea to double precision

For:
xCoord := (select encode(esri_shape::bytea,'escape')::float)
Error:
invalid input syntax for type double precision

For:
yCoord := get_byte(esri_shape, cnt + 8)::float;
Error:
It only returns the single byte (as I would expect), not all 8 bytes for the
float

The table 'tempdata' is just a table used for dumping results (Schema
Below).  The reason that I had to do this was because I was using get_byte
and thought it was working, but it really wasn't.  

CREATE TABLE tempdata
(
  id serial NOT NULL,
  func text,
  txtdata text
) 
WITHOUT OIDS;
ALTER TABLE tempdata OWNER TO postgres;


I am very close to getting this working but have run into this last road
block.  I would appreciate any help that anyone can provide as I do have a
client waiting on a solution.

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071016/a5e1e97a/attachment.html>