Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
 id int, 
 lat double precision,
 lng double precision 

-- copy data from csv with headers
COPY points (id, lat, lng)
FROM '/home/mydata.csv'

-- 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

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


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.