Geospatial support for SQL plugin

[awm33]

trafficstars

Overview

One of the reasons I like the frictionlessdata JSON Table Schema is that is supports a portable spatial type, geojson (also in topojson format). I'm in municipal government and a lot of our data is geospatial.

My organization specifically uses Postgres and Oracle for geospatial. Postgres/Postgis is very popular for geospatial. Ideally for these database types, we would create a geometry field and convert the geojson to a geometry. GeoAlchemy adds spatial support SQLAlchemy, but only supports Postgis.

I'm willing to contribute, but need guidance as to whether it makes sense to include geospatial in this lib/repo, and if so, how best to.

Plan

• [ ] discuss a design for this solution
• [ ] implement the project into the library

[roll]

@awm33 Hi. Great! Let me prepare in coming days some ideas for how it could be added to the library. So you'll be able to contribute.

[awm33]

@roll Awesome!

I started playing around here https://github.com/CityOfPhiladelphia/jsontableschema-sql-py/tree/geometry

I used geoalchemy with some changes to it's geometry type to use geojson to pull fields in and out https://github.com/CityOfPhiladelphia/jsontableschema-sql-py/blob/geometry/jsontableschema_sql/mappers.py#L15

I'm also taking a crack at SDE using a similar method. I'm calling it OracleSDE but I think this would work for postgres and orace SDE. Think of SDE as similar to postgis, it adds geospatial types and functions to the database.

[roll]

@awm33 Sorry for the late response I've been on holidays.

I'd like to ask for ideas here from @akariv and @amercader. Adam was working pretty closely on usage of jsontableschem-sql-py and Adria is a geospatial specialist.

[loleg]

See also relevant discussion on geospatial interest in #536

[sirex]

I had the same issue and added support for geometry type like this:

import frictionless
import sqlalchemy as sa
from geoalchemy2.types import Geometry
from frictionless.plugins.sql import SqlStorage


class GeoSqlStorage(SqlStorage):

    def _SqlStorage__read_convert_type(self, sql_type=None):
        if isinstance(sql_type, Geometry):
            return 'geometry'

        return super()._SqlStorage__read_convert_type(sql_type)


def test_geometry_type(mocker):
    metadata = sa.MetaData()
    sa.Table('test_table', metadata, sa.Column('geom', Geometry()))
    storage = GeoSqlStorage(url='sqlite://')
    storage._SqlStorage__metadata = metadata
    package = frictionless.Package.from_storage(storage)
    assert package.resources[0].schema.fields[0].type == 'geometry'

If this seems ok, I could provide a pull request, but this will introduce another dependency on GeoAlchemy.