Thread
#getting-started
    n

    narrow-painting-12219

    1 year ago
    Hello Had a problem ingesting pg db with geometry type column: https://gist.github.com/carrbrpoa/b4c62917921d1e320aeb72f396ffe14a
    this type comes with postgis extension
    m

    mammoth-bear-12532

    1 year ago
    @narrow-painting-12219 : can you drop the result of describe table here?
    g

    gray-shoe-75895

    1 year ago
    Based on the logs, it looks like you're using PostGIS extensions? This PR should make the ingest framework not crash with those, but will present the column as a null type. We use SQLAlchemy for the actual metadata extraction. If you'd like to capture the actual type, the GeoAlchemy2 extension might be helpful, especially section 4.2.4 of this manual
    n

    narrow-painting-12219

    1 year ago
    @gray-shoe-75895, yes, the type comes from PostGIS extension So, if I was going to fully capture the type and all it's aspects (dimension, reference system, etc), where should I start to cirurgically tinker? 😄
    Btw, seems that GeoAlchemy2 only deals with the pg type (and SQLite), but we also have lots of Oracle Spatial datasets
    g

    gray-shoe-75895

    1 year ago
    Yep tinkering with it would be good. It seems GeoAlchemy has support for a couple other databases, but hasn't been released in a while. In the meantime, I'll start looking into alternative approaches to capturing this data - it would be really helpful if you sent me a sample db schema that you're using with Postgres and PostGIS
    n

    narrow-painting-12219

    1 year ago
    Nice @gray-shoe-75895, I'll extract something for you
    For example..
    CREATE TABLE public.areas (
    	id serial NOT NULL,
    	...
    	geom geometry(POLYGON, 4326) NULL,
    	CONSTRAINT pk_areas PRIMARY KEY (id)
    );
    
    CREATE TABLE public.arvores (
    	id serial NOT NULL,
    	...
    	geom geometry(POINT, 4326) NULL,
    	CONSTRAINT arvores_pkey PRIMARY KEY (id)
    );
    The columns I named
    geom
    are the ones I'm interested. Also, usually there is a spatial index associated with the column and postgis provides functions to determine it's reference system (in this case 4326), the type (polygon, point, etc), dimensions..
    Please tell me if you need something more
    Being able to automagically have reference system and type, at least, would be very nice
    g

    gray-shoe-75895

    1 year ago
    Yep this should be enough - I'll see what I can do here
    n

    narrow-painting-12219

    1 year ago
    For the sake of completeness, there's a sample of a common spatial index:
    CREATE INDEX idx_areas_geom ON public.areas USING gist (geom);
    g

    gray-shoe-75895

    1 year ago
    I've been looking into the custom types support within SQL alchemy this morning - unfortunately it seems like using GeoAlchemy or GeoAlchemy2 is your best bet for the time being
    n

    narrow-painting-12219

    1 year ago
    Adapt that for use in the project is something doable? For our use cases, this requirement is essential
    g

    gray-shoe-75895

    1 year ago
    I added a very basic attempt at geospatial support to my personal fork here https://github.com/hsheth2/datahub/tree/postgis - I believe it should enable support for PostGIS, although I haven't tested it yet (let me know and I can raise a pull request 🙂). Oracle spatial datasets will be a bit harder since it requires the older GeoAlchemy instead of GeoAlchemy2
    n

    narrow-painting-12219

    1 year ago
    Great, I'll try it, thanks!