DbSchema Database Designer

DbSchema | SQL Server - How to Use Spatial Data Types?

Publish on DbSchema Blog >>>

SQL Server: How to Use Spatial Data Types in sqlcmd and DbSchema

SQL Server alt >

Table of Contents

  1. Introduction
  2. Prerequisites
  3. What are Spatial Data Types?
  4. Why Use Spatial Data Types?
  5. Restrictions and Permissions
  6. Advantages and Limitations
  7. Types of Spatial Queries
  8. Using Spatial Data Types in sqlcmd and DbSchema
  9. Conclusion
  10. References


SQL Server, the widely used relational database management system by Microsoft, provides advanced capabilities for the handling and manipulation of geographical and geometric data through its Spatial Data Types. In this article, we are going to delve deep into understanding these spatial data types, their purposes, advantages, limitations, and the restrictions and permissions required to use them. We will also look at how to employ these data types in sqlcmd and DbSchema, alongside explaining the different types of spatial queries with examples.


Before moving forward, ensure you have:

  • A basic understanding of SQL (Structured Query Language).
  • SQL Server Management Studio installed.
  • Basic knowledge of sqlcmd utility and DbSchema.
  • Access to a SQL Server instance for practice.

For installation and establishing connection you can read our article SQL Server-How to create a database?

Spatial Data Objects alt >

What are Spatial Data Types?

Spatial data types are used to represent geographical or geometric data. In SQL Server, two spatial data types are provided - geometry and geography. The geometry type is used to store planar (flat) shapes, while the geography type is used to store spherical (round) shapes, representing the earth’s surface.

Why Use Spatial Data Types?

Spatial data types enable storage, retrieval, and querying of geographical and geometric data within a database. This means data that represents physical locations and shapes can be used in your applications. They are extensively used in applications like Geographic Information Systems (GIS), logistics, and many others, making the handling of such data efficient and straightforward.

Restrictions and Permissions

To use spatial data types in SQL Server, the user must have at least db_datareader and db_datawriter roles, or equivalent permissions. Furthermore, certain methods associated with these types are available only if the database is set to compatibility level 100 or higher.

Advantages and Limitations


  1. Allow you to store complex geographic and geometric data.
  2. Provide a variety of built-in functions for manipulating spatial data.
  3. Enable location-based queries and operations.


  1. Complex to understand and use for beginners.
  2. The precision of calculations may be affected due to the Earth’s round shape (for geography data type).
  3. Requires a substantial understanding of the underlying mathematical concepts.

Types of Spatial Queries

Spatial queries can be broadly classified into three categories:

Query Type Description
Nearness Queries These queries help in finding out how close one geographical point is to another.
Region Queries These queries allow us to search within a specified geographical region.
Union/Intersection Queries These queries enable us to find the common area between two geographical entities or combine them.

Using Spatial Data Types in sqlcmd and DbSchema

In this section, we will look at the steps to use spatial data types in sqlcmd and DbSchema.

Using Spatial Data Types in sqlcmd

Step 1: Start sqlcmd from the command prompt using the following command.

sqlcmd -S localhost -U sa -P your_password

Replace localhost, sa, and your_password with your SQL Server instance’s address, username, and password, respectively:

Step 2: Create a new database named SpatialDB and switch to it:

USE SpatialDB;

Step 3: Create a table named Locations with three columns: ID, Name, and Location. ID is an integer that automatically increments with each new row. Name is a variable-length string. Location is a geography spatial data type:

CREATE TABLE Locations (
    Name NVARCHAR(50),
    Location GEOGRAPHY

Step 4: Insert a row into Locations. The Name is OpenAI HQ, and the Location is a point that corresponds to the latitude and longitude of OpenAI HQ. The 4326 SRID (Spatial Reference Identifier) is used for the WGS 84 spatial reference system, which is the standard for GPS and cartography:

INSERT INTO Locations (Name, Location)
VALUES ('OpenAI HQ', geography::STPointFromText('POINT(-122.4194 37.7749)', 4326));

Step 5: Run a select query to return all rows from Locations where the Name is OpenAI HQ:

SELECT * FROM Locations WHERE Name = 'OpenAI HQ';

Assuming the table only contains the row we’ve inserted, the output would be something like:

ID Name Location
1 OpenAI HQ 0xEA70C66C713225C9CF189F7E37B94249716823B0

Here, the Location is represented as a hexadecimal number. If you want to see it as human-readable text, you can use the ToString() method:

SELECT ID, Name, Location.ToString() as Location FROM Locations WHERE Name = 'OpenAI HQ';

The output would then be:

ID Name Location
1 OpenAI HQ POINT (-122.4194 37.7749)

These results indicate that we have a location named OpenAI HQ stored in our database, and its geographical coordinates correspond to a latitude of 37.7749 and a longitude of -122.4194.

Using Spatial Data Types in DbSchema

DbSchema is a visual database design tool. Once you have connected DbSchema to your SQL Server instance, you can use its Graphical Query Builder and SQL Editor to execute queries. You can also visually design the database layout.

Follow the steps 2-4 mentioned in the sqlcmd section to create a database, table, and insert data. DbSchema will visually represent the database schema.

To execute a query in DbSchema:

  1. Click on SQL Editor on the top bar.
  2. Type your SQL query in the editor. For example:
SELECT * FROM Locations WHERE Name = 'OpenAI HQ';
  1. Press the Run SQL (F5) button or hit F5 on your keyboard.
  2. The result set will appear at the bottom of the SQL Editor.

Since DbSchema is a GUI tool, you can see the actual points on a map if the result includes geographic data. You just have to switch to Geographic View in the result set viewer. This feature makes DbSchema particularly powerful for working with spatial data types.

Remember, using spatial data types can initially be complex due to the mathematical concepts involved. However, once understood, it opens up a whole new dimension of possibilities in database design and query.

Visually Manage SQL Server using DbSchema

DbSchema is a SQL Server client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Key Features of DbSchema:

Following are the key features of DbSchema which distinguish it from other database GUI tools.


The capability of SQL Server to handle spatial data types provides a comprehensive solution to manage geographic and geometric data in applications. Though it may seem complex, understanding the underlying concepts and working with sqlcmd and DbSchema can streamline the process, opening new horizons in application development.


  1. Microsoft Docs: Spatial Data Types Overview
  2. Working with Spatial Data Types
  3. DbSchema: SQL Server Database Design Tool

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.