Creating a spatial table in Enterprise database and write data

270
1
02-22-2024 02:31 AM
Labels (1)
TomGeo
by
Occasional Contributor III

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?

- We are living in the 21st century.
GIS moved on and nobody needs a format consisting out of at least three files! No, nobody needs shapefiles, not even for the sake of an exchange format. Folks, use GeoPackage to exchange data with other GIS!
0 Kudos
1 Reply
MarceloMarques
Esri Regular Contributor

@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.

| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov
0 Kudos