Re: [Qgis-user] SQL Updates on virtual layer shape file attribute failes
Hi, sorry for the delay. It's advised to answer to the group so that anybody can answer you or benefits from the answers. Sure you can map the original layer and the subsets. Give a look to "Join" [0] tab in the layer properties. Once joined you can use the field calculator to fill the type field doing something like (for each subset) _case when type is null then SubsetTypeField end_ [0] http://docs.qgis.org/2.8/en/docs/user_manual/working_with_vector/vector_properties.html#joins-menu 2016-07-03 13:27 GMT+02:00 Stephan Weiser : > Hey Harrissou, > > > > thank you so much, that worked!!! Sorry for that too easy question, I am a > Newbe in QGIS and did not know the field calculator feature. > > What I can do now is Setting the type-field to the string I wish > > > > è buildingsTypeA gets type= btA > > è buildingsTypeB gets type= btB > > è buildingsTypeC gets type= btC > > > > It would be perfect if I could map the new type values of buildingsTypeA, > buildingsTypeB, buildingsTypeC to the „raw“ shapefile buildings which > contains all the shapes (A+B+C+rest). > > > > Is that possible with field calculator, too? So, I Need a kind of „set > type in layer building from layers buildingsTypeA, buildingsTypeB, > buildingsTypeC. Othewise I would have to reselect the 5000+ shapes again > based on the raw shape file. > > > > Best regards, > > > > Stephan > > > > > > *Von: *DelazJ > *Gesendet: *Sonntag, 3. Juli 2016 12:52 > *An: *Stephan Weiser > *Cc: *qgis-user@lists.osgeo.org > *Betreff: *Re: [Qgis-user] SQL Updates on virtual layer shape file > attribute failes > > > > Hi > > Stephan, why not use the Field Calculator (with a Case When statement or > an expression-based selection beforehand)? > > Harrissou > > > > > > 2016-07-03 11:25 GMT+02:00 Stephan Weiser : > > Hey there, > > > > I am using QGIS 2.14.1 (Essen). In order to create a city scenery for a > Flight Simulator, I need to process shp-files. > > The shape files I use are the shape files of open street map. For a > certain city loaded the shp file into QGIS with the name „buildings“. For > the Flight Simulator I have to define several „classes“ of buildings using > the „type“ attribute of a single shape. As the shape file I downloaded is > only poorly descripted (most „types“ are NULL), I want to overwrite the > „type“ Attribute with the proper names To make it easy for you, i have the > following shape files als QGIS virtual layers: > > > > -„buildings“ (the downloaded shp file -> original layer with all > Buildings) > > -„buildingsTypeA“ (a subset of „Buildings“ with target „type“ > „btA“) > > -„buildingsTypeB“ (a subset of „Buildings“ with target „type“ > „btB“) > > -„buildingsTypeC“ (a subset of „Buildings“ with target „type“ > „btC“) > > > > The „buildingType“ Layers have been created by selecting the proper shapes > in „Buildings“ and copying them into a new virtual layer. > > > > As every building shape of A, B and C occur in „Buildings“ I tried to > write a simple SQL update Statement like this. All the virtual layers have > the same structure (osm_id, name, type, geometry) > > > > UPDATE buildings > > SET type = 'btA‘ > > WHERE buildings.osm_id IN (SELECT osm_id from buildingsTypeA) > > > > The same should be done for buildingsTypeA and buildingsTypeA. > > > > When I exectute the Statement, I get an error: „near "update": syntax > error“. Simple SELECT Statements work. To simplify it further I also > noticed that e.g the following query resulted in the same error > > > > UPDATE Buildings > > Set type =‘abc‘ > > WHERE buildings.osm_id = '12345567‘ > > > > So my questions are: > > > > -Does the Database Manager Plugin for QGIS only Support Select > Statements (Read Only)? > > -If yes, is there a different way to edit the data in batch mode? > (round about 5000 buildings have to be updated) > > -Are there other ways to Access shp-files in a SQL-like manor to > perform that UPDATE Statements? > > > > Hope you can help me out here. > > > > Best regards, > > > > Stephan > > > > > > > > > > > > > ___ > Qgis-user mailing list > Qgis-user@lists.osgeo.org > List info: http://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user > > > > > ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] SQL Updates on virtual layer shape file attribute failes
Hi Stephan, why not use the Field Calculator (with a Case When statement or an expression-based selection beforehand)? Harrissou 2016-07-03 11:25 GMT+02:00 Stephan Weiser : > Hey there, > > > > I am using QGIS 2.14.1 (Essen). In order to create a city scenery for a > Flight Simulator, I need to process shp-files. > > The shape files I use are the shape files of open street map. For a > certain city loaded the shp file into QGIS with the name „buildings“. For > the Flight Simulator I have to define several „classes“ of buildings using > the „type“ attribute of a single shape. As the shape file I downloaded is > only poorly descripted (most „types“ are NULL), I want to overwrite the > „type“ Attribute with the proper names To make it easy for you, i have the > following shape files als QGIS virtual layers: > > > > -„buildings“ (the downloaded shp file -> original layer with all > Buildings) > > -„buildingsTypeA“ (a subset of „Buildings“ with target „type“ > „btA“) > > -„buildingsTypeB“ (a subset of „Buildings“ with target „type“ > „btB“) > > -„buildingsTypeC“ (a subset of „Buildings“ with target „type“ > „btC“) > > > > The „buildingType“ Layers have been created by selecting the proper shapes > in „Buildings“ and copying them into a new virtual layer. > > > > As every building shape of A, B and C occur in „Buildings“ I tried to > write a simple SQL update Statement like this. All the virtual layers have > the same structure (osm_id, name, type, geometry) > > > > UPDATE buildings > > SET type = 'btA‘ > > WHERE buildings.osm_id IN (SELECT osm_id from buildingsTypeA) > > > > The same should be done for buildingsTypeA and buildingsTypeA. > > > > When I exectute the Statement, I get an error: „near "update": syntax > error“. Simple SELECT Statements work. To simplify it further I also > noticed that e.g the following query resulted in the same error > > > > UPDATE Buildings > > Set type =‘abc‘ > > WHERE buildings.osm_id = '12345567‘ > > > > So my questions are: > > > > -Does the Database Manager Plugin for QGIS only Support Select > Statements (Read Only)? > > -If yes, is there a different way to edit the data in batch mode? > (round about 5000 buildings have to be updated) > > -Are there other ways to Access shp-files in a SQL-like manor to > perform that UPDATE Statements? > > > > Hope you can help me out here. > > > > Best regards, > > > > Stephan > > > > > > > > > > > > ___ > Qgis-user mailing list > Qgis-user@lists.osgeo.org > List info: http://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user > ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] SQL Updates on virtual layer shape file attribute failes
Hey there, I am using QGIS 2.14.1 (Essen). In order to create a city scenery for a Flight Simulator, I need to process shp-files. The shape files I use are the shape files of open street map. For a certain city loaded the shp file into QGIS with the name „buildings“. For the Flight Simulator I have to define several „classes“ of buildings using the „type“ attribute of a single shape. As the shape file I downloaded is only poorly descripted (most „types“ are NULL), I want to overwrite the „type“ Attribute with the proper names To make it easy for you, i have the following shape files als QGIS virtual layers: - „buildings“ (the downloaded shp file -> original layer with all Buildings) - „buildingsTypeA“ (a subset of „Buildings“ with target „type“ „btA“) - „buildingsTypeB“ (a subset of „Buildings“ with target „type“ „btB“) - „buildingsTypeC“ (a subset of „Buildings“ with target „type“ „btC“) The „buildingType“ Layers have been created by selecting the proper shapes in „Buildings“ and copying them into a new virtual layer. As every building shape of A, B and C occur in „Buildings“ I tried to write a simple SQL update Statement like this. All the virtual layers have the same structure (osm_id, name, type, geometry) UPDATE buildings SET type = 'btA‘ WHERE buildings.osm_id IN (SELECT osm_id from buildingsTypeA) The same should be done for buildingsTypeA and buildingsTypeA. When I exectute the Statement, I get an error: „near "update": syntax error“. Simple SELECT Statements work. To simplify it further I also noticed that e.g the following query resulted in the same error UPDATE Buildings Set type =‘abc‘ WHERE buildings.osm_id = '12345567‘ So my questions are: - Does the Database Manager Plugin for QGIS only Support Select Statements (Read Only)? - If yes, is there a different way to edit the data in batch mode? (round about 5000 buildings have to be updated) - Are there other ways to Access shp-files in a SQL-like manor to perform that UPDATE Statements? Hope you can help me out here. Best regards, Stephan ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
