SQL Server spatial data type, draw a graph from the Management studio

Hi Guys!

Today we talk about spatial data type. 
We will see how to draw a graph directly from SQL Server Management Studio

Are you ready to see new datatype called Spatial data type?
Yes? OK! So, i will try to explain as simply as possible.

 

Introduction

Spatial data type are datatypes used for conserving spatial data which allows us to render graphical data.
They are used for representing the location, size, and shape of an object.
Every object such as lake or a car could be rapresented.
Spatial data may also include attributes which can be given more information of the entity that is being represented.
That there are two types of spatial data: Geometry or Geography.
Today we will examine Geometry spatial data type.

 

How to use Spatial geometry data type

We can define a spatial data type like any other data type.
For example we can create a table where a column (GEOM) is defined as geometry.

       
CREATE TABLE [dbo].[SPATIAL_TABLE](

       [ID] [int] IDENTITY(1,1) NOT NULL,

       [DESCR] Varchar(80),

       [GEOMTYPE] [nchar](10) NULL,

       [GEOM] [geometry] NULL,

CONSTRAINT [PK_SPATIAL_TABLE] PRIMARY KEY CLUSTERED

(

       [ID] 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]

Now we will insert some data into our SPATIAL_TABLE.

Look at the syntax of the geom field.

Into the geom field infact we can insert different kind of object such as

• POINT,
• LINESTRING,
• POLYGON 
• and many others.

       

Insert into spatial_table (geomtype,geom) 
values ('point','POINT(50 50)')

Insert into spatial_table (geomtype,geom) 
values ('linestring', 'LINESTRING(100 100,100 200,200 200)')

Insert into spatial_table (geomtype,geom) 
values ('polygons', 'POLYGON((110 100,110 190,130 190,130 100,110 100))')
      
 

For the example of today i will try to graph data taken from a my table called 'Compression PAQ8PXD$'

What this table contain is not important just remember:

• Each row of the table we must draw a polygon where a polygon have this sintax: POLYGON((X1 Y1, X2 Y2, X3 Y3, X4 Y4)
• The Value is inside the F7 column while a description is inside the F1  

Insert into spatial_table (descr,geomtype,geom)
Select 
  F1,'polygons',

  'POLYGON(('+
  -- X1 and Y1

   Cast( 7000 * (-1+ROW_NUMBER() over (order by F1)) as Varchar(10)) + ' 0, ' +


   Cast( 7000 * (-1+ROW_NUMBER() over (order by F1)) as Varchar(10)) + ' '+  
   Cast( Cast(f7 as decimal(13,0)) +', ' +
   -- X2 and Y2 

   Cast( 7000 + 7000 * (-1+ROW_NUMBER() over (order by F1)) as Varchar(10)) + 
   ' '+Cast( Cast(f7 as decimal(13,0)) +', ' +
   -- X3 and Y3 

   Cast( 7000 + 7000 * (-1+ROW_NUMBER() over (order by F1)) as Varchar(10)) +  
   ' 0, ' +
   -- X4 and Y4 

   Cast( 7000 * (-1+ROW_NUMBER() over (order by F1)) as Varchar(10)) +  
   ' 0))'

from ['Compression PAQ8PXD$']
 

Execute now the select below:

Select * from spatial_table where [GEOMTYPE] = 'polygons'

We will have a new tab called spatial results between results and Messages tabs:

Look now at the tab "spatial results"... WOW!

You can also choose a label for each value. I chose for example the descr field of the spatial_table table.

That's all for today.
Hope you enjoy the post and don't forget to take a look to the other posts!
See you soon!!


Luca Biondi @ SQLServerPerformance blog 2020!   

Previus post: A second Step into Machine Learning. How to read data in R language from SQL Server