Shulou(Shulou.com)05/31 Report--

Data Lake Analytics's Geospatial analysis function how to understand, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

0. Brief introduction

In order to meet the needs of some customers to analyze Geometry data on the cloud, Aliyun Data Lake Analytics (hereinafter referred to as: DLA) supports geospatial data processing functions in various formats, in line with Open Geospatial Consortium's (OGC) OpenGIS specification, and supports common data formats including:

WKT

WKB

GeoJson

ESRI Geometry Object Json

ESRI Shape

DLA adopts 4326 coordinate system standard, while EPSG 4326 uses longitude and latitude coordinates, which belongs to geographical coordinate system. This is the coordinate system used by GPS.

1. WKT data

Detailed description:

Https://en.wikipedia.org/wiki/Well-known_text

Http://www.opengeospatial.org/standards/wkt-crs

WKT-related strings similar to the following are supported.

POINT (0 0)

LINESTRING (0 0, 1 1, 1 2)

POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)

MULTIPOINT (0 0,1 2)

MULTILINESTRING ((0.0,11,12), (23,32,54))

MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1)

GEOMETRYCOLLECTION (POINT (2 3), LINESTRING (2 3, 3 4))

2. WKB data

Detailed description: https://en.wikipedia.org/wiki/Well-known_text#Well-known_binary

Examples of the above WKT data and WKB:

WKTWKBPOINT (00) 010100000000000000000000000000000000000000LINESTRING (00,11,12) 01020000000300000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F0000000000000040POLYGON ((00,40,44,04,00), (11,21,22,12,11) 01030000000200000005000000000000000000000000000000000000000000000000001040000000000000000000000000000010400000000000001040000000000000000000000000000010400000000000000000000000000000000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03FMULTIPOINT (00,12) 0104000000020000000101000000000000000000000000000000000000000101000000000000000000F03F0000000000000040MULTILINESTRING ((00,11,12), (23,32,54)) 01050000000200000001020000000300000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F0000000000000040010200000003000000000000000000004000000000000008400000000000000840000000000000004000000000000014400000000000001040MULTIPOLYGON ((00,40,44,04,00), (1,21,22,1211)) ((- 1-1,-1-2,-2-2,-2-1,-1-1)) 01060000000200000001030000000200000005000000000000000000000000000000000000000000000000001040000000000000000000000000000010400000000000001040000000000000000000000000000010400000000000000000000000000000000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03F01030000000100000005000000000000000000F0BF000000000000F0BF00000000000000C0000000000000F0BF00000000000000C000000000000000C0000000000000F0BF00000000000000C0000000000000F0BF000000000000F0BFGEOMETRYCOLLECTION (POINT (2 3), LINESTRING (2 3, 3 4)) 0107000000020000000000000000000000000000000000000000000000000000000000008400000000008400000000000000010403. GeoJson data

Detailed description of GeoJson: http://geojson.org/

4. ESRI Geometry Object Json data

Specification: http://resources.esri.com/help/9.3/arcgisserver/apis/REST/geometry.html

5. ESRI Shape binary data

Specification: http://www.esri.com/LIBRARY/WHITEPAPERS/PDFS/SHAPEFILE.PDF

6. The function list NameDescriptionST_asText converts Geometry data into string data in WKT format. ST_LineFromText inputs Line string data in WKT format to generate Geometry data for Line. ST_Point generates Geometry data of the corresponding Point from coordinate values (X, Y) of the coordinate system. ST_Polygon inputs Polygon string data in WKT format to generate Geometry data for Polygon. ST_Area returns the area of a face or multiple faces. For points and lines, 0.0 is returned. For GeometryCollection, returns the sum of all individual areas. ST_GeometryFromText inputs string data in WKT format to generate Geometry data. ST_Buffer gets the geometric object and the distance, and then returns the geometric object that represents the buffer around the source object. ST_Centroid gets the center point of the geometric object. ST_CoordDim returns the coordinate value dimension of the geometric object. ST_Dimension is used to return the dimensions of geometric objects. In this case, dimensions refer to length and width. For example, a point has neither length nor width, so its dimension is 0, while a line has only length but no width, so its dimension is 1. ST_IsClosed determines whether Line or MultiLine is closed. ST_IsEmpty determines whether the geometric object is empty. ST_Length calculates the length of Line or MultiLine. ST_XMax returns the maximum X coordinate value of the geometric object in the coordinate system. ST_XMin returns the minimum X coordinate value of the geometric object in the coordinate system. ST_YMax returns the maximum Y coordinate value of the geometric object in the coordinate system. ST_YMin returns the minimum Y coordinate value of the geometric object in the coordinate system. ST_NumInteriorRing takes Polygon as the input parameter and returns the number of its internal rings. ST_NumPoints is used to return the number of points (break points) in a geometric object. ST_IsRing takes Line as the input parameter to determine whether it is a loop (for example, Line is closed). ST_StartPoint is used to return the first point of Line. ST_EndPoint is used to return the last point of Line. ST_X returns the X coordinate of Point. ST_Y returns the Y coordinate of Point. ST_Boundary inputs a geometric object and then returns its combined boundary as a geometric object. ST_Envelope returns the smallest bounding box of a geometric object as a polygon. ST_Difference enters two geometric objects and returns a geometric object that represents the difference between the two source objects. ST_Distance is used to return the distance between two geometric objects. This distance is the distance between the closest break points of two geometric objects. ST_ExteriorRing returns the outer ring of the face as Line. ST_Intersection takes two geometric objects as input parameters and then returns the intersection as a two-dimensional geometric object. ST_SymDifference returns a geometric value object that represents the symmetrical difference in the set of points between two geometric objects. ST_Contains inputs two geometric objects to determine whether the first object fully contains the second object. ST_Crosses takes two geometric objects as input, and returns 1 if the dimension of the geometric object generated by the intersection of the two objects is less than the largest of the two source objects. The intersection object must contain points within the two source geometry and not equal to either of the source objects. Otherwise, 0 is returned. ST_Disjoint inputs two geometric objects to determine whether the intersection of two geometric objects is an empty set. ST_Equals determines whether two geometric objects are exactly the same. ST_Intersects determines whether the intersection of two geometric objects does not generate an empty set. ST_Overlaps determines whether the geometric objects generated by the intersection of two geometric objects have the same dimension but are not equal to any source object. ST_Relate compares two geometric objects to determine whether the conditions specified by the "DE-9IM" pattern (https://en.wikipedia.org/wiki/DE-9IM) matrix string are met. ST_Touches determines whether the common points of two geometric objects do not intersect with the interior of the two geometric objects. ST_Within determines whether the first geometric object is completely within the scope of the second geometric object. ST_asBinary inputs a geometric object and returns its recognizable binary WKB data. ST_GeometryFromWKBHexString inputs the HEX string data of WKB and returns the corresponding geometric object. ST_pointFromWKBHexString inputs the HEX string data of Point's WKB and returns the corresponding Point geometric object. ST_lineFromWKBHexString inputs the HEX string data of Line's WKB and returns the corresponding Line geometric object. ST_polyFromWKBHexString inputs the HEX string data of Polygon's WKB and returns the corresponding Polygon geometric object. ST_MPointFromWKBHexString inputs the HEX string data of MultiPoint's WKB and returns the corresponding MultiPoint geometric object. ST_MLineFromWKBHexString inputs the HEX string data of MultiLine's WKB and returns the corresponding MultiLine geometric object. ST_MPolyFromWKBHexString inputs the HEX string data of MultiPolygon's WKB and returns the corresponding MultiPolygon geometric object. ST_GeometryFromWKB inputs WKB data and returns the corresponding geometric objects. ST_pointFromWKB inputs the WKB data of Point and returns the corresponding Point geometric object. ST_lineFromWKB inputs the WKB data of Line and returns the corresponding Line geometric object. ST_polyFromWKB inputs the WKB data of Polygon and returns the corresponding Polygon geometric object. ST_MPointFromWKB inputs the WKB data of MultiPoint and returns the corresponding MultiPoint geometric object. ST_MLineFromWKB inputs the WKB data of MultiLine and returns the corresponding MultiLine geometric object. ST_MPolyFromWKB inputs the WKB data of MultiPolygon and returns the corresponding MultiPolygon geometric object. ST_GeometryFromGeoJson inputs the string data of GeoJson and returns the corresponding geometric object. ST_GeometryFromJson inputs the string data of ESRI Geometry Object Json and returns the corresponding geometric object. ST_asGeoJson converts geometric objects to GeoJson format for output. ST_asJson converts geometric objects to ESRI Geometry Object Json format for output. ST_GeometryFromEsriShape inputs the binary data of ESRI Shape and returns the corresponding geometric object. UDF_SYS_GEO_IN_CYCLE applies only to the Northern Hemisphere: circle latitude and longitude based on geographical location UDF_SYS_GEO_IN_RECTANGLE is only applicable to the Northern Hemisphere: UDF_SYS_GEO_DISTANCE is only applicable to the Northern Hemisphere: distance calculation used as a longitude and latitude column and a fixed coordinate point 7. Function definition and example

ST_asText

ST_asText (GEOMETRY)-> VARCHAR

Converts Geometry data into string data in WKT format.

Example:

Select ST_asText (ST_LineFromText ('LINESTRING (0 0, 1 1, 12)')) +-- + | _ col0 | +-- + | LINESTRING (0 0,1 1, 1 2) | +-+

ST_LineFromText

ST_LineFromText (VARCHAR)-> GEOMETRY

Input Line string data in WKT format to generate Geometry data for Line.

Example:

Select ST_asText (ST_LineFromText ('LINESTRING (0 0, 1 1, 12)')) +-- + | _ col0 | +-- + | LINESTRING (0 0,1 1, 1 2) | +-+

ST_Point

ST_Point (DOUBLE, DOUBLE)-> GEOMETRY

The Geometry data of the corresponding Point is generated from the coordinate values (X, Y) of the coordinate system.

Example:

Select ST_asText (ST_Point (30.2741500000120.1551500000)); +-+ | _ col0 | +-+ | POINT (30.27415 120.15515) | +-+

ST_Polygon

ST_Polygon (VARCHAR)-> GEOMETRY

Input Polygon string data in WKT format to generate Geometry data for Polygon.

Example:

Select ST_asText (ST_Polygon ('POLYGON ((0 0rect 4 0je 4 4 je 0 0 0), (1 1, 2 1, 22 pr 1 1) +-+ | _ col0 | + -- + | POLYGON (0 0 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1)) | +

ST_Area

ST_Area (Geometry)-> DOUBLE

Returns the area of a face or multiple faces. For points and lines, 0.0 is returned. For GeometryCollection, returns the sum of all individual areas.

Example:

Select ST_Area (ST_Polygon ('POLYGON ((0 0rect 4 0je 4 4 je 0 0 0), (1 1, 2 1, 22 pr 1 1) +-+ | _ col0 | +-+ | 15.0 | +-+ select ST_Area ('MULTIPOLYGON (0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2,-2-2,-2-1,-1-1) +-+ | _ col0 | +-+ | 16.0 | +-+

ST_GeometryFromText

ST_GeometryFromText (VARCHAR)-> GEOMETRY

Input string data in WKT format to generate Geometry data.

ST_Buffer

ST_Buffer (GEOMETRY, DOUBLE)-> GEOMETRY

Gets the geometric object and distance, and then returns the geometric object that represents the buffer around the source object.

ST_Centroid

ST_Centroid (GEOMETRY)-> GEOMETRY

Gets the center point of the geometric object.

Example:

Select ST_asText (ST_Centroid ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1) +-- + | _ col0 | +-- + | POINT (1.8125 1.8125) | +-- + select ST_asText (ST_Centroid (ST_GeometryFromText ('LINESTRING (0,11,12)') +-+ | _ col0 | +-+ | POINT (0.51) | +-+ select ST_asText (ST_Centroid (ST_GeometryFromText ('POINT (0.51)')) +-+ | _ col0 | +-+ | POINT (0.51) | +-+ select ST_asText (ST_GeometryFromText ('POLYGON (0 0meme 4 0meme 4 4 meme 0 4) 0 0), (11, 21, 22) +-+ | _ col0 | +-+ | POINT (2.033333333333333 2.033333333333333) | +-+

ST_CoordDim

ST_CoordDim (GEOMETRY)-> BIGINT

Returns the coordinate value dimension of a geometric object.

Example:

Select ST_CoordDim (ST_GeometryFromText ('POLYGON ((0 0rect 4 0je 4 4 je 0 0 0), (1 1, 2 1, 22 pr 1 1) +-+ | _ col0 | +-+ | 2 | +-+ select ST_CoordDim ('MULTIPOLYGON (0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2,-2-2,-2-2,-2-1,-1-1) +-+ | _ col0 | +-+ | 2 | +-+ select st_coorddim (st_geometryfromtext ('point Z (60.567222-140.404 5959)'); +-+ | _ col0 | +-+ | 3 | +-+ select st_coorddim (st_geometryfromtext ('point M (60.567222-140.404 5250)')) +-+ | _ col0 | +-+ | 3 | +-+ select st_coorddim (st_geometryfromtext ('point ZM (60.567222-140.404 5959 5250)'); +-+ | _ col0 | +-+ | 4 | +-+

ST_Dimension

ST_Dimension (GEOMETRY)-> BIGINT

Used to return the dimension of a geometric object. In this case, dimensions refer to length and width. For example, a point has neither length nor width, so its dimension is 0, while a line has only length but no width, so its dimension is 1.

Example:

Select ST_Dimension ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1); +-+ | _ col0 | +-+ | 2 | +-+

ST_IsClosed

ST_IsClosed (GEOMETRY)-> BOOLEAN

Determine whether the Line or MultiLine is closed.

Example:

Select ST_IsClosed (ST_GeometryFromText ('LINESTRING (0,11,12)); +-+ | _ col0 | +-+ | 0 | +-+ select ST_IsClosed (ST_GeometryFromText (' LINESTRING (0.0,11,12,00)')) +-+ | _ col0 | +-+ | 1 | +-+ select ST_IsClosed (ST_GeometryFromText ('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4); +-+ | _ col0 | +-+ | 0 | +-+

ST_IsEmpty

ST_IsEmpty (GEOMETRY)-> BOOLEAN

Determines whether the geometric object is empty.

Example:

Select ST_IsEmpty (ST_GeometryFromText ('LINESTRING (0,11,12,00)); +-+ | _ col0 | +-+ | 0 | +-+ select ST_IsEmpty (null); +-+ | _ col0 | +-+ | NULL | +-+ SELECT ST_IsEmpty (ST_GeometryFromText (' GEOMETRYCOLLECTION EMPTY')) +-+ | _ col0 | +-+ | 1 | +-+ SELECT ST_IsEmpty (ST_GeometryFromText ('POLYGON EMPTY')); +-+ | _ col0 | +-+ | 1 | +-+

ST_Length

ST_Length (GEOMETRY)-> DOUBLE

Calculate the length of Line or MultiLine.

Example:

SELECT ST_Length (ST_GeometryFromText ('LINESTRING (0 0, 1 1, 1 2, 0 0)) +-+ | _ col0 | +-+ | 4.650281539872885 | +-+ SELECT ST_Length (ST_GeometryFromText ('MULTILINESTRING ((0 0,1 1, 12), (2 3,3 2, 5 4) +-+ | _ col0 | +-+ | 6.656854249492381 | +-+

ST_XMax

ST_XMax (GEOMETRY)-> DOUBLE

Returns the maximum X coordinate value of the geometric object in the coordinate system.

Example:

SELECT ST_XMax (ST_GeometryFromText ('MULTILINESTRING ((0,11,12), (23,32,54); +-+ | _ col0 | +-+ | 5.0 | +-+ SELECT ST_XMax (ST_GeometryFromText (' POINT (0.51)'); +-+ | _ col0 | +-+ | 0.5 | +-+

ST_XMin

ST_XMin (GEOMETRY)-> DOUBLE

Returns the minimum X coordinate value of the geometric object in the coordinate system.

Example:

SELECT ST_XMin (ST_GeometryFromText ('MULTILINESTRING ((0,11,12), (23,32,54); +-+ | _ col0 | +-+ | 0.0 | +-+ SELECT ST_XMin (ST_GeometryFromText (' POINT (0.51)'); +-+ | _ col0 | +-+ |

ST_YMax

ST_YMax (GEOMETRY)-> DOUBLE

Returns the maximum Y coordinate value of the geometric object in the coordinate system.

Example:

SELECT ST_YMax (ST_GeometryFromText ('MULTILINESTRING ((0,11,12), (23,32,54); +-+ | _ col0 | +-+ | 4.0 | +-+ SELECT ST_YMax (ST_GeometryFromText (' POINT (0.51)'); +-+ | _ col0 | +-+ | 1.0 | +-+

ST_YMin

ST_YMin (GEOMETRY)-> DOUBLE

Returns the minimum Y coordinate value of the geometric object in the coordinate system.

Example:

SELECT ST_YMin (ST_GeometryFromText ('MULTILINESTRING ((0,11,12), (23,32,54); +-+ | _ col0 | +-+ | 0.0 | +-+ SELECT ST_YMin (ST_GeometryFromText (' POINT (0.51)'); +-+ | _ col0 | +-+ | 1.0 | +-+

ST_NumInteriorRing

ST_NumInteriorRing (GEOMETRY)-> BIGINT

Takes Polygon as the input parameter and returns the number of its internal rings.

Example:

SELECT ST_NumInteriorRing (ST_GeometryFromText ('POLYGON ((0 0meme 4 0jue 4 4jue 0 0), (1 1, 2 1, 22 recorder 12 1); +-+ | _ col0 | +-+ | 1 | +-+

ST_NumPoints

ST_NumPoints (GEOMETRY)-> BIGINT

Returns the number of points (break points) in a geometric object.

Example:

SELECT ST_NumPoints (ST_GeometryFromText ('POLYGON (0 0Power4 4) 0 0), (1 1, 2 1, 2 2); +-+ | _ col0 | +-+ | 8 | +-+ SELECT ST_NumPoints (ST_GeometryFromText (' POLYGON EMPTY')) +-+ | _ col0 | +-+ | 0 | +-+ SELECT ST_NumPoints (ST_GeometryFromText ('POINT (0.51)'); +-+ | _ col0 | +-+ | 1 | +-+

ST_IsRing

ST_IsRing (GEOMETRY)-> BOOLEAN

Take Line as the input parameter to determine whether it is a loop (for example, Line is closed).

Example:

Select ST_IsRing (ST_GeometryFromText ('LINESTRING (0,11,12)); +-+ | _ col0 | +-+ | 0 | +-+ select ST_IsRing (ST_GeometryFromText (' LINESTRING (0,11,12,00); +-+ | _ col0 | +-+ | 1 | +-+

ST_StartPoint

ST_StartPoint (GEOMETRY)-> GEOMETRY

Used to return the first point of the Line.

Example:

Select ST_asText (ST_StartPoint (ST_GeometryFromText ('LINESTRING (0,11,12,00); +-+ | _ col0 | +-+ | POINT (00) | +-+

ST_EndPoint

ST_EndPoint (GEOMETRY)-> GEOMETRY

Used to return the last point of the Line.

Example:

Select ST_asText (ST_EndPoint (ST_GeometryFromText ('LINESTRING (0,11,12); +-+ | _ col0 | +-+ | POINT (1 / 2) | +-+

ST_X

ST_X (GEOMETRY)-> DOUBLE

Returns the X coordinate of Point.

Example:

Select ST_X (ST_GeometryFromText ('POINT (0.51)'); +-+ | _ col0 | +-+ | 0.5 | +-+

ST_Y

ST_Y (GEOMETRY)-> DOUBLE

Returns the Y coordinate of Point.

Example:

Select ST_Y (ST_GeometryFromText ('POINT (0.51)'); +-+ | _ col0 | +-+ | 1.0 | +-+

ST_Boundary

ST_Boundary (GEOMETRY)-> GEOMETRY

Enter a geometric object and return its combined boundary as a geometric object.

Example:

Select ST_asText (ST_Boundary (ST_GeometryFromText ('POINT (0.51)')); +-+ | _ col0 | +-+ | POINT EMPTY | +-+ select ST_asText (ST_Boundary (ST_GeometryFromText ('LINESTRING (0,1,12)') +-- + | _ col0 | +-- + | MULTIPOINT ((0 0), (1 2)) | +-+ select ST_asText (ST_Boundary ('MULTIPOLYGON ((0 0)) 4 0, 4 4, 0 4, 00), (11, 21, 22, 12, 11), ((- 1-1,-1-2,-2-2,-2-1,-1-1) +-+ | _ col0 | | +- -- + | MULTILINESTRING (0 0 4 0, 4 4, 0 4, 00), (11, 12, 22, 21, 11), (- 1-1,-2-1,-2-2,-1-2) -1-1)) | +-+

ST_Envelope

ST_Envelope (GEOMETRY)-> GEOMETRY

Returns the minimum bounding box of a geometric object as a polygon.

Example:

Select ST_asText (ST_Envelope ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1) +-- + | _ col0 | +-- + | POLYGON ((- 2-2, 4-2, 4-4,-2) -2-2)) | +-- +

ST_Difference

ST_Difference (GEOMETRY, GEOMETRY)-> GEOMETRY

Enter two geometric objects and return the geometric object that represents the difference between the two source objects.

Example:

Select ST_asText (ST_Difference ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1))'), ST_GeometryFromText ('MULTILINESTRING ((0.0,40,44,04,00), (11,12,22,21) 1), (- 1-1,-2-1,-2-2,-1-2,-1-1) +-+ | _ col0 | | +- -- + | MULTIPOLYGON ((0 0 4 0, 4 4, 0 4, 00), (11, 12, 22, 21, 11), ((- 1-1,-2-1,-2-2,-1-2) -1-1) | +-+ select ST_asText (ST_Difference (ST_GeometryFromText) 'MULTIPOLYGON ((0 0) 4 0, 4 4, 0 4, 00), (11, 21, 22, 12, 11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1))'), ST_GeometryFromText ('MULTIPOLYGON (0 0, 4 0, 4 4, 0 4, 0 4, 0 0), (1, 2 1, 2 2, 12, 11)), ((1-1-1,-1-2)) -2-2,-2-1,-1-1) +-+ | _ col0 | +-+ | MULTIPOLYGON EMPTY | +-+

ST_Distance

ST_Distance (GEOMETRY, GEOMETRY)-> DOUBLE

Returns the distance between two geometric objects. This distance is the distance between the closest break points of two geometric objects.

Example:

Select ST_Distance (ST_GeometryFromText ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1))'), ST_GeometryFromText ('MULTIPOLYGON (0.0,40,44,04,00), (11,21,22,12,1) ((- 1-1,-1-2,-2-2,-2-1,-1-1) +-+ | _ col0 | +-+ | 0.0 | +-+ select ST_Distance (ST_GeometryFromText ('POINT (0 0)'), ST_GeometryFromText ('POINT (1 1)')) +-+ | _ col0 | +-+ | 1.4142135623730951 | +-+

ST_ExteriorRing

ST_ExteriorRing (GEOMETRY)-> GEOMETRY

Returns the outer ring of the face as Line.

Example:

Select ST_asText (ST_ExteriorRing (ST_GeometryFromText ('POLYGON ((0 0je 4 0je 4 4 je 0 0), (1 1, 2 1, 2 2 mage 12 1 1) +-- + | _ col0 | +-- + | LINESTRING (0 0,4 0,4 4,0 4) 0) | +-+

ST_Intersection

ST_Intersection (GEOMETRY, GEOMETRY)-> GEOMETRY

Takes two geometric objects as input parameters, and then returns the intersection as a two-dimensional geometric object.

Example:

Select ST_asText (ST_Intersection ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1), ST_GeometryFromText (' MULTIPOLYGON ((0.0,40,44,04,00), (11,21,22,12)) 1 1)), ((- 1-1-1,-1-2,-2-2,-2-1,-1-1) +-+ | _ col0 | | +- -- + | MULTIPOLYGON ((0 0 4 0, 4 4, 0 4, 00), (11, 12, 22, 21, 11), ((- 1-1,-2-1,-2-2,-1-2) -1-1) | +-+ select ST_asText (ST_Intersection (ST_GeometryFromText ('POINT (0 0)') ST_GeometryFromText ('POINT (1 1)')) +-+ | _ col0 | +-+ | MULTIPOLYGON EMPTY | +-+

ST_SymDifference

ST_SymDifference (GEOMETRY, GEOMETRY)-> GEOMETRY

Returns a geometric value object that represents the symmetrical difference in the set of points between two geometric objects.

Example:

Select ST_asText (ST_SymDifference ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1), ST_GeometryFromText (' MULTIPOLYGON ((0.0,40,44,04,00), (11,21,22,12)) 1 1)), ((- 1-1-1,-1-2,-2-2,-2-1,-1-1) +-+ | _ col0 | +-+ | MULTIPOLYGON EMPTY | +-+ select ST_asText (ST_SymDifference (ST_GeometryFromText ('POINT (0 0)'), ST_GeometryFromText ('POINT (1 1') +-- + | _ col0 | +-- + | MULTIPOINT ((0 0), (1 1)) | +-+

The shaded part of the image below shows the result of the symmetrical difference. The symmetric difference is a multi-surface pattern that includes two surfaces: one surface contains all the points within the square and outside the circle, and the other surface contains all the points within the circle and outside the square.

ST_Contains

ST_Contains (GEOMETRY, GEOMETRY)-> BOOLEAN

Enter two geometric objects to determine whether the first object fully contains the second object.

Example:

Select ST_Contains (ST_GeometryFromText ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1))'), ST_GeometryFromText ('MULTIPOLYGON (0.0,40,44,04,00), (11,21,22,12,1) ((- 1-1,-1-2,-2-2,-2-1,-1-1) +-+ | _ col0 | +-+ | 1 | +-+ select ST_Contains (ST_GeometryFromText ('POINT (0 0)'), ST_GeometryFromText ('POINT (1 1)'); +-+ | _ col0 | +-+ | 0 | +-+

ST_Crosses

ST_Crosses (GEOMETRY, GEOMETRY)-> BOOLEAN

Takes two geometric objects as input, and returns 1 if the dimension of the geometric object generated by the intersection of the two objects is less than the maximum dimension of the two source objects. The intersection object must contain points within the two source geometry and not equal to either of the source objects. Otherwise, 0 is returned.

Example:

Select ST_Crosses (ST_GeometryFromText ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1))'), ST_GeometryFromText ('MULTIPOLYGON (0.0,40,44,04,00), (11,21,22,12,1) ((- 1-1,-1-2,-2-2,-2-1,-1-1) +-+ | _ col0 | +-+ | 0 | +-+ select ST_Crosses (ST_GeometryFromText ('LINESTRING (0,22)'), ST_GeometryFromText ('LINESTRING (02,20)'); +-+ | _ col0 | +-+ | 1 | +-+

ST_Disjoint

ST_Disjoint (GEOMETRY, GEOMETRY)-> BOOLEAN

Enter two geometric objects to determine whether the intersection of the two geometric objects is an empty set.

Example:

Select ST_Disjoint (ST_GeometryFromText ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1))'), ST_GeometryFromText ('MULTIPOLYGON (0.0,40,44,04,00), (11,21,22,12,1) ((- 1-1,-1-2,-2-2,-2-1,-1-1) +-+ | _ col0 | +-+ | 0 | +-+ select ST_Disjoint (ST_GeometryFromText ('LINESTRING (0,22)'), ST_GeometryFromText ('LINESTRING (02,24)'); +-+ | _ col0 | +-+ | 1 | +-+

ST_Equals

ST_Equals (GEOMETRY, GEOMETRY)-> BOOLEAN

Determine whether two geometric objects are exactly the same.

Example:

Select ST_Equals (ST_GeometryFromText ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1))'), ST_GeometryFromText ('MULTIPOLYGON (0.0,40,44,04,00), (11,21,22,12,1) ((- 1-1,-1-2,-2-2,-2-1,-1-1) +-+ | _ col0 | +-+ | 1 | +-+ select ST_Equals (ST_GeometryFromText ('LINESTRING (0,22)'), ST_GeometryFromText ('LINESTRING (02,24)'); +-+ | _ col0 | +-+ | 0 | +-+

ST_Intersects

ST_Intersects (GEOMETRY, GEOMETRY)-> BOOLEAN

Determine whether the intersection of two geometric objects does not generate an empty set.

Example:

Select ST_Intersects (ST_GeometryFromText ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1))'), ST_GeometryFromText ('MULTIPOLYGON (0.0,40,44,04,00), (11,21,22,12,1) ((- 1-1,-1-2,-2-2,-2-1,-1-1) +-+ | _ col0 | +-+ | 1 | +-+ select ST_Intersects (ST_GeometryFromText ('LINESTRING (0,22)'), ST_GeometryFromText ('LINESTRING (02,24)'); +-+ | _ col0 | +-+ | 0 | +-+

ST_Overlaps

ST_Overlaps (GEOMETRY, GEOMETRY)-> BOOLEAN

Determine whether the geometric object generated by the intersection of two geometric objects has the same dimension but is not equal to any source object.

Example:

Select ST_Overlaps (ST_GeometryFromText ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1))'), ST_GeometryFromText ('MULTIPOLYGON (0.0,40,44,04,00), (11,21,22,12,1) ((- 1-1,-1-2,-2-2,-2-1,-1-1) +-+ | _ col0 | +-+ | 0 | +-+ select ST_Overlaps (ST_GeometryFromText ('LINESTRING (11,22)'), ST_GeometryFromText ('LINESTRING (00,1.51.5)'); +-+ | _ col0 | +-+ | 1 | +-+

ST_Relate

ST_Relate (GEOMETRY, GEOMETRY, VARCHAR)-> BOOLEAN

Compare two geometric objects to determine whether the conditions specified by the "DE-9IM" pattern (https://en.wikipedia.org/wiki/DE-9IM) matrix string are met.

Example:

Select ST_Relate (ST_GeometryFromText ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1))'), ST_GeometryFromText ('MULTIPOLYGON (0.0,40,44,04,00), (11,21,22,12,1) ((- 1-1,-1-2,-2-2,-2-1,-1-1))'), '102101FF2') +-+ | _ col0 | +-+ | 0 | +-+ select ST_Relate (ST_GeometryFromText ('LINESTRING (1 1, 2 2)'), ST_GeometryFromText ('LINESTRING (0 0,1.5)'),'1 col0'); +-+ | _ col0 | +-+ | 1 | +-+

ST_Touches

ST_Touches (GEOMETRY, GEOMETRY)-> BOOLEAN

Determines whether the common points of two geometric objects do not intersect with the interior of the two geometric objects.

Example:

Select ST_Touches (ST_GeometryFromText ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1))'), ST_GeometryFromText ('MULTIPOLYGON (0.0,40,44,04,00), (11,21,22,12,1) ((- 1-1,-1-2,-2-2,-2-1,-1-1) +-+ | _ col0 | +-+ | 0 | +-+ select ST_Touches (ST_GeometryFromText ('LINESTRING (1,22)'), ST_GeometryFromText ('LINESTRING (0,11)'); +-+ | _ col0 | +-+ | 1 | +-+

ST_Within

ST_Within (GEOMETRY, GEOMETRY)-> BOOLEAN

Determines whether the first geometric object is completely within the scope of the second geometric object.

Example:

Select ST_Within (ST_GeometryFromText ('MULTIPOLYGON ((0,40,44,04,00), (11,21,22,12,11)), ((- 1-1,-1-2,-2-2,-2-1,-1-1))'), ST_GeometryFromText ('MULTIPOLYGON (0.0,40,44,04,00), (11,21,22,12,1) ((- 1-1,-1-2,-2-2,-2-1,-1-1) +-+ | _ col0 | +-+ | 1 | +-+ select ST_Within (ST_GeometryFromText ('LINESTRING (1,22)'), ST_GeometryFromText ('LINESTRING (0,11)'); +-+ | _ col0 | +-+ | 0 | +-+

ST_asBinary

ST_asBinary (GEOMETRY)-> VARBINARY

Enter a geometric object and return its recognizable binary WKB data.

ST_GeometryFromWKBHexString

ST_GeometryFromWKBHexString (VARCHAR)-> GEOMETRY

Enter the HEX string data of WKB and return the corresponding geometric object.

Example:

Select ST_ASTEXT (ST_GeometryFromWKBHexString ('0000000001400000000000004010000000000000000); +-+ | _ col0 | +-+ | POINT (2 4) | +-+

ST_pointFromWKBHexString

ST_pointFromWKBHexString (VARCHAR)-> GEOMETRY

Enter the HEX string data of the WKB of Point and return the corresponding Point geometric object.

Example:

Select ST_ASTEXT (ST_pointFromWKBHexString ('0000000001400000000000004010000000000000000); +-+ | _ col0 | +-+ | POINT (2 4) | +-+

ST_lineFromWKBHexString

ST_lineFromWKBHexString (VARCHAR)-> GEOMETRY

Enter the HEX string data of the WKB of Line and return the corresponding Line geometric object.

ST_polyFromWKBHexString

ST_polyFromWKBHexString (VARCHAR)-> GEOMETRY

Enter the HEX string data of the WKB of Polygon and return the corresponding Polygon geometric object.

ST_MPointFromWKBHexString

ST_MPointFromWKBHexString (VARCHAR)-> GEOMETRY

Enter the HEX string data of the WKB of MultiPoint and return the corresponding MultiPoint geometric object.

Example:

SELECT ST_asText (ST_MPointFromWKBHexString ('0104000000020000000101000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000040')); +-+ | _ col0 | +-+ | MULTIPOINT ((00), (1 2)) | +-+

ST_MLineFromWKBHexString

ST_MLineFromWKBHexString (VARCHAR)-> GEOMETRY

Enter the HEX string data of the WKB of MultiLine and return the corresponding MultiLine geometric object.

ST_MPolyFromWKBHexString

ST_MPolyFromWKBHexString (VARCHAR)-> GEOMETRY

Enter the HEX string data of the WKB of MultiPolygon and return the corresponding MultiPolygon geometric object.

ST_GeometryFromWKB

ST_GeometryFromWKB (VARBINARY)-> GEOMETRY

Enter WKB data and return the corresponding geometric object.

Example:

Select ST_ASTEXT (ST_GeometryFromWKB (from_hex ('000000000140000000000000401000000000000000)); +-+ | _ col0 | +-+ | POINT (2 4) | +-+

ST_pointFromWKB

ST_pointFromWKB (VARBINARY)-> GEOMETRY

Enter the WKB data of Point and return the corresponding Point geometric object.

Example:

Select ST_ASTEXT (ST_pointFromWKB (from_hex ('000000000140000000000000401000000000000000)); +-+ | _ col0 | +-+ | POINT (2 4) | +-+

ST_lineFromWKB

ST_lineFromWKB (VARBINARY)-> GEOMETRY

Enter the WKB data of Line and return the corresponding Line geometric object.

ST_polyFromWKB

ST_polyFromWKB (VARBINARY)-> GEOMETRY

Enter the WKB data of Polygon and return the corresponding Polygon geometric object.

ST_MPointFromWKB

ST_MPointFromWKB (VARBINARY)-> GEOMETRY

Enter the WKB data of MultiPoint and return the corresponding MultiPoint geometric object.

Example:

SELECT ST_asText (ST_MPointFromWKB (from_hex ('010400000002000000010100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000040'); +-+ | _ col0 | +-+ | MULTIPOINT ((00), (1 2)) | +-+

ST_MLineFromWKB

ST_MLineFromWKB (VARBINARY)-> GEOMETRY

Enter the WKB data of MultiLine and return the corresponding MultiLine geometric object.

ST_MPolyFromWKB

ST_MPolyFromWKB (VARBINARY)-> GEOMETRY

Enter the WKB data of MultiPolygon and return the corresponding MultiPolygon geometric object.

ST_GeometryFromGeoJson

ST_GeometryFromGeoJson (VARCHAR)-> GEOMETRY

Enter the string data of GeoJson and return the corresponding geometric object.

ST_GeometryFromJson

ST_GeometryFromJson (VARCHAR)-> GEOMETRY

Enter the string data of ESRI Geometry Object Json and return the corresponding geometric object.

ST_asGeoJson

ST_asGeoJson (GEOMETRY)-> VARCHAR

Convert geometric objects to GeoJson format for output.

Example:

SELECT ST_asGeoJson (ST_GeometryFromText ('MULTILINESTRING ((0.0,11,12), (23,32,54) +-+ | _ col0 | | +-+ | {"type": "MultiLineString" "coordinates": [0rect 0], [1rem 1], [1je 2]], [[2pr 3], [3je 2], [5je 4] "crs": null} | +-+

ST_asJson

ST_asJson (GEOMETRY)-> VARCHAR

Convert geometric objects to ESRI Geometry Object Json format for output.

Example:

SELECT ST_asJson (ST_GeometryFromText ('MULTILINESTRING ((0.0,11,12), (23,32,54) +-- + | _ col0 | +-- -+ | {"paths": [[0jin0] [1rem 1], [1jue 2]], [[2jue 3], [3jue 2], [5jre 4]]} | +-- +

ST_GeometryFromEsriShape

ST_GeometryFromEsriShape (VARBINARY)-> GEOMETRY

Enter the binary data of ESRI Shape and return the corresponding geometric object.

Example:

SELECT california_counties.name, COUNT (*) cntFROM california_countiesCROSS JOIN earthquakesWHERE ST_CONTAINS (ST_GeometryFromesrishape (california_counties.boundaryshape), ST_POINT (earthquakes.longitude, earthquakes.latitude)) GROUP BY california_counties.nameORDER BY cnt DESC, california_counties.name +-+-+ | name | cnt | +-+-+ | San Benito | 8 | San Bernardino | 7 | Riverside | 6 | Inyo | 5 | Imperial | 3 | San Diego | 2 | Kern | 1 | Kings | | 1 | | Monterey | 1 | | San Luis Obispo | 1 | | Santa Clara | 1 | | Ventura | 1 | +-+ |

UDF_SYS_GEO_IN_CYCLE

UDF_SYS_GEO_IN_CYCLE (longitude, latitude, point, radius)-> BOOLEAN the first parameter is the longitude column name, the second parameter of type FLOAT/DOUBLE is the latitude column name, and the third parameter of type FLOAT/DOUBLE is the position of the center point of the circle, format = > 'longitude, dimension', = > '120.85979, 30.011984' the fourth parameter is the radius of the circle, in meters

Judge whether the point of longitude and latitude is in the circle with the center point point as the center and the radius radius.

Example:

SELECT count (*) as cnt FROM earthquakes WHERE UDF_SYS_GEO_IN_CYCLE (longitude,latitude, '120.85979 reel 30.011984 million, 50000000000) = true;+-+ | cnt | +-+ | 2858 | +-+

UDF_SYS_GEO_IN_RECTANGLE

UDF_SYS_GEO_IN_RECTANGLE (longitude, latitude, pointA, pointB)-> BOOLEAN the first parameter is the longitude column name, the second parameter of type FLOAT/DOUBLE is the latitude column name, the third parameter of type FLOAT/DOUBLE is the lower left corner coordinate of the rectangle, the format = > 'longitude, dimension', the fourth parameter is the upper right corner coordinate of the rectangle, the format = > 'longitude, dimension', = > '120.88450, 31.21011'

Judge whether the points of longitude and latitude are in the rectangle composed of two bevel points.

Example:

SELECT count (*) as cnt FROM earthquakes WHERE UDF_SYS_GEO_IN_RECTANGLE (longitude, latitude, '69.037, 36.5759, 142.018) = true;+-+ | cnt | +-+ | 55 | +-+

UDF_SYS_GEO_DISTANCE

UDF_SYS_GEO_DISTANCE (longitude, latitude, pointA)-> INTEGER the first parameter is the longitude column name, the second parameter of type FLOAT/DOUBLE is the latitude column name, and the third parameter of type FLOAT/DOUBLE is the longitude and latitude of the fixed coordinate point, format = > 'longitude, dimension', = > '120.85979pm 30.011984'

Find the distance between the point of longitude and latitude and the point point, in meters.

Example:

SELECT count (*) as cnt FROM earthquakes WHERE UDF_SYS_GEO_DISTANCE (longitude, latitude, '69.037, 36.5759') > 10000 Geospatial analysis + | cnt | +-+ | 2857 | +-+ after reading the above, have you mastered how to understand the Geospatial analysis function of Data Lake Analytics? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.