|
|
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:
| idno | name | ptyp | Contained_By |
| 11 | Giardini pubblici | 02 - garden | 0 |
| 5 | Duomo | 02 - building | 1 |
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:
| idno | name | dist_km |
| 1 | Duomo | 0.00 |
| 7 | Galleria Vittorio Emanuele | 0.26 |
| 9 | La Scala | 0.44 |
| 12 | Pinacoteca di Brera | 0.93 |
| 4 | Castello Sforzesco | 1.52 |
| 13 | Stazione Centrale | 2.73 |
| 6 | Fiera Di Milano | 4.50 |
| 10 | Meazza (San Siro) Stadio | 7.71 |
sqlexamples.info
|