Using a sql view of another view to generate points on the fly for the past day of phone calls

bsanders69 — Thu, 07 Jul 2022 20:38:51 GMT

I have joined another database that gives us a limited schema of emergency calls, but is in a state of constant growth. My goal is to get those calls statistics mapped and into a Dashboard. I created a view of another view to generate point geometries of the past 24 hours, but I am not having much success getting the view into Pro to generate a map.

First view (selects and manipulates a running day of calls):

ALTER VIEW [sdeadmin].[FCALLS_w_LAT_LONG_vw]
AS
SELECT [CALL_NO]
,[CALL_TYPE_FINAL_D]
,[LOCATION]
,[LOCATION_ADDRESS]
,[APARTMENT]
,[BEAT] AS [STATION]
,[PRIMARY_UNIT]
,[XCOORD]
,[YCOORD]
,[CALL_CREATED_DATE]
,SUBSTRING([CALL_CREATED_TIME], 1, 2) + ':' + SUBSTRING([CALL_CREATED_TIME], 3, 2) + ':' + SUBSTRING([CALL_CREATED_TIME], 5, 2) AS [CALL_CREATED_TIME]
,SUBSTRING([CALL_DISPATCH_TIME], 1, 2) + ':' + SUBSTRING([CALL_DISPATCH_TIME], 3, 2) + ':' + SUBSTRING([CALL_DISPATCH_TIME], 5, 2) AS [CALL_DISPATCH_TIME]
,SUBSTRING([CALL_ENROUTE_TIME], 1, 2) + ':' + SUBSTRING([CALL_ENROUTE_TIME], 3, 2) + ':' + SUBSTRING([CALL_ENROUTE_TIME], 5, 2) AS [CALL_ENROUTE_TIME]
,SUBSTRING([CALL_ONSCENE_TIME], 1, 2) + ':' + SUBSTRING([CALL_ONSCENE_TIME], 3, 2) + ':' + SUBSTRING([CALL_ONSCENE_TIME], 5, 2) AS [CALL_ONSCENE_TIME]
,SUBSTRING([CALL_CLOSE_TIME], 1, 2) + ':' + SUBSTRING([CALL_CLOSE_TIME], 3, 2) + ':' + SUBSTRING([CALL_CLOSE_TIME], 5, 2) AS [CALL_CLOSE_TIME]
,CAST(REPLACE([XCOORD],',','') AS FLOAT) AS LONG
,CAST(REPLACE([YCOORD],',','') AS FLOAT) AS LAT
,GETDATE() - 1 as Date_Time
FROM [TIBCADWH\Tiburon].[DWLIVE].[dbo].[FCalls]
WHERE [CALL_TYPE_FINAL_D] NOT IN ('ADMIN STATUS', 'Training') AND [CALL_CREATED_DATE] + CAST(SUBSTRING([CALL_CREATED_TIME], 1, 2) + ':' + SUBSTRING([CALL_CREATED_TIME], 3, 2) + ':' + SUBSTRING([CALL_CREATED_TIME], 5, 2) AS DATETIME) >= DATEADD(DAY,-1, CAST(GETDATE() AS DATE))

Second View (pulls final fields, generates OBJECTID from the Call ID that I need to strip out first character in the call number field (unique) to make a numeric value and then create the point geometry:

ALTER View [sdeadmin].[FCALLS_w_Shape_vw]
AS
SELECT CAST(SUBSTRING([CALL_NO],2,10) AS INT) as OBJECTID
,CALL_NO, CALL_TYPE_FINAL_D, LOCATION, LOCATION_ADDRESS, APARTMENT, STATION, PRIMARY_UNIT, XCOORD, YCOORD, CALL_CREATED_DATE, CALL_CREATED_TIME, CALL_DISPATCH_TIME, CALL_ENROUTE_TIME
,CALL_ONSCENE_TIME, CALL_CLOSE_TIME, LONG, LAT, Date_Time
,geography::Point(LAT, LONG, 4326) AS SHAPE

FROM [sdeadmin].[FCALLS_w_LAT_LONG_vw]
GO

This does create geometries in SSMS and imports into Arc Pro (Enterprise 10.9.1, Pro 2.9.3) but does not draw features or open the table without an error: Underlying DBMS error converting data type varchar to float.

topic Using a sql view of another view to generate points on the fly for the past day of phone calls in Data Management Questions

Using a sql view of another view to generate points on the fly for the past day of phone calls