Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

The current approach that I am using to load a csv file into a PostGIS enabled database on a server is the following:

-- create table
CREATE TABLE points(
 id int, 
 lat double precision,
 lng double precision 
);

-- copy data from csv with headers
COPY points (id, lat, lng)
FROM '/home/mydata.csv'
WITH 
    DELIMITER AS ',' 
    CSV HEADER ;

-- Add point geometry column to table
SELECT AddGeometryColumn('public', 'points', 'geom', 900913, 'POINT', 2);

-- Populate column with point geometries
update points set geom = ST_SetSRID(ST_MakePoint(lng, lat), 900913);

-- Create a spatial index on points
CREATE INDEX idx_points ON points USING GIST (geom);

Although this creates a table with point geometries, there appear to be some restrictions when using the COPY command as you need to be a superuser. Also, I can't figure out how to upload a csv file from my laptop to the server using this approach (I need to transfer the file using ftp and then run the script).

Is there another strategy that I should be considering for generating a table with point geometries from a csv? I want to perform this entire process in PostGIS/SQL.

share|improve this question
    
As far as I know, your only way to load data is using copy or executing an sql script. Since you only have a csv and don't want to preprocess is to sql, you're limited to copy. And copy only works on local files. So you're stuck ... but maybe somebody else knows a trick. –  underdark Aug 27 '12 at 21:32
    
Preprocessing to SQL could be ok; what would that involve? –  djq Aug 27 '12 at 21:44
    
How about creating a Python script that runs on your laptop, reads the CSV, connects to the remote database and inserts the data? –  underdark Aug 27 '12 at 21:50
    
Yes, that is a possibility. Is it possible to use COPY on that command, or would it need to be INSERT ? –  djq Aug 27 '12 at 22:09
    
That would be inserts. –  underdark Aug 28 '12 at 6:37

1 Answer 1

You can pipe from STDIN to COPY from a client. If you're using psql...

cat myfile.csv | psql -c "COPY mytable(col1, col2, col3) FROM stdin"
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.