2

I am working with a replica database managed in Sql Server Management Studio 2016. My geographic data of points, lines, and polygons are projected in WGS_1984_UTM_Zone_17N Meter and SRID of 32617. Its geographic system is GCS_WGS_1984 Degree and SRID 4326. Everyone of my feature classes contains a latitude and longitude field; however, all of my feature classes are geometry types. I'm in the process of developing triggers to auto-populate several fields but the lat/longs are giving me trouble.

Does anyone know of a way to project/transform geometry data into a geography variable where a lat/long can be extracted? Are there other ways to do get this information that are not accomplished within the ArcGIS applications?

The trigger I'm developing on my A-delta table shows that I am stumped after calling the x and y since I don't know how to get the lat long.

USE [DATABASE]
GO
/****** Object:  Trigger [dbo].[AreaCalc_Insert]    Script Date: 2/13/2018     12:52:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[AreaCalc_Insert]
ON [dbo].[a101]
FOR INSERT
AS

BEGIN
DECLARE @areaCalc decimal(10, 2);
SET @areaCalc = 10.7639;
DECLARE @Lftfrommeter decimal(38, 8);
SET @Lftfrommeter = 0.3048;
DECLARE @AreaUOM nvarchar(20);
SET @AreaUOM = 'squareFoot';
DECLARE @perimUOM nvarchar(25);
SET @perimUOM = 'USsurveyFoot';
DECLARE @strt nvarchar(20);
SET @strt = '00010000000';
DECLARE @fstr nvarchar(20);
DECLARE @oid int, @IDPK int, @total int;
DECLARE @X float, @Y float;

SET NOCOUNT ON;

UPDATE dbo.a101
    SET @X = shape.STCentroid().STX,
        @Y = shape.STCentroid().STY,
        @oid = OBJECTID,
        @IDPK = DATALENGTH(@oid),
        @total = 20 - @IDPK,
        @fstr = LEFT(@strt, @total) + CAST(@oid as nvarchar(20)),
        buildingIDPK = @fstr,
        onInstallation = 'yes',
        installationID = '%%%%',
        installationName = '&&&&',
        siteID = '&&&&0001',
        realPropertySiteUniqueID = '4949',
        majorCommand = '!!!!',
        wacInnrCode = '76',
        country = 'US',
        owner = '****',
        areaSizeUOM = @AreaUOM,
        perimeterSizeUOM = @perimUOM,
        perimeterSize = Round((Shape.STLength() * @Lftfrommeter), 0),
        areaSize = Round((Shape.STArea() * @areaCalc), 0)
    WHERE OBJECTID IN (SELECT OBJECTID FROM INSERTED)

update dbo.BUILDING_A
    SET @oid = OBJECTID,
        @IDPK = DATALENGTH(@oid),
        @total = 20 - @IDPK,
        @fstr = LEFT(@strt, @total) + CAST(@oid as nvarchar(20)),
        buildingIDPK = @fstr,
        onInstallation = 'yes',
        installationID = '%%%%',
        installationName = '&&&&',
        siteID = '&&&&0001',
        realPropertySiteUniqueID = '4949',
        majorCommand = '!!!!',
        wacInnrCode = '76',
        country = 'US',
        owner = '****',
        areaSizeUOM = @AreaUOM,
        perimeterSizeUOM = @perimUOM,
        perimeterSize = Round((Shape.STLength() * @Lftfrommeter), 0),
        areaSize = Round((Shape.STArea() * @areaCalc), 0)
    WHERE OBJECTID IN (SELECT OBJECTID FROM INSERTED)

END
  • In our experience, MSSQL server is not a place to maintain geographic data, only a place to use it - so we use PostGIS and ETL the spatial data to MSSQL on a daily basis, delivering it in the format that our MSSQL analysts require, without having to worry about doing any kind of transformation hack within MSSQL. – DPSSpatial Feb 14 '18 at 20:30
  • 1
    Unforunately SQL Server has no way to project/unproject spatial data. There is set of tools that can be implemented as CLR's to do this though. I'm not a programmer, so I can't help much, other than pointing you towards them SQL Server Spatial Tools – MickyT Feb 14 '18 at 21:37
  • You're using a CRS with an Esri-style name. Do you have access to Esri software? ST_transform will convert data between coordinate reference systems. – mkennedy Feb 14 '18 at 22:02
  • I do have ArcGIS desktop 10.5.1 with an enterprise level license on this system. How could I implement ST_Transform? I noticed that SQL Server Spatial Tools are quite old and the read_me file refers to a dll file that is not part of the package on GitHub. – KWoosley Feb 15 '18 at 12:21

Your Answer

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

Browse other questions tagged or ask your own question.