Geometry SQL Extensions

1. Available functions

These are the Geometry SQL functions available in LeanXcale.

When a function returns a specific data type, it’s a good practice to cast it to the expected one to avoid problems.

1.1. byte[] ST_AsWkb(Geom geom)

Converts geom to Well Known Binary

1.2. String ST_fromWkbAsWkt(byte[] value)

Transform WKB to WKT

1.3. String ST_AsText(Geom g)

Converts geom to Well Known Text

1.4. String ST_AsWKT(Geom g)

1.5. Geom ST_GeomFromText(String s)

1.6. Geom ST_GeomFromText(String s, int srid)

1.7. Geom ST_GeomFromWkb(byte[] value)

1.8. Geom ST_LineFromText(String s)

1.9. Geom ST_LineFromText(String wkt, int srid)

1.10. Geom ST_MPointFromText(String s)

1.11. Geom ST_MPointFromText(String wkt, int srid)

1.12. Geom ST_PointFromText(String s)

1.13. Geom ST_PointFromText(String wkt, int srid)

1.14. Geom ST_PolyFromText(String s)

1.15. Geom ST_PolyFromText(String wkt, int srid)

1.16. Geom ST_MLineFromText(String s)

Creates a line-string from the given POINTs (or MULTIPOINTs).

1.17. Geom ST_MLineFromText(String wkt, int srid)

1.18. Geom ST_MPolyFromText(String s)

1.19. Geom ST_MPolyFromText(String wkt, int srid)

1.20. String ST_GeomFromGeoJSON(String geoJson)

Takes as input a geojson representation of a geometry and outputs a geometry object.

1.21. String ST_AsGeoJSON(Geom geom)

Return the geometry as a GeoJSON element.

1.22. Geom ST_MakeLine(Geom geom1, Geom geom2)

1.23. Geom ST_MakeLine(Geom geom1, Geom geom2, Geom geom3)

1.24. Geom ST_MakeLine(Geom geom1, Geom geom2, Geom geom3,

1.25. Geom ST_MakeLine(Geom geom1, Geom geom2, Geom geom3,

1.26. Geom ST_MakeLine(Geom geom1, Geom geom2, Geom geom3,

1.27. Geom ST_MakePoint(double x, double y)

Constructs a 2D point from coordinates.

1.28. Geom ST_MakePoint(BigDecimal x, BigDecimal y)

Alias for ST_Point(BigDecimal, BigDecimal).

Use Geom ST_MakePoint(double x, double y) for better perfomance*

1.29. Geom ST_MakePoint(BigDecimal x, BigDecimal y, BigDecimal z)

Alias for ST_Point(BigDecimal, BigDecimal, BigDecimal)

1.30. Geom ST_Point(BigDecimal x, BigDecimal y)

Constructs a 2D point from coordinates.

Use Geom ST_MakePoint(double x, double y) for better perfomance

1.31. Geom ST_Point(BigDecimal x, BigDecimal y, BigDecimal z)

Constructs a 3D point from coordinates.

1.32. boolean ST_Is3D(Geom geom)

Alias for ST_Point(BigDecimal, BigDecimal, BigDecimal)

1.33. Double ST_X(Geom geom)

Returns the x-value of the first coordinate of {@code geom}.

1.34. Double ST_Y(Geom geom)

Returns the y-value of the first coordinate of {@code geom}

1.35. Double ST_Z(Geom geom)

Returns the y-value of the first coordinate of {@code geom}

1.36. Geom ST_Boundary(Geom geom)

Returns the boundary of {@code geom}

1.37. double ST_Distance(Geom geom1, Geom geom2)

Returns the distance between {@code geom1} and {@code geom2}

1.38. String ST_GeometryType(Geom geom)

Returns the type of {@code geom}.

1.39. int ST_GeometryTypeCode(Geom geom)

Returns the OGC SFS type code of {@code geom}

1.40. Geom ST_Envelope(Geom geom)

Returns the minimum bounding box of {@code geom} (which may be a GEOMETRYCOLLECTION).

1.41. boolean ST_Contains(Geom geom1, Geom geom2)

Returns whether {@code geom1} contains {@code geom2}.

1.42. boolean ST_ContainsProperly(Geom geom1, Geom geom2)

Returns whether {@code geom1} contains {@code geom2} but does not intersect its boundary

1.43. boolean ST_Crosses(Geom geom1, Geom geom2)

Returns whether {@code geom1} crosses {@code geom2}

1.44. boolean ST_Disjoint(Geom geom1, Geom geom2)

Returns whether {@code geom1} and {@code geom2} are disjoint.

1.45. boolean ST_EnvelopesIntersect(Geom geom1, Geom geom2)

Returns whether the envelope of {@code geom1} intersects the envelope of {@code geom2}.

1.46. boolean ST_Equals(Geom geom1, Geom geom2)

Returns whether {@code geom1} equals {@code geom2}.

1.47. boolean ST_Intersects(Geom geom1, Geom geom2)

Returns whether {@code geom1} intersects {@code geom2}.

1.48. boolean ST_OrderingEquals(Geom geom1, Geom geom2)

Returns whether {@code geom1} equals {@code geom2} and their coordinates and component Geometries are listed in the same order.

1.49. boolean ST_Overlaps(Geom geom1, Geom geom2)

Returns {@code geom1} overlaps {@code geom2}.

1.50. boolean ST_Touches(Geom geom1, Geom geom2)

Returns whether {@code geom1} touches {@code geom2}.

1.51. boolean ST_Within(Geom geom1, Geom geom2)

Returns whether {@code geom1} is within {@code geom2}.

1.52. boolean ST_DWithin(Geom geom1, Geom geom2, double distance)

Returns whether {@code geom1} and {@code geom2} are within

{@code distance} of each other.

1.53. Geom ST_Buffer(Geom geom, double distance)

Computes a buffer around {@code geom}.

1.54. Geom ST_Buffer(Geom geom, double distance, int quadSegs)

Computes a buffer around {@code geom} with .

1.55. Geom ST_Buffer(Geom geom, double bufferSize, String style)

Computes a buffer around {@code geom}.

1.56. Geom ST_Union(Geom geom1, Geom geom2)

Computes the union of {@code geom1} and {@code geom2}.

1.57. Geom ST_Union(Geom geomCollection)

Computes the union of the geometries in {@code geomCollection}.

1.58. Geom ST_Intersection(Geom geom1, Geom geom2)

Computes the intersection of {@code geom1} and {@code geom2}.

1.59. Geom ST_Transform(Geom geom, int srid)

Transforms {@code geom} from one coordinate reference system (CRS) to the CRS specified by {@code srid}

1.60. Geom ST_SetSRID(Geom geom, int srid)

Returns a copy of {@code geom} with a new SRID.

2. Using Geometry functions in LeanXcale

Example of how to select all those countries within a distance of 10 degrees from Madrid:

CREATE TABLE countries(
    country varchar,
    latitude decimal,
    longitude decimal,
    name varchar,
    primary key(country)
);

SELECT name
FROM countries
WHERE
    ST_DWithin(
        ST_MakePoint(
            cast(40.25 as double), cast(3.41 as double)
        ),
        ST_MakePoint(
            cast(latitude as double), cast(longitude as double)
        ),
        cast(10 as double)
    );