Advanced Database
Management System
Spatial Database
Queries
Spatial Database
Spatial data is associated with geographic
locations such as cities , towns etc. A spatial
database is optimized to store and query data
representing objects. These are the objects
which are defined in a geometric space.
Example
A road map is a visualization of geographic information. A
road map is a 2-dimensional object which contains points,
lines, and polygons that can represent cities, roads, and
political boundaries such as states or provinces.
spatial data can be of two types
Vector data: This data is represented as discrete points, lines
and polygons
Rastor data: This data is represented as a matrix of square
cells.
SDBMS
Spatial Data
SQL quries
SQL commands are instructions. It is used to communicate with the
database. It is also used to perform specific tasks, functions and queries
of data.
Spatial Database Queries
Spatial query refers to the process of retrieving a data subset
from a map layer by working directly with the map features. In
a spatial database, data are stored in attribute tables and
feature/spatial tables.
Types of spatial queries:
Nearness queries
Region queries
Union/Intersection
Nearness queries:
The nearest-neighbor query requests the
object that is nearest to a specified point.
This query does not have to specify a limit
on the distance, and hence we can ask it even
if we have no idea how far the nearest
Railway station lies.
Query
Example:
USE AdventureWorks2022
GO
DECLARE @g geography = 'POINT(-
121.626 47.8315)';
SELECT TOP(7) Spatial Location. ToString(),
City FROM Person . Address
WHERE Spatial [Link](@g) IS
NOT NULL
ORDER BY Spatial Location .
STDistance(@g);
Region queries
It deal with spatial regions.
For example, a query can ask for objects
that is present partially or completely within a
fixed region. A query to find all medicine shops
within the geographic boundaries of a given town
or we can find all the available school in a
particular city.
Diagram
Region:
Union/Intersection
In this type of queries, we may also request
intersections and unions of regions .
For example, given region information,
such as annual rainfall and population
density, a query may request all regions
with a low annual rainfall as well as a high
population density.
Diagram
union
SQL:
(
SELECT 1 ID
UNION
SELECT 2
UNION
SELECT 3
)
UNION
(
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
);
THANK YOU