[postgis-users] ESRI Shapes from database

Lee Keel lee.keel at uai.com
Tue Oct 16 13:55:09 PDT 2007


> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Chris Hermansen
> Sent: Tuesday, October 16, 2007 3:13 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] ESRI Shapes from database
>  
> So in C#, you tried building a great big string containing a set of SQL
> statements, which you were going to hand off to some kind of C# /
> PostgreSQL interface.  The string got too big and it broke.
> 
> What is the nature of that C# interface?  Is it ODBC-like?  Or were you
> just going to heave the string at psql embedded in some kind of "system"
> call?  Pardon my ignorance of C# - PostgreSQL interfaces...
> 
> If the C# interface is ODBC like, can you not issue a bunch of method
> calls, one for the CREATE TABLE, one for each INSERT INTO, one for the
> index, etc.  Then your strings should be nice and short, and you can
> conceal - to some degree at least - the user name and password within
> the compiled code?
> 
> If that's not the case, what about writing the SQL out to a temporary
> text file, or set of text files if length is an issue, and then either
> using the "system" facility to call psql or writing a small Python
> program that uses say psychopg to fire the data off into PostgreSQL?
> You can obscure the user name and password by compiling the Python.
> 
> Or you could write the whole thing in Python, which you should be able
> to use to pull data out of the geodatabase as well as put data into
> PostgreSQL.  Or maybe Java, JDBC allowing you the same ability to hide
> user names and passwords within the compiled code.
> 
[Lee Keel] 

The process that I am using now is select * from table using an sqlserver
driver on sql server side and select * from table using npgsql driver on
postgres side.  Yes, these drivers are ODBC-like.  I then go through and
update all columns on npgsql table with values from sql server.  When I get
to the geometry column, I parse that binary as a byte array and determine
the shape type, number of parts, points, etc.  The problem comes in when
trying to get a string together with all the points in it for some of my
multipart polygons.  The Microsoft bug that I have found is that I am using
a stringbuilder to concatenate these points together (this is a microsoft
object that does all the concatenation work for you and is suppose to be the
most efficient in .NET) and there is a limitation to the size this object
can get.  The problem is that garbage collection doesn't occur so it tries
to allocate double memory next time it comes into the function.  I have
added a garbage collection call and seeing if that will get me around that
problem, but in either case the driver to push the data into the system is
just not performing as well as I had hoped, so that is why I went to the
postgres function and tried to do the math in the function for diffing the
shapes.  Which I could do everything except the one bytea to float method.
Which I have looked at the C code from shp2pgsql and it is doing a memcpy to
push the 8bytes from the byte array to the float.  I would be willing to do
this part in C or Perl or Anything else if I just knew how to get these
functions added so a pgsql function could call it.

Hope that all makes sense...
LK
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.



More information about the postgis-users mailing list