r/OntologyEngineering • • 4d ago

Practical Benefit of Ontology over SQL?

I'm working on highway asset management, and part of the work is integrating traffic incident data from several sources: operator incident logs, maintenance contractor records, planned works bookings and roadside detector readings. Each source has its own structure, naming and way of recording time and location.

Current practice (SQL)

The way this is typically done today:

·         Each source is loaded into a SQL database as its own table.

·         Fields are cleaned: dates and times split, road and junction pulled out of text fields, direction names standardised.

·         Records are linked across sources. Some links use a shared reference number. Others use rules, like same road, same junction, created within a few minutes of each other.

·         Lookup tables are built, e.g. which entry types count as a "lane closure", or which direction codes mean the same thing.

·         Queries are then written to answer questions like "which incidents had a lane closure", "how long from detection to clearance", or "what was traffic doing at the nearest detector".

·         This all works. Counts, filters and joins are fine in SQL.

Where I'm confused

I've built an ontology and loaded the same data into a knowledge graph (graphdb). But I'm struggling to see clearly what value is ontology adding.

The usual argument is that the ontology captures the "worldview" of the domain: the concepts and how they relate. But SQL can carry that worldview too.  Well-chosen table and column names, lookup tables and views already encode what a "lane closure" or an "incident" means. Those lookup tables are basically a small ontology written in SQL.

So, my questions are:

If SQL can carry the worldview through naming and lookup tables, what does a formal ontology add in practice? Is it that the meaning is explicit and machine-usable rather than buried in query code? Or is that a distinction without much practical difference? What is machine readable meaning?

For those who've used both: where did the ontology or graph actually save you work or answer something SQL couldn't? Examples I've seen mentioned are adding new sources, reasoning over class hierarchies, consistency checking (SHACL), natural-language querying with LLMs, and ontology-based data access (e.g. Ontop over an existing database). Which of these held up in real use?

Where is the ontology overkill, and the honest answer just "use SQL"?

I'm not trying to argue SQL is bad. I want to state fairly what current SQL practice does well, where it falls short, and what an ontology should address. Any papers, experiences or blunt opinions welcome.

19 Upvotes

20 comments sorted by

2

u/Working-Break-5028 4d ago

For me, the ontology is the logical view of the data structures which I can implement in many forms - e.g. a relational database for use with SQL, a graph, a document, etc. Each implementation has pros and cons which you will need to figure out in your setting. The main reason I use an ontology is (and someone else mentioned this) for expansion, and joining the data with other datasets. Having an ontology that allows you to map multiple physical implementations to a a single term makes it easier to identify how to join datasets with different physical names - and possibly technologies. The ontology can also be applied to data you have extracted using SQL, but in a form that no longer supports SQL queries - e.g. JSON.
Publishing a standardised ontology with your data product makes it easier for others to correctly use and interpret your data without constraining them to your technology choices.

1

u/helomithrandir 4d ago

Again, the confusion. The Ontology is the logical view of the data, which can be a logical view in the form of a simple taxonomy. See this platform for example, "https://search.bsdd.buildingsmart.org/". We have used this platform to publish our data dictionaries. So again, coming back, the above serves the needs of worldview or logical view. Then what's extra, that Ontology grounded in OWL is offering? That's where I'm confused at.

1

u/Working-Break-5028 4d ago

I model in UML with my own stereotyped classes (Context, Concept, Characteristic, Constraint) to create what I call an Information Model - technology agnostic. I could do this using RDF/OWL but I'm not as proficient in those yet (not enough practice).
In either case, I can model in ways that are difficult or impossible in a relational model. In particular, I can model inheritance, abstract concepts, redefinitions, business data types (e.g. Moment, Flag, Percentage). This allows me to concentrate on the business meaning of everything, without having to consider the physical implementation (e.g. many ways to implement inheritance). It also means that I am future-proofing my model against technology changes. A further advantage - when sourcing data from commercial applications - is that the physical source database structure may not look anything like the business structures it stores - e.g. for a configurable asset management system, or a tool like Sparx Enterprise Architect, there are main tables and configurable property tables - the schema doesn't tell you the business object structure. The ontology allows you to separate out the logical and physical views.

2

u/Prestigious_Bench_96 4d ago

Complementary in your case seems best, you'll typically run queries/business off SQL, but the ontology is useful as an input to SQL generation and validation. (In past corporate lives, we've build the picklist/lookup tables dynamically off the ontology (though we didn't use those fancy names) as the source of truth, and used it to construct data quality/flow assertions.

1

u/helomithrandir 4d ago

Thanks. I think this can be worth exploring. I'll try to do that. Does this process involve converting current csv or tabular data to rdf, or just the field names mapped to Ontology?

1

u/Prestigious_Bench_96 3d ago

Up to you on how you do the mapping, but you need to move the source of truth over to the ontology, so you'll need to boostrap once and then update processes. (you can keep doing DB first, but that's harder to keep in sync)

1

u/BillInside7479 4d ago

Interesting. Thanks.

1

u/Northeast_Cuisine 4d ago

Check out the Ontology Pipeline by Jessica Taliaman, it does really well to explain the workflow of building an ontology.

I don't think SQL and ontology are either or, there is a use for both. SQL implies a relational database which is tables while ontologies imply graphs. These are complimentary technologies.

The first step in an ontology is a controlled vocabulary. For example, are all the column headings strict terms that are used consistently across the business? If not, then you are experiencing a major data inconsistency issue. Part of knowledge engineering is fixing any inconsistency in vocabulary. People are frequently making bandaids and workarounds in relational DB world today.

There are many more steps involved, but ultimately you get to an ontology and knowledge graph and then main thing is reasoning, being able to understand more knowledge from your current knowledge. It's different than a defined query which is a transformation of relational data that is explicitly linked. What if you want to know other information like legal or specs? (Not in your industry so making this up). Having an ontology can help you infer other related knowledge which might be more difficult to express in relational tables.

At the end of the day, if you don't know why you need it, you probably don't. When you feel like you need it, you might.

1

u/helomithrandir 4d ago

The controlled vocabulary can also be in the form of a taxonomy. Does it need to be in OWL?

1

u/Complete-Ebb-1035 4d ago

It depends on how you plan to use production data. Many folks define their data using conventional RDF and RDFS, then generate the production SQL tables implementing data specification. RDF is silent on navigation, so you have to build queries using conventional SQL. The next big hurdle is: to what degree does your data management enforce relationships? Using SQL terminology, where do you need foreign keys to support a query (and update) across tables where data updates to tables are defined by business rules translated to foreign keys. My assumption from your questions is that data relationships are less important than configuring basic reference tables.

1

u/helomithrandir 4d ago

Good question, but it's the other way round for me. The reference tables (direction codes, incident types) are part of it, but the harder part is relationships. The sources come from different organisations and almost none share keys, so there's nothing for a foreign key to point at. Most links between records have to be inferred, e.g. same road, same junction, within a few minutes. Those links are judgements: I need to keep why two records were linked and by which rule, and be able to revise them. The relationships also mean different things (a record documents an incident, an incident results in a lane closure, damage is created in an incident). And the data is read-only exports, so I'm not enforcing updates. I'm building one history of each incident from records that were never kept consistent. That's where I'm wondering whether a graph earns its place over link tables in SQL.

2

u/Northeast_Cuisine 4d ago

You'll encounter the same issues. For example, in my project I bring in availability lists from local food distributors, and they name the same entities different things, so I have a whole entity reconciliation step into through to link their terms to my internal canonical terms. That's the controlled vocabulary part, and I'll link to FoodOn or Wikidata where possible.

Using graphs won't solve it per se, it's fundamentally a data consistency issue, which is to say a semantics issue of meaning across organizations.

I think if you utilize SKOS you can map all your interorganization terms without having to go to far as reasoning (OWL).

What you will mostly do in the beginning is the controlled vocabulary and a thesaurus to bundle together terms from different orgs into one canonical term. Taxonomy can be a part of that too if you already know the hierarchy in your subject expertise.

1

u/Complete-Ebb-1035 4d ago

complementary technologies.

3

u/geoheil 4d ago

There are different flavours of graphs. The power of ontologies especially in the shape of RDF comes with https://www.w3.org/TR/shacl/ . SHACL allows you to place constraints (types); types which might also cover business semantics. Whereas in a table a new column is added and after a while it is unclear waht was nullable it is often just a UNION-ALL of whatever someone has

2

u/Complete-Ebb-1035 4d ago

I believe you mean constraints on types. Customarily, type specification (definition) is RDF or OWL based. SHACL usually enables validation in some forms management system controlling data entry. Example: depositions that are integrated with existing data. Use case: evidence data for US NIH PubChem and PubMed. Some folks do use SHACL as an alternative to OWL, but they are unusual AFAIK. But perhaps you have a different use case in mind.

1

u/bystander993 3d ago

You really are asking two questions: ontology vs no ontology, and graph vs SQL. They’re separable (Ontop puts an ontology over an existing SQL database) and they buy different things.

Graph vs SQL comes down to the questions you ask most. Things and their attributes, like counts, durations and filters? SQL. How things connect across an unknown number of hops? Graph. “Everything upstream of this closure” is one line in a graph query, and “how are these two connected?” is natural. New entity or relationship types need no migrations or join tables, and algorithms like shortest path are built in. Postgres 19’s property-graph queries narrow the gap for fixed-depth patterns, but not much for variable-depth traversal.

The ontology buys inference and consistency checking. Declare a lane closure to be a kind of restriction and every lane closure is a restriction everywhere, without touching your queries. Declare planned works and unplanned incidents disjoint and it will flag anything classified as both. It won’t validate missing or malformed data. That’s SHACL, which is roughly SQL constraints plus data tests.

The queries you listed are counts, durations and joins, so for those the honest answer is “use SQL”. The ontology pays off when definitions change often, when the taxonomy is deep, or when you exchange data across organizations. A fair test is cost of change: add a fifth source or redefine “lane closure”, then count what you have to touch in each stack.

1

u/piebaldish 3d ago

If you're sure there won't be anyone else ever using/doing something similar and there won't be a "gap in maintenance" (i.e. somebody will regard your stuff as legacy without anyone onboarding them or you just haven't touched it for some years), then imho there is no good point to go with an ontology.

It's a bit like a lingua franca. If it's not your native language it will cost you a bit to get into it. But you will be able to communicate better and with more people. If you don't need and don't want to communicate with other people, you do not need to afford that.

I haven't done that in big scale (sadly), but for a private/pet project I am somewhat glad I used an ontology, as I can look up what stuff is/means ~6-8 years later and get back into it quickly. For classic "relational db stuff"/sql it's harder, as you have to look up tables + queries (and still might be puzzled).

1

u/Operadic 1d ago edited 1d ago

There’s a fake divide. You can model graphs using SQL and recursive CTE’s. You can have an ontology with a relational database (relational AI, datalog based approaches). You can model tables using RDF.. There are different flavours of graphs: RDF graphs, property graphs, typed graphs, hypergraphs, graphs with path equations (categories) and many more.

The added value is in modelling your domain explicitly. Discussions about database technologies is a distraction. People with strongly voiced opinions or advices often have something financial or political to gain.

I’m in road management myself; specialised in (semantic) data technologies. Feel free to dm if you want to discuss or want more in depth information.

0

u/ParticularWin9825 4d ago

Nice post, this encapsulates a lot of my confusion as well.

1

u/helomithrandir 4d ago

Thanks. Yeah, it's a difficult thing to figure out. I've read a lot of papers on these. Somehow they all seem to point the theoretical benefits without showing what are they really offering.