Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm using QGIS 2.0.1 and am connected to a MS SQL Server spatially enabled database (not ESRI). I am able to connect to the layer and load it into my TOC just fine. I have hidden a number of fields that are not important for visibility sake.

Now I want to export that data to another data type (we'll use shapefile for this example). When I right-click the layer, choose "Save As" and define the output file, I get the following error:

Export to vector file failed. Error: unsupported type for field GlobalID

Note 1: If I check "Skip attribute creation" in the dialog box, the shapefile is created with no errors.

Note 2: ArcGIS does not have this problem, it just converts the GUID field to "TEXT" type.

Looking at the table, one of the fields that I have hidden is a GUID of SQL type <uniqueidentifier>, which is in all likelyhood the culprit. In an attempt to resolve this, I made the field "hidden". I would have assumed that because the field was hidden, QGIS would not try to export this field, but that turned out to not be the case.

Can you define the fields you want to export from QGIS?

Is it possible for QGIS to export just the "Visible" fields? Or is there another alternative way of doing this from QGIS (maybe define the fields via an ogr2ogr query)? This seems like a shortcoming of QGIS if I cannot define which fields to export.

UPDATE: I did a test using a sample shapefile to see if I could limit the output fields. The following ogr2ogr command line worked and eliminated all fields except for "name":

ogr2ogr -f "ESRI Shapefile" test.shp city.shp -sql "select name from city"

but using QGIS and adding:

-sql "select name from city"

to the "layer" section of the "Save As" dialog did not result in a shapefile with just the single field. So I don't really see how I can manipulate the file output to show just a select few fields. Any help is appreciated.

share|improve this question
    
If ogr2ogr can do it, QGIS can, because that's exactly the tool which is running in the background. Unfortunately, I have no SQL Server db to test ... –  underdark Sep 27 '13 at 14:15
    
Right. So I tried adding -sql "select <fields> from <table>" (excluding the GlobalID field from the <fields>) to the "Layer" portion of the "Save As" dialog, but the same error still popped up. Did I misuse this code block, or is this a bug? –  RyanDalton Sep 27 '13 at 17:24
    
this answer suggests -select instead of -sql gis.stackexchange.com/questions/30691/… ... I think it goes in the data source section, not into layer. –  underdark Sep 27 '13 at 20:20
    
No luck for me with either of your suggestions I also tried multiple combinations of both the -select & -sql options in both "Layer" and "Data Source" fields of the "OGR creation options" :( Surely, I can't be the first one with the need to export a subset of fields... –  RyanDalton Sep 27 '13 at 20:37
    
might be worth checking with the developers. might be a bug. –  underdark Sep 27 '13 at 21:21

2 Answers 2

up vote 1 down vote accepted

If it works for you in command line with ogr2ogr, I think it is the best solution to use that. You can write a simple bat file to create a shapefile from your MSSQL database. Obviously you can not edit the data and save back to the server that way.

QGIS has the Data Source and Layer input boxes, but those are only for -dsco and -lco parameters of ogr2ogr. The OGR shapefile driver has no useful dsco or lco for your needs:

http://www.gdal.org/ogr2ogr.html

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

Alternatively, it should be possible to write a python plugin that requests the data from the server with a SQL SELECT command at your needs. This would even enable data editing.

share|improve this answer
    
So far this seems like the most feasible idea. Is there a way to directly call ogr2ogr command-line from the QGIS user interface in order roll your own ogr2ogr syntax? –  RyanDalton Oct 1 '13 at 16:48
    
Not directly. But you can hijack the terminal of gdalwarp to enter your ogr2ogr command in there. You get an error that the layer is not created, but it is anyway in the file system. Not sure what happens if the file already exists, so better add -overwrite too. And better use full path names for the files. –  AndreJ Oct 1 '13 at 17:08
    
Alternatively, have you ever tried using a GDAL VRT virtual file as mediator between MSSQL and QGIS? –  AndreJ Oct 2 '13 at 3:57
    
I have not, but isn't GDAL for working with rasters? I'm working with a point vector layer. I like the idea of a "virtual table" as a go-between, though. Doesn't QGIS 2 have an in-memory layer that we could write to? –  RyanDalton Oct 2 '13 at 14:16
1  
There is also a GDAL ogrvrt driver: gdal.org/ogr/drv_vrt.html. But I think this should better put into another question. –  AndreJ Oct 2 '13 at 14:57

I would suggest you to check attributes table for layer in TOC and if GlobalID field exists. Toggle to edit mode, remove the column which creates the problem during export and close the window. Then save as shapefile or whatever output format you want. I hope this helps.

share|improve this answer
1  
I'm not sure if deleting the field in the SQL Server table is a good choice. It might still be needed by the server. –  AndreJ Oct 1 '13 at 13:17
    
I think deleting an attribute column from QGIS TOC Layer save the updated structure in the QGIS buffer and allows you to export file in other format. once you finish export don't save changes to the layer (what you have deleted) –  Farhat Abbas Oct 1 '13 at 13:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.