https://linen.dev logo
Join Slack
Powered by
Hello everyone, My name is Diego. I am a geographe...
# announcements
d
Hello everyone, My name is Diego. I am a geographer and I work on geospatial data. I usually use postgres and postgis to store my data. Sometimes I use elasticsearch too. In the last project, finished this week, I used a soup of strategies to extract and to load all the data I needed (shell, python, PDI). I used the rundeck to build and schedule the pipelines. For the next projects I intend to use a more modern approach with new tools. Googling for modern FOSS ETL tools I found the Airbyte. I like its approach but I have some doubts. The main doubt concern the support for geospatial data. Reading the postgres docs, I couldn't find any mention to spatial data. So I'd like to know if I lost something or if this feature is yet not completely developed.
c
Hi diego. Do you have a specific feature or set of features in mind?
Or data source / destination?
I'd be happy to think through this with you and figure out what we can support for you.
But at this time we have no special handling of geospatial data.
d
Hi Charles, I thought in 2 approachs. The first one is implement the suport to spatial data( geometry field) in any source and destination that can work with geo. The second one is to implement a OGR source and OGR destination. OGR can read and write many kinds of files and formats formats. (https://gdal.org/drivers/vector/index.html). I usually use the ogr2ogr utilitie.
there 5 main formats for me, shapefile, geojson, kml/kmz, wfs(ogc standard) and postgres/postgis. there's anothers importants sources like gml, geopackge, spatialite... But the main destination would be postgres/postgis.
c
The first one is implement the suport to spatial data( geometry field) in any source and destination that can work with geo.
can you give me a better sense of this this entails? i'd be curious to undersatnd better what this would look like for, say, postgis.
so if i'm understanding okay, adding geospatial support to postgres and then having a source that could ingest ogr files, that would hit a large set of use cases?
depending on what the postgis requires this seems pretty doable.
d
Postgis is an extension of postgres and it provides some special data types, geometry and geography and many function that can operates over them. The geometry type is stored as WKB. I like your suggestion. To implement in postgres destination the geometry type. But it's important to verify if postgis is installed in the target. And to implement a OGR source. I think theses features are welcome in the Geospatial Community. I'm a programmer to and I could work with you in this features.
c
hi diego, had a crazy day and didn't have a ton of time to think about this, but i'll try to spend some time tomorrow figuring out some next steps here.
We've thought through this some more and I want to try to be transparent about what we can do right now. We're pushing forward for a big launch to get some of the final features we need to be "production-ready" done and that is our main focus right now. We are definitely interested in adding geospatial to our platform, but we can't focus on it until after out launch.
The technical question I'm trying to wrap my head around is whether in our first implementation of what you are describing we will need to add new data type primitives to airbyte or if they can all be serialized as strings internally to airbyte.
for example if the output of the OGR source is some string representation of one of these data types. can we we just do 
INSERT INTO my_table(string_representation_of_loc_data);
?
If so this might be pretty straight forward.
d
Hello Charles, I had some problems in the last month and stay away from work. The ogr2ogr utility has a drive named pg_dump, this drive generate a dump file that could be utilized of psql, like this: ogr2ogr --config PG_USE_COPY YES -f PGDump /vsistdout/ abc.shp | psql -d my_dbname -f - https://gdal.org/drivers/vector/pgdump.html#vector-pgdump
c
interesting. are you familiar with DBT or some sort of similar data normalization tool?
the issue we still have is translating our data types into the special data types that geospatial needs.
we are definitely a little ways from airbyte being able to support that internally and so we need to use something to shim that type conversion.
3 Views
Open in Slack
PreviousNext
Powered by