# Babelfish supports Geospatial data types Starting with versions 3.5.0 and 4.1.0, Babelfish includes support for the following two spatial data types: + **Geometry data type** – This data type is intended for storing planar or Euclidean (flat-earth) data. + **Geography data type** – This data type is intended for storing ellipsoidal or round-earth data, such as GPS latitude and longitude coordinates. These data types allow for the storage and manipulation of spatial data, but with limitations. ## Understanding the Geospatial data types in Babelfish + Geospatial data types are supported in various database objects such as views, procedures, and tables. + Supports point data type to store location data as points defined by latitude, longitude, and a valid Spatial Reference System Identifier (SRID). A point may contain Z (elevation), M (measure) values and can be empty. + Supports linestring data type (from version 5.4.0) defined by a sequence of points and the line segments connecting them and a valid Spatial Reference System Identifier (SRID). A linestring may contain points with Z (elevation), M (measure) values and can be empty. + Supports polygon data type (from version 5.5.0 and 6.0.0). A Polygon is a two-dimensional surface stored as a sequence of points defining an exterior bounding ring and zero or more interior rings. + Applications connecting to Babelfish through drivers like JDBC, ODBC, DOTNET, and PYTHON can utilize this Geospatial feature. ### Geometry data type functions supported in Babelfish + **STGeomFromText (*geometry\$1tagged\$1text*, SRID )** – Creates a geometry instance using Well-Known Text (WKT) representation. + **STPointFromText (*point\$1tagged\$1text*, SRID )** – Creates a point instance using WKT representation. + **Point ( X, Y, SRID )** – Creates a point instance using float values of x and y coordinates. + **.STAsText ( )** – Extracts WKT representation from geometry instance. + **.STAsBinary ( )** – Extracts WKB representation from geometry instance. + **.STArea ( )** – Calculates the total surface area of geometry instance. + **.STSrid ( )** – Extracts the spatial reference identifier (SRID) of the geometry instance. + **.STDimension ( )** – Retrieves spatial dimension of geometry instance. + **.STIsEmpty ( )** – Checks if the geometry instance is empty. + **.STIsClosed ( )** – Checks if the geometry instance is closed. + **.STIsValid ( )** – Checks if the geometry instance is valid. + **.STDistance (other\$1geometry)** – Calculates the distance between two geometry instances. + **.STEquals (other\$1geometry)** – Checks if the geometry instance represents the same point set as another geometry instance. + **.STContains (other\$1geometry)** – Checks if the geometry instance contains the other\$1geometry instance. + **.STDisjoint (other\$1geometry)** – Checks if two geometry instances have no points in common. + **.STIntersects (other\$1geometry)** – Checks if two geometry instances spatially intersect. + **.STX** – Extracts the X coordinate (longitude) for geometry instance. + **.STY** – Extracts the Y coordinate (latitude) for geometry instance. Starting with versions 4.7.0 and 5.3.0, Babelfish includes support for the following spatial data functions: + **.M** – Extracts the M coordinate of the geometry instance. + **.Z** – Extracts the Z coordinate of the geometry instance. + **.HasM** – Checks if the geometry instance has atleast one M value. + **.HasZ** – Checks if the geometry instance has atleast one Z value. Starting with versions 5.4.0, Babelfish includes support for the following spatial data function: + **STLineFromText (*linestring\$1tagged\$1text*, SRID )** – Creates a linestring instance using WKT representation. Starting with versions 5.5.0 and 6.0.0, Babelfish includes support for the following spatial data function: + **STPolyFromText (*polygon\$1tagged\$1text*, SRID )** – Creates a polygon instance using WKT representation. ### Geography data type functions supported in Babelfish + **STGeomFromText (*geography\$1tagged\$1text*, SRID )** – Creates a geography instance using WKT representation. + **STPointFromText (*point\$1tagged\$1text*, SRID )** – Creates a point instance using WKT representation. + **Point (Lat, Long, SRID)** – Creates a point instance using float values of Latitude and Longitude. + **.STAsText ( )** – Extracts WKT representation from geography instance. + **.STAsBinary ( )** – Extracts WKB representation from geography instance. + **.STArea ( )** – Calculates the total surface area of geography instance. + **.STSrid ( )** – Extracts the spatial reference identifier (SRID) of the geography instance. + **.STDimension ( )** – Retrieves spatial dimension of geography instance. + **.STIsEmpty ( )** – Checks if the geography instance is empty. + **.STIsClosed ( )** – Checks if the geography instance is closed. + **.STIsValid( )** – Checks if the geography instance is valid. + **.STDistance (other\$1geography)** – Calculates the distance between two geography instances. + **.STEquals (other\$1geography)** – Checks if the geography instance represents the same point set as another geography instance. + **.STContains (other\$1geography)** – Checks if the geography instance contains the other\$1geography instance. + **.STDisjoint (other\$1geography)** – Checks if two geography instances have no points in common. + **.STIntersects (other\$1geography)** – Checks if two geography instances spatially intersect. + **.Lat** – Extracts the Latitude value for geography instance. + **.Long** – Extracts the Longitude value for geography instance. Starting with versions 4.7.0 and 5.3.0, Babelfish includes support for the following spatial data functions: + **.M** – Extracts the M coordinate of the geography instance. + **.Z** – Extracts the Z coordinate of the geography instance. + **.HasM** – Checks if the geography instance has atleast one M value. + **.HasZ** – Checks if the geography instance has atleast one Z value. Starting with versions 5.4.0, Babelfish includes support for the following spatial data function: + **STLineFromText (*linestring\$1tagged\$1text*, SRID )** – Creates a linestring instance using WKT representation. Starting with versions 5.5.0 and 6.0.0, Babelfish includes support for the following spatial data function: + **STPolyFromText (*polygon\$1tagged\$1text*, SRID )** – Creates a polygon instance using WKT representation. ## Limitations in Babelfish for Geospatial data types + Geometry and Geography types other than point, linestring and polygon instances aren't currently supported: + CircularString + CompoundCurve + CurvePolygon + MultiPoint + MultiLineString + MultiPolygon + GeometryCollection + Currently, spatial indexing isn't supported for Geospatial data types. + Only the listed functions are currently supported for these data types. For more information, see [Geometry data type functions supported in Babelfish](#babelfish-geospatial-overview-geometry) and [Geography data type functions supported in Babelfish](#babelfish-geospatial-overview-geography). + STDistance function output for Geography data might have minor precision variations compared to T-SQL. This is due to the underlying PostGIS implementation. For more information, see [ST\$1Distance](https://postgis.net/docs/ST_Distance.html) + STIsValid function output for both Geometry and Geography data might have some deviations compared to T-SQL. Due to this, the functions - STDistance, STContains, STInstersects, STDisjoint, STDimension, STArea, STEquals may also deviate from T-SQL for some cases ( returns output instead of throwing error ). This is due to the underlying PostGIS implementation. For more information, see [ST\$1IsValid](https://postgis.net/docs/ST_IsValid.html). + For optimal performance, use built-in Geospatial data types, without creating additional layers of abstraction in Babelfish. + In Babelfish, Geospatial function names are used as keywords and will perform spatial operations only if used in the intended way.