Displaying a SQL view with Lat/Longs

[Anonymous User]

Here's the scenario:

I have two tables in a SQl database, one with lat/longs and the other with data I want to see.

I have created a view in SQL that shows what we need, but how do we view that in ArcGIS? We do not want to create a new table from xy data (static) and we need the data to update when the users and another record. Hence the view.

[JoshuaBixby]

What datastore/database did you create the view in?

[Anonymous User]

The view is in a SQL 2016 database,

[JoshuaBixby]

Is this an enterprise geodatabase or just regular database?  Either way, it starts with Create Database Connection (Data Management)—ArcGIS Pro | Documentation 

[Anonymous User]

It is a regular SQL database and I have the database connection set, among many others and I can add the view to a map, but it is not spatial just a table. I am trying to view the data using the lat longs without creating a feature class.

something like a spatial view

[JoshuaBixby]

A couple options.  One is Make XY Event Layer (Data Management)—ArcGIS Pro | Documentation .  Another is to store the data in native SQL Server spatial type (GEOMETRY or GEOGRAPHY) in additional to storing as non-spatial lat/long.

[Anonymous User]

That is where I am at. A XY Event Layer works ok, but I don think that will update when a new record is entered into the underlying table. I do not know how to add a geometry data type to the underlying table.

[George_Thompson]

You may want to look at something like this: Create a geography/geometry column from x and y fields - SQL Server 2008 - Geographic Information Sy...  (this worked on my SQL 2017 install). You could then set up a trigger in SQL server if you wanted.

You want to also make sure that there are no "<NULL>" fields in the XY or they may be ignored.

[CraigSwadner]

Not sure if this is what you are looking for however I have done this in the past.

ALTER VIEW .[MOBILE_GPS_LOCATIONS_LAT_LON]

AS

SELECT gis.GPS_LOCATION_DATA.OBJECTID, gis.GPS_LOCATION_DATA.SERNO, gis.GPS_LOCATION_DATA.SEQNO,

(SELECT Description

FROM htetables.dbo.Dart2_Reason_Codes

WHERE (R_Val = gis.GPS_LOCATION_DATA.REASON)) AS Reason, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, gis.GPS_LOCATION_DATA.DATEUTC), DATENAME(TzOffset, SYSDATETIMEOFFSET())))

AS CC_Date, CAST(REPLACE(CAST(CONVERT(time, SWITCHOFFSET(CONVERT(datetimeoffset, gis.GPS_LOCATION_DATA.DATEUTC), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS Varchar(5)), ':', '') AS int) AS CC_Time,

gis.GPS_LOCATION_DATA.DATEUTC, gis.GPS_LOCATION_DATA.FIELDS_LAT, gis.GPS_LOCATION_DATA.FIELDS_LONG, CONVERT(int, gis.GPS_LOCATION_DATA.FIELDS_ALT * 3.28084) AS FIELDS_ALT, CONVERT(int,

gis.GPS_LOCATION_DATA.FIELDS_SPD * 0.0223694) AS FIELDS_SPD, gis.GPS_LOCATION_DATA.FIELDS_SPDACC, gis.GPS_LOCATION_DATA.FIELDS_DEVSTAT, htetables.dbo.Faster_Assets.DepartmentDesc,

htetables.dbo.Faster_Assets.Model, htetables.dbo.Faster_Assets.Make, htetables.dbo.Faster_Assets.AssetNumber, htetables.dbo.Faster_Assets.AssetComment, htetables.dbo.Faster_Assets.AssetDepartment,

htetables.dbo.Faster_Assets.AssetStatus, htetables.dbo.Faster_Assets.License, htetables.dbo.Faster_Assets.Location, htetables.dbo.Faster_Assets.SerialNumber, htetables.dbo.Faster_Assets.Year,

htetables.dbo.Faster_Assets.FleetNumber, gis.GPS_LOCATION_DATA.UNITCODE, gis.GPS_LOCATION_DATA.PSDEPT,

CASE WHEN PSDEPT = 'LAW' THEN 'Police' WHEN PSDEPT = 'FIRE' THEN 'Fire' WHEN DepartmentDesc LIKE '%Utilities%' AND departmentdesc NOT IN ('40100 Utilities Field Service')

THEN 'Utilities' WHEN DepartmentDesc LIKE '%Public Works%' THEN 'Public Works' WHEN DepartmentDesc LIKE '%ITS%' THEN 'Information Technology' WHEN DepartmentDesc LIKE '%parks%' THEN 'Parks' WHEN DepartmentDesc

LIKE '%DCD%' THEN 'DCD' WHEN DepartmentDesc LIKE '%police%' THEN 'Police' WHEN departmentdesc LIKE '%city clerk%' THEN 'City Clerk' WHEN departmentdesc LIKE '%EOC%' THEN 'Fire' WHEN departmentdesc LIKE '% PW %'

THEN 'Public Works' WHEN departmentdesc LIKE '%city manager%' THEN 'City Manager' WHEN departmentdesc LIKE '%finance%' THEN 'Finance' WHEN departmentdesc LIKE '%Utilities Field Service%' THEN 'Utilities Customer Service'

END AS Department, CASE WHEN reason IN (1, 3, 4, 5, 6, 7, 12, 13, 14, 15, 16) THEN 'In Motion' WHEN REASON IN (11) THEN 'Ignition Off Vehicle Dormant' WHEN REASON IN (2, 8, 9, 10) THEN 'Stationary' WHEN reason IN (20)

THEN 'Towing Alert' END AS Vehicle_Status, geometry::STGeomFromText('POINT(' + CAST(CAST(gis.GPS_LOCATION_DATA.FIELDS_LONG AS decimal(15, 9)) AS varchar)

+ ' ' + CAST(CAST(gis.GPS_LOCATION_DATA.FIELDS_LAT AS decimal(15, 9)) AS varchar) + ')', 4326) AS shape

FROM gis.GPS_LOCATION_DATA WITH (nolock) LEFT OUTER JOIN

htetables.dbo.Faster_Assets ON gis.GPS_LOCATION_DATA.SERNO = htetables.dbo.Faster_Assets.GPS_Serno

[BrianOevermann · Accepted solution]

We've successfully created various views from our permitting system that, ahem, permit me to add those tables as query layers (points) in ArcGIS.

Full credit for this code goes to our database admin guru whom works with our permitting and other databases--all SQL Server-based.

Here is his SQL code snippet:

SELECT

geography::Point(CASE ISNULL(geo.LAT,0)

                     WHEN 0 THEN '47.56580'

                           ELSE geo.LAT END,

                CASE ISNULL(geo.LON,0)

                     WHEN 0 THEN '-122.07409'

                           ELSE geo.LON END

                , 4326)  AS GeomPoint

FROM dbo.. etc etc

The basic syntax is:

geography::Point(lat,long,4326) with '4326' being the WKID of the coordinate system.

FYI, geo.LAT and geo.LON are the field names for latitude/longitude in our permitting database. The CASE statement 'hard codes' a coordinate for any records that don't have a lat/long and puts them in the middle of a lake adjacent to our City. We chose the middle of the lake so it is obviously not an explicit location and because these are permits that have multiple locations throughout the City--just one example might be a special event permit for a 'fun run' that has a route winding through the City. On our internal viewers showing these permits I also have a square polygon container at the location in the lake to draw attention to the 'bucket' of non-explicitly located permits and indicate that the point(s) in the lake are not an error.

Edit: I'll add, also, that when creating a query layer in ArcGIS you will need a uniqueID field (integer--think about it as the ObjectID). Your view may already contain a suitable field. If not, you will need to create something to use. Our permit guru has sometimes gotten creative by morphing, say, the building permit (both alpha and numeric characters) into an integer field by substituting an integer 'code' for the alpha permit type portion. Sorry, no code available to share on this...