Creating a spatial table in Enterprise database and write data

TomGeo — Thu, 22 Feb 2024 10:54:42 GMT

I have an Enterprise database and want to write data into it. In case the table I am addressing does not exists I would need to create the table. Creating the table seems to give me some challenge with respect to the required data type for the geometry column.

When I look into other Enterprise database tables the geometry column is initiated by

SHAPE geometry NULL

and using the geometry type let's me at least create the table.
However, looking for the right procedure I came across this workflow, where there are several versions of how to create a spatial table in an Enterprise database, but SQL Server is not in there. But I was wondering if the geometry type is the right one.

Once the table is created, I went to register the table with the geodatabase in ArcGIS Pro.

Back in SQL my next statement is

INSERT INTO Buildings (SHAPE) VALUES (geometry::STGeomFromText('POLYGON Z ((695023.53 6181945.6 25.55,695014.58 6181947.59 25.7,695014.32 6181946.41 25.7,695010.94 6181947.17 25.4,695009.57 6181941.03 25.4, 695013.02 6181940.26 25.55,695010.59 6181929.37 25.55,695019.48 6181927.39 25.55, 695023.53 6181945.6 25.55))', 25832);

But this one gives me the following error

Query failed, possibly due to a malformed statement. Query Text `INSERT
INTO
Buildings (SHAPE)
VALUES (geometry::STGeomFromText('POLYGON Z ((695023.53 6181945.6 25.55,695014.58 6181947.59 25.7,695014.32 6181946.41 25.7,695010.94 6181947.17 25.4,695009.57 6181941.03 25.4,695013.02 6181940.26 25.55,695010.59 6181929.37 25.55,695019.48 6181927.39 25.55,695023.53 6181945.6 25.55))',
25832)'. Provider error `(-2147217900) Incorrect syntax near ')'.'

Does somebody can point me at the mistake I am making?

Re: Creating a spatial table in Enterprise database and write data

MarceloMarques — Thu, 22 Feb 2024 16:46:22 GMT

@TomGeo - see the SQL Server Documentation about the Geometry Spatial Data Type

geometry (Transact-SQL) - SQL Server | Microsoft Learn

SQL Server Geodatabases use the SQL Server Native Geometry and Geography Spatial Data Types, there is no esri st_geometry spatial type for SQL Server.

If the SQL Server Database has an ArcSDE Geodatabase Repository, then you can create the table using ArcMap or ArcGIS Pro instead of trying to manually create the table via SQL, this can also help you to determine the proper syntax to create the table and to insert data, but the SQL Server Documentation will have more examples and details.

I hope this helps.

topic Re: Creating a spatial table in Enterprise database and write data in Data Management Questions

Creating a spatial table in Enterprise database and write data

Re: Creating a spatial table in Enterprise database and write data