[GIS] How to get .csv file into postGIS

PHPpostgis

I am using this:

COPY data_table (col1,col2,col3) FROM 'datafile.csv' DELIMITERS ',' CSV HEADER;

to get csv data into my postGIS table. It works wonderfully, except I need to have a 'pk_id serial PRIMARY KEY' in the database table as well, which will then make the columns not match up between 'data_table' and 'datafile.csv'. And I think this will give an error when I run the COPY command.

I could add the pk_id serial PRIMARY KEY after the import, but I want to run many imports into data_table so this doesn't seem like it would work.

So my question is: what is an alternative way to get csv data in – can I still use COPY? Is there a way like in mySQL to "INSERT INTO table_name (col1, col2,col3) VALUES (val1,val2,val3) where I specify the affected database columns?

Thanks

Best Answer

GDAL using a .vrt file could also be a solution.

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

Related Question