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

Obe, Regina robe.dnd at cityofboston.gov
Wed Jul 11 05:23:49 PDT 2007


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



More information about the postgis-users mailing list