MYSQL space Query

Blog.sina.com.cnsblog_a48af8c001018q1p.html ). This article is suitable for those who are familiar with SQL and MYSQL. Step 1: Create a table that supports space query. First, let's talk about how to create a table that contains space.

Http://blog.sina.com.cn/s/blog_a48af8c001018q1p.html this article will show you how to use the spatial database in MySql5.x and show you its efficient performance (provided that it is used correctly ). This article is suitable for those who are familiar with SQL and MYSQL. Step 1: Create a table that supports space query. First, let's talk about how to create a table that contains space.

Http://blog.sina.com.cn/s/blog_a48af8c001018q1p.html

This article will show you how to use the spatial database in MySql5.x and its efficient performance (provided that it is used correctly ).

This article is suitable for those who are familiar with SQL and MYSQL.

Step 1: Create a table that supports space Query

First, let's talk about how to create a table named Points that contains spatial data.

Create table 'points '(

'Name' varchar (20) not null default '',

'Location' point not null,

'Description' varchar (200) default null,

Primary key ('name '),

Spatial key 'SP _ Index' ('location ')

) ENGINE = MyISAM default charset = gbk;

This DDL command creates a table named Points, which contains a name field and a location and descrption field of the point type.

As you can see, the use of space type fields is the same as other types in Mysql. You can select the corresponding type when creating a space type field.

The base class of the spatial data type is Geometry.

You can find all space data types supported by Mysql in the following documents:

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

Step 2: insert data to the spatial data table

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

Insert into Points (name, location) VALUES ('point1', GeomFromText ('point (31.5 42.2 )'))

This is a common SQL insert operation, and only the GeomFromText () function is something we have never seen before. This function accepts a string and returns a geometric object. For the GIS standard format of this string, see:

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

Step 3: read data from the spatial data table

Reading data from the Points table is also very simple:

SELECT name, AsText (location) FROM Points;

In the returned results of the preceding statement, location is converted to the same GIS standard string as in step 2. In fact, the AsText function only formats the geometric objects stored in the database into a string.

The following function is also very useful:

SELECT name, AsText (location) FROM Points where x (location) <10 and Y (location)> 12;

This Select statement returns a set of points with location X () (longitude) less than 10 and Y (longitude) greater than 12.

Step 4: Advanced query of spatial tables

Convert a 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 )'));

Returns the type of the specified geometric object:

SELECT GeometryType (GeomFromText ('point (1 )'));

Search for vertices within the specified rectangle:

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 the point in the circular area

This step describes how to query geometric objects in a circular area (usually expressed by a center point and a radius.

The first statement you think of may be:

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

SET @ radius = 20;

SELECT name, AsText (location) FROM Points WHERE Distance (location, GeomFromText (@ point) <@ radius;

However, an error occurs when running this statement because the Distance function is not implemented yet. The MySql space extension documentation describes that they only implement Part of the OpenGis standard.

An alternative is to use the intersect function.

The MySql space extension document specifies that various geometric objects can use the intersect function to determine whether the geometric objects are intersecting with a rectangle.

In this way, we can use Distance Estimation to filter out the correct results after obtaining the approximate range.

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 (ABS (X (location)-X (@ center), 2) + POW (ABS (Y (location)-Y (@ center )), 2) <@ radius

Order by distance;

Step 6: test the performance

In the last step, we will try the performance of spatial data query in the case of large data volumes.

First, we create a new stored procedure, specifying a random number to randomly generate records and insert them 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

Then, call the stored procedure and specify a large number for the parameter. For example, we want to generate 1 million records:

CALL fill_points (1000000 );

Then we run the Query [1] and [2]

The test result on my machine (Intel Core Duo 2.0 GHz Laptop) is:

The selection of the circular area (that is, the surrounding search) results are not sorted [1]

43862 rows in set ~ 1.10 sec with 1.000.000 records

Round area selection (I .e. peripheral search) Result sorting [2]

43862 rows in set ~ 1.72 sec with 1.000.000 records

Http://howto-use-mysql-spatial-ext.blogspot.com/