Converting shapefile to CSV including attributes and geometry

Question

I have a shapefile with 60k+ entries, all of which are polygons with corresponding attributes (acreage totals, landowner names, tax ID #s, etc.). What I ultimately need is a CSV file with all of these attributes and their corresponding geometry (in the KML compatible xyz format, that is, NOT the WKT format).

I know that I can open the .dbf file in Excel and get the attributes. I also know that I can open the shapefile in QGIS and copy the data into Excel, which gets me attributes and WKT geometry.

Is there a simple way to convert the shapefile to CSV (openable in Excel) with attribute and Google Earth friendly geometry?

• Stephen Quan: To meet your specifications, an application or a script would have to be built. I don't think it's difficult, but I don't think it's non trivial either. If I was task to do it, I probably would estimate 1 week fulltime for such a job.
• Rikk: Thanks, this is helpful. This is kind of crazy though. If my file size weren't so big I could do it automatically through Google. How I typically do this is by importing a .shp into Google Earth and saving as a .kml. I then upload the .kml into Google Fusion Tables (FYI: Google Fusion Tables has a 100 mb upload limit) and export to .csv. I can then play around with the .csv however I want - change attributes, add new polygons that I have the coordinates for, etc., etc. - and then I reupload the newly saved .csv to Google Fusion Tables, export to .kml, and open in Google Earth.
• Rikk: So I guess the question then becomes... how do I split a .shp file into two parts? This would allow me to get the individual .kml files under the 100 mb limit and I can use the above-mentioned method to convert.
• user2856: I've answered the question as asked below, but just saw your comment. Why do you need csv at all? Why not do your attribute editing, adding new polygons, etc. in the GIS (you said you had QGIS) and then export that to your final KML?
• Radar: @Rikk - in response to the question in your comment: A simple way to split a shapefile would be to do a spatial selection on part of your shapefile and then right-clicking your layer and selecting "Export". You could then simply export the selected features to a new, smaller shapefile. This is by no means scientific, but it's simple. Alternatively you could select by some attributes if you wanted a more organized approach.

Answer 1

Here's a simple script that uses the OGR python bindings:

import ogr,csv,sys
shpfile=r'C:\Temp\test.shp' #sys.argv[1]
csvfile=r'C:\Temp\test.csv' #sys.argv[2]
#Open files
csvfile=open(csvfile,'wb')
ds=ogr.Open(shpfile)
lyr=ds.GetLayer()
#Get field names
dfn=lyr.GetLayerDefn()
nfields=dfn.GetFieldCount()
fields=[]
for i in range(nfields):
 fields.append(dfn.GetFieldDefn(i).GetName())
fields.append('kmlgeometry')
csvwriter = csv.DictWriter(csvfile, fields)
try:csvwriter.writeheader() #python 2.7+
except:csvfile.write(','.join(fields)+'\n')
# Write attributes and kml out to csv
for feat in lyr:
 attributes=feat.items()
 geom=feat.GetGeometryRef()
 attributes['kmlgeometry']=geom.ExportToKML()
 csvwriter.writerow(attributes)
#clean up
del csvwriter,lyr,ds
csvfile.close()

EDIT: and another script to convert from your CSV to KML

import ogr,csv,sys,os
ogr.UseExceptions()
csvfile=r'C:\temp\test.csv' #sys.argv[1]
kmlfile=r'C:\temp\test.kml' #sys.argv[2]
csvreader=csv.reader(open(csvfile,'rb'))
headers=csvreader.next()
ds = ogr.GetDriverByName('KML').CreateDataSource(kmlfile)
lyr = ds.CreateLayer(os.path.splitext(os.path.basename(kmlfile))[0])
for field in headers[:-1]: #skip kmlgeometry
 field_def = ogr.FieldDefn(field)
 print lyr.CreateField(field_def)
for rec in csvreader:
 feat = ogr.Feature(lyr.GetLayerDefn())
 for i,field in enumerate(headers[:-1]): #skip kmlgeometry
 feat.SetField(field, rec[i])
 feat.SetGeometry(ogr.CreateGeometryFromGML(rec[-1]))
 lyr.CreateFeature(feat)
del lyr,ds

• Andrew S: Can you describe more about how to use those bindings as that glosses over important information your readers would need to know.

Answer 2

If you convert your shapefile to spatialite, you should be able to do the following:

1) Experiment with SQL to test the output:

ex.

SELECT col1, col2, col3, AsKml(geometry_column) FROM tab

2) Once you are satisfied with the result, you can export it to CSV format:

https://stackoverflow.com/questions/5776660/export-from-sqlite-to-csv-using-shell-script

For more information on Spatialite:

https://www.gaia-gis.it/fossil/libspatialite/index

Spatialite SQL functions:

http://www.gaia-gis.it/gaia-sins/spatialite-sql-3.0.0.html

Answer 3

If you are working on QGIS, you can instantly generate a CSV document by right clicking on the layer -> Save as -> CSV.

If you are working with ArcMap, then you can export the KML by using the tool Layer to KML (Go to Search option in the program). For some reasons it generates a KMZ instead of an KML (atleast that's what happened on my case).

In order to convert a KMZ to KML:

• Import your KMZ file to Google Earth and right click on your layer and save it as KML
• Open QGIS and drag and drop the KML file - it will automatically load the layer (>QGIS 2.10 PISA)
• Right click on the file and save it as CSV

This procedure is longer if you are working with ArcGIS but in QGIS it can be done in no time. You will have to install QGIS in any case.

• Philipp Schwarz: Tested your solution for QGIS and it does not work. Save as csv does not preserve spatial component.
• kozyr: QGIS gives you an option to select WKT during the export - this export the geometry (spatial component) in WKT format along with the rest of the features in the shape file.
• leo: From QGIS 3.0 you will look for the "geometry" dropdown list under "Layer Options" in the export dialogue, and select AS_XY, AS_XYZ or AS_WKT

Answer 4

At the command line you can use ogr2ogr, like this:

ogr2ogr -f CSV output.csv input.shp -lco GEOMETRY=AS_XYZ

lco is for "layer creation options", i think. Other available options are documented here: http://www.gdal.org/drv_csv.html

• Andrew S: Leo, I get "Error 1 could'nt fetch requested layer 'GEOMETRY=AS_XYZ'. I can bring the file up in QGIS and clearly see a vector containing country outlines.

Answer 5

there is a tool on the 'Geoprocessing Model and Script Tool Gallery' that does what you're looking for. it can export to excel or csv.

http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=95009B25-1422-2418-7FB5-B8638ECB2FA9

• user2856: I had a look at that, it doesn't export the geometry in any format, let alone KML.

Answer 6

simply go to conversion tool in arc map. then go to table to excel. an excel file will made. convert that excel doc to .csv file