Hello Had a problem ingesting pg db with geometry type colum | Getting-started

Channels

Thread

#getting-started

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

mammoth-bear-12532

1 year ago

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

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

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

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

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

gray-shoe-75895

1 year ago

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

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

👍 1

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

narrow-painting-12219

1 year ago

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

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

narrow-painting-12219

1 year ago

Great, I'll try it, thanks!

View count: 1

Join thread in Slack