[GIS] How to create a point table from a remote csv file

csvpointpostgissql

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.

Best Answer

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"
Related Question