<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>3DSite</TITLE>
<META content="MSHTML 6.00.6000.16481" name=GENERATOR></HEAD>
<BODY text=#000000 bgColor=#ffffff>
<DIV dir=ltr align=left><SPAN class=514445516-21072007>Thank you for the
response Milo.</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=514445516-21072007></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=514445516-21072007>We store each shape as a
blob in the database (image datatype). You might already know this but this is
the format that the blob is stored in <A
href="http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf">http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf</A>.</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=514445516-21072007></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=514445516-21072007>I tried to read these
bytes and build a WKT, this works great for smaller polygons, lines and points,
but I have some huge multipolygons (parcels) which take for ever to finish the
process. My application is command line utility and I am using C#.</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=514445516-21072007></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=514445516-21072007>This is what I am trying
to do now.</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=514445516-21072007><FONT color=#ff0000
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=514445516-21072007><FONT color=#ff0000
size=2>string </FONT><FONT size=2>sql </FONT><FONT color=#006400 size=2>=
</FONT><FONT color=#0000ff size=2>"select
geometry(:bytesData::bytea)"</FONT><FONT color=#006400 size=2>;</DIV>
<DIV dir=ltr align=left>
<P></FONT><B><FONT color=#008080 size=2>try</B></FONT><FONT color=#006400
size=2>{</P>
<P></FONT><B><FONT color=#008000 size=2>using</B></FONT><FONT color=#006400
size=2>(</FONT><FONT color=#000000 size=2>NpgsqlConnection cn </FONT><FONT
color=#006400 size=2>= </FONT><B><FONT color=#008b8b size=2>new </FONT><FONT
color=#191970 size=2>NpgsqlConnection</B></FONT><FONT color=#006400
size=2>(</FONT><FONT color=#000000 size=2>_dst_cn</FONT><FONT color=#006400
size=2>)){</P></FONT><FONT color=#008000 size=2>
<P></FONT><FONT color=#000000 size=2>cn</FONT><FONT color=#006400
size=2>.</FONT><B><FONT color=#191970 size=2>Open</B></FONT><FONT color=#006400
size=2>();</P>
<P></FONT><FONT color=#000000 size=2>NpgsqlCommand cmd </FONT><FONT
color=#006400 size=2>= </FONT><B><FONT color=#008b8b size=2>new </FONT><FONT
color=#191970 size=2>NpgsqlCommand</B></FONT><FONT color=#006400
size=2>(</FONT><FONT color=#000000 size=2>sql</FONT><FONT color=#006400 size=2>,
</FONT><FONT color=#000000 size=2>cn</FONT><FONT color=#006400 size=2>);</P>
<P></FONT><FONT color=#000000 size=2>NpgsqlParameter param </FONT><FONT
color=#006400 size=2>= </FONT><B><FONT color=#008b8b size=2>new </FONT><FONT
color=#191970 size=2>NpgsqlParameter</B></FONT><FONT color=#006400
size=2>(</FONT><FONT color=#0000ff size=2>":bytesData"</FONT><FONT color=#006400
size=2>, </FONT><FONT color=#000000 size=2>DbType</FONT><FONT color=#006400
size=2>.</FONT><FONT color=#000000 size=2>Binary</FONT><FONT color=#006400
size=2>);</P>
<P></FONT><FONT color=#000000 size=2>param</FONT><FONT color=#006400
size=2>.</FONT><FONT color=#000000 size=2>Value </FONT><FONT color=#006400
size=2>= (</FONT><FONT color=#000000 size=2>Byte</FONT><FONT color=#006400
size=2>[])</FONT><FONT color=#000000 size=2>src_dr</FONT><FONT color=#006400
size=2>[</FONT><FONT color=#000000 size=2>column</FONT><FONT color=#006400
size=2>];</P>
<P></FONT><FONT color=#000000 size=2>cmd</FONT><FONT color=#006400
size=2>.</FONT><FONT color=#000000 size=2>Parameters</FONT><FONT color=#006400
size=2>.</FONT><B><FONT color=#191970 size=2>Add</B></FONT><FONT color=#006400
size=2>(</FONT><FONT color=#000000 size=2>param</FONT><FONT color=#006400
size=2>);</P></FONT><FONT color=#006400 size=2>
<P></FONT><FONT color=#000000 size=2>dst_dr</FONT><FONT color=#006400
size=2>[</FONT><FONT color=#0000ff size=2>"the_geom"</FONT><FONT color=#006400
size=2>] = </FONT><FONT color=#000000 size=2>cmd</FONT><FONT color=#006400
size=2>.</FONT><B><FONT color=#191970 size=2>ExecuteScalar</B></FONT><FONT
color=#006400 size=2>();</FONT></P>
<P><FONT color=#006400 size=2>}</FONT></P>
<P><SPAN class=514445516-21072007><FONT color=#006400 size=2>My source database
is MSSQLServer 2000 and my destination database is Postgres. I am not able to
use the Sharpmap or Ogr libraries as they take a shape file.</FONT></SPAN></P>
<P><SPAN class=514445516-21072007>Am I on the right track? Please
advice.</SPAN></P></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=514445516-21072007>Thank you,</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=514445516-21072007></SPAN> </DIV>
<DIV align=left><FONT color=#0000ff size=2>Murali K. Maddali</FONT></DIV>
<DIV align=left><FONT color=#0000ff size=2>256-705-5191</FONT></DIV>
<DIV align=left>
<DIV align=left><FONT color=#0000ff size=2><A
href="mailto:murali.maddali@uai.com">murali.maddali@uai.com</A></FONT></DIV></DIV>
<DIV> </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>Milo
van der Linden<BR><B>Sent:</B> Saturday, July 21, 2007 8:46 AM<BR><B>To:</B>
PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users]
geometry(bytea)<BR></FONT><BR></DIV>
<DIV></DIV>Hello Murali!<BR><BR>I am doing exactly the same at this moment! In
our case, the shape files are zip files in the MS SQL database (do these blobs
come from MapObjects? then you are probably in the same
situation.)<BR><BR><BIG><BIG><FONT color=#ff0000><I><B>The Byte is probably a
ZIP, not a shape..</B></I></FONT></BIG></BIG><BR><BR><BR>What we do:<BR>-
Extract the blobs with a php-script -> store them as <B>ZIP</B><BR>-
<B>Unzip</B> with the php_zip_lib (or something like that, I am not behind the
development machine right now) into a <I>temp directory</I><BR>- start a shell
from the php-script that activates ogr2ogr -> ESRI shape to PostGIS for the
shape file in the temp directory into a temporary table in PostGIS<BR>- Copy the
temp table content in PostGIS to a production table with an INSERT
statement<BR>- loop this sequence<BR><BR>Our proces is 80% under control, we are
now generating the insert into production (with a little help here on the
PostGIS forums, thanks again Kevin)<BR><BR><BR>Let me know if you want to know
more. ....<BR><BR>Kind regards,<BR>Milo van der Linden<BR><A
class=moz-txt-link-freetext
href="http://www.3dsite.nl">http://www.3dsite.nl</A><BR><BR><BR>Murali Maddali
schreef:
<BLOCKQUOTE cite=mid:76758090F8686C47A44B6FF52514A1D308C9C544@hermes.uai.int
type="cite"><PRE wrap="">This is a ESRI shape file record, untranslated. Can you suggest me how to
translate this.
I am reading the blob from SQL Server 2000 using C# which return a Byte[]
and convert this Byte[] to bytea and pass this to Npgsql command object as a
parameter.
Thank you,
Murali K. Maddali
256-705-5191
<A class=moz-txt-link-abbreviated href="mailto:murali.maddali@uai.com">murali.maddali@uai.com</A>
-----Original Message-----
From: <A class=moz-txt-link-abbreviated href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</A>
[<A class=moz-txt-link-freetext href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of Paul
Ramsey
Sent: Friday, July 20, 2007 6:10 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] geometry(bytea)
What is the format of your bytea? If it's the ESRI shape file record,
untranslated, you're out of luck. If it's a WKB byte array, then use
GeomFromWKB.
P
On 20-Jul-07, at 8:17 AM, Murali Maddali wrote:
</PRE>
<BLOCKQUOTE type="cite"><PRE wrap="">Hello All,
Can any one tell what I am doing wrong with the following select.
select geometry('\\005\\000\\000\\000\\010\\247\\033\\113\\056\\263\
\053\\101\\213\\236\\223\\165\\164\\062\\046\\101\\006\\377\\131\
\025\\172\\266\\053\\101\\350\\300\\323\\152\\241\\067\\046\\101\
\001\\000\\000\\000\\006\\000\\000\\000\\000\\000\\000\\000\\010\
\247\\033\\113\\056\\263\\053\\101\\376\\053\\330\\300\\234\\067\
\046\\101\\205\\364\\147\\256\\134\\266\\053\\101\\350\\300\\323\
\152\\241\\067\\046\\101\\006\\377\\131\\025\\172\\266\\053\\101\
\230\\025\\330\\025\\171\\062\\046\\101\\034\\227\\351\\330\\331\
\263\\053\\101\\366\\105\\154\\104\\165\\062\\046\\101\\136\\376\
\167\\260\\113\\263\\053\\101\\213\\236\\223\\165\\164\\062\\046\
\101\\010\\247\\033\\113\\056\\263\\053\\101\\376\\053\\330\\300\
\234\\067\\046\\101\\000'::bytea);
I am trying to convert a ESRI shape which is stored as a blob in SQL
Server 2000 to postgis. I am using Npgsql to connect to postgis. This
is the out I got from Npgsql. I know this is a valid shape and when I
imported the data using shp2pgsql from the shape file it imported
fine.
Thank you,
Regards,
Murali K. Maddali
UAI, Inc.
(256) 705-5191 (W)
<A class=moz-txt-link-abbreviated href="mailto:murali.maddali@uai.com">murali.maddali@uai.com</A>
"Always bear in mind that your own resolution to succeed is more
important than any one thing." - Abraham Lincoln
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.
_______________________________________________
postgis-users mailing list
<A class=moz-txt-link-abbreviated href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>
<A class=moz-txt-link-freetext href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A>
</PRE></BLOCKQUOTE><PRE wrap=""><!---->
_______________________________________________
postgis-users mailing list
<A class=moz-txt-link-abbreviated href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>
<A class=moz-txt-link-freetext href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A>
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.
_______________________________________________
postgis-users mailing list
<A class=moz-txt-link-abbreviated href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>
<A class=moz-txt-link-freetext href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A>
</PRE></BLOCKQUOTE><BR><BR>
<DIV class=moz-signature>-- <BR>
<STYLE type=text/css>BODY {
MARGIN: 5px; BACKGROUND-COLOR: #ffffff
}
BODY {
FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial, Helvetica, sans-serif
}
TD {
FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial, Helvetica, sans-serif
}
TH {
FONT-SIZE: 12px; COLOR: #333333; FONT-FAMILY: Arial, Helvetica, sans-serif
}
..style4 {
FONT-SIZE: 9px
}
..style5 {
FONT-SIZE: 9px; COLOR: #cccccc
}
</STYLE>
<META content="MSHTML 6.00.2900.2912" name=GENERATOR><BR>
<TABLE cellSpacing=0 cellPadding=0 width=400 border=0>
<TBODY>
<TR>
<TD vAlign=bottom align=left width=15 height=0 rowSpan=3><BR></TD>
<TD vAlign=top align=left width=0 colSpan=2 height=78>
<P class=style4>Milo van der Linden <BR><A
href="mailto:mlinden@zeelandnet.nl">mlinden@zeelandnet.nl</A><BR><A
href="mailto:milovanderlinden@gmail.com">milovanderlinden@gmail.com</A><BR><A
href="mailto:milo@3dsite.nl">milo@3dsite.nl</A><BR><A
href="http://www.3dsite.nl">http://www.3dsite.nl</A><BR></P></TD>
<TD vAlign=top align=left width=15 height=0 rowSpan=3> </TD>
<TD vAlign=top width=300>
<P class=style5><SPAN lang=NL>De informatie in dit bericht reflecteerd
mijn persoonlijke mening en niet die van een bedrijf of instantie. Aan de
informatie kunnen geen rechten worden ontleend. Indien dit bericht
onderdeel is van een forum, mailing-list of community dan gelden
automatisch de bijbehorende
voorwaarden.</SPAN></P></TD></TR></TBODY></TABLE></DIV>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>