1

I have created a view in MSSQL Server 2012 that does a spatial join as below:

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS PID, DS.Dip_No, PB.SP_GEOMETRY
FROM dbo.Dipsites AS DS INNER JOIN
  dbo.Parcel_Base AS PB ON PB.SP_GEOMETRY.STContains(DS.SP_GEOMETRY) = 1

I can view the resulting data in SQL Mgmt Studio but I cannot see it in QGIS v3.4. Is it possible to open a view in QGIS? Or am I missing something in my SQL that is required for this to happen?


@DPSSpatial, @Mapperz, @JHG I have added the following SQL now:

    SELECT DS.MI_PRINX AS OBJECTID, DS.Dip_No, PB.Pcl_num, geometry::STGeomFromText(PB.SP_GEOMETRY.STAsText(), 28356) AS GEOMETRY
FROM dbo.Dipsites AS DS INNER JOIN dbo.Parcel_Base AS PB ON PB.SP_GEOMETRY.STIntersects(DS.SP_GEOMETRY.STBuffer(200)) = 1

I have pulled a unique ID field and called it OBJECTID. I have made sure the GEOMETRY field is actually geometry. But this still won't come through to QGIS.

Any other suggestions I may be missing?

  • do you have the metadata set for the geometry table? gis.stackexchange.com/questions/80610/… – Mapperz Feb 5 at 2:58
  • Hi Mapperz, I have added the metadata into this table but still isn't visible. – J Wadsworth Feb 5 at 3:05
  • Does your view have an Object ID? PostGIS will create a row_number > gis.stackexchange.com/questions/12233/… – Mapperz Feb 5 at 3:35
  • Hi Mapperz, It doesn't. I have created a PID from the row numbers. – J Wadsworth Feb 5 at 3:45
  • 1
    On a side note, the order by clause in the over() is unreliable - it's a constant - and could lead to some unpleasant surprises like associating the wrong geometries to the table records. One should order by fields that can identify distinct (unique) records – JGH Feb 5 at 12:26
1

I have upgraded SQL Server from 2012 to 2017 and now all views are visible in QGIS. Not sure as to why this is the case, but 2017 seems to allow the access and viewing of views within QGIS.

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.