Mastering Spatial Queries
Spatial queries are essential for geographic data analysis. These queries allow you to retrieve and manipulate data like points, lines, and polygons. Commonly used in GIS, mapping, and location-based services, spatial queries help analyze geospatial data efficiently.
1. Finding Nearby Locations (ST_DWithin)
ST_DWithin retrieves locations within a specific distance from a point.
SELECT location_name
FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(106.84513, -6.21462), 1000);
This query finds locations within 1 km of (106.84513, -6.21462).
2. Finding Locations Inside an Area (ST_Within)
ST_Within identifies geometries inside a boundary.
SELECT location_name
FROM locations
WHERE ST_Within(geom, ST_GeomFromText('POLYGON((...))'));
This query retrieves all locations inside a polygon, useful for zoning analysis.
3. Identifying Intersecting Geometries (ST_Intersects)
ST_Intersects finds geometries that intersect with a line or area.
SELECT street_name
FROM streets
WHERE ST_Intersects(geom, ST_MakeLine(ST_MakePoint(106.84513, -6.21462), ST_MakePoint(106.85000, -6.22000)));
This query identifies streets that intersect a line.
4. Calculating Area (ST_Area)
ST_Area calculates the area of a polygon.
SELECT area_name, ST_Area(geom) AS area_size
FROM areas;
This query calculates the area of each polygon, useful for land management.
5. Finding Adjacent Geometries (ST_Touches)
ST_Touches identifies geometries sharing a boundary.
SELECT location_name
FROM locations
WHERE ST_Touches(geom, ST_GeomFromText('POLYGON((...))'));
This query retrieves locations that touch a polygon's boundary.
6. Finding Centroids (ST_Centroid)
ST_Centroid finds the center of a polygon.
SELECT ST_Centroid(geom)
FROM areas
WHERE area_name = 'National Park';
This query returns the centroid of 'National Park,' useful for mapping.
7. Creating Buffers (ST_Buffer)
ST_Buffer creates a buffer around a geometry.
SELECT ST_Buffer(geom, 500)
FROM locations
WHERE location_name = 'Monas';
This query creates a 500-meter buffer around 'Monas.'
8. Measuring Distance (ST_Distance)
ST_Distance calculates the shortest distance between two points.
SELECT ST_Distance(
ST_MakePoint(106.84513, -6.21462),
ST_MakePoint(107.6114, -6.9345)
) AS distance;
This query calculates the distance between two points, useful for logistics.
9. Finding Overlapping Geometries (ST_Overlaps)
ST_Overlaps identifies geometries that partially overlap.
SELECT area_name
FROM areas
WHERE ST_Overlaps(geom, ST_GeomFromText('POLYGON((...))'));
This query finds areas overlapping a polygon.
10. Transforming Coordinate Systems (ST_Transform)
ST_Transform changes the spatial reference system of a geometry.
SELECT ST_Transform(geom, 4326)
FROM locations
WHERE id = 1;
This query transforms geometry to SRID 4326 (WGS 84), the GPS standard.
GHC 23'|Actively Looking for DE/SDE full time - MS DS UCSD
2moI think I'm a perfect fit for this job and my background matches perfectly! Please take a look at the resume I submitted and look forward to your reply. Thank you very much!