can Well Known Text tables be used effectively, stored in Access or Base?

Question

My question is: How do I import WKT text data back into QGIS, and how useful could Base or Access be in storing the data using WKT rather than spatially enabling it within a proper GIS database, like Spatialite?

I get an error when I use the "Add Delimited Text Layer" (within the Manage Layers toolbar) in QGIS 1.8 on Windows XP, to add data with multipolygon geometries from a WKT field. I select the WKT[] and Delimiters[] radio buttons. Before I press [OK], I check that the Sample text box shows the columns parsed correctly.

A CSV file with commas scrambles the columns together by the tool, even though Excel reads it perfectly (it was created using QGIS, exporting a Spatialite table the OGR LAYER option to add WKT geometries column).

And so I used Excel to SAVE AS a TAB delimited .txt file. The txt file, TAB delimited, pre-processing sample text shows that the data is being parsed into the proper columns perfectly. This looks promising, then I hit [OK] to process it and a dialog box pops up reporting it failed, "The layer...is not a valid layer and can not be added to the map [OK]"

Currently I'm using Spatialite with great success. But I'm experimentally trying to learn how to create and use WKT text files so that I can perhaps import them into Access or Base, to see how feasible it is to deliver data in alternative, consumer level database formats. The users would have experience with them, but I currently don't know very much about either.

Answer 1

Good question, that would be very handy in lots of datasources. You can try VRT GDAL format in first approach, which allow you to generate 'fake' spatial datasources on the fly, using WKT or XY fields. One problem remains, reading MS Access or Excel efficiently..

Answer 2

I use often this GDAL/OGR VRT format with SQLite files:

<OGRVRTDataSource>
    <OGRVRTLayer name="test">
       <SrcDataSource>path/testwkt.sqlite</SrcDataSource> 
       <SrcLayer>geom</SrcLayer> 
       <GeometryType>wkbPolygon</GeometryType>
       <LayerSRS>EPSG:31370</LayerSRS>
       <GeometryField encoding="WKT" field="geom"/> 
    </OGRVRTLayer>       
</OGRVRTDataSource>

And I can use the same format for Access databases in Mac OS X (GDAL 1.10) with the Actual ODBC Driver for Access Databases (Microsoft Access 97 or later, including Access 2007, and I can read the ODBC connection in QGIS) so I suppose that it is possible on Windows...

 <OGRVRTDataSource>
    <OGRVRTLayer name="my_layer">
       <SrcDataSource>ODBC:test</SrcDataSource>
       <SrcLayer>COORDO</SrcLayer>
       <GeometryType>wkbPoint</GeometryType>
       <GeometryField g="WKT" field="wkt"//>
    </OGRVRTLayer>
 </OGRVRTDataSource>

But I think that this XML format is difficult for common users and from GDAL: ODBC RDBMS

Starting with GDAL 1.10, and on Windows provided that the "Microsoft Access Driver (*.mdb)" ODBC 
driver is installed, non-spatial MS Access Databases (not Personnal Geodabases or Geomedia databases) 
can be opened directly by their filenames. 

So, theoretically, in Windows, you can open an Access database in QGIS (if it uses GDAL 1.10 and not 1.9.x) but the WKT format should be treated (?).