Services that need to perform CRUD on data use a DB. Data needed to run the service is stored in the DB, and by properly managing and manipulating it, the service's features are delivered.

In the case of GIS, due to its nature, large volumes of data are commonly encountered. For example, the number of buildings nationwide easily exceeds a few million. Not only is having millions of rows an issue by itself, but given the nature of building information, the number of columns is also often several dozen. All of this makes the data volume grow very large.

Because of this, geospatial information also needs to be stored and managed in a DB. The problem, though, is that geospatial data is fairly tricky to store in a DB. Other kinds of data can just be inserted roughly whether as strings or integers, but coordinate values cause issues. For points it's relatively simple with $(x, y)$, but for lines and polygons, since there can be countless $xy$ coordinates, the problem grows bigger.

On top of that, storing more complex shapes not mentioned separately (like a polygon with a hollow center, such as a donut) also causes a lot of trouble. That's why it's most effective to use a suitable tool to insert SHP into a DB.

In this chapter, we'll use ogr2ogr as the method for inserting SHP into a DB. This tool is a library for handling geospatial information and gets installed automatically alongside QGIS.

Since it's much more convenient to use the ogr2ogr that comes bundled with a QGIS installation rather than installing ogr2ogr separately, this article describes that approach.

As of QGIS 3.16, the typical install path is C:\Program Files\QGIS 3.16. Of course, the exact path may vary slightly depending on the version, so treat this as a reference only.

You can find ogr2ogr under C:\Program Files\QGIS 3.16\bin. Let's try inserting SHP by running commands against ogr2ogr via cmd from that path.

BATCH
ogr2ogr -f MySQL MySQL:"{DB name},host={DB host},user={DB account name},password={DB password}" {SHP path} -nln {table name to create} -a_srs {SHP's EPSG code} -lco engine=MYISAM

Running the command above lets you insert SHP into MariaDB. If you don't specify the -nln option, it uses the SHP's own name as-is.

When inserting, in addition to the SHP's table, two additional tables that manage geospatial metadata get created, as follows:

• geometry_columns: metadata about each layer's coordinate information
• spatial_ref_sys: metadata about the coordinate systems used

Developers will rarely need to use these two tables directly, so it's enough to just be aware that they exist. Without knowing this, you might get confused seeing tables other than the one you intended show up.

🙋‍♂️ The DB doesn't have to be MariaDB!
This document explains things using MariaDB as the example, but that's simply because I happen to run MariaDB on my own server—there's no other particular reason.
If you're planning to build a database dedicated to GIS, PostgreSQL is a far better choice. Since PostgreSQL's GIS-related features are extremely powerful, the DB itself provides many convenient features out of the box.
Many GIS tools provide direct Connection Pool support for PostgreSQL by default.

While inserting SHP data, you'll often notice cases where insertion fails due to malformed shapes. Adding the --skipfailure option at the end of the command will skip the rows that error out and insert the rest, so keep that in mind.

An example of SHP inserted into MariaDB. Checking the database with DBeaver even shows the shape of the data based on OSM. You can set the EPSG code in the menu at the bottom, so refer to that as needed.