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?