[postgis-users] RE: insert interactively asc or text files

Wed Jul 11 06:55:13 PDT 2007

Here is the php implementation I promised.  Hopefully this will be
useful to you.

the config.inc.php - contains db connection, temp path location info so
change accordingly for your system
On line 14 of the phpupload.php file you'll see a line with a table name
- change that to the table you want to insert into.  Also I assumed your
table has a field called batch_id which will denote the user's
particular upload batch (varchar around 100 characters should do) and
I'm using phps built in session id to get a batch code.

I have debugging turned on on line 26 of the phpupload.php so I could
see the generated insert statements of the PHP ADODB driver; in
production of course you'll want to get rid of that.

I left out all error handling since I thought it would become really
confusing the way I do it to follow the code and thought it would be
better to be succinct than to account for all contingencies. Besides
everyone has their own preference on how they handle errors.

Also forgot to mention since I'm piggy backing on PHP ADODB, you'll need
to download that from here
http://sourceforge.net/project/showfiles.php?group_id=42718

Hope that helps,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
Regina
Sent: Wednesday, July 11, 2007 8:24 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] RE: insert interactively asc or text files

Actually for php, there is a built in function for handling csv files
that I have used called fgetcsv.
Then I use PHP ADODB http://adodb.sourceforge.net/ database abstraction
layer for setting up my database connection to postgres and loading in
the data.  As an aside, I really love PHP ADODB because of the ease with
which I can swap out one DBMS for another.  I know PEAR DB does the same
thing, but ADODB seemed a little cleaner of an implementation to me and
also coming from an ASP background a bit easier to grasp than PEAR DB.

I'll try to get together a basic package that demonstrates prompting for
the file, uploading the file, and then inserting to db.

Thanks,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of pere
roca
Sent: Wednesday, July 11, 2007 6:21 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] RE: insert interactively asc or text files

    Hi Regina, 
  sorry for the delay, I'm new in Perl and had to setup and understand a
little the code of Chetan. I must say that it works fine! Thanks again,
Chetan.
  But I would like to evaluate other possibilities, because I'm more
familiar to php  and maybe I can construct easily new functionalities.
Could
you send me the code?

  Sorry, but I don't understand the benefits related where your
webserver
resides that you told me in the previous message...

  I've seen an interesting project in PHP
http://gforge2.uwc.ac.za/projects/postgis/ but dumps shapefiles, not
text or
.csv files. 

  Cheers,
  Pere

Obe, Regina     DND\MIS wrote:
> 
> What language are you web programming in?  I've done this kind of
stuff
> in both PHP and ASP.NET and for those cases,  I just have a routine
that
> uploads the file into some temp folder on the website and then do
> inserts into the Pg database from the temp file.
> 
> The benefit of this approach over COPY is the following
>  if your webserver does not have network file access to where your
> Postgresql resides, then this will work where as COPY really only
works
> if you can COPY the file to your PostgreSQL server, have network
access
> across or your PostgreSQL server is on the same box as your webserver.
> 
> The other benefit is that it gives you much more granular control than
> you get with COPY for error handling and such.
> 
> The downside is that it is slower than the COPY method, but for files
> smaller than say 1MB its probably adequately fast.
> 
> Both PHP and ASP.Net have utilities for reading in csv and dbf files
> among others.  If you are interested I can provide you some code that
> does it in both languages.  I just have to cut out all the fluff and
> proprietary stuff.
> 
> Thanks,
> Regina
> 
>    
> 
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
pere
> roca
> Sent: Thursday, July 05, 2007 4:00 AM
> To: postgis-users at postgis.refractions.net
> Subject: Re: [postgis-users] insert interactively asc or text files
> 
> 
> 
>   hi Michael,
>   thanks for your references, the problem is that what I want is not
to
> type
> anything in console (psql) but to construct an automatic insertion to
Db
> (when the user uploads a "text" (.csv, .txt file) and send it to my
> server,
> then it runs "something" to insert into a table). Shortly, what I want
> is to
> let users see his point data (species occurences) in my web
application;
> when session finishes, data should disappear. So, I should construct a
> script, maybe using COPY, but how to write such kind of scripts? some
> PDF,
> web references to learn it?
> 
>   I was thinking to restrict the data to insert (X,Y and species,
genus,
> family) and to have a pre-constructed table in PostGIS with these
> columns. I
> suppose it should minimize much more the work and time to insert data
> and
> should also make much easier the scripting. 
>   
> 
>   Thanks.
> 
> 
> Michael Fuhr wrote:
>> 
>> On Wed, Jul 04, 2007 at 12:53:27AM -0700, pere roca wrote:
>>>   I just was wondering if there is some tool to insert automatically
> data
>>> from a .asc or .csv or text file (just X,Y and thematic data) to
> PostGIS
>>> (something similar to shp2pgsql.exe).
>> 
>> Are you familiar with COPY (or psql's \copy)?
>> 
>> http://www.postgresql.org/docs/8.2/interactive/sql-copy.html
>> http://www.postgresql.org/docs/8.2/interactive/app-psql.html
>> 
>>>   What I want to do is that after a user inserts a file in our
> server,
>>> automatically this file is inserted in someway to PostGIS. How would
> you
>>> do
>>> it?
>> 
>> How are files being sent to the server?  Can you explain in more
>> detail how your system works and what you're trying to do?
>> 
>> -- 
>> Michael Fuhr
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> 
>> 
> 
> -- 
> View this message in context:
>
http://www.nabble.com/insert-interactively-asc-or-text-files-tf4022887.h
> tml#a11442333
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> -----------------------------------------
> 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.
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 

-- 
View this message in context:
http://www.nabble.com/insert-interactively-asc-or-text-files-tf4022887.h
tml#a11537703
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
A non-text attachment was scrubbed...
Name: phpupload.zip
Type: application/x-zip-compressed
Size: 1553 bytes
Desc: phpupload.zip
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070711/cdadae88/attachment.bin>