Spatial SQL | Simple Feature Access SQL functions
ESRI's new ST_GEOMETRY data type for Oracle offers a total of 88 functions. For SQL select statements, their are basically two groups of functions: ones that you can use in a select clause; and others you can use in a where clause. These functions are in additional to being able to use all the standard SQL functions (sum,count etc) so you can mix spatial criteria with traditional SQL (ESRI's webhelp published a full list of ST_GEOMETRY functions).
An example of the select clause functions are the map extent basics like:
- ST_X, ST_Y: Which will return the x and y coordinates (that's Latitude and Longitude) of a point
- ST_minX, ST_minY, ST_maxX, ST_maxY: Which return the extend (or bounding box) for a geographic feature/row
- ST_Area: No prizes for guess this returns the area of a spatial feature.
These are the simple one, the real power of ST_GEOMETRY comes when you look into the where clause functions.
The where clause functions allow you to do traditional spatial overlay operations in plain old SQL. The list of functions is pretty good, some examples are: ST_Intersects; ST_Union (returns the union of two source objects); ST_Overlaps; ST_Crosses; ST_Touches, ST_Contains, ST_Buffer, ... the list goes on. (Ah, it brings back great memories of high school maths and Venn diagrams!)
It is these type of functions that really give the ST_GEOMETRY data type its teeth. These functions are the building block that allow you to ask questions like:
- "What fire hydrants are with a x distance buffer of major roads?"
- "What is the count and total area of land parcels with a given area?"
- "What is the closest public library/hotspot/toilet to me?"
- "What ATMs are within x distance of my location?"
These are the type of questions that make geography so much fun!