[postgis-users] insert interactively asc or text files

Obe, Regina robe.dnd at cityofboston.gov
Thu Jul 5 08:04:33 PDT 2007

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.



-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of pere
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
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
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
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
should also make much easier the scripting. 


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
>> from a .asc or .csv or text file (just X,Y and thematic data) to
>> (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
>> automatically this file is inserted in someway to PostGIS. How would
>> 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:
Sent from the PostGIS - User mailing list archive at Nabble.com.

postgis-users mailing list
postgis-users at postgis.refractions.net

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.

More information about the postgis-users mailing list