A complete solution for open data platforms, enterprise data catalogs, data lakes and data management. Open source, mature, fully-featured and production ready.

DataHub

Hello
Had a problem ingesting pg db with geometry type column: <https://gist.github.com/carrbrpoa/b4c62917921d1e320aeb72f396ffe14a>

<@U01NH9VPHQD> : can you drop the result of describe table here?

Based on the logs, it looks like you're using PostGIS extensions? <https://github.com/linkedin/datahub/pull/2125|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 <https://geoalchemy-2.readthedocs.io/_/downloads/en/stable/pdf/|this manual>

<@U01GZEETMEZ>, 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? :smile:

Btw, seems that GeoAlchemy2 only deals with the pg type (and SQLite), but we also have lots of Oracle Spatial datasets

Yep tinkering with it would be good. It seems <https://pypi.org/project/GeoAlchemy/|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

Nice <@U01GZEETMEZ>, 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

Yep this should be enough - I'll see what I can do here

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);`

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

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

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 :slightly_smiling_face:). Oracle spatial datasets will be a bit harder since it requires the older GeoAlchemy instead of GeoAlchemy2