Re: Postgres for SQL Server users

Brent,

On Tue, May 7, 2019 at 12:42 PM Brent Wood <pcreso(at)yahoo(dot)com> wrote:

> I have not used SS for spatial data, but I don't have a Postgres database
> without Postgis installed. The OSGEO ecosystem and synergies with other
> FOSS GIS tools is fantastic.
>
> And it does not stop with the Postgis extension. For time series data
> (anything from fleet management to sensor data) Postgres has the (new)
> TimescaleDB extension. I ran this very effectively with a 600,000,000
> record database of sensor readings from a research vessel - on a $400
> laptop (with an SSD) for testing/prototyping. The sensor data was stored in
> Timescaledb Hypertables & the location data in Postgis geometry columns in
> those tables. Significantly better performance than native Postgres.
>

Excellent information with impressive numbers.

>
> Also consider language support for database functions... pl/R supports
> some very nice capabilities, especially supporting websites. Instead if
> running a Postgres query to return the data to plot via the web page, or
> storing static plots in your CMS that need updating when you get new data,
> you can use Postgres functions in pl/R to render the plot of the data in a
> file, and return the name of the file. The web site does no rendering, just
> invokes the SQL & displays the file that is returned. So the DB can return
> the data and/or the graphic. Back up your database & back up your
> functions. This paradigm can work very effectively...
>

Is there any tutorial/example code for rendering pl/R images on a website?
Cool feature

>
> Generally, the FOSS ecosystem around Postgres offers an incredible array
> of tools and capabilities that I don't think any other db - FOSS or not -
> can provide. I have had limited exposure to Oracle, SQL Server, Sybase,
> Empress, Teradata, Netezza, DB2, Sqlite/Spatialite, Interbase & Informix.
> Of these, Postgres & Sqlite3 (which one depends on use cases) are all I use
> these days.
>

Yep. agreed.

Thanks,

Igal

>
>
> On Tuesday, May 7, 2019, 5:36:00 PM GMT+12, Tony Shelver <
> tshelver(at)gmail(dot)com> wrote:
>
>
> I have to agree on the geospatial (GIS) features.
> I converted from SQL Server to Postgresql for our extended tracking
> database. The SS geospatial feature set doesn't seem nearly as robust or
> complete or perfoirmant as that supplied by PostGIS.
> The PostGIS ecosystem of open source / 3rd party tools is also far bigger,
> for anything to do with mapping. Openstreetmaps.org stores their world
> dataset on Postgresql / PostGIS, and there a ton of mostly open
> source-based tools and organizations that work with it or any other PostGIS
> data to provide a complete GIS solution.
>
> My first sS implementation had me backing out of storing geographic points
> in the relevant SQL Server datatype as the performance hit during loading
> was just too big. Doing the same thing in Postgresql / PostGIS is nardly
> noticeable.
>
> Another feature in Postgres is that you are not restricted to just plpgsql
> as an internal procedural language.
>
> I am not an expert, but it also seems far easier to create, install and
> work with major extensions to Postgresql than SQL Server. I found
> installing the GIS featureset in SS to be a bit of a pain back oin the
> day..
>
> On Tue, 7 May 2019 at 00:53, Michel Pelletier <pelletier(dot)michel(at)gmail(dot)com>
> wrote:
>
> On Mon, May 6, 2019 at 2:49 PM Adam Brusselback <adambrusselback(at)gmail(dot)com>
> wrote:
>
> I think the main "gotcha" when I moved from SQL Server to Postgres was I
> didn't even realize the amount of in-line t-sql I would use to just get
> stuff done for ad-hoc analysis. Postgres doesn't have a good way to emulate
> this. DO blocks cannot return resultsets, so short of creating a function
> and dropping it, it's not possible to get the same workflow.
>
>
> Just ruminating here, and this has probably already been discussed in the
> past, but I've always wanted something like a 'SELECT DO [LANGUAGE ...]
> RETURNS rettype | TABLE (...) $$ RETURN [NEXT | QUERY] ... $$; but haven't
> had any serious problem with creating/dropping functions like you mentioned.
>
> -Michel
>
>
> The lack of GUI tooling was also a huge "whoa" moment for me, which I
> still grapple with.
>
>