Expand the mysqlspatial Function

Extended mysqlspatial FUNCTION 1) Whether points in space are equal [delphi] DELIMITER $ create definer = 'smart' @ 'localhost' FUNCTION ArePointsEqual (p1 POINT, p2POINT) returns tinyint (1) deterministic no SQL BEGIN RETURN IsZero (x (p1)-x (p2) AND IsZero (y (p1)-y (p2); END $ DELIMITER; 2) find the central POINT of the region [SQL] USE smartu; DELIMITER $ create definer = 'smart' @ 'localhost' FUNCTION GetCenterPoint (g GEOMETRY) returns point deterministic no SQL BEGIN DECLARE envelope POLYGON; DECLARE sw, ne POINT; # South-West and North-East points DECLARE lat, lng DOUBLE; SET envelope = exteriorring (Envelope (g); SET sw = pointn (envelope, 1); SET ne = pointn (envelope, 3); SET lat = x (sw) + (x (ne)-x (sw)/2; SET lng = y (sw) + (y (ne)-y (sw)/2; return point (lat, lng); END $ DELIMITER; 3) lineN [SQL] USE smartu; DELIMITER $ CREATE DEFINER = 'smart' @ 'localhost' FUNCTION LineN (ls LINESTRING, n INT) returns linestring deterministic no SQL BEGIN IF n> = numpoints (ls) THEN RETURN NULL; END IF; RETURNLineString (pointn (ls, n), pointn (ls, n + 1 )); END $ DELIMITER; 4) Calculate the spatial DISTANCE between two points [SQL] USE smartu; DELIMITER $ CREATE DEFINER = 'smart' @ 'localhost' FUNCTION DISTANCE (lat1 DOUBLE, lon1DOUBLE, lat2DOUBLE, lon2DOUBLE) returns double deterministic no SQL COMMENT 'counts distance (km) between 2 points on Earth surface 'begin DECLARE dtor DOUBLEDEFAULT 57.295800; RETURN (6371 * acos (sin (lat1/dtor) * sin (lat2/dtor) + cos (lat1/dtor) * cos (lat2/dtor) * cos (lon2/dtor-lon1/dtor); END $ DELIMITER; 5) whether it is 0 [SQL] USE smartu; DELIMITER $ create definer = 'smart' @ 'localhost' FUNCTION IsZero (n DOUBLE) returns tinyint (1) deterministic no SQL BEGIN DECLARE epsilon DOUBLEDEFAULT 0.00000000001; RETURN (abs (n) <= epsilon); END $ DELIMITER; 2. project foreground and background Implementation 1) project background implementation l database design [SQL] -- create a table createtable smart_u_convenience_item_spatial (item_spatial_id varchar (36) not null, location point not null, latitude varchar (20), longpolling varchar (20), convenience_item_code varchar (500), convenience_item_name varchar (500), primary key ('item _ spatial_id '), spatial key 'SP _ Index' (location) ENGINE = MyISAM; -- insert INSERTINTO smart_u_convenience_item_spatial SELECTt into the table. convenience_item_id, PointFromText (concat ('point (', t. item_latitude, '', t. item_longpolling, '), t. item_latitude, t. item_longpolling, t. convenience_item_code, t. convenience_item_name from smart_u_convenience_item t; l SQL of the query point used by the background code [SQL] SELECT * FROM (SELECT * FROM smart_u_convenience_item t WHERE t. convenience_item_idIN (SELECTs. item_spatial_id FROM s WHERE intersects (location, geomfromtext (concat ('polygon (', 3.9921123E7, '', comment,', ', 3.9921123E7,'', 1.16441881E8 ,', ', 3.9879484E7, '', 1.16441881E8,', ', 3.9879484E7,'', expires,', ', 3.9921123E7, '', 1.16365462E8 ,'))'))))) t order by item_long1_asc