Why are database features being ignored, and instead reinvented in the middle tier?

Because stored procedures:

• add another development language, increasing complexity and potentially redundant code (logic written in both languages);
• generally have worse tooling, monitoring and debugging capabilities than PHP, C#, Java, Python, etc;
• are generally less capable than most middle tier languages;
• only have an advantage with high volume data transformation (where you avoid the server roundtrip), which tends to only be a minimum of actual usage.

That being said, it's a common methodology on C# ASP.NET applications.

As Jeff Atwood put it, stored procedures are the assembly language of databases and people don't tend to code in assembly language unless they need to.

I've frequently used materialized views and sometimes used CONNECT BY in Oracle, neither of which I believe exist in MySQL.

I don't tend to use XML/XSLT in the database because, well, that means I'm using XML and XSLT.

As for geographical or spatial data structures, the reason there is probably that they're hard to just "pick up". It's a fairly specialist area. I've read the MySQL manual on spatial data structures and I'm sure it makes sense to someone with extensive GIS experience but to me and my limited needs (which tend to be around marking the latitude/longitude of a point) it just doesn't seem worth the time investment to figure it out.

Another issue is that if you go beyond ANSI SQL (much) then you've just tied yourself somewhat to a particular database vendor and possibly to a specific version. For that reason you'll often find application developers will tend to treat their databases at the lowest common denominator, which means treating them as a dumping ground for relational data.

Because developers don't know about SQL. They rely on DDL and DML generated by tools like Hibernate and language level constructs like JPA annotations. Developers don't care if these are horribly inefficient because they are mercifully hidden by normal log levels and because DBAs are not part of development teams.

That's why I like tools iBATIS. They make you write and understand SQL, including DBMS specific features.

I guess one reason is the fear of vendor lockin.

This doesn't get said all that often, but the benefits of using vendor-specific features need to be weighed against the cost. Mainly the cost of having to rewrite the parts that rely on vendor-specific features for every database you want to support. There is also a performance cost if you implement something in a general purpose way when the vendor provides a better way.

I'll bring up this example: one might find the "lockin" of SQL Server to be more acceptable once one realizes all of the things Analysis Services, Reporting Services, and so on can do for your application. For major commercial database systems, it is not "just" the SQL database engine that needs to be taken into account.