Exporting to CSV with Geometries

[Adam Estrada]

All,

It's pretty easy to export tables in a Spatialite DB to single CSV's,
but I am curious to see if there is a way to export the contents of
the geometry column in addition to the attribute data. Is this even
possible?

Thanks in advance,
Adam

[markb]

I've tried to include the export of geometries (only as WKT) in CSV
output (for possible exchange with MS SQL Server), but without success
(the SL gui would unexpectedly quit). I never reported on this
because I hadn't been able to research the issue properly (changed
jobs). This was with SL 2.4.0 'pre'-rc3. Of course the SRID would
have to be manually entered during imports, but that isn't a big deal
(I can't speak for any rounding issues though, and I can't remember if
it was projected or un-projected data). I pre-created an export table
(with WKT & attributes, no geom column) before I attempted the CSV
output, and I also simplified down to a single point field & record
(but no avail). Though using a comma as the delimiter seams bad with
WKT strings (the command line provides more options I think). This
was an at the moment thing for someone else, so they didn't want to
wait for me to research it, wasn't very important to them.

I'd be interested in any thoughts on this as well. Thanks -Mark

[Noli Sicad]

Have they done this exporting of geometry column in PostGIS?

I tried Spatialite_osm_map CLI tool and the layers have points and
lines only. No symbology has been exported.

Noli

> --
> You received this message because you are subscribed to the Google Groups
> "SpatiaLite Users" group.
> To post to this group, send email to spatiali...@googlegroups.com.
> To unsubscribe from this group, send email to
> spatialite-use...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/spatialite-users?hl=en.
>
>

[Adam Estrada]

I have not tried this in Postgres/PostGIS. That is my next step
though...I am experimenting with Solr indexes and I know that there is
a way to import data from a RDBMS like Postgres but I did not want to
go that far with it. There is an easy CSV handler that I have been
using but I can't seem to grab any geometries through OGR or through
Spatialite. This is the problem... I can write a converter to go from
KML or GML but again, that seems like a lot of work for something that
should be available already.

Adam

[Keith]

Ogr2ogr will export a spatialite table to csv, including the Geometry
values:

C:\Program Files\FWTools2.4.7\bin\ogr2ogr.exe" f "CSV" output
test-2.3.sqlite -sql "select OGR_GEOM_WKT,* from Regions" -dialect
OGRSQL -nln regions

First, I had to update the sqlite3.dll in my fwtools\bin folder to a
newer version.

Keith

[Adam Estrada]

I just found out that the latest version of GDAL/OGR has the ability to export to csv and include the geometry using the -lco flag.

tl_2010_11001_roads Adam$ ogr2ogr -f "CSV" DistrictofColumbia tl_2010_11001_roads.shp -lco GEOMETRY=AS_WKT

http://www.gdal.org/ogr/drv_csv.html

Pretty cool!

Adam

[Noli Sicad]

Hi Adam,

Off topic but related to this :-)

Tim Sutton has a nice tutorial video on this csv and include the
geometry using QGIS trunk.

http://linfiniti.com/2010/12/video-tutorial-2-delimited-text-plugin/

Noli