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.

  • 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
  • 1
    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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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