topic Re: How to use GEOGRAPHY or GEOMETRY function in a SQL for Register as Geodatabase tool in Geodatabase Questions

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

MatthewTsui5 — Fri, 31 Jan 2025 16:11:16 GMT

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

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

George_Thompson — Fri, 31 Jan 2025 17:16:42 GMT

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-sql-server-2008

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

MatthewTsui5 — Fri, 31 Jan 2025 18:28:39 GMT

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