Import large CSV file into PostGIS

Question

I am trying to import CSV files into PostGIS. Following this post , I have created tables before. I found other suggestions saying that I can run the copy command.

If I run this command:

COPY table FROM '/Users/macbook/file.csv' DELIMITERS ',' CSV HEADER;

it didn't copy the table at all. It says that "table" is not recognized.

I tried this:

COPY moulding
(Borough,Block,Lot,CD,CT2010,CB2010,SchoolDist,Council,ZipCode,FireComp,PolicePrct,Address,ZoneDist1,ZoneDist2,ZoneDist3,ZoneDist4,Overlay1,Overlay2,SPDist1,SPDist2,LtdHeight,AllZoning1,AllZoning2,SplitZone,BldgClass,LandUse,Easements,OwnerType,OwnerName,LotArea,BldgArea,ComArea,ResArea,OfficeArea,RetailArea,GarageArea,StrgeArea,FactryArea,OtherArea,AreaSource,NumBldgs,NumFloors,UnitsRes,UnitsTotal,LotFront,LotDepth,BldgFront,BldgDepth,Ext,ProxCode,IrrLotCode,LotType,BsmtCode,AssessLand,AssessTot,ExemptLand,ExemptTot,YearBuilt,BuiltCode,YearAlter1,YearAlter2,HistDist,Landmark,BuiltFAR,ResidFAR,CommFAR,FacilFAR,BoroCode,BBL,CondoNo,Tract2010,XCoord,YCoord,ZoneMap,ZMCode,Sanborn,TaxMap,EDesigNum,APPBBL,APPDate,PLUTOMapID,Version)
FROM
'/Users/macbook/file.csv'
DELIMITERS
','
CSV HEADER;

but didn't work either.

An example of such data set can be downloaded from this link :

Should I create a model and then execute it?

• underdark: Did you try COPY moulding FROM '/Users/macbook/file.csv' DELIMITERS ',' CSV HEADER;
• Luís de Sousa: Do you have a table named table in your public schema?
• user3001937: No ! I am trying to import that file using Copy function.
• BradHards: Are you really using CSV or Shapefile? Your question says CSV, but the sample is shp... Also, which version of postgis are you using?
• user3001937: @BradHards : If you download the file, I find the file with CSV extension. I use the latest version of Postgis. psql (9.3.1, server 9.3.2)

Answer 1

You are almost there but I think the problem might be the table you are loading into.

You must have already had a table created in PostGIS with the correct column types

For example

CREATE TABLE nycdata (
 BOROUGH varchar,
 BLOCK varch,
 DATE date,
 VERSION numeric);

But you need to match the column type with the same type of data in the CSV.

You can see all the Data Types here http://www.postgresql.org/docs/9.1/static/datatype.html

Once you have created the table you can then use the original command

COPY nycdata FROM '/Users/macbook/data.csv' DELIMITERS ',' CSV HEADER;

You will then need to create indexes and a geometry

• walkermatt: The psql \copy command might also be of interest as it allows loading a file from another machine and doesn't require as such high privileges. See the last part of this article for details: postgresqltutorial.com/export-postgresql-table-to-csv-file

Answer 2

This can also be done with GDAL using a .vrt file, although it can be memory intensive.

You vrt would look like:

<OGRVRTDataSource> 
 <OGRVRTLayer name="feature_name"> 
 <SrcDataSource>your_csv.csv</SrcDataSource> 
 <GeometryType>wkbPoint</GeometryType> 
 <LayerSRS>EPSG:27700</LayerSRS> 
 <GeometryField encoding="PointFromColumns" x="Eastings" y="Northings"/> 
 </OGRVRTLayer> 
</OGRVRTDataSource>

Then simply:

ogr2ogr -progress -nln table_name_doesnt_need_to_exist -skipfailures PostgreSQL PG:"dbname='dbname' host='localhost' port='5432' user='username' password='password'" vrt_filename.vrt

For a full guide see:

Loading CSV OS CodePoint Data into PostGIS