MySQL spatial query (reprint)

SELECT x (location), Y (location) from frddata.points;

This article will show you how to use the spatial database in mysql5.x and demonstrate its efficient performance (if used correctly).

This article is suitable for people who are familiar with SQL and MySQL.

Step 1: Create a table that supports spatial queries

First of all, how to create a table named points that contains spatial data.

CREATE TABLE ' Points ' (

' Name ' varchar (a) Not NULL DEFAULT ' ',

' Location ' point is not NULL,

' Description ' varchar ($) DEFAULT NULL,

PRIMARY KEY (' name '),

SPATIAL KEY ' sp_index ' (' Location ')

) Engine=myisam DEFAULT CHARSET=GBK;

This DDL command creates a table named points that contains a name field and a field location with a type of point (where) and a descrption (description) field.

As you can see, the Space Type field is used just like any other type in MySQL, so you can select the appropriate type when you create it.

The base class for spatial data types is geometry.

You can find all of the spatial data types supported by MySQL in the following document:

Http://dev.mysql.com/doc/refman/4.1/en/spatial-extensions.html

Step 2: Insert data into the spatial data table

Let's take a look at how simple it is to insert data in a points table:

INSERT into Points (name, location) VALUES (' Point1 ', Geomfromtext (' point (31.5 42.2) '))

This is a normal SQL insert operation, only the function Geomfromtext () is what we have not seen before. This function takes a string and returns a geometric object. The GIS standard format for this string is described in:

Http://dev.mysql.com/doc/refman/4.1/en/gis-wkt-format.html

Step 3: Read data from the Spatial data table

Reading the data from the points table is also very simple:

SELECT name, Astext (location) from Points;

The location in the returned result of the above statement will be converted to the same GIS standard string as in the second step. In fact, the Astext function simply formats the geometric object stored inside the database into a string.

The following function is also very useful:

SELECT name, astext from Points WHERE X (location) < ten and Y (location) > 12;

The SELECT statement returns a collection of points with a range of Location X () (longitude) less than 10 and Y () (longitude) greater than 12.

Step 4: Advanced query for the spatial table

Transforms the specified geometric object into readable text:

SELECT Astext (Envelope (Geomfromtext (' LineString (1 2)));

Returns the size of the specified geometric object:

SELECT Geometrytype (Geomfromtext (' point (1 1) '));

Returns the type of the specified geometric object:

SELECT Geometrytype (Geomfromtext (' point (1 1) '));

Finds points within the specified rectangle range:

SET @bbox = ' POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0)) ';

SELECT name, Astext (location) from Points WHERE intersects (location, Geomfromtext (@bbox));

Step 5: Find points within a circular area

This step describes how to query a geometric object within a circular area (typically represented by a center point and radius).

The first statement you thought of might be:

SET @point = ' point (10 10) ';

SET @radius = 20;

SELECT name, astext from Points WHERE Distance (location, Geomfromtext (@point)) < @radius;

However, this statement runs in error because the distance function is not yet implemented. The MySQL Space extension documentation explains that they only implement part of the OpenGIS standard.

An alternative approach is to use the Intersect function.

The MySQL space extension document already indicates that various geometries can use the Intersect function to determine whether a geometry intersects a rectangle.

This allows us to use distance estimates to filter out the correct results after the approximate range has been obtained.

SET @center = Geomfromtext (' point (10 10) ');

SET @radius = 30;

SET @bbox = CONCAT (' POLYGON (',

X (@center)-@radius, ', Y (@center)-@radius, ', ',

X (@center) + @radius, ', Y (@center)-@radius, ', ',

X (@center) + @radius, ', Y (@center) + @radius, ', ',

X (@center)-@radius, ', Y (@center) + @radius, ', ',

X (@center)-@radius, ", Y (@center)-@radius, ')) '

);

[1]

SELECT name, Astext (location)

From Points

WHERE intersects (location, Geomfromtext (@bbox))

and SQRT (POW (ABS (x (location)-X (@center)), 2) + POW (ABS (Y (location)-y (@center)), 2)) < @radius; To obtain a result ordered by distance from the center of the selection area:

[2]

SELECT name, Astext (location), SQRT (POW (ABS (x (location)-X (@center)), 2) + POW (ABS (Y (location)-y (@center)), 2)) as Distance

From Points

WHERE intersects (location, Geomfromtext (@bbox))

and SQRT (POW (x (location)-X (@center)), 2) + POW (ABS (Y (location)-y (@center)), 2)) < @radius

ORDER by distance;

Step 6: Test performance

The last step is to try the performance of spatial data queries in the case of large data volumes.

First we create a new stored procedure that specifies a random numeric random value to be inserted into the points table.

CREATE PROCEDURE fill_points (

In size INT (10)

)

BEGIN

DECLARE i DOUBLE (10,1) DEFAULT size;

DECLARE Lon FLOAT (7,4);

DECLARE lat FLOAT (6,4);

DECLARE position VARCHAR (100);

--Deleting all.

DELETE from Points;

While i > 0 do

SET lon = RAND () * 360-180;

SET lat = RAND () * 180-90;

SET position = CONCAT (' Point (', ' lon, ' ', lat, ') ');

INSERT into Points (name, location) VALUES (CONCAT (' name_ ', i), geomfromtext (position));

SET i = i-1;

END while;

END

The stored procedure is then called, and the parameter specifies a larger number, for example, we want to generate 1 million records:

Call Fill_points (1000000);

Then we execute the query [1] and [2]

The test results on my machine (Intel Core Duo 2.0 GHz Laptop) are:

Circular area selection (i.e. perimeter search) results not sorted [1]

43862 rows in Set ~1.10 sec with 1.000.000 Records

Circular area selection (i.e. perimeter search) results sorted [2]

43862 rows in Set ~1.72 sec with 1.000.000 Records

Original address: http://howto-use-mysql-spatial-ext.blogspot.com/

MySQL spatial query (reprint)