MySQL donnée Spatial

[saturne2008]

Bonjour

Voila l'exemple que je veux essayer, j'ai fait ça sous phpMyAdmin.

The first example is a very simple database with just two tables: one that contains addresses and the location of the address as a POINT, and a second table that contains information on taxi cabs, including the location of the cab, again as a POINT.

Note that we also create a spatial index on both tables.

CREATE TABLE address (
address CHAR(80) NOT NULL,
address_loc POINT NOT NULL,
PRIMARY KEY(address),
SPATIAL KEY(address_loc)
);

CREATE TABLE cab (
cab_id INT AUTO_INCREMENT NOT NULL,
cab_driver CHAR(80) NOT NULL,
cab_loc POINT NOT NULL,
PRIMARY KEY(cab_id),
SPATIAL KEY(cab_loc)
);
Now we need to insert some data into these tables to test the spatial features. To do this, the easiest way is to use the GeomFromText() function, that takes a string in WKT format and converts that to a spatial object.

INSERT INTO address VALUES('Foobar street 12', GeomFromText('POINT(2671 2500)'));
INSERT INTO address VALUES('Foobar street 56', GeomFromText('POINT(2971 2520)'));
INSERT INTO address VALUES('Foobar street 78', GeomFromText('POINT(3171 2510)'));
INSERT INTO address VALUES('Foobar street 97', GeomFromText('POINT(5671 2530)'));
INSERT INTO address VALUES('Foobar street 99', GeomFromText('POINT(6271 2460)'));
INSERT INTO address VALUES('Bloggs lane 10', GeomFromText('POINT(5673 3520)'));
INSERT INTO address VALUES('Bloggs lane 20', GeomFromText('POINT(5665 3550)'));
INSERT INTO address VALUES('Bloggs lane 45', GeomFromText('POINT(5571 3510)'));

INSERT INTO cab VALUES(0, 'Joe Bloggs', GeomFromText('POINT(2262 2100)'));
INSERT INTO cab VALUES(0, 'Bill Bloggs', GeomFromText('POINT(2441 1980)'));
INSERT INTO cab VALUES(0, 'Sam Spade', GeomFromText('POINT(5400 3200)'));
Now we have some data to work with, let's try a spatial query: We will query for the closest cab to a given location. As both of the columns involved in the query have the POINT data type, the standard OpenGIS function Distance() could be used. Regrettably, this is not yet implemented in the MySQL Spatial Extensions, so we have to take a different route. (The Distance() function will be implemented in a later release of MySQL.) We convert the two POINT values to a LINESTRING and then compute the length of that, which will be the same as the distance between the two points. The finished query then looks like this:

SELECT
c.cab_driver,
ROUND(GLength(LineStringFromWKB(LineString(AsBinary(c.cab_loc),
AsBinary(a.address_loc)))))
AS distance
FROM cab c, address a
WHERE a.address = 'Foobar street 110'
ORDER BY distance ASC LIMIT 1;

Les valeurs de address_loc de type POINT sont mal affichées. Voilà un imprime écran :


J'utilise WampServer avec MySql 5.1.36

[CinePhil]

Je suppose que c'est lié au fait que POINT n'est pas exactement un type mais une classe qui n'est peut-être pas directement lisible en dehors des fonctions géométriques.

[saturne2008]

Envoyé par CinePhil

Je suppose que c'est lié au fait que POINT n'est pas exactement un type mais une classe qui n'est peut-être pas directement lisible en dehors des fonctions géométriques.

CinePhil


et comment je peux modifier ça?