Spatial SQL -> MySQL. Check relations between geometries

MySQL have some functions for testing Spatial relations between geometric objects. Most of them checks relations using Geometry Minimal Bounding Rectangles (MBRs). In the following examples we'll use geo-data created in script1 and script2. First we will check wich one of the polygons contains "Duomo" central point (LAT: 9.191837781 LON:45.464282256 ):

SELECT idno, name, ptyp,
  MBRContains(g, GeomFromText( 'POINT(9.191837781 45.464282256)')) AS Contained_By
FROM poi_milan_fp;


Here we have got no surprises. "Duomo" central point is contained by "Duomo" polygon:
idnonameptypContained_By
11Giardini pubblici02 - garden0
5Duomo02 - building1

Next we can check distance between objects using UDF fn_distance() that recives coordinates of two points and returns distance between them. In the following query distance is measured from Duomo to all other points in Milan city center:
SELECT idno, name,
  fn_distance( X(g), Y(g), 9.191837781, 45.464282256) AS dist_km
FROM poi_milan
ORDER BY fn_distance( X(g), Y(g), 9.191837781, 45.464282256);

Results are as follows:
idnonamedist_km
1Duomo0.00
7Galleria Vittorio Emanuele0.26
9La Scala0.44
12Pinacoteca di Brera0.93
4Castello Sforzesco1.52
13Stazione Centrale2.73
6Fiera Di Milano4.50
10Meazza (San Siro) Stadio7.71



sqlexamples.info