Bug report #12924

Sql Server Point Layer

Added by David Vanescentes over 5 years ago. Updated over 5 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:-
Affected QGIS version:2.8.2 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:invalid
Crashes QGIS or corrupts data:No Copied to github as #:21007

Description

I just downloaded and installed QGIS a few days ago (version 2.8.2-Wien). I tried pulling in spatial data from sql server (a "geometry" column containing points...used SRID 4326). I click the button that says "Add MSSQL Spatial Layer" and find my points table but when I click the "Add" button it gives me this error (after a minute):
dbname='myDb' host=localhost\\sqlserver2014 srid=4326 type=POINT table="dbo"."Locations_Sample"
(spatial_data_point) sql= is an invalid layer and cannot be loaded. Please check the message log for further info.

The "log message" words in that error were a link in QGIS so I clicked it and it opened a "Log Messages" panel and on that panel (on the "General" tab), it just had the same error message I typed above (with one inconsequential text difference... it says "is an invalid layer - not loaded" instead of "and cannot be loaded").

I tried making that source table ("Locations_Sample") smaller (it was around 120,000 points so I tried making it just 100 points/rows) and that smaller table imported with no problems. I then tried increasing the amount of data in that table and hitting the "Refresh" button in QGIS to find what the row limit was but it succeeded in refreshing every time I tried all the way up to the full 120,000 rows (showing all the points on the map as far as I can tell). This may seem like a workaround but I don't think it really is because every time I close and re-open the project, it errors when trying to load that layer...also other things (like the heatmap raster generator) are failing possibly due to this "invalid layer" issue.

FYI, I subsequently tried loading the table with 100 records at first and then putting all the records in the source table "Locations_Sample" and that always worked (so you don't have to "ramp up" to the full table, you can load it all once it successfully loads the smaller version of that table).

I made sure that all points were between -90 and 90 latitude (not inclusive) and between -180 and 180 longitude (not inclusive). In sql server the STIsValid function indicates that all points are valid ("instance is well-formed, based on its Open Geospatial Consortium (OGC) type"). The following sql returns a count of zero (no invalid points):
SELECT COUNT(*) FROM [dbo].[Locations] WHERE [spatial_data_point].STIsValid()=0

History

#1 Updated by Nathan Woodrow over 5 years ago

Do you have a int based primary key on the table?

#2 Updated by Nathan Woodrow over 5 years ago

  • Status changed from Open to Feedback

#3 Updated by David Vanescentes over 5 years ago

Nathan Woodrow wrote:

Do you have a int based primary key on the table?

I do not (the first column is a unique int but it is not defined as a primary key). I will try doing that now and let you know what happens. By the way, I also just tried this on a development version (558f0b2) and the issue occurs with that the same way as 2.8.2.

#4 Updated by David Vanescentes over 5 years ago

David Vanescentes wrote:

Nathan Woodrow wrote:

Do you have a int based primary key on the table?

I do not (the first column is a unique int but it is not defined as a primary key). I will try doing that now and let you know what happens. By the way, I also just tried this on a development version (558f0b2) and the issue occurs with that the same way as 2.8.2.

It turns out I was wrong, I forgot that the int column in this table was not unique (which I discovered when I tried to make it a primary key). I added a unique int column and tried loading the full table and it succeeded (I did need to not define it as an actual primary key). Thanks for your help on this!

Should I close this issue? I guess it is more of a feature request now to have the error message say something about requiring a primary key (or a unique int column at least).

#5 Updated by Nathan Woodrow over 5 years ago

  • Resolution set to invalid
  • Status changed from Feedback to Closed

Closing this request but you can open another one about better error messages for this case (assign it to me, it's on my todo list anyway)

Also available in: Atom PDF