Tuesday 26 February 2019

postgresql - Import large CSV file into PostGIS


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?




Answer



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


No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...