Database • SQL • GIS • PostGIS

Geometry Data Type in a Database: A Simple Guide with SQL Examples

Learn how databases store locations, roads, routes, and boundaries using geometry data types—with beginner-friendly definitions, sample data, diagrams, and practical PostgreSQL/PostGIS queries.

📍 POINT 〰️ LINESTRING ⬡ POLYGON 🗺️ Spatial SQL

In simple terms: A geometry data type lets a database understand a location or shape—not just store its name. That makes questions like “Which shop is closest?” or “Is this customer inside our delivery area?” possible with spatial SQL.

Geometry Data Type in a Database – PostgreSQL PostGIS tutorial with POINT, LINESTRING, POLYGON and SQL examples | IndianTechnoEra

1. What Is a Geometry Data Type?

A geometry data type stores spatial objects: points, lines, and areas described by coordinates. It is useful in GIS (Geographic Information Systems), maps, delivery apps, real estate, transport systems, and location-based search.

Compare it with familiar database types:

Data type	Example	What it stores
`INT`	`101`	A whole number
`VARCHAR`	`'India Gate'`	Text
`DATE`	`'2026-10-27'`	A date
`GEOMETRY`	`POINT(77.2295 28.6129)`	A spatial shape or location

Example A · Shop finder

📍 Store location

Save each store as a point. Then search for stores within a chosen radius of a customer.

Example B · Delivery

🛵 Delivery route

Represent a route as a line and a service area as a polygon.

Example C · Real estate

🏠 Land boundary

Store a parcel boundary and test whether a coordinate lies inside it.

2. Three Fundamental Geometry Types

2.1 POINT — one position

A POINT represents one location. Think of a pin on a map.

POINT(10 20)

Here, 10 is X and 20 is Y. A shop, customer, ATM, or office can be represented by a point.

2.2 LINESTRING — a connected path

A LINESTRING connects two or more points in order. Imagine drawing a road by connecting several map coordinates.

LINESTRING(0 0, 10 10, 20 5)

Use it for a road, railway segment, river section, walking path, or delivery route. The order of coordinates determines how the line is drawn.

2.3 POLYGON — an enclosed area

A POLYGON describes an enclosed area. Think of a property boundary or delivery zone drawn on a map.

POLYGON((
  0 0,
  10 0,
  10 10,
  0 10,
  0 0
))

Remember: POINT = where; LINESTRING = path; POLYGON = area. A polygon ring must be closed—the first coordinate is repeated at the end.

2.4 Other geometry types

Type	Simple definition	Example
`MULTIPOINT`	Several points grouped together	Several ATM locations
`MULTILINESTRING`	Several separate line strings	Separate road segments
`MULTIPOLYGON`	Several polygon areas	Separated land parcels
`GEOMETRYCOLLECTION`	Different geometry types in one collection	A point plus a line

MULTIPOINT((1 2), (3 4), (5 6))

MULTILINESTRING(
  ((0 0, 5 5)),
  ((10 0, 15 5))
)

GEOMETRYCOLLECTION(
  POINT(1 2),
  LINESTRING(0 0, 3 3)
)

3. Coordinates: X, Y, Longitude and Latitude

A coordinate tells the database where a point is. For GPS-style coordinates using SRID 4326 in PostGIS, the common order is:

For these GPS examples, X comes first and Y comes second.

ST_MakePoint(77.2295, 28.6129)
-- longitude first, latitude second

This coordinate is approximately the location of India Gate in New Delhi. In a different coordinate reference system, X and Y may represent projected map units instead of longitude and latitude.

What is SRID?

SRID means Spatial Reference System Identifier. It identifies the coordinate system that tells spatial software how to interpret coordinate values.

SRID	System	Common use
`4326`	WGS 84	GPS longitude and latitude
`3857`	Web Mercator	Many web map displays
Other projected SRIDs	Regional coordinate systems	Local mapping and measurement

-- Create a point and assign SRID 4326
SELECT ST_SetSRID(
  ST_MakePoint(77.2295, 28.6129),
  4326
);

-- Convert known coordinates to another CRS
SELECT ST_Transform(
  ST_SetSRID(ST_MakePoint(77.2295, 28.6129), 4326),
  3857
);

Important difference: ST_SetSRID assigns the SRID; it does not convert coordinate values. ST_Transform converts coordinates between reference systems. Only transform coordinates when the source CRS is known correctly.

4. Create a Small Spatial Database with PostgreSQL + PostGIS

PostgreSQL is a relational database. PostGIS is an extension that adds spatial types, functions, and indexes. The SQL below requires PostGIS to be installed or enabled in your database.

Step 1 — Enable PostGIS

CREATE EXTENSION IF NOT EXISTS postgis;

SELECT PostGIS_Full_Version();

Step 2 — Create the places table

CREATE TABLE places (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  location GEOMETRY(POINT, 4326) NOT NULL
);

Part	Meaning in simple terms
`id`	Unique number for each row
`name`	Readable place name
`GEOMETRY`	The spatial data type
`POINT`	Only point-shaped values are allowed in this column
`4326`	The column expects SRID 4326 coordinates
`NOT NULL`	A value is required

Step 3 — Insert realistic sample data

INSERT INTO places (name, location)
VALUES
(
  'India Gate',
  ST_SetSRID(ST_MakePoint(77.2295, 28.6129), 4326)
),
(
  'Taj Mahal',
  ST_SetSRID(ST_MakePoint(78.0421, 27.1751), 4326)
),
(
  'Qutub Minar',
  ST_SetSRID(ST_MakePoint(77.1855, 28.5245), 4326)
);

ST_MakePoint creates a point. ST_SetSRID assigns the coordinate reference system. Each insert creates one row.

Step 4 — Read the sample records

SELECT
  id,
  name,
  ST_AsText(location) AS geometry_data
FROM places
ORDER BY id;

Expected output:

id	name	geometry_data
1	India Gate	`POINT(77.2295 28.6129)`
2	Taj Mahal	`POINT(78.0421 27.1751)`
3	Qutub Minar	`POINT(77.1855 28.5245)`

ST_AsText converts geometry into readable WKT (Well-Known Text). The stored value is still a spatial value—not an ordinary text string.

5. Practical Spatial SQL Queries

Query A — Extract longitude and latitude

Business prompt: “Show each place with separate longitude and latitude columns so my API can return coordinates to the frontend.”

SELECT
  name,
  ST_X(location) AS longitude,
  ST_Y(location) AS latitude
FROM places;

Expected sample result:

name	longitude	latitude
India Gate	77.2295	28.6129
Taj Mahal	78.0421	27.1751
Qutub Minar	77.1855	28.5245

ST_X extracts X; ST_Y extracts Y from a point.

Query B — Find places within 5 kilometres

Business prompt: “A customer is near India Gate. Return all saved places within 5 km.”

SELECT name
FROM places
WHERE ST_DWithin(
  location::geography,
  ST_SetSRID(
    ST_MakePoint(77.2295, 28.6129),
    4326
  )::geography,
  5000
);

Expected output for this sample:

name
India Gate

Why does it work?

ST_DWithin checks whether two spatial values are within a distance.
::geography makes this a geographic surface-distance calculation.
5000 means 5,000 metres, or 5 kilometres.

The query checks the supplied sample coordinates. The other two places are much farther than 5 km from India Gate.

Query C — Calculate distance and sort nearest first

Business prompt: “Show all places ordered from nearest to farthest from the customer's location.”

SELECT
  name,
  ROUND(
    ST_Distance(
      location::geography,
      ST_SetSRID(
        ST_MakePoint(77.2295, 28.6129),
        4326
      )::geography
    )::numeric,
    2
  ) AS distance_meters
FROM places
ORDER BY distance_meters;

India Gate appears first at zero distance, followed by Qutub Minar and then the Taj Mahal. Exact nonzero values depend on the supplied coordinates and the calculation method.

Illustrative distance-order chart

The bars below show relative order only, not measured distances.

India Gate

Qutub Minar

Taj Mahal

Query D — Create a delivery-zone polygon

Business prompt: “Our service operates only inside a defined zone. Find which stored places are covered by that zone.”

CREATE TABLE delivery_zones (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  zone_name VARCHAR(100) NOT NULL,
  boundary GEOMETRY(POLYGON, 4326) NOT NULL
);

INSERT INTO delivery_zones (zone_name, boundary)
VALUES (
  'Sample Delivery Zone',
  ST_GeomFromText(
    'POLYGON((
      77.20 28.60,
      77.25 28.60,
      77.25 28.65,
      77.20 28.65,
      77.20 28.60
    ))',
    4326
  )
);

SELECT p.name, z.zone_name
FROM places p
JOIN delivery_zones z
  ON ST_Covers(z.boundary, p.location);

For the sample coordinates, India Gate falls inside the illustrative rectangle. This example is for learning; a production delivery area should use verified boundaries.

ST_Covers includes points on the boundary. ST_Contains generally does not consider a point on the polygon boundary to be contained by the polygon.

WKT and WKB: two ways to represent geometry

WKT (Well-Known Text): human-readable, such as POINT(77.2295 28.6129).
WKB (Well-Known Binary): binary representation, useful for software and data interchange.

-- Readable geometry
SELECT ST_AsText(location)
FROM places
WHERE id = 1;

-- Binary geometry
SELECT ST_AsBinary(location)
FROM places
WHERE id = 1;

Plain WKT does not inherently include its SRID in the same way as an EWKT representation.

6. GEOMETRY vs GEOGRAPHY

These names are similar, but they do not mean exactly the same thing in PostGIS.

Feature	GEOMETRY	GEOGRAPHY
Model	Planar coordinate geometry	Coordinates on Earth's surface
Typical use	Shapes, topology, projected/local calculations	Global distance and geographic calculations
Distance units	Coordinate units of the CRS	Usually metres for supported distance functions
Important caution	4326 geometry distance is in degrees	Some operations may cost more computation

Example: why the type matters

-- Geometry in SRID 4326: output is in coordinate units (degrees)
SELECT ST_Distance(
  ST_SetSRID(ST_MakePoint(77.2295, 28.6129), 4326),
  ST_SetSRID(ST_MakePoint(77.1855, 28.5245), 4326)
);

-- Geography: surface distance in metres
SELECT ST_Distance(
  ST_SetSRID(ST_MakePoint(77.2295, 28.6129), 4326)::geography,
  ST_SetSRID(ST_MakePoint(77.1855, 28.5245), 4326)::geography
);

Practical rule: Use GEOGRAPHY for straightforward global surface-distance calculations in metres. Use GEOMETRY for planar spatial operations, ideally with a suitable projected coordinate system when measuring local distances or areas. Choose based on the calculation—not just the name.

7. Spatial Indexes: Help Queries Scale

A spatial index helps the database find candidate shapes without examining every record. PostGIS commonly uses a GiST index for spatial columns.

CREATE INDEX idx_places_location
ON places
USING GIST (location);

This can help spatial filters run faster, especially with large datasets. However, a geometry index does not automatically mean every query using location::geography will use that index in the expected way.

If geographic distance searches are frequent, consider an expression index:

CREATE INDEX idx_places_geography
ON places
USING GIST ((location::geography));

Use EXPLAIN or EXPLAIN ANALYZE on realistic data to inspect the query plan. Small tables may be faster to scan directly, so an index does not guarantee a better plan for every query.

8. Common Mistakes to Avoid

⚠️ 1. Reversing coordinates

For the SRID 4326 examples, use longitude first and latitude second.

⚠️ 2. Treating degrees as metres

Geometry distance in SRID 4326 is measured in coordinate units, not metres.

⚠️ 3. Confusing SRID functions

ST_SetSRID assigns a CRS identifier; ST_Transform converts coordinates.

⚠️ 4. Leaving a polygon open

Repeat the first ring coordinate at the end and validate incoming geometries.

⚠️ 5. Ignoring spatial indexes

Large location tables may need an appropriate GiST index and query-plan testing.

⚠️ 6. Assuming every database is identical

PostGIS, SQL Server, and MySQL have different syntax and spatial capabilities.

9. Practice Questions

Which geometry type represents one ATM location?
Write a query that returns a place name and its geometry as WKT.
For the examples using SRID 4326, which coordinate comes first in ST_MakePoint?
Which PostGIS function checks whether a location is within a specified distance?
What is the difference between ST_SetSRID and ST_Transform?
Which index type is commonly used for PostGIS spatial columns?
Why should geometry distance in SRID 4326 not be described as metres?

Show answers

POINT.
SELECT name, ST_AsText(location) FROM places;
Longitude (X), then latitude (Y).
ST_DWithin.
ST_SetSRID assigns the identifier; ST_Transform converts coordinates.
GiST is a common choice.
SRID 4326 geometry coordinates are in degrees, so the output is in coordinate units—not metres.

10. Quick Revision Cheat Sheet

Term	Remember this
`GEOMETRY`	Stores spatial shapes
`POINT`	One location
`LINESTRING`	Connected path
`POLYGON`	Closed area
`SRID 4326`	WGS 84 longitude/latitude system
`ST_MakePoint`	Creates a point
`ST_AsText`	Converts geometry to readable WKT
`ST_Distance`	Calculates distance; units depend on type and CRS
`ST_DWithin`	Checks whether objects are within a distance
`ST_Covers`	Tests coverage, including boundary points
`GEOGRAPHY`	Useful for Earth's surface distance calculations
`GiST`	Common spatial index in PostGIS

Frequently Asked Questions

Is Geometry available only in PostgreSQL?

No. PostgreSQL with PostGIS, Microsoft SQL Server, and MySQL all support spatial data, but their data types and functions differ.

Is a geometry value the same as a map image?

No. Geometry is structured spatial data. A map image is a visual rendering. Mapping software can draw a geometry value on a map.

Should I store latitude and longitude in two numeric columns instead?

Separate numeric columns can be useful for simple storage, but a spatial column is more convenient when you need spatial predicates, distance queries, shape operations, and spatial indexing.

What should I learn first?

Start with POINT, LINESTRING and POLYGON. Then learn coordinate order and SRIDs, create the sample table, and practise distance and point-in-polygon queries before learning spatial indexes.

Geometry Data Type in a Database: Simple Guide with SQL Examples