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.