MySQL for Geospatial Applications

Install MySQL on Windows as part of the AMP XAMPP package, set up Apache, MariaDB, PHP, and phpMyAdmin, and start the control panel to manage databases with MySQL Workbench.

Install QGIS 3.28 on Windows with the OSGeo4W installer using express install, then use the OSGeo4W shell and Python 3.9.5 to view and edit spatial data for MySQL.

Install the amp stack on macOS to run Apache, MariaDB (MySQL compatible), and PHP. Use phpMyAdmin and MySQL Workbench to manage the local databases and connect via localhost.

Install QGIS 3.28 on Mac OS, drag to applications, and run the open source GIS to connect to a MySQL database, view and verify spatial data on a map.

Learn to load non-spatial data into a MySQL database by converting Excel tables to CSV, creating a database in phpMyAdmin, and importing data with MySQL Workbench.

Learn how to load spatial data into MySQL using CSV, shapefiles, and QGIS, convert to geometry using well known text, and manage geometry and primary keys.

Access MySQL from the command line on Windows, macOS, or Linux, log in as root, and run SQL; explore using mysql dump for backups.

Learn how a database instance organizes data into databases, schemas, and tables, with rows, columns, and geometry attributes for spatial data in MySQL.

Master the SQL select statement to retrieve data, using *, field lists, order by with ascending or descending sorts, and aliases; learn semicolon termination, from clauses, subqueries, and MySQL basics.

Master the where clause and predicates in MySQL to filter records. Apply operators like =, >, and, or, in, and like, with proper quoting and date handling.

Learn how aggregate functions and the group by clause summarize data. Use count, average, min, max, standard deviation, variance, covariance, and correlation, grouping by fields.

Learn to perform multi-table joins in SQL by linking data across tables with common keys, using on clauses and aliases, and modeling one-to-one, one-to-many, and many-to-many relationships.

Explore inner and outer joins in MySQL, including left, right, full outer, and cross joins, with practical examples of 1-to-1, 1-to-many, and many-to-many relationships.

Explore MySQL data types for geospatial applications, including integers, real numbers, strings, dates, times, and blobs, and learn how casting converts values for functions and queries.

Understand how expressions, functions, and operators shape SQL queries in from-clause loops for geospatial applications. See examples like random, upper, mod, right, substring, and between to compute values.

Explore user defined functions in MySQL, including creating classify_longitude, returning a varchar, and using functions in queries to classify longitude into west, center, or east.

Master the MySQL case statement to reclassify values with when-then-else and else, create labeled results in a select, and use case anywhere an expression appears.

Explore MySQL views as named stored queries that act like tables, remain current with underlying data, and can be joined or queried for summaries without storing extra data.

Explain the vector geometry model used by post GIS and QGIS, aligned with the Open Geospatial Consortium simple features for SQL, covering point, line string, polygon, and features with attributes.

Explore spatial data in MySQL by understanding spatial reference IDs, coordinate reference systems, and how to assign SRIDs to geometries using well known text and geometry functions.

Explore how the MySQL geometry column stores spatial data, including spatial reference IDs and geometry types for points, lines, and polygons, and view results as text or JSON.

Compare geometry and geography data types in MySQL, and explain how flat-plane and spherical earth calculations affect accuracy, available spatial functions, and performance.

Learn how to inspect geometry properties in MySQL—type, dimension, srid, rings, and interior rings—validate geometries, and perform basic transforms, including UTMB zone 13 coordinates and SQL exports.

Learn to measure geometry in MySQL geospatial work, calculating lengths, areas, and distances with distance and distance_sphere functions, and compare planar and spherical calculations across coordinate systems.

Learn to access coordinates and geometry parts in MySQL geospatial data. Convert binary WKB to text or JSON and extract points, rings, and lines.

Explore spatial predicates that test relationships between geometries, returning true or false, and learn how to use them in multi-table joins with intersects, disjoint, contains, within, and equals.

Explore multi-table joins with spatial predicates in MySQL, including intersects and distance-based filters. Build three-table queries with raptor nests and surveys and compare SQL workflows to desktop GIS.

Discover how MySQL executes geospatial multi-table queries, with subqueries, join predicates, and where clauses guiding performance. Learn to use subqueries, explain plans, and indexing for speed.

Explore geometry processing with MySQL's sd_buffer function, learning how to create buffers using fixed, field-based, or classification radii, and applying point, circle, and square strategies for both points and lines.

Master spatial buffers in mysql for polygons and lines, using interior and exterior buffers with positive and negative radii, then verify results in qgis via tables and views.

Explore MySQL geometry processing functions, including intersection, union, difference, and symmetric difference, with practical examples like buffering and convex hulls of geometry collections.

Explore challenge exercises that teach writing a SQL select using raptor nest buffers and intersections to compute acres, then sum habitat length in kilometers with aggregation and coalesce.

Continue challenge exercises on SQL select statements, reorder results by length in kilometers, apply a limit of ten, and group by type to summarize counts and total length.

Explore the data definition language in MySQL, learning create and alter statements to define databases (schemas), tables, keys, and constraints, including referential integrity and indexes.

Create a wetlands table with a serial primary key, unique name, and fields for width, depth, wetland system (check constraint: riverine, lacustrine, palustrine) plus a non-null multi-polygon geometry.

Populate a MySQL spatial wetlands table by digitizing polygons in QGIS, generating SQL inserts, and enforcing constraints through the database, with careful one-feature-at-a-time validation.

Explore primary keys and foreign keys in MySQL, including auto increment and UUID options, and implement referential integrity with lookup tables and cascade or restrict behaviors.

Explore primary keys and foreign keys, enforce referential integrity to prevent orphan records, and configure on update cascade and on delete restrict for a wetland system lookup table.

Explore creating and managing non-spatial and spatial indexes in MySQL, including unique and multi-column indexes, plus vacuum analyze and optimized table guidance.

Explore how the alter command reshapes existing tables in MySQL by adding, dropping, or renaming columns and modifying or adding constraints, including not null and foreign keys.

Explore GUI methods for changing table structure in MySQL using phpMyAdmin and MySQL Workbench, including adding, dropping, and altering columns, primary keys, indexes, and foreign keys, with SQL preview.

Discover how to add new records with MySQL's insert into, including field lists, multiple rows, defaults, and inserts from a select, while ensuring data type compatibility.

Learn to insert spatial data in MySQL using geometry constructors for points, line strings, polygons, and multi geometries, and when to use geometry from text or JSON with an SRID.

Update existing data with the update statement using set clauses and optional where predicates; apply a case statement and subqueries to populate fields like row width and wetland subsystem.

Master deleting records with the SQL delete statement using a where predicate to prevent disasters, and compare delete, truncate, and drop table with practical examples.

Apply SQL data manipulation techniques to import a geospatial CSV into a MySQL database, preserve legacy IDs, and populate nest coordinates with accurate geometry.

Learn how to create users and roles in MySQL, assign host restrictions, set passwords, and grant or drop privileges, including role-based access control for scalable security.

Grant privileges to roles in MySQL with the grant command, including select, insert, update, delete, truncate, create, and column-level rights, plus revoke and show grants for assigning roles to users.

Demonstrate granting privileges to roles in MySQL for geospatial data by creating admin, viewer, and client roles with select, insert, update, and delete rights across schemas and tables.

Explore how viewer and client roles govern geospatial data in QGIS, enforcing insert, update, and delete permissions through database interactions.

Deploy your geospatial MySQL database to a web hosting plan to enable global access. Learn choosing a hosting provider, registering a domain, and enabling SSL for security.

Create a database and a user in cPanel, assign privileges, and import a localhost database into hosting panel. Export from localhost with phpMyAdmin and import into hosting for remote access.

Connect to a remote MySQL server by whitelisting your public IP, understanding static vs dynamic addresses, and granting host access, then verify connections with MySQL Workbench and QGIS.

Resolve a mysql and mariadb spatial data discrepancy by setting the geometry column's srid and validating spatial indexing for consistent, cross-system geospatial data.

Explore how to dissolve overlapping buffers and compute accurate area using ST_Collect and ST_Union in MySQL, with subqueries to optimize performance and handle geometry collections.

Learn practical MySQL backup strategies, including exporting with phpMyAdmin, backing up data directories, and using MySQL dump, with insights on write ahead logging and replication options for reliability.

Explore how Python and MySQL access via Jupyter notebooks blend interactive code with Markdown and LaTeX, enabling easy sharing and rich documentation for geospatial data and maps.

Access MySQL data from Python using a connector that follows pep 249. Learn to import, create a connection, and fetch results in a Jupyter notebook.

Explore how GeoPandas extends pandas with geospatial capabilities, introducing geo data frames and geo series, enabling in‑memory analysis and plotting within Jupyter notebooks.

Get a high-level view of web technology, covering client-side HTML, CSS, and JavaScript, server-side scripting with PHP and databases, and geospatial mapping with Leaflet and Turf JS.

Build a basic client-side web map with Leaflet using a 29-line HTML page and OpenStreetMap tiles. Learn the HTML, CSS, and JavaScript basics and prep for Ajax with MySQL.

Read MySQL data in the WGS84 coordinate system and display it on a web map using Ajax and PHP, returning JSON for a Leaflet layer.

Read utm data from mysql for geospatial web maps and convert it to latitude longitude with proj4 js, transforming utm zone 13 to epsg 4326 on the client.

Shows how to load a web map to a remote server, using a PHP script to fetch geospatial data as json from a local MySQL database and publish live.