Introduction to Geospatial Data

Geospatial data involves information related to locations on the Earth's surface. This data is often complex and requires specialized tools and techniques for storage, retrieval, and analysis. Geospatial data can represent physical locations, boundaries, or various phenomena that occur at certain places, such as weather events, traffic patterns, or population distributions.

Geospatial Data Types

Point

A single location on the earth, defined by a pair of coordinates (latitude and longitude).

Example:

SELECT ST_GeomFromText('POINT(30 10)');

LineString

A series of points connected by straight lines.

Example:

SELECT ST_GeomFromText('LINESTRING(30 10, 10 30, 40 40)');

Polygon

A shape defined by a closed ring of points.

Example:

SELECT ST_GeomFromText('POLYGON((30 10, 40 40, 20 40, 10 20, 30 10))');

Setting Up a Database for Geospatial Queries

1. Install PostGIS

PostGIS is a spatial database extender for PostgreSQL, adding support for geographic objects. It's critical to have PostGIS installed in your PostgreSQL server to handle geospatial data.

2. Create Your Database

CREATE DATABASE geospatial_db;

3. Enable PostGIS Extension

Once your database is created, you need to enable the PostGIS extension:

\c geospatial_db; -- Connect to your database
CREATE EXTENSION postgis;

4. Create a Table with Geospatial Data

Here's an example of creating a table that stores location data:

CREATE TABLE landmarks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    location GEOGRAPHY(Point, 4326) -- 4326 is the SRID for WGS 84, a common coordinate system
);

5. Insert Data

After the table is created, you can insert geospatial data:

INSERT INTO landmarks (name, location) 
VALUES ('Statue of Liberty', ST_SetSRID(ST_MakePoint(-74.0445, 40.6892), 4326));

Performing Geospatial Queries

Basic Geospatial Queries

Find Locations Within a Certain Distance

To find all landmarks within 10 kilometers of a given point:

SELECT name FROM landmarks
WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(-74.0445, 40.6892), 4326), 10000);

Calculate Distance Between Two Points

To calculate the distance between the Statue of Liberty and a given point:

SELECT ST_Distance(
    location,
    ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)
) FROM landmarks WHERE name = 'Statue of Liberty';

More Complex Queries

Find Intersecting Geospatial Data

Suppose you have another polygon data set representing different regions. You can find which landmarks fall within a specific region:

SELECT l.name FROM landmarks l, regions r
WHERE ST_Within(l.location, r.region);

Note: This assumes you have a regions table with a region field of type GEOGRAPHY(Polygon, 4326).

Conclusion

This guide introduces the fundamental concepts and SQL operations required to work with geospatial data. Using PostGIS with PostgreSQL, you can effectively store, query, and analyze geospatial information, enabling a wide array of real-world applications.

Spatial Data Types in SQL

Introduction to Spatial Data Types

Spatial data types come with several SQL databases like PostgreSQL (with PostGIS extension) and MySQL. These types allow us to store and query geometrical and geographical data like points, lines, and polygons.

Spatial Data Types

Here are the commonly used spatial data types with brief explanations:

• POINT: Represents a single location.
• LINESTRING: Represents a line, composed of two or more points.
• POLYGON: Represents an area, defined by a closed ring of points.
• MULTIPOINT: A collection of POINTs.
• MULTILINESTRING: A collection of LINESTRINGs.
• MULTIPOLYGON: A collection of POLYGONs.
• GEOMETRYCOLLECTION: A collection of different geometry types.

Create a Table with Spatial Columns

Here is how you would create a table with spatial columns:

PostgreSQL with PostGIS

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(Point, 4326)
);

MySQL

CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    geom POINT SRID 4326
);

Inserting Spatial Data

PostgreSQL with PostGIS

INSERT INTO locations (name, geom)
VALUES ('Central Park', ST_GeomFromText('POINT(-73.965355 40.782865)', 4326));

MySQL

INSERT INTO locations (name, geom)
VALUES ('Central Park', ST_GeomFromText('POINT(-73.965355 40.782865)', 4326));

Querying Spatial Data

To find all locations within a certain distance from a point:

PostgreSQL with PostGIS

SELECT name
FROM locations
WHERE ST_DWithin(
    geom::geography,
    ST_MakePoint(-73.965355, 40.782865)::geography,
    1000
);

MySQL

SELECT name
FROM locations
WHERE ST_Distance_Sphere(
    geom,
    ST_GeomFromText('POINT(-73.965355 40.782865)', 4326)
) <= 1000;

Updating Spatial Data

PostgreSQL with PostGIS

UPDATE locations
SET geom = ST_SetSRID(ST_MakePoint(-73.985135, 40.748817), 4326)
WHERE name = 'Empire State Building';

MySQL

UPDATE locations
SET geom = ST_GeomFromText('POINT(-73.985135 40.748817)', 4326)
WHERE name = 'Empire State Building';

Deleting Spatial Data

PostgreSQL with PostGIS and MySQL

DELETE FROM locations
WHERE name = 'Central Park';

Indexing Spatial Data

PostgreSQL with PostGIS

CREATE INDEX idx_locations_geom
ON locations
USING GIST (geom);

MySQL

CREATE SPATIAL INDEX idx_locations_geom
ON locations (geom);

Conclusion

This section covers practical implementations for using spatial data types in SQL, specifically in PostgreSQL with PostGIS and MySQL systems. From creating tables to querying, updating, and indexing, these examples provide a straightforward guide for handling geospatial data efficiently.

Comprehensive Guide to Understanding and Implementing Geospatial Queries Using SQL: Part 3

Creating and Managing Spatial Databases

Step 1: Creating a Spatial Database

First, create a new spatially-enabled database. Assuming you are using PostgreSQL with the PostGIS extension, here is how you do it:

CREATE DATABASE geospatial_db;
\c geospatial_db
CREATE EXTENSION postgis;

Step 2: Creating Spatial Tables

Create tables with spatial data types. Here's an example of creating a table to store geographic information about cities:

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    location GEOGRAPHY(POINT, 4326), -- Using the geography data type with SRID 4326 (WGS 84)
    population INTEGER
);

Step 3: Inserting Spatial Data

Insert spatial data into the table. Below is an example:

INSERT INTO cities (name, location, population)
VALUES
('New York', ST_GeogFromText('POINT(-74.0060 40.7128)'), 8419000),
('Los Angeles', ST_GeogFromText('POINT(-118.2437 34.0522)'), 3980400),
('Chicago', ST_GeogFromText('POINT(-87.6298 41.8781)'), 2716000);

Step 4: Querying Spatial Data

Perform spatial queries using various geospatial functions. Examples include finding the distance between two points and retrieving all cities within a certain radius.

1. Calculate the distance between two cities:

SELECT a.name AS city1, b.name AS city2, 
ST_Distance(a.location, b.location) AS distance_meters
FROM cities a, cities b
WHERE a.name = 'New York' AND b.name = 'Los Angeles';

2. Find cities within a 500 km radius of 'New York':

SELECT name, population
FROM cities
WHERE ST_DWithin(location, 
   (SELECT location FROM cities WHERE name = 'New York'), 
   500000);  -- 500 kilometers in meters

Step 5: Updating Spatial Data

Update the location coordinates for an existing record:

UPDATE cities
SET location = ST_GeogFromText('POINT(-73.935242 40.730610)')
WHERE name = 'New York';

Step 6: Deleting Spatial Data

Remove spatial records from the table:

DELETE FROM cities
WHERE name = 'Chicago';

Step 7: Indexing Spatial Data

To optimize spatial queries, create spatial indexes:

CREATE INDEX idx_cities_location
ON cities
USING GIST (location);

Step 8: Combining Data in Complex Queries

Join spatial data with non-spatial data for comprehensive queries. Here's an example of finding the population within certain regions:

SELECT regions.name AS region_name, SUM(cities.population) AS total_population
FROM regions
JOIN cities 
ON ST_Contains(regions.boundaries, cities.location)
GROUP BY regions.name;

By following these steps and examples, you can effectively create, manage, and query spatial databases using SQL.

Basic Geospatial Functions in SQL

1. ST_Distance

Calculates the minimum distance between two geometry objects.

SELECT ST_Distance(
    ST_GeomFromText('POINT(1 1)', 4326),
    ST_GeomFromText('POINT(2 2)', 4326)
) AS distance;

2. ST_Within

Determines if one geometry is completely within another.

SELECT ST_Within(
    ST_GeomFromText('POINT(1 1)', 4326),
    ST_GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 4326)
) AS is_within;

3. ST_Intersects

Checks if two geometry objects intersect.

SELECT ST_Intersects(
    ST_GeomFromText('POINT(1 1)', 4326),
    ST_GeomFromText('LINESTRING(0 0, 2 2)', 4326)
) AS does_intersect;

4. ST_Buffer

Creates a buffer around a geometry object.

SELECT ST_Buffer(
    ST_GeomFromText('POINT(1 1)', 4326),
    1
) AS buffered_geometry;

5. ST_Area

Calculates the area of a geometry object.

SELECT ST_Area(
    ST_GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 4326)
) AS area;

6. ST_Contains

Determines if one geometry contains another.

SELECT ST_Contains(
    ST_GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 4326),
    ST_GeomFromText('POINT(1 1)', 4326)
) AS does_contain;

7. ST_Centroid

Finds the centroid of a geometry object.

SELECT ST_Centroid(
    ST_GeomFromText('POLYGON((0 0, 0 4, 4 4, 4 0, 0 0))', 4326)
) AS centroid;

8. ST_Transform

Transforms the spatial reference system identifier (SRID) of a geometry object.

SELECT ST_Transform(
    ST_GeomFromText('POINT(1 1)', 4326),
    3857
) AS transformed_geometry;

9. ST_Intersection

Returns a geometry object that represents the shared portion of two geometry objects.

SELECT ST_Intersection(
    ST_GeomFromText('LINESTRING(0 0, 2 2)', 4326),
    ST_GeomFromText('LINESTRING(2 0, 0 2)', 4326)
) AS intersection;

10. ST_Union

Returns a geometry object that represents the union of two geometry objects.

SELECT ST_Union(
    ST_GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 4326),
    ST_GeomFromText('POLYGON((1 1, 1 3, 3 3, 3 1, 1 1))', 4326)
) AS union_geometry;

You can execute these SQL commands in a spatially-enabled database such as PostGIS (PostgreSQL) to perform basic geospatial functions.

Advanced Geospatial Functions

In this section of your comprehensive guide, we will cover the implementation of some advanced geospatial functions in SQL. These functions go beyond basic operations and allow for more complex spatial data manipulations and queries.

1. ST_Intersects

This function checks if two geometries intersect.

SELECT a.id, b.id
FROM table_a a, table_b b
WHERE ST_Intersects(a.geom, b.geom);

2. ST_Union

This function returns a geometry representing the point set union of the input geometries.

SELECT id, ST_Union(geom) AS unified_geom
FROM table_name
GROUP BY id;

3. ST_Difference

This function returns a geometry representing the point set difference of two geometries.

SELECT ST_Difference(geom_a, geom_b) AS difference_geom
FROM table_a, table_b
WHERE table_a.id = table_b.id;

4. ST_Buffer

This function returns a geometry that represents all points whose distance from this Geometry is less than or equal to a certain distance.

SELECT id, ST_Buffer(geom, 100) AS buffered_geom
FROM table_name;

5. ST_ConvexHull

This function returns the smallest convex geometry that encloses all geometries in the input set.

SELECT id, ST_ConvexHull(ST_Collect(geom)) AS convex_hull
FROM table_name
GROUP BY id;

6. ST_Within

This function checks if a geometry is completely within another geometry.

SELECT a.id, b.id
FROM table_a a, table_b b
WHERE ST_Within(a.geom, b.geom);

7. ST_Intersection

This function returns a geometry that represents the shared portion of two input geometries.

SELECT a.id, ST_Intersection(a.geom, b.geom) AS intersection_geom
FROM table_a a, table_b b
WHERE ST_Intersects(a.geom, b.geom);

8. ST_Centroid

This function returns the geometric center of a geometry.

SELECT id, ST_Centroid(geom) AS centroid_geom
FROM table_name;

9. ST_Transform

This function transforms a geometry into a different spatial reference system.

SELECT id, ST_Transform(geom, 4326) AS transformed_geom
FROM table_name;

10. ST_Simplify

This function returns a simplified version of a geometry using the Douglas-Peucker algorithm.

SELECT id, ST_Simplify(geom, 0.01) AS simplified_geom
FROM table_name;

These implementations will help you handle advanced geospatial queries and manipulations in SQL. Each function has practical applications in various domains such as spatial analysis, GIS, and location-based services.

Geospatial Indexing and Optimization in SQL

Overview

This section focuses on implementing geospatial indexing and query optimization in SQL. The primary goal is to enhance the performance of geospatial queries by efficiently using spatial indices.

Creating a Spatial Index

Assuming you have a table locations with a spatial column geom, let's create a spatial index to optimize queries involving this column.

-- Create spatial index for the 'geom' column in the 'locations' table
CREATE INDEX idx_locations_geom
ON locations USING GIST (geom);

Optimizing Queries with Spatial Index

Spatial indices are particularly useful for queries involving spatial relationships, such as finding all points within a certain area. Below are examples of optimized queries leveraging the spatial index.

Query: Find All Points Within a Certain Distance

-- Find locations within a 10 km radius of a given point
SELECT id, name, geom
FROM locations
WHERE ST_DWithin(
    geom::geography, 
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography, 
    10000
);

Query: Retrieve Points in a Bounding Box

-- Find locations within a given bounding box
SELECT id, name, geom
FROM locations
WHERE geom && ST_MakeEnvelope(min_lon, min_lat, max_lon, max_lat, 4326);

Query: Nearest Neighbor Search

-- Find the closest location to a given point
SELECT id, name, geom
FROM locations
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(lon, lat), 4326)
LIMIT 1;

Measuring Index Performance

It's useful to measure query performance to ensure the spatial index is providing the desired optimization.

EXPLAIN ANALYZE
SELECT id, name, geom
FROM locations
WHERE ST_DWithin(
    geom::geography,
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography,
    10000
);

The EXPLAIN ANALYZE output will show the query plan and help in identifying if the spatial index is being utilized efficiently.

Conclusion

By creating spatial indices and leveraging them within your SQL queries, you can significantly improve the performance of geospatial operations. This implementation is vital for handling large geospatial datasets and ensuring swift query execution.

Part 7: Working with PostGIS

Practical Implementation of Geospatial Queries Using SQL with PostGIS

This section will cover practical implementations of common geospatial queries using SQL within the PostGIS extension of PostgreSQL.

1. Finding Points within a Distance

To find all points (e.g., cities) within a specified distance from a given point (e.g., a location), you can use the ST_DWithin function.

SELECT city_name
FROM cities
WHERE ST_DWithin(
  cities.geom,
  ST_SetSRID(ST_MakePoint(lon, lat), 4326),
  distance_in_meters
);

2. Calculating the Distance between Two Points

To calculate the distance between two points in meters, use the ST_Distance function.

SELECT city1.name AS city1_name, city2.name AS city2_name, 
       ST_Distance(city1.geom::geography, city2.geom::geography) AS distance_meters
FROM cities AS city1, cities AS city2
WHERE city1.id <> city2.id;

3. Finding Intersecting Geometries

Find all geometries (e.g., parks) that intersect with a given geometry (e.g., a proposed building site).

SELECT park_name
FROM parks
WHERE ST_Intersects(parks.geom, building_site.geom);

4. Union of Multiple Geometries

To create a single geometry that represents the union of multiple geometries, use the ST_Union function.

SELECT ST_Union(ARRAY(SELECT geom FROM regions))
FROM regions;

5. Geospatial Join (Spatial Join)

To perform a spatial join to find information about points (e.g., schools) that fall within specific areas (e.g., districts):

SELECT districts.district_name, COUNT(schools.school_id)
FROM districts
JOIN schools
ON ST_Contains(districts.geom, schools.geom)
GROUP BY districts.district_name;

6. Clipping Geometries

To clip one geometry set by another, you can use the ST_Intersection function.

SELECT ST_Intersection(forest.geom, river.geom) AS clipped_geom
FROM forest, river
WHERE ST_Intersects(forest.geom, river.geom);

7. Aggregating Geospatial Data

Creating aggregated data based on geospatial attributes using ST_Collect to gather geometries and ST_ConvexHull to simplify into a convex hull.

SELECT district_id,
       ST_ConvexHull(ST_Collect(geom)) AS convex_hull
FROM parcels
GROUP BY district_id;

8. Buffering Geometries

Creating a buffer around geometries to represent areas within a certain distance of features.

SELECT ST_Buffer(geom, buffer_distance_meters) AS buffered_geom
FROM highways;

9. Transforming Coordinate Systems

Transform geometries from one spatial reference system to another using ST_Transform.

SELECT ST_Transform(geom, 3857) AS web_mercator_geom
FROM spatial_table
WHERE ST_SRID(geom) = 4326;

Conclusion

The above practical implementations cover key geospatial queries and operations you can perform using SQL with PostGIS. These can be applied directly in your database to manage and analyze geospatial data effectively.

Part 8: Distance and Proximity Queries with SQL

In this part, we will cover practical implementations of distance and proximity queries using SQL. Here, we assume you already have a spatial database with relevant geospatial data and have some familiarity with basic SQL.

Distance Queries

To calculate the distance between two geometries, we use the ST_Distance function. This function takes two geometries and returns the distance between them.

Example: Calculating Distance Between Two Points

SELECT 
    ST_Distance(
        ST_GeomFromText('POINT(-71.060316 48.432044)', 4326),
        ST_GeomFromText('POINT(-73.935242 40.730610)', 4326)
    ) AS distance;

Proximity Queries

Proximity queries are used to find all geometries within a certain distance from a given geometry. We use the ST_DWithin function, which returns true if geometries are within the given distance.

Example: Finding All Points Within a Certain Distance

Assume we have a table called locations with a geometry column. We want to find all locations within 10 kilometers of a given point.

SELECT 
    id, 
    name,
    geometry
FROM 
    locations
WHERE 
    ST_DWithin(
        geometry,
        ST_GeomFromText('POINT(-71.060316 48.432044)', 4326),
        10000  -- Distance in meters
    );

Example: Finding All Rows Using ST_Distance

Another approach is using ST_Distance in the WHERE clause for maximum flexibility:

SELECT 
    id, 
    name,
    geometry
FROM 
    locations
WHERE 
    ST_Distance(
        geometry,
        ST_GeomFromText('POINT(-71.060316 48.432044)', 4326)
    ) <= 10000;  -- Distance in meters

Query Optimization

When dealing with large datasets, an index can significantly speed up proximity queries. Make sure your geometry column is indexed:

CREATE INDEX idx_locations_geom ON locations USING GIST (geometry);

Using an index will optimize both ST_DWithin and ST_Distance queries, ensuring they are performed efficiently.

Further Exploration

1. Buffer Queries: To visualize the area around a point within a specific distance.
2. Union and Intersection: Combine or intersect various geometries based on distance.

SELECT 
    ST_Union(buffered_geom)
FROM
    (SELECT 
        ST_Buffer(
            ST_GeomFromText('POINT(-71.060316 48.432044)', 4326), 
            10000
        ) AS buffered_geom
    ) AS subquery;

In conclusion, distance and proximity queries in SQL provide powerful tools for geospatial analysis. Make sure your data is appropriately indexed and leverage functions like ST_DWithin and ST_Distance for efficient query performance. This practical implementation should integrate seamlessly into your existing geospatial database work.

Clustering and Spatial Analysis with SQL

Step 1: Load Data

First, ensure that you have your geospatial data loaded into your SQL database. Assume you have a table locations with id, name, and geom (geometry column).

SELECT * FROM locations;

Step 2: Spatial Clustering using K-Means

Unfortunately, SQL does not natively support K-Means clustering directly. Hence, you might use common table expressions (CTEs) and window functions to approximate this or call external functions if the database supports them (like PostGIS).

Here's a simplified version of hierarchical clustering:

WITH RECURSIVE clusters AS (
    SELECT
        id,
        name,
        geom,
        id AS cluster_id
    FROM locations
    UNION ALL
    SELECT
        l.id,
        l.name,
        l.geom,
        c.cluster_id
    FROM clusters AS c
    JOIN locations AS l
    ON ST_DWithin(c.geom, l.geom, 1000)  -- distance threshold in meters
    WHERE l.id <> c.id
)
SELECT DISTINCT ON (id) id, name, geom, cluster_id
FROM clusters;

Step 3: Calculate Centroids of Clusters

Calculate the centroid of each cluster to represent the cluster center.

WITH clusters AS (
    SELECT
        id,
        name,
        geom,
        -- Use some clustering method like the previous example's cluster_id
        cluster_id
    FROM locations
)
SELECT
    cluster_id,
    ST_Centroid(ST_Collect(geom)) AS centroid
FROM clusters
GROUP BY cluster_id;

Step 4: Nearest Neighbor Analysis

Find the nearest neighbor for each location within a specified distance.

SELECT
    a.id AS loc_id,
    b.id AS neighbor_id,
    ST_Distance(a.geom, b.geom) AS distance
FROM
    locations AS a,
    locations AS b
WHERE
    a.id <> b.id
    AND ST_DWithin(a.geom, b.geom, 1000) -- distance threshold in meters
ORDER BY
    a.id,
    distance;

Step 5: Spatial Join for Point-in-Polygon Queries

Determine which points fall within given polygons.

SELECT
    l.id,
    l.name,
    p.id AS polygon_id,
    p.name AS polygon_name
FROM
    locations AS l,
    polygons AS p
WHERE
    ST_Within(l.geom, p.geom);

Step 6: Visualizing Clusters (Optional)

While SQL itself can't visualize data, exporting the results to a GIS tool (like QGIS) will allow for visual representation.

Conclusion

This practical implementation outlines how SQL can be used for clustering and spatial analysis by defining clusters, calculating centroids, performing nearest neighbor analysis, and running spatial join operations to analyze geospatial data effectively.

Real-world Applications of Geospatial Queries

Introduction

Geospatial queries are crucial for various applications in fields such as urban planning, environmental monitoring, logistics, and more. This section focuses on practical implementations of geospatial queries in real-world scenarios using SQL.

---

Example Use Cases

1. Finding Nearby Amenities

You might need to find amenities (like schools, hospitals, or stores) within a certain distance from a given point. This can be beneficial for real estate apps or urban development planning.

Query: Schools within 5 km of a given point

SELECT id, name, ST_Distance(location, ST_GeomFromText('POINT(lon lat)', 4326)) as distance
FROM schools
WHERE ST_DWithin(location, ST_GeomFromText('POINT(lon lat)'), 5000);

2. Identifying Areas at Risk of Natural Disasters

Local governments and emergency services need to identify properties within flood zones to allocate resources or alert residents.

Query: Properties within a flood zone

SELECT p.id, p.address
FROM properties p
JOIN flood_zones fz ON ST_Intersects(p.geometry, fz.geometry);

3. Optimizing Delivery Routes

Logistics companies often need to optimize delivery routes based on the spatial distribution of delivery points.

Query: Nearest delivery points for optimization

SELECT dp1.id, dp2.id, ST_Distance(dp1.location, dp2.location) as distance
FROM delivery_points dp1, delivery_points dp2
WHERE dp1.id != dp2.id
ORDER BY distance ASC
LIMIT 1;

4. Monitoring Environmental Changes

Environmental agencies monitor changes in land usage by comparing satellite images over time.

Query: Areas of deforestation detected between two time periods

SELECT area_id, 
       ST_Area(ST_Difference(old_image.geometry, new_image.geometry)) as deforested_area
FROM satellite_images old_image, satellite_images new_image
WHERE old_image.timestamp < '2020-01-01'
AND new_image.timestamp > '2020-01-01'
AND ST_Intersects(old_image.geometry, new_image.geometry);

5. Urban Planning and Zoning

Urban planners need to identify which zones can be developed or modified based on existing infrastructure and spatial regulations.

Query: Vacant lots available for development in a specific zone

SELECT l.id, l.address
FROM lots l
JOIN zones z ON ST_Within(l.geometry, z.geometry)
WHERE z.zone_type = 'residential'
AND l.status = 'vacant';

---

Conclusion

These queries demonstrate how geospatial data can be applied in various real-world scenarios. By integrating spatial queries into your SQL workflows, you can extract meaningful insights and make informed decisions based on spatial data.