SQL Server is a powerful database management system that offers a wide range of features and functionalities. One of the most interesting and useful features is the Spatial Database, which allows you to work with geographic and spatial data. In this blog post, we will explore some concepts and ideas related to Spatial Database Queries in SQL Server.

What is Spatial Database?

A Spatial Database is a database that is optimized for storing and querying spatial data, such as maps, locations, and geographic features. It allows you to perform various operations on spatial data, such as finding distances between points, determining intersections between shapes, and creating maps.

Getting Started with Spatial Database Queries

Before we dive into the code examples, it is important to set up the environment. In order to follow along with the examples, you will need to create a database using the World Shapefile, which can be downloaded from the provided link. Once you have created the database, you can start running the queries.

Displaying the Map of India

Let’s start by displaying the map of India from the spatial database. Run the following query:

USE Spatial
GO

-- India Map
SELECT [CountryName], [BorderAsGeometry], [Border]
FROM [Spatial].[dbo].[Countries]
WHERE Countryname = 'India'
GO

Please note that this map is not an accurate political map and is only a representation.

Finding the Longitude and Latitude of IT Cities in India

Next, let’s find the longitude and latitude of two major IT cities in India, Hyderabad and Bangalore. The values are as follows:

• Longitude-Latitude for Bangalore: 77.5833300000, 13.0000000000
• Longitude-Latitude for Hyderabad: 78.4675900000, 17.4531200000

We can now put these values on the map of India and see their locations. Run the following queries:

-- Bangalore
DECLARE @GeoLocation GEOGRAPHY
SET @GeoLocation = GEOGRAPHY::STPointFromText('POINT(77.5833300000 13.0000000000)', 4326).STBuffer(20000);

-- Hyderabad
DECLARE @GeoLocation1 GEOGRAPHY
SET @GeoLocation1 = GEOGRAPHY::STPointFromText('POINT(78.4675900000 17.4531200000)', 4326).STBuffer(20000);

-- Bangalore and Hyderabad on Map of India
SELECT name, [GeoLocation]
FROM [IndiaGeoNames] I
WHERE I.[GeoLocation].STDistance(@GeoLocation) <= 0
UNION ALL
SELECT name, [GeoLocation]
FROM [IndiaGeoNames] I
WHERE I.[GeoLocation].STDistance(@GeoLocation1) <= 0
UNION ALL
SELECT '', [Border]
FROM [Spatial].[dbo].[Countries]
WHERE Countryname = 'India'
GO

Drawing a Straight Line Between the Cities

We can also draw a straight line between Hyderabad and Bangalore. Run the following queries:

DECLARE @GeoLocation GEOGRAPHY
SET @GeoLocation = GEOGRAPHY::STPointFromText('POINT(78.4675900000 17.4531200000)', 4326).STBuffer(10000);

DECLARE @GeoLocation1 GEOGRAPHY
SET @GeoLocation1 = GEOGRAPHY::STPointFromText('POINT(77.5833300000 13.0000000000)', 4326).STBuffer(10000);

DECLARE @GeoLocation2 GEOGRAPHY
SET @GeoLocation2 = GEOGRAPHY::STGeomFromText('LINESTRING(78.4675900000 17.4531200000, 77.5833300000 13.0000000000)', 4326)

SELECT name, [GeoLocation]
FROM [IndiaGeoNames] I
WHERE I.[GeoLocation].STDistance(@GeoLocation) <= 0
UNION ALL
SELECT name, [GeoLocation]
FROM [IndiaGeoNames] I1
WHERE I1.[GeoLocation].STDistance(@GeoLocation1) <= 0
UNION ALL
SELECT '' name, @GeoLocation2
UNION ALL
SELECT '', [Border]
FROM [Spatial].[dbo].[Countries]
WHERE Countryname = 'India'
GO

Calculating the Distance Between the Cities

We can use the distance function of the spatial database to find the straight line distance between Hyderabad and Bangalore. Run the following query:

-- Distance Between Hyderabad and Bangalore
DECLARE @GeoLocation GEOGRAPHY
SET @GeoLocation = GEOGRAPHY::STPointFromText('POINT(78.4675900000 17.4531200000)', 4326)

DECLARE @GeoLocation1 GEOGRAPHY
SET @GeoLocation1 = GEOGRAPHY::STPointFromText('POINT(77.5833300000 13.0000000000)', 4326)

SELECT @GeoLocation.STDistance(@GeoLocation1)/1000 'KM';
GO

The result of the above query is the distance between the two cities in kilometers.

Conclusion

SQL Server’s Spatial Database is a powerful tool for working with geographic and spatial data. In this blog post, we explored some basic concepts and ideas related to Spatial Database Queries in SQL Server. We learned how to display maps, find locations, draw lines, and calculate distances between points. The Spatial Database feature in SQL Server is easy to use and can be very useful in various applications. I hope you found this blog post informative and helpful. Feel free to share your thoughts and opinions in the comments section below.