Bug report #5227

Can't read linstring table from new MSSQL provider

Added by Jeremy Palmer over 13 years ago. Updated over 13 years ago.

Status:Closed
Priority:Normal
Assignee:Tamas Szekeres
Category:Data Provider
Affected QGIS version:master Regression?:No
Operating System:winxp Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:14963

Description

I can't seem to read any line-string spatial tables from MSSQL 2008 R2

linestring_test_data.7z (107 KB) Jeremy Palmer, 2012-03-24 08:02 PM

Associated revisions

Revision 51011559
Added by Tamas Szekeres over 13 years ago

Improve handling of estimated metadata (fixes #5227 fixes #5252)

History

#1 Updated by Nathan Woodrow over 13 years ago

Do you get any errors?

Can you open line and polygon tables correctly?

Are you able to supply and sample set so I can test it.

#2 Updated by Sergey M over 13 years ago

The same problem

SQL:

SELECT *
INTO test
FROM
(    
    SELECT 1 ID, geometry::Parse('LINESTRING (37.5213796 47.1254473, 37.5234009 47.1251464, 37.5236238 47.1251132)') Geo
    UNION ALL
    SELECT 2, geometry::Parse('LINESTRING (37.5131171 47.1227004, 37.5119065 47.122804, 37.5114139 47.1229543, 37.5108024 47.1232576, 37.5101528 47.1235379, 37.5097069 47.1237922, 37.5088067 47.12424, 37.5074309 47.1249825, 37.5073969 47.1250345, 37.5073969 47.1251154, 37.5074691 47.1251963, 37.5102037 47.1279814, 37.5103055 47.1281257)')
) q

INSERT INTO [geometry_columns]
           ([f_table_catalog]
           ,[f_table_schema]
           ,[f_table_name]
           ,[f_geometry_column]
           ,[coord_dimension]
           ,[srid]
           ,[geometry_type])
     VALUES
           ('mydb'
           ,'dbo'
           ,'test'
           ,'Geo'
           ,2
           ,4326
           ,'LINESTRING')

Connection:

<!DOCTYPE connections>
<qgsMssqlConnections version="1.0">
<mssql port="1433" saveUsername="false" savePassword="false" sslmode="1" service="" host="localhost" database="mydb" name="test" estimatedMetadata="true"/>
</qgsMssqlConnections>

error message:

---------------------------
Invalid Layer
---------------------------
dbname='mydb;tables=test(Geo)' host=localhost srid=4326 type=Line table="dbo"."test" (Geo) sql= is an invalid layer and cannot be loaded.
---------------------------
OK 
---------------------------

#3 Updated by Jeremy Palmer over 13 years ago

I can connect to the server and list the tables. But as per Sergey M, I get an invalid layer error message when I try to add the layer. I have attached the table I'm having trouble with, but test case above is probably easier for you to load.

Cheers,
Jeremy

#4 Updated by Nathan Woodrow over 13 years ago

Seems to be related to a primary key issue. Looking into it now.

#5 Updated by Nathan Woodrow over 13 years ago

  • Target version set to Version 1.8.0
  • Category set to Data Provider

Ok this is related to the int column not being a primary key and also not being a identity spec.

Create the table with both those things and it opens correctly.

I'll look at getting them fixed. Really shouldn't need a identity spec on the column.

#6 Updated by Sergey M over 13 years ago

Yes, if you specify a primary key and identity - a layer is added. Thank.

#7 Updated by Nathan Woodrow over 13 years ago

  • Assignee changed from Nathan Woodrow to Tamas Szekeres

#8 Updated by Tamas Szekeres over 13 years ago

  • Assignee changed from Tamas Szekeres to Nathan Woodrow
  • Resolution set to fixed

should be working for now

#9 Updated by Jeremy Palmer over 13 years ago

  • Assignee changed from Nathan Woodrow to Tamas Szekeres
  • Resolution deleted (fixed)

HI Tamas,

Thanks for the fix on the PK.

I still seems to be having problem with this linestring dataset (http://creeping.orconhosting.net.nz/qgis/roads.7z). The layer loads into the map ok but the provider extents are corrupted. e.g.

Extents:
In layer spatial reference system units : xMin,yMin -179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00,-179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00 : xMax,yMax 179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00,179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00

Then if I create a spatial index no data is selected because the index BOUNDING_BOX is set to incorrect values.

#10 Updated by Tamas Szekeres over 13 years ago

Fixed in :szekerest/Quantum-GIS.git

#11 Updated by Tamas Szekeres over 13 years ago

  • Status changed from Open to Closed

Also available in: Atom PDF