How to use GEOGRAPHY or GEOMETRY function in a SQL for Register as Geodatabase tool

[MatthewTsui5]

Hi Esri Community,

Does anybody recommend how to convert latitude and longitude columns using the GEOGRAPHY and GEOMETRY functions in a SQL view, which will source a Registered Feature Class within an Enterprise Geodatabase?

Here is my workflow:

1. Create SQL view in SSMS in SQL Server 2019 database. Tables are in another SQL Server 2019 database.

2. In ArcGIS Pro 2.9.5, use Create Database View tool 

3. Use Register with Geodatabase (Database Management) tool?

Unfortunately, I receive the error message: "ERROR 160236: The operation is not supported by this implementation."

My troubleshooting narrowed the issue down to the Shape Field paramter rin the Register with Geodatabase tool. This column is sourced from the geometry SQL function., which was created in the SQL VIEW. tried 2 SQL function:

• geography::Point(CORE.global.ProfitCenters.Latitude, CORE.global.ProfitCenters.Longitude, 4326) AS geographypoint, geometry::Point(CORE.global.ProfitCenters.Latitude, CORE.global.ProfitCenters.Longitude, 4326) AS geometrypoint
• geography::Point(CORE.global.ProfitCenters.Latitude, CORE.global.ProfitCenters.Longitude, 4326) AS geographypoint,
  geometry::Point(CORE.global.ProfitCenters.Latitude, CORE.global.ProfitCenters.Longitude, 4326) AS geometrypoint

[George_Thompson · Accepted solution]

If you change the "geometrypoint" field to "shape" then try to register, does that work?

**************************************************************************

Here is a query to take x/y in a table and create a shape field.

alter table [data].[CADRecordsData_Schema2] add shape as geometry::STGeomFromText('POINT('+convert(varchar(20),xCoord)+' '+convert(varchar(20),yCoord)+')',4326)

4326 = SRID

Added the spatial point shape column from the X/Y data in the table:

https://gis.stackexchange.com/questions/21108/create-a-geography-geometry-column-from-x-and-y-fields...

[MatthewTsui5]

Hi George,

Thank you! I was able to register the following SQL view:

Here is what worked:

SELECT ISNULL(CAST(ROW_NUMBER() OVER (ORDER BY [Field1] ASC) AS int), 0) AS [ID], Field1, Latitude, Longitude,
geometry::STGeomFromText('POINT(' + CONVERT(varchar(20), CORE.global.Places.Longitude) + ' ' + CONVERT(varchar(20), CORE.global.Places.Latitude) + ')', 4326) AS Shape
FROM CORE.global.Places