[Qgis-us-user] DBF table for SHP geo file

I am new to Qgis although I have used MapInfo extensively many years ago so I am familiar with GIS.

I was provide an application in ArcInfo SHP format and the data appears to be stored in a DBF file (Dbase)

The first column in the table is a field OBJECTID which I believe might hold the line geometric information

I created a subset of the map, Test.DBF, with just a few columns including the OBJECTID column and a columns called “merged” that I need to edit from an external application

I ATTACH the Test.dfb table using an external database (R:Base) and I can see the column data just fine, the “merged” column show all values as NULL.

When I change the value of “merged” from NULL to “Y” either via a grid or command, it changes it correctly but also changes the value of OBJECTID to NULL. When I open the map in QGIS and enable the layer “Test”, the lines still display but now the column OBJECT ID shows as NULL.

Since the value of the column OBJECTID is my link to other tables, I would prefer not to have it change to NULL.

I have tried creating an additional column, “XOBJECTID” and copying the values of OBJECTID to XOBJECTID and now, when I open the table from R:Baseand change the value of column “merged”, both, the values of OBJECTID and XOBJECTID, are changed to NULL.

It would appear that when I copy the values of OBJECTID to XOBJECTID it copies more than just the value but whatever code it has that triggers the changing to NULL of these columns.

I imagine that there is a way to copy just the value without any code from the original column to the new column where I want to save a copy of the original values

Any help you can provide would be greatly appreciated.

Best regards,

Javier Valencia, PE

14315 S. Twilight Ln.

Olathe, KS 66062

Cell: 913-915-3137

Hi Javier,

I was provide an application in ArcInfo SHP format and the data appears to be stored in a DBF file (Dbase)
Yes it is in dbf format .

The first column in the table is a field OBJECTID which I believe might hold the line geometric information

Nope. The geometry is in the *.shp file , while the *.shx is the index that connects the. Thus, editing the .dbf does not touch the geometries . So one 'shape is a file is in fact (at a minimum) the three files - the *.shp, a *.dbf and the *.shx file…

I created a subset of the map, Test.DBF, with just a few columns including the OBJECTID column and a columns called “merged” that I need to edit from an external application

That could work if you actually used a selection in QGIS to do this and have the saved that selected trifecta of the files Test.shp, Test.dbf and Test.shx . If you only created test.dbf instead that will not work. In that case recreate a new subset with those 3 files mentioned above (cerate selection then save layer (only selected) to a new shape file)

I ATTACH the Test.dfb table using an external database (R:Base) and I can see the column data just fine, the “merged” column show all values as NULL.

When I change the value of “merged” from NULL to “Y” either via a grid or command, it changes it correctly but also changes the value of OBJECTID to NULL. When I open the map in QGIS and enable the layer “Test”, the lines still display but now the column OBJECT ID shows as NULL. >>>

I am not exactly sure how you are doing that in detail but in QGIS one can not usually edit joined data from another source (like other dbf files join on an id field, or joined xls(x) Excel files or csv data that where joined. The way to do it is to either

1.) edit the data outside QGIS and then join that table to the shape file it in QGIS (use the join tab) , or

2.) join the data in QGIS and then save the entire dataset (with those joined data) to a new shape file. If going that second route one can then edit the data easily inside QGIS. One good way to edit the data in QGIS is also using the field calculator - see here e.g. https://docs.qgis.org/3.16/en/docs/user_manual/working_with_vector/attribute_table.html?highlight=calculator#using-the-field-calculator .

Cheers

Karsten

Thank you so much Karsten,

I am indeed creating the 3 files, in fact 5 files: Test.cpg, Test.dbf, Test.prj, Test.shp and Test.shx

How are the SHP and DBF file related to each other? It cannot be the OBJECTID value since I when the values is nulled it still display the line but in a different color and with the OBJECTID column as NULL. Hmmm….

What we are doing is merging smaller segment (city blocks) into larger segments (0.5 to 1 mile long) and the entire process is done on a separate database since there is a lot of programming going on to merge the segments and the data for the merged segment can be added, averaged, max or min selected or other calculations for over 100 columns/fields and all the programming to do this has already been done, we just need to display the map with segment already merged in on color and the segment to be merged on a separate color. It would take a lot of effort to reprogram it do it all in QGIS and at this time my expertise on Qgis is pretty limited…as you can tell. L

The interesting part is when I edit records, the table does take changes to some other fields but it always sets the one field to NULL. I have tried taking a copy of just the DBF file and placing it on a separate directory and making the changes to that file with the same results. Also, the segments still display but when using the information tool the OBJECTID shows as NULL. Perhaps I can use this to show the segments with the OBJECTD column set to NULL as being already used and the ones with a values as available.

I will play using MS Access to do the editing since I can easily connect to it and work with it from R:Base.

I am new to this board and looks like this response goes to the Forum and to you.

Again, thank you for the help, much appreciated.

Best regards,

Javier Valencia, PE

14315 S. Twilight Ln.

Olathe, KS 66062

Cell: 913-915-3137

From: karsten [mailto:karsten@terragis.net]
Sent: Tuesday, December 29, 2020 2:40 PM
To: javier.valencia@vtgonline.com; qgis-us-user@lists.osgeo.org
Subject: RE: [Qgis-us-user] DBF table for SHP geo file

Hi Javier,

I was provide an application in ArcInfo SHP format and the data appears to be stored in a DBF file (Dbase)

Yes it is in dbf format .

The first column in the table is a field OBJECTID which I believe might hold the line geometric information

Nope. The geometry is in the *.shp file , while the *.shx is the index that connects the. Thus, editing the .dbf does not touch the geometries . So one 'shape is a file is in fact (at a minimum) the three files - the *.shp, a *.dbf and the *.shx file…

I created a subset of the map, Test.DBF, with just a few columns including the OBJECTID column and a columns called “merged” that I need to edit from an external application

That could work if you actually used a selection in QGIS to do this and have the saved that selected trifecta of the files Test.shp, Test.dbf and Test.shx . If you only created test.dbf instead that will not work. In that case recreate a new subset with those 3 files mentioned above (cerate selection then save layer (only selected) to a new shape file)

I ATTACH the Test.dfb table using an external database (R:Base) and I can see the column data just fine, the “merged” column show all values as NULL.

When I change the value of “merged” from NULL to “Y” either via a grid or command, it changes it correctly but also changes the value of OBJECTID to NULL. When I open the map in QGIS and enable the layer “Test”, the lines still display but now the column OBJECT ID shows as NULL. >>>

I am not exactly sure how you are doing that in detail but in QGIS one can not usually edit joined data from another source (like other dbf files join on an id field, or joined xls(x) Excel files or csv data that where joined. The way to do it is to either

1.) edit the data outside QGIS and then join that table to the shape file it in QGIS (use the join tab) , or

2.) join the data in QGIS and then save the entire dataset (with those joined data) to a new shape file. If going that second route one can then edit the data easily inside QGIS. One good way to edit the data in QGIS is also using the field calculator - see here e.g. https://docs.qgis.org/3.16/en/docs/user_manual/working_with_vector/attribute_table.html?highlight=calculator#using-the-field-calculator .

Cheers

Karsten

www.terragis.net

Hi Javier,

I am indeed creating the 3 files, in fact 5 files: Test.cpg, Test.dbf,

Test.prj, Test.shp and Test.shx
ok that's good .

How are the SHP and DBF file related to each other?

The geometries are stored inside the Test.shp file, the attribute table is
in Test.dbf (including a fid column which is invisible for most software and
which should not be touched) and the Test.shx connects the two - geometry
with attributes using that fid field ...

It cannot be the OBJECTID value since I when the values is nulled it...

I am not sure what happened there in your case or how that came about.

What we are doing is merging smaller segment (city blocks) into larger

segments (0.5 to 1 mile long) ... n QGIS and at this time my expertise on
Qgis is pretty limited.as you can tell. L >>>
I don't think that any of that has to be redone it should be able to work
out fine going the correct route.

The interesting part is when I edit records, ...
I will play using MS Access to do the editing since I can easily connect

to it and work with it from R:Base.
I am not familiar with R:base and again to reiterate I am not sure what
happened in your case or how that came about.
Possibly the shape file collection (Test.cpg, Test.dbf, Test.prj, Test.shp
and Test.shx ) got corrupted somehow in the process.
Having not seen what happened might I advise you to save the origin (full)
shape file in Geopackage (*.gpkg) format instead (just to make sure all is
good and that is a much better format to use anyways with lots of advantages
above shape files) and go from there. The process would be something like
this
* take original full data shape file
* create selection of the records you nee as a subset
* save layers as -> choose to save only selected and choose Geopackage
format
* add and edit any fields you need in than geopackes using attribute table
tools or field calculator
* after being done with all edits - and only then - use the join tab of QGIS
to attach additional attributes coming from your processing procedure

I am new to this board and looks like this response goes to the Forum and

to you. Again, thank you for the help, much appreciated.
Well I am just one of the hundreds of recipients on the email list and
happen to have responded :wink:
Cheers
Karsten

Javier,
Here are a couple of links on the shapefile format.
You can also convert to geopackage, install the sqlite obdc connector , and connect to the geopackage as a ODBC source in Access.

https://en.wikipedia.org/wiki/Shapefile#:~:text=The%20shapefile%20format%20is%20a,information%20system%20(GIS)%20software.&text=The%20shapefile%20format%20can%20spatially,wells%2C%20rivers%2C%20and%20lakes.

http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf

On Tue, Dec 29, 2020 at 5:28 PM <javier.valencia@vtgonline.com> wrote:

Thank you so much Karsten,

I am indeed creating the 3 files, in fact 5 files: Test.cpg, Test.dbf, Test.prj, Test.shp and Test.shx

How are the SHP and DBF file related to each other? It cannot be the OBJECTID value since I when the values is nulled it still display the line but in a different color and with the OBJECTID column as NULL. Hmmm….

What we are doing is merging smaller segment (city blocks) into larger segments (0.5 to 1 mile long) and the entire process is done on a separate database since there is a lot of programming going on to merge the segments and the data for the merged segment can be added, averaged, max or min selected or other calculations for over 100 columns/fields and all the programming to do this has already been done, we just need to display the map with segment already merged in on color and the segment to be merged on a separate color. It would take a lot of effort to reprogram it do it all in QGIS and at this time my expertise on Qgis is pretty limited…as you can tell. L

The interesting part is when I edit records, the table does take changes to some other fields but it always sets the one field to NULL. I have tried taking a copy of just the DBF file and placing it on a separate directory and making the changes to that file with the same results. Also, the segments still display but when using the information tool the OBJECTID shows as NULL. Perhaps I can use this to show the segments with the OBJECTD column set to NULL as being already used and the ones with a values as available.

I will play using MS Access to do the editing since I can easily connect to it and work with it from R:Base.

I am new to this board and looks like this response goes to the Forum and to you.

Again, thank you for the help, much appreciated.

Best regards,

Javier Valencia, PE

14315 S. Twilight Ln.

Olathe, KS 66062

Cell: 913-915-3137

From: karsten [mailto:karsten@terragis.net]
Sent: Tuesday, December 29, 2020 2:40 PM
To: javier.valencia@vtgonline.com; qgis-us-user@lists.osgeo.org
Subject: RE: [Qgis-us-user] DBF table for SHP geo file

Hi Javier,

I was provide an application in ArcInfo SHP format and the data appears to be stored in a DBF file (Dbase)

Yes it is in dbf format .

The first column in the table is a field OBJECTID which I believe might hold the line geometric information

Nope. The geometry is in the *.shp file , while the *.shx is the index that connects the. Thus, editing the .dbf does not touch the geometries . So one 'shape is a file is in fact (at a minimum) the three files - the *.shp, a *.dbf and the *.shx file…

I created a subset of the map, Test.DBF, with just a few columns including the OBJECTID column and a columns called “merged” that I need to edit from an external application

That could work if you actually used a selection in QGIS to do this and have the saved that selected trifecta of the files Test.shp, Test.dbf and Test.shx . If you only created test.dbf instead that will not work. In that case recreate a new subset with those 3 files mentioned above (cerate selection then save layer (only selected) to a new shape file)

I ATTACH the Test.dfb table using an external database (R:Base) and I can see the column data just fine, the “merged” column show all values as NULL.

When I change the value of “merged” from NULL to “Y” either via a grid or command, it changes it correctly but also changes the value of OBJECTID to NULL. When I open the map in QGIS and enable the layer “Test”, the lines still display but now the column OBJECT ID shows as NULL. >>>

I am not exactly sure how you are doing that in detail but in QGIS one can not usually edit joined data from another source (like other dbf files join on an id field, or joined xls(x) Excel files or csv data that where joined. The way to do it is to either

1.) edit the data outside QGIS and then join that table to the shape file it in QGIS (use the join tab) , or

2.) join the data in QGIS and then save the entire dataset (with those joined data) to a new shape file. If going that second route one can then edit the data easily inside QGIS. One good way to edit the data in QGIS is also using the field calculator - see here e.g. https://docs.qgis.org/3.16/en/docs/user_manual/working_with_vector/attribute_table.html?highlight=calculator#using-the-field-calculator .

Cheers

Karsten

www.terragis.net


Qgis-us-user mailing list
Qgis-us-user@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/qgis-us-user