• Blog posts

    Bio

    By David Crowther

    QGIS___Only_Showing_One_Attribute_Record_-_1.PNG

    Question:

    When I open my spatial data from a SQL Server Spatial Database, and I view the data in the Attribute Table why is there only 1 record?.... even though the table has multiple geometry features in the map window.

    Answer:

    This issue occurs when your source SQL Table does not have an INTEGER column with a  unique value.

     For example, here we have a SQL Table of Air Field Extents which has 5 records with 5 different geometries, but there is no column containing a unique value in INTEGER format.

    QGIS___Only_Showing_One_Attribute_Record_-_2.PNG

    QGIS___Only_Showing_One_Attribute_Record_-_3.PNG

    When viewed in QGIS the 5 geometry features are shown in the map:

    QGIS___Only_Showing_One_Attribute_Record_-_4.PNG

    If we use the Identify Features tool we get individual records back for each feature that we click on:

    QGIS___Only_Showing_One_Attribute_Record_-_5.PNG

    However, if you use the Select tool and click on a map feature all objects are selected:

    QGIS___Only_Showing_One_Attribute_Record_-_6.PNG

    And if you Browse the Table, the Attribute window states there are 5 records , but 1 is filtered and 1 is selected:

    QGIS___Only_Showing_One_Attribute_Record_-_7.PNG

    This issue is caused as QGIS requires at least one unique column in Integer format in the source SQL Table – Note: this does not have to be set as the Primary Key.

    So to resolve this, simply update your SQL table with a ROW ID using this query:

    ALTER TABLE [dbo].[YOUR_TABLENAME]

    ADD [ROWID] INT IDENTITY(1,1)

    If you remove the layer from QGIS, refresh the Connection to SQL and re-load the SQL Table, the layer will now allow you to select the individual geometry in the map and show all records in the Attribute Table.

    QGIS___Only_Showing_One_Attribute_Record_-_8.PNG