0

This is odd - I've successfully connected to my spatial sql server 2014 instance, and loaded two point layers and one Polygon layer into QGIS. I see nothing on the map. I zoom to the layer, I know it goes to the right location and zoom level. I use the Info option, click the map, and it doesn't find anything. Very puzzling. The Style appears to be fine. I don't know what else to check. All looks good configuration wise - I just don't see anything.

I then selected each of the three layers one at a time, and used the Layer drop down menu, SaveAs... and saved the layer as a shape file at my local computer.

When I load the three Shape files that I've just created by saving the SQL Server layers as shape files using QGIS, these shape files display and act perfectly.

Has anyone else experienced this? I don't know why I cannot successfully work with SQL Server 2014 spatial layers.

For the record, all my layers use the EPSG:2234 CT State Plane CRS, in case that may be relevant. The geometries in the database do specify this CRS, and QGIS displays this as well, and it is set correctly in the saved shape files.

  • did you load geometry or geography? docs.microsoft.com/en-us/sql/relational-databases/spatial/… – Mapperz Oct 4 '19 at 18:12
  • the geom column is a Geometry type – Brian B Oct 4 '19 at 18:17
  • Double check CRS! Zoom to layer, open the attribute table, open a query with the database manager, to investigate further. Indeed it's odd. – Andreas Müller Oct 5 '19 at 9:11
  • From "Properties" of the SQL layer: Geometry: Point (Point) CRS: EPSG:2234 - NAD83 / Connecticut (ftUS) - Projected From properties of the generated shape file: Geometry: Point (Point) CRS: EPSG:2234 - NAD83 / Connecticut (ftUS) - Projected – Brian B Oct 16 '19 at 16:23
0

I figured it out. I'm not sure which of these was required, one or both, but when I created and filled in properly the spacial_ref_sys and geometry_columns tables, all worked perfectly well!

For the spatial_ref_sys, which I think QGIS created for me, but was empty, I went to url https://spatialreference.org/ref/epsg/2234/ then clicked on the "PostGIS spatial_ref_sys INSERT statement" link, copied the insert statement and ran it. The geometry_columns was pretty straight forward as well.

So this may or may not be in the QGIS doc for loading layers from a Microsoft SQL Server spatial database, I'm not certain, but should anyone else experience something similar, make sure you have any custom SRID data in the spatial_ref_sys table, and that your DB with the spatial tables contains a properly filled in geometry_columns table for any spatial tables you want QGIS to load.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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