<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=utf-8">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META NAME="Generator" CONTENT="MS Exchange Server version 6.0.6556.0">
<TITLE>Re: [postgis-users] insert interactively asc or text files</TITLE>
</HEAD>
<BODY dir=ltr>
<DIV>Hi Pere,</DIV>
<DIV> </DIV>
<DIV>-OGR2OGR (a command-line tool you can find with the open-source GDAL
library) can also upload automatically csv to Postgis . </DIV>
<DIV>It is very similar to pgsql2gsp and shp2pgsal, but supports many different
formats (csv, GML, MapInfo, etc...)</DIV>
<DIV> see:</DIV>
<DIV><A
href="http://www.bostongis.com/?content_name=ogr_cheatsheet">http://www.bostongis.com/?content_name=ogr_cheatsheet</A></DIV>
<DIV>(in some situation you must use a metadata file called 'vrt':</DIV>
<DIV><A
href="http://lists.maptools.org/pipermail/gdal-dev/2006-October/010377.html">http://lists.maptools.org/pipermail/gdal-dev/2006-October/010377.html</A></DIV>
<DIV> </DIV>
<DIV>Alternative solution</DIV>
<DIV> </DIV>
<DIV>-I had a treatement which was very close to your need.</DIV>
<DIV>We used the following steps:</DIV>
<DIV>1) We built a Postgis table whose structure was close to the source
CSV, without 'geometry' columns, but with 'latitude' and longitude
fields (with decimal data type);</DIV>
<DIV>2) We used a COPY instruction to import the csv file into the Postgis
table.</DIV>
<DIV>The COPY instruction was executed in a PL/PGSQL function whose input
parameter was the path of the CSV file (we used the 'EXECUTE' function to
launch the treatment);</DIV>
<DIV>3) We built a view with a geometry columns which was based on the
first table (with the geometry column being: 'GEOMFROMTEXT('||
decimalLongitudeField||' '||decimalLatitudeField ||', mySRID)')</DIV>
<DIV>4) We used a pl/pgsql function (you couls also use a pl/pgsql
trigger) to replicate the view into a SQL table which was used by a Web
application.</DIV>
<DIV> </DIV>
<DIV>We didn't use Perl script, but we gathered all the treatments in a single
pl/pgsql functionas called from the DOS or the Shell environnement (the command
had this form 'psql>SELECT * FROM importFromCSV(mySourceCSVFile)', although
this is not at all a SELECT command).</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>Kind regards,</DIV>
<DIV> </DIV>
<DIV>Franck</DIV>
<DIV> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><FONT size=2>-------- Message d'origine-------- <BR><B>De:</B>
postgis-users-bounces@postgis.refractions.net de la part de pere
roca <BR><B>Date:</B> jeu. 5/07/2007 9:59 <BR><B>À:</B>
postgis-users@postgis.refractions.net <BR><B>Cc:</B> <BR><B>Objet:</B> Re:
[postgis-users] insert interactively asc or text
files<BR><BR></FONT></DIV><BR><BR>
<P><FONT size=2> hi Michael,<BR> thanks for your references, the
problem is that what I want is not to type<BR>anything in console (psql) but
to construct an automatic insertion to Db<BR>(when the user uploads a "text"
(.csv, .txt file) and send it to my server,<BR>then it runs "something" to
insert into a table). Shortly, what I want is to<BR>let users see his point
data (species occurences) in my web application;<BR>when session finishes,
data should disappear. So, I should construct a<BR>script, maybe using COPY,
but how to write such kind of scripts? some PDF,<BR>web references to learn
it?<BR><BR> I was thinking to restrict the data to insert (X,Y and
species, genus,<BR>family) and to have a pre-constructed table in PostGIS with
these columns. I<BR>suppose it should minimize much more the work and time to
insert data and<BR>should also make much easier the
scripting.<BR> <BR><BR> Thanks.<BR><BR><BR>Michael Fuhr
wrote:<BR>><BR>> On Wed, Jul 04, 2007 at 12:53:27AM -0700, pere roca
wrote:<BR>>> I just was wondering if there is some tool to
insert automatically data<BR>>> from a .asc or .csv or text file (just
X,Y and thematic data) to PostGIS<BR>>> (something similar to
shp2pgsql.exe).<BR>><BR>> Are you familiar with COPY (or psql's
\copy)?<BR>><BR>> <A
href="http://www.postgresql.org/docs/8.2/interactive/sql-copy.html">http://www.postgresql.org/docs/8.2/interactive/sql-copy.html</A><BR>>
<A
href="http://www.postgresql.org/docs/8.2/interactive/app-psql.html">http://www.postgresql.org/docs/8.2/interactive/app-psql.html</A><BR>><BR>>>
What I want to do is that after a user inserts a file in our
server,<BR>>> automatically this file is inserted in someway to PostGIS.
How would you<BR>>> do<BR>>> it?<BR>><BR>> How are files
being sent to the server? Can you explain in more<BR>> detail how
your system works and what you're trying to do?<BR>><BR>> --<BR>>
Michael Fuhr<BR>> _______________________________________________<BR>>
postgis-users mailing list<BR>>
postgis-users@postgis.refractions.net<BR>> <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>><BR><BR>--<BR><BR>Sent
from the PostGIS - User mailing list archive at
Nabble.com.<BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR>postgis-users@postgis.refractions.net<BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></FONT></P></BLOCKQUOTE>
###########################################<br><br>This message has been scanned by ICT - Africa Museum<br></BODY>
<!--[object_id=#africamuseum.be#]--><P><FONT face=Verdana size=1>________________________________________ </FONT></P>
<P><FONT face=Verdana size=1>5/7/2007 - Filtered through antispam by ICT</FONT></P></HTML>