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

THEETEN Franck franck.theeten at
Thu Jul 5 05:22:54 PDT 2007

Hi Pere,
-OGR2OGR (a command-line tool you can find with the open-source GDAL library) can also upload automatically csv to Postgis . 
It is very similar to pgsql2gsp and shp2pgsal, but supports many different formats (csv, GML, MapInfo, etc...)
(in some situation you must use a metadata file called 'vrt':
Alternative solution
-I had a treatement which was very close to your need.
We used the following steps:
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);
2) We used a COPY instruction to import the csv file into the Postgis table.
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);
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)')
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.
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).
Kind regards,

	-------- Message d'origine-------- 
	De: postgis-users-bounces at de la part de pere roca 
	Date: jeu. 5/07/2007 9:59 
	À: postgis-users at 
	Objet: 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.
	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)?
	>>   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
	Sent from the PostGIS - User mailing list archive at
	postgis-users mailing list
	postgis-users at


This message has been scanned by ICT - Africa Museum

5/7/2007 - Filtered through antispam by ICT

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <>

More information about the postgis-users mailing list