Category: GIS

MySQL Spatial Sorting. Let’s think of the situation where you want to color the map in such a way, that no adjacent group of features should have the same color, where group of feature is more than the number of colors you can draw. Obviously this problem can be solved if we sort feature group from left to right and assign the color in the same order. The above described situation has to be solved by us where we need to restrict our technology to only to, GeoJSON, Leaflet, Php and MySQL and no server side API or any GIS server as per the  Client requirement.

Now as the task is assigned to us, we need to identify and solve the same maintaining the time complexity and load at both client as well as server end.  Here we need to sort the feature according to upper left latitude longitude values  of each feature bounding box and not with an area or length or with any other attribute in ascending or descending order. Someone can think to sort the features by finding the center latitude longitude point of the features and then sort feature according to the center, but this will only work, when all features are of same size which is not in real world case.

Sorting the features with known bounding box from left to right can be performed with the array of geoJSON feature and leaflet alone at client side, but this will definitely increase the time complexity and load on the browser when spatial data file is very big. The last and the final option we have, is to store the Shapefile or GeoJSON file in MySQL Spatial Database and perform the spatial operation in MySQL to get selected feature in sorted order.  Sorting feature from left to right order means we need to work with longitude values of bounding box of feature. If you want to perform sorting the features from top down then use latitude values.

If you understand the store procedure easily then you may navigate to bottom of the page else you can follow the tutorial to know what steps are taken to make the complete stored procedure to sort the features in MySQL.

Note: The tutorial given here for the GI S person who don’t know to operate on database. To make them understand every step is detailed clearly. If you are expert in database, do navigate to bottom of the post and find the stored procedure.

MySQL Spatial Sorting – Steps Performed to Sort the features in MySQL:

1.) First step is to import the shapefile in the Database using ogr2ogr command tool and check whether every feature is inserted correctly in the desired table. Once inserted the next step we need to perform is to calculate MBR(minimum bounding rectangle) for each feature and only select the longitude values of the first starting point, as the first point is the upper left corner point of MBR.

2.) Let’s start our task by finding the MBR for the entire features. We can also call MBR as bounding box of the feature. If you noticed after importing the shapefile in MySQL all the features are saved in a column named SHAPE with a datatype GEOMETRY which makes us eligible to perform spatial query on this column. To find the bounding box of the feature there is a spatial function envelope(g) which returns the MBR for the geometry

SELECT ASTEXT( ENVELOPE(  `SHAPE` ) ) FROM  `feature`

• In the above query ‘feature’ is the name of the table.
• ASTEXT() function returns as text the result returned by the ENVELOPE().

Using this query you will get the MBR but we don’t need all the coordinates we only need the longitude of the first point of the result. So we need to take out that longitude and save it to the new column which will ultimately make very easy to sort feature in ascending order.

Unfortunately, we cannot save the result of the select query to the same table so we have to go little further and have to save the result into a new table or a view. Make a new table with two columns, one column for saving the longitude value having data type as text and one for foreign key referenced to the ID column in the main table. Don’t insert any other values or column in the table.

Now let’s do this tedious task:

INSERT INTO new_feature( stpoint ,id)  SELECT SUBSTRING( SUBSTRING_INDEX( ASTEXT( ENVELOPE(  `SHAPE` ) ) ,  ‘,’, 1 ) , 10, 9 ),id  FROM  `feature`

• Above query take out the longitude of the starting point and save it into stpoint in the new_feature table and at same time save the same value of the id from feature table to the id in the new_feature table.
• SUBSTRING_INDEX() function will take out the text before the first comma that appears in the result given by ASTEXT( ENVELOPE( `SHAPE` ) ).
• SUBSTRING() will give us the required longitude value. 10 in this function represents the starting value and 9 represent the length of the value that we want.

If you analyse the result of the query i.e by

SELECT ASTEXT( ENVELOPE(  `SHAPE` ) ) FROM  `feature`

You will find that the result is in the form POLYGON((long lat,long lat…)).

From this result 10 and 9 came characters to be used in SUBSTRING(). Now we can join the two table using id column in both table and get the desired result. But the sorted result we get is wrong.

If you noticed during the process we get the result as text and we apply all the operation on the text and our stpoint column have data type as text. So the sorting will be done as text sorting so any longitude value starting with 1 will come first and values starting with 9 will come last. To avoid this problem we have to change the data type of the stpoint column to decimal.

ALTER TABLE new_feature MODIFY stpoint DECIMAL( 7, 4 )

In DECIMAL( 7, 4 ) 7 represent the length of the number. That we need and 4 represent the numbers after the decimal so the result be something like 23.4567 or 123.4567.

3.) Now we will join the two table and simultaneously sort the result:

SELECT feature.*, new_feature.stpoint FROM feature INNER JOIN new_feature ON feature.OGR_FID = new_feature.OGR_FID ORDER BY stpoint

We want all the columns from the main table and only stpoint from new_feature. The above query by default will sort the feature in ascending order. Save the result. MySQL will export the result as excel file.

You can also write a stored procedure for this process. In stored procedure you don’t have to create a new table instead you just need to create a temporary table which will destroy after the end of the session. Here is the Stored procedure:

MySQL Spatial Sorting from left to right – Stored Procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `SortFeature`() MODIFIES SQL DATA COMMENT ‘sort feature in ascending order(long of 1st co. of mbr)’
begin

CREATE TEMPORARY TABLE longitude(OGR_FID int(4) not null , stpoint text(10));

INSERT INTO longitude( stpoint,OGR_FID )
SELECT SUBSTRING( SUBSTRING_INDEX( ASTEXT( ENVELOPE(`SHAPE`)) , ‘,’, 1 ) , 10, 9 ) , OGR_FID
FROM feature ;

ALTER TABLE longitude MODIFY stpoint DECIMAL(7,4);

SELECT feature.*, longitude.stpoint
FROM feature
INNER JOIN longitude ON feature.OGR_FID = longitude.OGR_FID ORDER BY stpoint ASC;

end

I hope the above lengthier post might helped you to find a way to sort the features from left direction to right direction with MySQL Spatial Sorting query.