💡 Support spatial types

[sjaakp]

I have an idea!

I think it's time for Cycle ORM to support spatial types. Currently, you have to jump through all kind of hoops to make them work!

[roxblnfk]

Hi.
Sounds like a big challenge.
I've done some research into the issue. It's quite difficult to find a balance and provide a user-friendly API for all supported DBMSs at once.
I don't yet have a complete picture of how this would look in Cycle.
Do you have any ideas what this might look like and are you willing to invest resources into it?

---

Claude

Details

Spatial Types in ORMs: Cross-Platform Analysis and Implementation Challenges

Executive Summary

This document analyzes spatial data type implementations across major ORM frameworks in different programming languages, examining their approaches, challenges, and providing context for implementing spatial types in Cycle ORM. The analysis reveals a complex landscape where spatial data support varies significantly in maturity, approach, and database compatibility across different ecosystems.

Key Findings:

• Most spatial ORM implementations are database-specific or require significant architectural adaptations
• Performance considerations often outweigh convenience, leading to hybrid approaches
• Schema management and migrations present unique challenges for spatial data
• Cross-database compatibility remains elusive across all examined ORMs

Introduction

Spatial data types represent one of the most challenging aspects of ORM design due to their complexity, database-specific implementations, and performance requirements. Unlike traditional scalar types, spatial types require specialized handling for:

• Storage Format Variations: Different databases use incompatible binary formats (WKB, EWKB, proprietary formats)
• Coordinate System Management: SRID handling and transformations
• Performance Optimization: Spatial indexes and query optimization
• Standards Compliance: OGC Simple Features specification adherence
• Serialization Complexity: Converting between database formats, object representations, and exchange formats (GeoJSON, WKT)

This analysis examines how various ORM ecosystems have addressed these challenges and identifies patterns that could inform Cycle ORM's implementation strategy.

ORM Implementations Analysis

Laravel Eloquent (PHP)

Primary Implementation: Third-party packages (MatanYadaev/laravel-eloquent-spatial, Grimzy/laravel-mysql-spatial)

// Modern approach (MatanYadaev)
use MatanYadaev\EloquentSpatial\Objects\Point;

#[Column(type: 'geometry', subtype: 'point')]
class Place extends Model {
    use HasSpatial;
    
    protected $casts = [
        'location' => Point::class,
    ];
}

// Usage
$place = Place::create([
    'location' => new Point(51.5032973, -0.1195537, 4326)
]);

$places = Place::whereDistance('location', $point, '<', 1000)->get();

Strengths: Object-oriented geometry classes, Cast integration, GeoJSON serialization
Challenges: External package dependency, MySQL/PostgreSQL only, package fragmentation

Doctrine ORM (PHP)

Primary Implementation: jsor/doctrine-postgis, creof/doctrine2-spatial

// PostGIS approach
use Jsor\Doctrine\PostGIS\Types\PostGISType;

#[ORM\Entity]
class MyEntity {
    #[ORM\Column(type: PostGISType::GEOMETRY, options: ['geometry_type' => 'POINT'])]
    public string $point; // WKT format strings
}

// Multi-platform approach
use CrEOF\Spatial\PHP\Types\Geometry\Point;

#[ORM\Column(type: "point")]
private Point $coordinates;

Strengths: Event subscriber integration, DQL spatial functions, Multi-database support (creof)
Challenges: String-based storage (PostGIS), Complex setup, Limited active maintenance

Ruby on Rails ActiveRecord

Primary Implementation: activerecord-postgis-adapter + RGeo

class Place < ActiveRecord::Base
  # Automatic RGeo geometry object casting
end

places = Place.where("ST_DWithin(location, ?, ?)", point, distance)

Strengths: Mature PostGIS integration, Rich migration syntax
Challenges: PostgreSQL lock-in, Schema dumping issues, Complex factory configuration

Django GeoDjango

Primary Implementation: Built-in django.contrib.gis

from django.contrib.gis.db import models

class Location(models.Model):
    point = models.PointField(srid=4326)

nearby = Location.objects.filter(point__distance_lte=(user_location, D(km=1)))

Strengths: Built-in multi-database support, Rich query API, Admin integration
Challenges: Complex setup requirements, Backend-specific limitations

Entity Framework Core (.NET)

Primary Implementation: NetTopologySuite integration

optionsBuilder.UseSqlServer(connectionString, x => x.UseNetTopologySuite());

public class Location {
    public Point Coordinates { get; set; }
}

Strengths: LINQ integration, Multi-database support, Strong typing
Challenges: Migration complexity from EF6, Geography/Geometry confusion

Sequelize (Node.js)

Primary Implementation: Built-in GEOMETRY DataType

const User = sequelize.define('User', {
  location: DataTypes.GEOMETRY('POINT', 4326)
});

// GeoJSON input
await User.create({ location: { type: 'Point', coordinates: [lon, lat] } });

Strengths: GeoJSON standardization, Simple integration
Challenges: Limited spatial operations, Raw SQL requirements

Cross-Cutting Challenges

1. Database Abstraction Complexity

Each database implements spatial types differently, forcing ORMs to choose between database lock-in or feature limitations.

2. Schema Management Complications

Spatial indexes require specific syntax, column type changes are often impossible, and extension dependencies complicate database dumps.

3. Performance vs Convenience Trade-offs

Spatial operations are computationally expensive, requiring hybrid approaches combining ORM and raw SQL.

4. Type System Integration

Complex binary format conversions, coordinate system transformations, and serialization challenges across different formats (WKB, WKT, GeoJSON).

Implementation Patterns

Pattern 1: Single-Database Specialization (Rails + PostGIS)

• Pros: Deep integration, full feature access
• Cons: Database lock-in, limited portability

Pattern 2: Built-in Multi-Database Support (Django)

• Pros: Consistent API, framework integration
• Cons: Lowest-common-denominator features

Pattern 3: External Package Integration (Laravel, Doctrine)

• Pros: Specialized functionality, community-driven
• Cons: Package fragmentation, maintenance concerns

Pattern 4: Library Integration (EF Core + NetTopologySuite)

• Pros: Rich functionality, leverages specialized libraries
• Cons: External dependencies, version compatibility

Key Findings & Recommendations for Cycle ORM

Critical Issues Observed:

• Laravel Eloquent: Package fragmentation, external dependencies, limited database support
• Doctrine: String-based storage limitations, complex setup, maintenance concerns
• Rails: PostgreSQL lock-in, schema dumping complications, adapter detection problems
• Django: Complex setup requirements, backend-specific limitations
• Entity Framework: Migration complexity from legacy types, geography/geometry confusion
• Sequelize: Limited spatial operations, database function incompatibilities

Recommended Strategy for Cycle ORM:

1. Hybrid Architecture: Combine library integration (GeoPHP/Brick/Geo) with database-specific optimizations
2. Progressive Implementation: Start with Point type, expand incrementally
3. Multi-Database Support: PostgreSQL+PostGIS and MySQL 5.7+ as primary targets
4. Strong Typing: Leverage PHP 8+ features for type safety
5. Performance Focus: Query builder integration with spatial function support

Success Factors:

• Developer Experience: Intuitive API design with comprehensive documentation
• Backward Compatibility: Smooth upgrade paths and migration support
• Community Engagement: Early feedback from GIS developers
• Testing Strategy: Comprehensive test suite across database platforms

Conclusion

Spatial types in ORMs reveal fundamental tensions between simplicity and functionality. PHP ORMs face particular challenges with package fragmentation (Laravel) and maintenance issues (Doctrine), while other ecosystems show either deep database integration (Rails) or comprehensive built-in support (Django).

The evidence suggests Cycle ORM should adopt a hybrid approach: leverage PHP's spatial libraries while providing database-specific optimizations. Success depends on balancing functionality, performance, and developer experience through careful API design and progressive feature introduction.

[sjaakp]

I appreciate your concerns. It looks complicated indeed.

As for me, I would be happy if I could maintain spatial entity-data in GeoJSON format.

In other words, to insert, Cycle should send a query to the database like this:

INSERT INTO `table` (..., `location`, ...) VALUES (... , GeomFromGeoJSON('{"type": "Point", "coordinates": [4.654588, 51.799698]}'), ... 

And a select would have:

SELECT ... , ST_AsGeoJSON(`location`) AS location , ... FROM `table`

I managed to make the insertion operation work with a hack like this:

        $entity = $mapper->hydrate($entity, $mapper->cast($body));
        $entity->location = new Fragment("ST_GeomFromGeoJSON('{$entity->location}')");
        $manager->persist($entity)->run();

Selection is quite a lot tougher. Using reflection, I am able to change the columns property of the loader in Select. I can then set:

        $columns['location'] = new Fragment('ST_AsGeoJSON(location)');

Alas, the Driver, using ColumnsTrait::mountColumns(), prepends the table-alias, so I get:

         alias.ST_AsGeoJSON(location)

which the database doesn't accept. As for now, I am stuck at this point.

It could be so simple...