insert interactively asc or text files

[pere roca]

  Hi everybody,
  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).

  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?
 
   Thanks!
   Pere

[Santosh Gaikwad]

Hi,

You can try out Pentaho Kettle software.

Santosh Gaikwad
Senior Software Developer
Saama Technologies (India) Pvt Ltd.
6th Floor West Wing, Marisoft III,
Marigold Premises, Kalayani Nagar,
Pune - 411014. India
Phone : [phone] Extn: 397
Mobile: [phone]
E-mail :Santosh.Gaikwad@...
http://www.saama.com

-----Original Message-----
From: postgis-users-bounces@...
[mailto:postgis-users-bounces@...] On Behalf Of pere
roca
Sent: Wednesday, July 04, 2007 1:23 PM
To: postgis-users@...
Subject: [postgis-users] insert interactively asc or text files


  Hi everybody,
  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).

  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?
 
   Thanks!
   Pere
--
View this message in context:
http://www.nabble.com/insert-interactively-asc-or-text-files-tf4022887.h
tml#a11426251
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Santosh Gaikwad.vcf]

---

BEGIN:VCARD
VERSION:2.1
N:Gaikwad;Santosh
FN:Santosh Gaikwad
EMAIL;PREF;INTERNET:Santosh.Gaikwad@...
REV:20070529T045803Z
END:VCARD

---

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Michael Fuhr]

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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Chetan Tiwari]

Hi Pere

I have a little perl script that reads CSV files uploaded via a browser and inserts the data into a table in postgres. I'll be happy to share that with you. It's setup to run with active perl and apache on windows, but it would be fairly easy to tweak it to your system setup.

- Chetan

On 7/4/07, pere roca <peroc79@...> wrote:

  Hi everybody,
  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).

  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?

   Thanks!
   Pere
--
View this message in context: http://www.nabble.com/insert-interactively-asc-or-text-files-tf4022887.html#a11426251
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users@... ons.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

--
Chetan Tiwari, Ph.D. (ABD)
Dept. of Geography, The Univ. of Iowa
316 Jessup Hall, Iowa City, IA 52242
Ph. [phone] Fax [phone]

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Stephen Frost]

* Chetan Tiwari (chetan.tiwari@...) wrote:
> I have a little perl script that reads CSV files uploaded via a browser and
> inserts the data into a table in postgres. I'll be happy to share that with
> you. It's setup to run with active perl and apache on windows, but it would
> be fairly easy to tweak it to your system setup.

If you want to do it on-the-fly you can use CGI.pm and its upload-hook
to avoid any disk i/o on the web server (and avoid any temp file
creation, or limits due to space on the disk, etc).

        Thanks,

                Stephen

> On 7/4/07, pere roca <peroc79@...> wrote:
>>
>>
>>   Hi everybody,
>>   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).
>>
>>   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?
>>
>>    Thanks!
>>    Pere
>> --
>> View this message in context: http://www.nabble.com/insert
>> -interactively-asc-or-text-files-tf4022887.html#a11426251
>> Sent from the PostGIS - User mailing list archive at Nabble.com.
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users@...
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
>
>
> --
> Chetan Tiwari, Ph.D. (ABD)
> Dept. of Geography, The Univ. of Iowa
> 316 Jessup Hall, Iowa City, IA 52242
> Ph. [phone] Fax [phone]

> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Chetan Tiwari]

Hi Stephen

That's a good point. The way I've got things setup right now is that the files are first uploaded to a temp location and are then parsed into postgresql/postgis. I need to look into what you're suggesting and it's obviously a more efficient way of achieving the same result. Thanks,

Chetan

On 7/4/07, Stephen Frost <sfrost@... > wrote:

* Chetan Tiwari (chetan.tiwari@...) wrote:
> I have a little perl script that reads CSV files uploaded via a browser and
> inserts the data into a table in postgres. I'll be happy to share that with
> you. It's setup to run with active perl and apache on windows, but it would
> be fairly easy to tweak it to your system setup.

If you want to do it on-the-fly you can use CGI.pm and its upload-hook
to avoid any disk i/o on the web server (and avoid any temp file
creation, or limits due to space on the disk, etc).

        Thanks,

                Stephen

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[pere roca]

  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
[e-mail]
http://postgis.refractions.net/mailman/listinfo/postgis-users

[pere roca]

  Hi Chetan and Stephen ,
  many thanks for your offer! I must definitely update about scripting (and never used perl) along with psql, so I think your perl script will help me in learning too.
  Shortly, what I want is to let users see his point data (species occurences) in my web application (for that I must update a PostGIS table) ; when session finishes, data should disappear.
  I was thinking to restrict the data to insert (X,Y and species, genus, family for example) 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. Chetan, does your script work fine and quickly for huge data (about... 30.000 records) ?

  Thanks also Stephen for your collaboration! I will inform you about my progress [ or problems ;.) ]

 

Chetan Tiwari wrote:

Hi Pere
I have a little perl script that reads CSV files uploaded via a browser and
inserts the data into a table in postgres. I'll be happy to share that with
you. It's setup to run with active perl and apache on windows, but it would
be fairly easy to tweak it to your system setup.

- Chetan

On 7/4/07, pere roca <[e-mail]> wrote:
>
>
>   Hi everybody,
>   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).
>
>   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?
>
>    Thanks!
>    Pere
> --
> View this message in context: http://www.nabble.com/insert
> -interactively-asc-or-text-files-tf4022887.html#a11426251
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
> _______________________________________________
> postgis-users mailing list
> [e-mail]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



--
Chetan Tiwari, Ph.D. (ABD)
Dept. of Geography, The Univ. of Iowa
316 Jessup Hall, Iowa City, IA 52242
Ph. [phone] Fax [phone]

_______________________________________________
postgis-users mailing list
[e-mail]
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Regina Obe]

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@...
[mailto:postgis-users-bounces@...] On Behalf Of pere
roca
Sent: Thursday, July 05, 2007 4:00 AM
To: postgis-users@...
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@...
> 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@...
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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Chetan Tiwari]

Hi Pere

 

I am attaching a zip file with the perl script, an example html form, a test dataset and setup instructions. I am assuming that your data has the following columns: id, longitude, latitude, species, genus, family. You can change this in the script if I got it wrong. It should work fine with large datasets (30,000 records should not be a problem at all). The script is configured to work on a windows machine, although with some minor changes to pathnames, it should work in linux/unix environments as well. Please let me know if you have any questions or have trouble getting it to work. I've never sent attachments on this list, so if the zipfile does not come through please let me know and I'll send it to you directly.

 

- Chetan

 

PS This version simply uploads the file to a temp location and then loads it into postgresql/postgis. I still need to look into Stephen's suggestion.

 

On 7/5/07, pere roca <peroc79@...> wrote:

Hi Chetan and Stephen ,
many thanks for your offer! I must definitely update about scripting (and
never used perl) along with psql, so I think your perl script will help me
in learning too.
Shortly, what I want is to let users see his point data (species
occurences) in my web application (for that I must update a PostGIS table) ;
when session finishes, data should disappear.
I was thinking to restrict the data to insert (X,Y and species, genus,
family for example) 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. Chetan, does
your script work fine and quickly for huge data (about... 30.000 records) ?

Thanks also Stephen for your collaboration! I will inform you about my
progress [ or problems ;.) ]

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Chetan Tiwari]

Hi All

It seems like my previous message (see below) was deleted because of the attachment. Sorry for this off topic question, but is it possible to send attachments to this list?

- Chetan 

On 7/5/07, Chetan Tiwari <chetan.tiwari@...> wrote:

Hi Pere

 

I am attaching a zip file with the perl script, an example html form, a test dataset and setup instructions. I am assuming that your data has the following columns: id, longitude, latitude, species, genus, family. You can change this in the script if I got it wrong. It should work fine with large datasets (30,000 records should not be a problem at all). The script is configured to work on a windows machine, although with some minor changes to pathnames, it should work in linux/unix environments as well. Please let me know if you have any questions or have trouble getting it to work. I've never sent attachments on this list, so if the zipfile does not come through please let me know and I'll send it to you directly.

 

- Chetan

 

PS This version simply uploads the file to a temp location and then loads it into postgresql/postgis. I still need to look into Stephen's suggestion.

 

On 7/5/07, pere roca <peroc79@...> wrote:

Hi Chetan and Stephen ,
many thanks for your offer! I must definitely update about scripting (and
never used perl) along with psql, so I think your perl script will help me
in learning too.
Shortly, what I want is to let users see his point data (species
occurences) in my web application (for that I must update a PostGIS table) ;
when session finishes, data should disappear.
I was thinking to restrict the data to insert (X,Y and species, genus,
family for example) 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. Chetan, does
your script work fine and quickly for huge data (about... 30.000 records) ?

Thanks also Stephen for your collaboration! I will inform you about my
progress [ or problems ;.) ]

--
Chetan Tiwari, Ph.D. (ABD)
Dept. of Geography, The Univ. of Iowa
316 Jessup Hall, Iowa City, IA 52242
Ph. [phone] Fax [phone]

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Regina Obe]

I actually got your last message fine with the attachment and was able to unzip it so maybe it varies depending on what mailservers people are using or the mail client.

 

Hope that helps,

Regina

---

From: postgis-users-bounces@... [mailto:postgis-users-bounces@...] On Behalf Of Chetan Tiwari
Sent: Thursday, July 05, 2007 6:21 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] insert interactively asc or text files

Hi All

It seems like my previous message (see below) was deleted because of the attachment. Sorry for this off topic question, but is it possible to send attachments to this list?

- Chetan 

On 7/5/07, Chetan Tiwari <chetan.tiwari@...> wrote:

Hi Pere

 

I am attaching a zip file with the perl script, an example html form, a test dataset and setup instructions. I am assuming that your data has the following columns: id, longitude, latitude, species, genus, family. You can change this in the script if I got it wrong. It should work fine with large datasets (30,000 records should not be a problem at all). The script is configured to work on a windows machine, although with some minor changes to pathnames, it should work in linux/unix environments as well. Please let me know if you have any questions or have trouble getting it to work. I've never sent attachments on this list, so if the zipfile does not come through please let me know and I'll send it to you directly.

 

- Chetan

 

PS This version simply uploads the file to a temp location and then loads it into postgresql/postgis. I still need to look into Stephen's suggestion.

 

On 7/5/07, pere roca <peroc79@...> wrote:

Hi Chetan and Stephen ,
many thanks for your offer! I must definitely update about scripting (and
never used perl) along with psql, so I think your perl script will help me
in learning too.
Shortly, what I want is to let users see his point data (species
occurences) in my web application (for that I must update a PostGIS table) ;
when session finishes, data should disappear.
I was thinking to restrict the data to insert (X,Y and species, genus,
family for example) 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. Chetan, does
your script work fine and quickly for huge data (about... 30.000 records) ?

Thanks also Stephen for your collaboration! I will inform you about my
progress [ or problems ;.) ]

--
Chetan Tiwari, Ph.D. (ABD)
Dept. of Geography, The Univ. of Iowa
316 Jessup Hall, Iowa City, IA 52242
Ph. [phone] Fax [phone]

---

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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[pere roca]

    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: [e-mail]
[mailto:[e-mail]] On Behalf Of pere
roca
Sent: Thursday, July 05, 2007 4:00 AM
To: [e-mail]
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
> [e-mail]
> 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
[e-mail]
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
[e-mail]
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Regina Obe]

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@...
[mailto:postgis-users-bounces@...] On Behalf Of pere
roca
Sent: Wednesday, July 11, 2007 6:21 AM
To: postgis-users@...
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@...
> [mailto:postgis-users-bounces@...] On Behalf Of

pere
> roca
> Sent: Thursday, July 05, 2007 4:00 AM
> To: postgis-users@...
> 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@...
>> 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@...
> 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@...
> 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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Regina Obe]

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@...
[mailto:postgis-users-bounces@...] 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@...
[mailto:postgis-users-bounces@...] On Behalf Of pere
roca
Sent: Wednesday, July 11, 2007 6:21 AM
To: postgis-users@...
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@...
> [mailto:postgis-users-bounces@...] On Behalf Of

pere
> roca
> Sent: Thursday, July 05, 2007 4:00 AM
> To: postgis-users@...
> 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@...
>> 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@...
> 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@...
> 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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Stephen Davies-2]

If you are using *NIX, you can add to the automation by configuring your
mail server to receive files via email and direct them to a program to
do the actual data insertion.

eg  add an alias entry like:

loascsv:|/usr/local/bin/loadcsv

Any email sent to loadcsv@... will be directed to the
program/shell script /usr/local/bin/loadcsv.

HTH,
Stephen Davies
--
========================================================================
This email is for the person(s) identified above, and is confidential to
the sender and the person(s).  No one else is authorised to use or
disseminate this email or its contents.

Stephen Davies Consulting                            Voice: 08-8177 1595
Adelaide, South Australia.                             Fax: [phone]
Computing & Network solutions.                       Mobile:[phone]
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users