How do I declare GEOMETRY datatype properties in a SQL CREATE script?

Question

How do I do this in SQL Server?

SQL/SpatiaLite: how to declare a column as geometry?

Creating the column datatype is easy - but I cannot see a way to define the properties in the CREATE script.

As I understand it, creating a column with a geometry datatype is step 1, but then one must initialise it with the type of geometry (polygon/point/etc).

[Showing how to add and query geometry data](https://learn.microsoft.com/en-us/sql/t-sql/spatial-geometry/spatial-types-geometry-transact-sql#a-showing-how-to-add-and-query-geometry-data) What properties are you looking for? — AlwaysLearning, Apr 29 '21 at 09:20
The referenced question is about SQLite. In MS SQL Server, `geometry` columns can't be restricted to a particular kind of geometry, as far as I know, unless (I guess) you use constraints — AakashM, Apr 29 '21 at 09:21
@AakashM That is a very interesting point, which I did not know, and explains a lot. When I was trying to put in geometric data, I was getting errors. But If a created a first pseudo record manually, then it seems to "set" the type of geometry for the table — Maxcot, Apr 29 '21 at 23:32

Maxcot · Answer 1 · 2021-05-03T21:01:26.433

I stumbled on this

DECLARE @g geometry;   
SET @g = geometry::STPolyFromText('POLYGON ((5 5, 10 5, 10 10, 5 5))', 0);  

INSERT INTO [dbo].[...](Shape)
VALUES (@g.ToString());

After adding this one record, the table would accept polygon geometry from my arcpy / python script.

So, I can now, with one CREATE script (see below), create a brand new table and have it accept polygon data.

USE [....]
GO

IF OBJECT_ID('.......', 'U') IS NOT NULL 
DROP TABLE .......; 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[......](
       [OBJECTID] [int] IDENTITY(1,1) NOT NULL,
        …
        …
        …
       [Shape] [geometry] NULL,
PRIMARY KEY CLUSTERED 
(
       [OBJECTID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


DECLARE @g geometry;   
SET @g = geometry::STPolyFromText('POLYGON ((5 5, 10 5, 10 10, 5 5))', 0);  

INSERT INTO [dbo].[.....](Shape)
VALUES (@g.ToString());

How do I declare GEOMETRY datatype properties in a SQL CREATE script?

1 Answers1