[postgis-users] geometry(bytea)

Sat Jul 21 10:09:37 PDT 2007

Thank you for the response Milo.

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
http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf
<http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf> .

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#.

This is what I am trying to do now.

string sql = "select geometry(:bytesData::bytea)";

try{

using(NpgsqlConnection cn = new NpgsqlConnection(_dst_cn)){

cn.Open();

NpgsqlCommand cmd = new NpgsqlCommand(sql, cn);

NpgsqlParameter param = new NpgsqlParameter(":bytesData", DbType.Binary);

param.Value = (Byte[])src_dr[column];

cmd.Parameters.Add(param);

dst_dr["the_geom"] = cmd.ExecuteScalar();

}

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.

Am I on the right track? Please advice.

Thank you,

Murali K. Maddali
256-705-5191
murali.maddali at uai.com <mailto:murali.maddali at uai.com> 

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Milo van
der Linden
Sent: Saturday, July 21, 2007 8:46 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] geometry(bytea)

Hello Murali!

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.)

The Byte is probably a ZIP, not a shape..

What we do:
- Extract the blobs with a php-script -> store them as ZIP
- Unzip with the php_zip_lib (or something like that, I am not behind the
development machine right now) into a temp directory
- 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
- Copy the temp table content in PostGIS to a production table with an
INSERT statement
- loop this sequence

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)

Let me know if you want to know more. ....

Kind regards,
Milo van der Linden
http://www.3dsite.nl <http://www.3dsite.nl> 

Murali Maddali schreef: 

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

murali.maddali at uai.com <mailto:murali.maddali at uai.com> 

-----Original Message-----

From: postgis-users-bounces at postgis.refractions.net
<mailto:postgis-users-bounces at postgis.refractions.net> 

[mailto:postgis-users-bounces at postgis.refractions.net
<mailto:postgis-users-bounces at postgis.refractions.net> ] 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:

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)

murali.maddali at uai.com <mailto:murali.maddali at uai.com> 

"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

postgis-users at postgis.refractions.net
<mailto:postgis-users at postgis.refractions.net> 

http://postgis.refractions.net/mailman/listinfo/postgis-users
<http://postgis.refractions.net/mailman/listinfo/postgis-users> 

_______________________________________________

postgis-users mailing list

postgis-users at postgis.refractions.net
<mailto:postgis-users at postgis.refractions.net> 

http://postgis.refractions.net/mailman/listinfo/postgis-users
<http://postgis.refractions.net/mailman/listinfo/postgis-users> 

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

postgis-users at postgis.refractions.net
<mailto:postgis-users at postgis.refractions.net> 

http://postgis.refractions.net/mailman/listinfo/postgis-users
<http://postgis.refractions.net/mailman/listinfo/postgis-users> 

-- 

Milo van der Linden 
mlinden at zeelandnet.nl <mailto:mlinden at zeelandnet.nl> 
milovanderlinden at gmail.com <mailto:milovanderlinden at gmail.com> 
milo at 3dsite.nl <mailto:milo at 3dsite.nl> 
http://www.3dsite.nl <http://www.3dsite.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.

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/20070721/eda0f657/attachment.html>