New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

farmOS + PostGIS #43

Closed
mstenta opened this Issue Sep 9, 2017 · 7 comments

Comments

Projects
None yet
2 participants
@mstenta
Member

mstenta commented Sep 9, 2017

Drupal's database abstraction layer supports PostgreSQL databases. Using PostgreSQL with farmOS would give us the ability to leverage PostGIS, which "is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL."

@mstenta

This comment has been minimized.

Show comment
Hide comment
@mstenta

mstenta Sep 13, 2017

Member

I created a new branch of farmOS called "postgis" and pushed it to Github: https://github.com/farmOS/farmOS/tree/postgis

It simply replaces the database Docker image (which defaults to MariaDB) with mdillon/postgis (https://hub.docker.com/r/mdillon/postgis/), which is an image that provides a PostgreSQL database (based on the official Postgres docker image) and also installs PostGIS.

I went through the normal farmOS installation process and created a test area with a polygon geometry.

Installation went smooth, with the exception of a small issue that was reported previously: https://www.drupal.org/node/2709107 (installing the farm_soil_test module tries to create a database table name that is too long - the workaround is to not install that module - but I will work on a proper fix to that as well).

Everything seems to be working fine in farmOS running on PostgreSQL - no obvious issues.

So it should be easy to start leveraging some of the advanced geo functions that PostGIS provides. We don't have a use-case for that right now, but I am open to ideas!

Member

mstenta commented Sep 13, 2017

I created a new branch of farmOS called "postgis" and pushed it to Github: https://github.com/farmOS/farmOS/tree/postgis

It simply replaces the database Docker image (which defaults to MariaDB) with mdillon/postgis (https://hub.docker.com/r/mdillon/postgis/), which is an image that provides a PostgreSQL database (based on the official Postgres docker image) and also installs PostGIS.

I went through the normal farmOS installation process and created a test area with a polygon geometry.

Installation went smooth, with the exception of a small issue that was reported previously: https://www.drupal.org/node/2709107 (installing the farm_soil_test module tries to create a database table name that is too long - the workaround is to not install that module - but I will work on a proper fix to that as well).

Everything seems to be working fine in farmOS running on PostgreSQL - no obvious issues.

So it should be easy to start leveraging some of the advanced geo functions that PostGIS provides. We don't have a use-case for that right now, but I am open to ideas!

@mstenta

This comment has been minimized.

Show comment
Hide comment
@mstenta

mstenta Sep 13, 2017

Member

Currently, geometry data storage in farmOS is handled by the Geofield module (https://www.drupal.org/project/geofield), which stores WKB (https://en.wikipedia.org/wiki/Well-known_text#Well-known_binary) in a byte array.

If we want to change that to use a native PostGIS geometry type, that would be possible, but we would have to consider how that affects farmOS users who do not have access to PostGIS (ie: most shared hosting environments). Ideally, we should use the normal Geofield WKB storage by default, but maybe PostGIS geometry storage could be a progressive enhancement (used if available).

That might not be necessary, though. @woodbri and I ran some experiment queries which uses the existing field storage and casts it to PostGIS geometry/geography data, and they seem to work just fine:

Cast to geometry and output as WKT:

SELECT st_astext(field_farm_geofield_geom::geometry) FROM field_data_field_farm_geofield;

Cast to geography and output area in square meters:

SELECT st_area(field_farm_geofield_geom::geography) FROM field_data_field_farm_geofield;

Both worked.

This is exciting also because it means we can leverage PostGIS for area calculation when it's available. Currently we do those calculations in PHP, and they are pretty good, but we could pretty easily include a condition that checks to see if PostGIS is available, and use that if it is. So with PostGIS we can get very accurate area calculations, but people who don't have PostGIS can still get pretty good measurements.

Member

mstenta commented Sep 13, 2017

Currently, geometry data storage in farmOS is handled by the Geofield module (https://www.drupal.org/project/geofield), which stores WKB (https://en.wikipedia.org/wiki/Well-known_text#Well-known_binary) in a byte array.

If we want to change that to use a native PostGIS geometry type, that would be possible, but we would have to consider how that affects farmOS users who do not have access to PostGIS (ie: most shared hosting environments). Ideally, we should use the normal Geofield WKB storage by default, but maybe PostGIS geometry storage could be a progressive enhancement (used if available).

That might not be necessary, though. @woodbri and I ran some experiment queries which uses the existing field storage and casts it to PostGIS geometry/geography data, and they seem to work just fine:

Cast to geometry and output as WKT:

SELECT st_astext(field_farm_geofield_geom::geometry) FROM field_data_field_farm_geofield;

Cast to geography and output area in square meters:

SELECT st_area(field_farm_geofield_geom::geography) FROM field_data_field_farm_geofield;

Both worked.

This is exciting also because it means we can leverage PostGIS for area calculation when it's available. Currently we do those calculations in PHP, and they are pretty good, but we could pretty easily include a condition that checks to see if PostGIS is available, and use that if it is. So with PostGIS we can get very accurate area calculations, but people who don't have PostGIS can still get pretty good measurements.

@mstenta

This comment has been minimized.

Show comment
Hide comment
@mstenta

mstenta Sep 14, 2017

Member

FYI: I fixed the issue with the Soil Test module that was causing farmOS installation to crash on PostgreSQL: https://www.drupal.org/node/2709107 - So now the default installation works without having to disable the Soil Test module as a workaround.

Member

mstenta commented Sep 14, 2017

FYI: I fixed the issue with the Soil Test module that was causing farmOS installation to crash on PostgreSQL: https://www.drupal.org/node/2709107 - So now the default installation works without having to disable the Soil Test module as a workaround.

@mstenta

This comment has been minimized.

Show comment
Hide comment
@mstenta

mstenta Sep 14, 2017

Member

I think we can close this issue, since the primary goal was to test farmOS installation on PostgreSQL and make sure that PostGIS functions will work. That's done.

Before I close it, though, I'd like to decide what to do with the "postgis" branch. I can't merge it in as-is because it would replace MariaDB as the default Docker image. I'm not opposed to that idea itself, but it will cause issues for anyone who is already running a farmOS instance using the docker-compose.yml file provided in the repo.

Perhaps the best thing to do is provide an alternative docker-compose.yml file that just overrides the default one, along with some instructions on farmOS.org for folks who would rather use that one.

Moving forward, we could consider making PostgreSQL the default in the 8.x-2.x branch of farmOS (on Drupal 8). Since that development has only just begun, we won't be interrupting anyone already using it.

Member

mstenta commented Sep 14, 2017

I think we can close this issue, since the primary goal was to test farmOS installation on PostgreSQL and make sure that PostGIS functions will work. That's done.

Before I close it, though, I'd like to decide what to do with the "postgis" branch. I can't merge it in as-is because it would replace MariaDB as the default Docker image. I'm not opposed to that idea itself, but it will cause issues for anyone who is already running a farmOS instance using the docker-compose.yml file provided in the repo.

Perhaps the best thing to do is provide an alternative docker-compose.yml file that just overrides the default one, along with some instructions on farmOS.org for folks who would rather use that one.

Moving forward, we could consider making PostgreSQL the default in the 8.x-2.x branch of farmOS (on Drupal 8). Since that development has only just begun, we won't be interrupting anyone already using it.

@mstenta

This comment has been minimized.

Show comment
Hide comment
@mstenta

mstenta Sep 14, 2017

Member

I added a task on drupal.org under the 8.x-2.x branch to consider using PostgreSQL as the default in that branch: https://www.drupal.org/node/2908823

Member

mstenta commented Sep 14, 2017

I added a task on drupal.org under the 8.x-2.x branch to consider using PostgreSQL as the default in that branch: https://www.drupal.org/node/2908823

@woodbri

This comment has been minimized.

Show comment
Hide comment
@woodbri

woodbri Sep 14, 2017

I think leaving the default docker as MariaDB is fine and adding an additional docker yml file for postgresql and/or postgis is adequate. That give people a choice.

woodbri commented Sep 14, 2017

I think leaving the default docker as MariaDB is fine and adding an additional docker yml file for postgresql and/or postgis is adequate. That give people a choice.

@mstenta

This comment has been minimized.

Show comment
Hide comment
@mstenta

mstenta Sep 14, 2017

Member

Done! And I documented how to use it: http://farmos.org/development/docker/

Member

mstenta commented Sep 14, 2017

Done! And I documented how to use it: http://farmos.org/development/docker/

@mstenta mstenta closed this Sep 14, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment