Spatial query refers to the questions asked to a spatial database containing spatial data through set of commands (SQL). The questions may be like ‘what are the neighbouring local levels of a particular Palika’, ‘Number of point data within a particular area ’, ‘Distance between two points’ and so on.
To have clear understanding of spatial SQl lets see some of it’s example.
1. Selecting local levels that lies only in Rupandehi district
SELECT local, type
FROM “public”.”LocalLevel”
where district= ‘Rupandehi’;
- Above query gives only local levels that are in Rupandehi district.
2. Counting number of local levels that starts with ‘B’
SELECT Count(*)
FROM “public”.”LocalLevel”
WHERE local LIKE ‘B%’; Here public = schema that contains table LocalLevel; local is the attribute name.
3. Finding area of a geometry
SELECT ST_Area(geom)/1000000 as AreaKmSq, local
FROM “public”.”LocalLevel_44N”
WHERE local=’Butwal’
- The above sql code calculates the area of only Butwal which is by default in m2 and is later converted to km2 by dividing by 1000000.
- IF your data is in geographic coordinate system then you may not get exact area and for that you have to transform it to projected system.
4. Finding neighbouring palika of Butwal
SELECT l1.local, l1.geom
FROM “public”.”LocalLevel_44N” l1, “public”.”LocalLevel_44N” l2
WHERE ST_Touches(l1.geom, l2.geom) AND l2.local = ‘Butwal’
- Above code simply shows the neighbouring local level of ‘Butwal’. Here we have considered two layer l1 and l2 and told database to show only l1 with condition to show only that local from l1 that is in the neighbourhood of local = ‘Butwal’ from l2.
- ST_Touches: It is used when two geometries touch at the boundaries not intersecting.
NOTE: if you give “Butwal” instead of ‘Butwal’ it will show you with error that the column name Butwal doesn’t exist.
5. Finding local levels that lie completely within a given district
SELECT l1.local
FROM “public”.”LocalLevel_44N” l1, “public”.”District_44N” D1
WHERE ST_Within(l1.geom, D1.geom) AND D1.district = ‘RUPANDEHI’
- For this query two data layers (table) are used one LocalLevel_44N containing local level data and other District_44N with only district data.
- This query will show only local of table LocalLevel_44N that lies completely within of district RUPANDEHI of table District_44N. It’s not going to show the local level that are with in boundary of RUPANDEHI.
6. Selecting all Palikas that are within 2500 m of a location.
SELECT local
FROM “public”.”LocalLevel_44N”
WHERE ST_DWithin(
geom,
ST_GeomFromText(‘POINT(736116.63 3051532.88)’, 32644),
2500
);
