[postgis-users] ESRI Shapes from database

[Obe, Regina]

Sorry if  my questions are kind of stupid and basic
 
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.
 
Is your 2GB problem for an individual geometry or for a whole shape
table stuffed in a row or something?
How did it get into SQL Server in the first place?
 
Thanks,
Regina
 
 

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Lee
Keel
Sent: Tuesday, October 16, 2007 10:49 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] ESRI Shapes from database



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. 



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