12

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?

  • 3
    Did you try COPY moulding FROM '/Users/macbook/file.csv' DELIMITERS ',' CSV HEADER; – underdark Jan 13 '14 at 9:34
  • Do you have a table named table in your public schema? – Luís de Sousa Jan 13 '14 at 9:59
  • No ! I am trying to import that file using Copy function. – user3001937 Jan 13 '14 at 10:00
  • Are you really using CSV or Shapefile? Your question says CSV, but the sample is shp... Also, which version of postgis are you using? – BradHards Jan 13 '14 at 10:05
  • @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) – user3001937 Jan 13 '14 at 10:16
10

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

4

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> 

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

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.