[postgis-users] ESRI Shapes from database
Obe, Regina
robe.dnd at cityofboston.gov
Wed Oct 17 05:41:42 PDT 2007
Lee,
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.)
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.
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 sb := sb || '(' ||
xCoord || ' ' || yCoord;
anyway.
Hope that helps,
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/20071017/19d78952/attachment.html>
More information about the postgis-users
mailing list