[postgis-users] Promoting PostgreSQL and PostGIS to wider business intelligence community

[Shaozhong SHI]

Though we have got some good examples of serving data to wider business intelligence community, we are still interested in finding excellent, compelling examples for showing the value of PostgreSQL/PostGIS as a data service to desktop application users.

I just wonder whether there are excellent examples, for general users to appreciate?

[ruv...@beamerbrooks.com]

I doubt whether PostGIS has any direct value whatsoever for desktop application users.  At a very minimum,  using PostGIS directly requires a  knowledge of SQL.  In fact, the more knowledge of SQL a user has, the more powerful PostGIS will be.   SQL is usually taught in a database course which, in many computer science curriculums, is taught in the second or third year, not to end users in another occupation.

Business intelligence systems such as Power BI and Tableau can connect directly to PostGIS data bases and provide end user commands and operations for querying and modifying those databases.  GIS systems such as ArcGIS and QGIS provide similar capabilities.   End users can get nearly all of the power of PostGIS without having to learn anything outside of the business intelligence system or the GIS system. 

PostGIS is probably best reserved for people who have a programming background and whose jobs or avocations involve doing things that are difficult or impossible to do in existing business intelligence or GIS applications.  There seem to be more than enough people like that to keep the PostGIS developers quite busy.

Ruven Brooks

On 9/24/2020 3:58 PM, Shaozhong SHI wrote:

Though we have got some good examples of serving data to wider business intelligence community, we are still interested in finding excellent, compelling examples for showing the value of PostgreSQL/PostGIS as a data service to desktop application users.

I just wonder whether there are excellent examples, for general users to appreciate?

_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[karsten]

Ruven: yes interesting thoughts and probably you are correct with many of your points.

 

Regarding if PostGIS has a relevance to be used directly:  that is a hard one to answer - depending what tools and skills a user has available in the tool belt

 

However, in order to throw something into the example mix here is a GitHUB repro https://www.youtube.com/watch?v=92EC-cZWEUc&feature=youtu.be where I have some examples on how PostGIS and dynamic layers can be used in QGIS desktop. The repro also has some more advanced uses of plpython functions that enable cool dynamic data visualizations e.g. of dynamic weather data via PostGIS

If anyone is interested there is a link to a video talk from QGIS NA 2020 with more details on there as well ...

Cheers

Karsten 

---

From: postgis-users [mailto:postgis-us...@lists.osgeo.org] On Behalf Of ruv...@beamerbrooks.com
Sent: Thursday, September 24, 2020 15:10
To: postgi...@lists.osgeo.org
Subject: Re: [postgis-users] Promoting PostgreSQL and PostGIS to wider business intelligence community

[karsten]

wrong link - here it goes https://github.com/karstenv/dynamic_layers_qgis

---

From: karsten [mailto:kar...@terragis.net]
Sent: Thursday, September 24, 2020 15:29
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] Promoting PostgreSQL and PostGIS to wider business intelligence community

Ruven: yes interesting thoughts and probably you are correct with many of your points.

 

Regarding if PostGIS has a relevance to be used directly:  that is a hard one to answer - depending what tools and skills a user has available in the tool belt

 

However, in order to throw something into the example mix here is a GitHUB repro https://www.youtube.com/watch?v=92EC-cZWEUc&feature=youtu.be where I have some examples on how PostGIS and dynamic layers can be used in QGIS desktop. The repro also has some more advanced uses of plpython functions that enable cool dynamic data visualizations e.g. of dynamic weather data via PostGIS

If anyone is interested there is a link to a video talk from QGIS NA 2020 with more details on there as well ...

Cheers

Karsten 

---

From: postgis-users [mailto:postgis-us...@lists.osgeo.org] On Behalf Of ruv...@beamerbrooks.com
Sent: Thursday, September 24, 2020 15:10
To: postgi...@lists.osgeo.org
Subject: Re: [postgis-users] Promoting PostgreSQL and PostGIS to wider business intelligence community

[Shaozhong SHI]

Hi,  Ruven Brooks,

Good points.  I would think that you are correct.

However, there are experts saying that people who work on PostgreSQL/PostGIS should consider work like data blending and generating reusable data products for desktop application users to consume new data products.

Interesting questions arise.  And, most people are short of knowledge how to do data blending and generating reusable data products.   Therefore, they need clear examples to show them the future path to the new journey of data blending and generation of reusable data products.

That is what I am looking for.

Regards,

Shao 

[Andreas Neumann]

Hi,

In our GIS team (small team of 10, local government, province level) we use a lot of SQL in collaboration with Gradle/GRETL and Jenkins for our automated data flows and statistics. It is amazing how much analysis and data aggregation you can do with SQL only - without having to touch QGIS or ArcGIS or any other so called "business intelligence" tools (that are often quite expensive).

Every new employee that wants to join our team has to have SQL knowledge - that's a prerequisite - or they wouldn't get the job. Most of our employees are not programmers though.

I also teach PostgreSQL/Postgis training courses (2-3 days usually) - a lot of the participants are not programmers but still manage to do analysis with SQL. Typical course participants are scientists, people working at engineering companies or government.

So - I do think there is a significant number of people who use SQL, but aren't programmers.

Andreas

[Shaozhong SHI]

All very interesting and useful points!

I am also thinking about data blending and new data production on the data service platform for supporting the wider Business Intelligence community.

Surely, we need excellent examples to show them that we make data ready for their consumption, and are here to help them.

Regards,

Shao

[Basques, Bob (CI-StPaul)]

All,

 

I almost jumped over this thread from the beginning because I wasn’t understanding the original question very well, mostly based on my own labels for these types of services/datasets.  I reference things like this as “derived” data.  The actual source data doesn’t exist (unless it gets cached for performance reasons) but rather it a data blending via a SQL call. 

 

I agree with other comments here too, related to this type of product usually being the sort of thing that can’t be easily done by most GIS apps out of the box.  Also provides for a pipelining of processes of sorts for sudo processing on the fly.

 

I’ve got a few different examples of this, some fairly simple, some very complicated that are treated as datasets by the end users, because the SQL is embed into a config, like a Mapserver Mapfile for example.  More and more of our datasets are being created in this fashion vs historically sourcing a “real” dataset directly.

 

In general the end users are starting to think and expect this type of analysis approach to the data, especially related to time indexing and looking at data over time.  Consequently, this is pushing me (us) to start thinking about time indexing of data and how to store datasets accordingly.

 

Bobb

 

 

 

From: postgis-users <postgis-us...@lists.osgeo.org> on behalf of Shaozhong SHI <shisha...@gmail.com>
Reply-To: PostGIS Discussion <postgi...@lists.osgeo.org>
Date: Friday, September 25, 2020 at 3:11 AM
To: PostGIS Discussion <postgi...@lists.osgeo.org>
Subject: Re: [postgis-users] Promoting PostgreSQL and PostGIS to wider business intelligence community

 

Think Before You Click: This email originated outside our organization.

[ruv...@beamerbrooks.com]

Let me get this straight: are the end users the ones using SQL to create these derived/blended data sets or is the actual creation done by the support or data library team, who are SQL specialists?

Ruven Brooks
 

[Basques, Bob (CI-StPaul)]

Ruven,

 

In our case it’s both, but where the same type of request occurs frequently, it can be automated, consequently we the admins, set up these types of recurring SQL based “derived” dataset services.

 

The admins are much more versed in the SQL side, but that shouldn’t preclude those end users who do know how to write SQL, which is what we target in our support structure.  In truth, we the admins set up the majority of the SQL though.  In the last few years, the number of end users in our organization that has an interest in using SQL has been increasing, but it still overall, a minority sub-group of the end users.

-->

[Bruce Rindahl]

We have a great use case for using PostGIS with ArcGIS.  The secret is connecting to PostGIS via a query layer.  I connect to a view in PostGIS and ArcGIS figures it out just fine (maybe with a little help).

Why PostGIS?  Because the data is constantly changing.  One layer is the current rainfall from our gauges and can change every 2 minutes.  Another layer is predicted inundation from flooding.  The PostGIS table has numerous areas at various flooding discharges but the view only shows what our models are predicting.  This is all behind the scenes and ArcGIS just shows what we want.

QGIS does it even better but nobody here uses it.

[Martin Davis]

That's a nifty use case!

Are the flooding polygons precomputed from some external analysis?  

[Bruce Rindahl]

Yes the inundation polygons are the results of numerous hydraulic models at a range of discharges.  Then tables of predicted discharges from our hydrology models selects the associated inundation polygons for display via a view.

[Shaozhong SHI]

Very interesting points.

Any success in serving raster maps to ArcGIS desktop users?

Any showcases?

Regards,

Shao

On Sat, 26 Sep 2020 at 21:09, Bruce Rindahl <bruce....@gmail.com> wrote:

Yes the inundation polygons are the results of numerous hydraulic models at a range of discharges.  Then tables of predicted discharges from our hydrology models selects the associated inundation polygons for display via a view.