3

I started using QGIS a few weeks ago. I've been able to create some very nice maps with it. I've used the OpenLayers plugins to bring in different basemaps, I've brought in shapefiles and connected to our local WMS server.

But, when I connect to our local SQL server, I can bring in shapefiles, but they don't show up. I can see all the data associated with them if I look at the properties table. QGIS even gives me the item count, if I ask for it.

Does anyone know of a reason why I wouldn't be able to see the geometry in my maps and what I might do to fix it?

Thanks.

QGIS 2.0.1

Regarding coordinate system: The SQL database has everything in three different projections, SRID 3078, SRID 4326 and SRID 3857 (I don't know why). I chose one set and tried each projection. In each case, the data I brought in was the only layer in the project and the project CRS was set to match the layer CRS. Only the data from SRID 3078 was visible.

I can't see the database directly, so I don't know if there's a geometry column. Looking at all three datasets, each shows a feature count of 915. I can do a query (set filter) and the same query for all three results in 116. Nothing looks different from one to another. It's just that two of the three don't show up in the map.

I should also note that I had our IT guy who's gathered all this data and put it in the SQL database create a shapefile from one of the datasets that I needed and couldn't read. That showed up fine, too.

Improve this question
3
  • 1
    What version of QGIS are you using?
    – DPierce
    Oct 17, 2013 at 20:52
  • 1
    Is your MSSQL data in the same projection and coordinate system as the OpenLayers data? Have a looking in the meta data section of the layer properties. Also try right-clicking on the layer and choosing Zoom to layer extent (or selecting some of the rows you can see in the attribute table and zooming to selection). If you jump to somewhere where you can see the geometry but not the baselayer, then you maybe need to tell QGIS what the correct SRS is
    – MappaGnosis
    Oct 17, 2013 at 21:11
  • You could check your geometry, too. If you have invalid geometry, QGIS can do funny stuff...
    – Alex Leith
    May 28, 2014 at 23:59

2 Answers 2

Reset to default
3

I had this problem before and it was down to incorrect metadata. How did you create the spatial tables in MSSQL in the first place? Is there a GEOMETRY_COLUMNS table in your db? I think QGIS uses this table to read the spatial metadata. In my case the spatial column name was incorrect and could not see the data. But of course it can be something very simple like incorrect SRS as @MappaGnosis suggested!

Improve this answer
4
  • I didn't create the spatial tables. I have nothing to do with and no influence or control over it.
    – Bill
    Dec 2, 2013 at 11:11
  • 1
    Has there been any more progress towards a resolution on this? I am in a similar situation. I uploaded a tab file into a SQL database using Easy Loader in MapInfo. I overwrote an existing table.(the way all my tables have been loaded). I can see the new table in the database list. When I add it to my canvas, it's not there. I have Zoomed to Layer, I can see the attributes table. There is geometry in the table (I can copy and paste the table contents into Excel). I can see the SQL table in MapInfo. It's only in QGIS that I can't see it. I don't have any issues with any other table.
    – Johanna
    Feb 18, 2015 at 23:54
  • I have found that my issues seem to be related to Windows and differences in 32bit and 64bit ODBC drivers. When I tried to reproduce the error for someone a few days later, I could not. The only difference that I could tell was that there had been some Windows Updates.
    – Johanna
    Feb 20, 2015 at 5:32
  • +1 it uses GEOMETRY_COLUMNS table along with geometry table and column mapping.
    – Anupam Singh
    Jan 29, 2016 at 10:11
0

In spatial MSSQL (when it works) you dont need geometry_columns table (just take that option of in qgis) but atleast i had to have gid as identity column in spatial table. MSSQL doesn't care about your srid so you can use any integer when using geometry type, so dont panic if you dont find your srid in MSSQL sys.spatial_ref_systems table. Georaphy type is another case.

Easiest way to check if data really is in db , is you SMMS and query table, there is spatial_data tab in query results.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.