Hands-on MySQL Spatial Data Exercises and Solutions
MySQL Spatial Data [20 exercises with solution]
1. Create a Table with a Spatial Column
Write a MySQL query to create a table that includes a spatial column for storing geographic location data.
2. Insert a Spatial Data Point Using ST_GeomFromText
Write a MySQL query to insert a new record into the Locations table with a geographic point using ST_GeomFromText.
3. Calculate Distance between Two Points
Write a MySQL query to calculate the distance between a stored location and a given point using ST_Distance.
4. Find Locations Within a Radius
Write a MySQL query to retrieve all locations within a specified radius (e.g., 1000 units) from a given point.
5. Check if a Point is Within a Polygon
Write a MySQL query to verify if a given point lies inside a specified polygon using ST_Within.
6. Create a Spatial Index on a Column
Write a MySQL query to add a spatial index on the location column of the Locations table.
7. Update a Spatial Data Point
Write a MySQL query to update the location of a specific record in the Locations table using ST_GeomFromText.
8. Delete Records Based on Spatial Criteria
Write a MySQL query to delete records from the Locations table where the distance from a given point exceeds 5000 units.
9. Convert Spatial Data to Well-Known Text (WKT)
Write a MySQL query to convert the spatial data in the location column to WKT format using ST_AsText.
10. Retrieve the Nearest Location
Write a MySQL query to find the nearest location to a given point by ordering results using ST_Distance.
11. Create a Table with Multiple Spatial Data Types
Write a MySQL query to create a table that includes both POINT and POLYGON spatial columns.
12. Insert a Polygon Using ST_GeomFromText
Write a MySQL query to insert a polygon into the GeoData table representing an area.
13. Find Polygons That Intersect a Given Area
Write a MySQL query to select all records from the GeoData table where the stored polygon intersects a specified polygon.
14. Calculate the Area of a Polygon
Write a MySQL query to calculate the area of polygons stored in the GeoData table using ST_Area.
15. Calculate the Perimeter of a Polygon
Write a query to calculate the perimeter of polygons in the GeoData table using ST_Perimeter.
16. Create a Buffer around a Point
Write a query to create a buffer zone around a point stored in the Locations table using ST_Buffer.
17. Transform Spatial Data to a Different SRID
Write a MySQL query to transform the spatial data in the location column to SRID 4326 using ST_Transform.
18. Insert a Point with a Specific SRID
Write a MySQL query to insert a new record into the Locations table with a point that includes an SRID using ST_GeomFromText.
19. Find Locations within a Bounding Box
Write a query to find all locations that fall within a specified bounding box using the MBRContains function.
20. Retrieve the SRID of a Geometry
Write a MySQL query to extract the SRID of the geometry stored in the location column using ST_SRID.
More to Come !
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics