Re: [postgis-users] PostGIS verse those in Azure Cloud
Suprio Ray and Ahmed Eldway are two researchers that do a lot of spatial computation work on big data (Hadoop, Spark, and parallel computation frameworks). In my opinion, PostGIS is a more robust tool for spatial operations. However other tools and platforms can be very good for specific spatial operations. On Tue, Aug 29, 2023, 12:40 PM Shaozhong SHI wrote: > Geospatial capability has varied maturity in different systems, particular > of interest is those in Azure Cloud as compared to PostGIS. > > Is there any publication on this topic? > > Regards, > > David > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS verse those in Azure Cloud
I'm not sure this is considered Azure Cloud, but one more interesting tidbit in the Microsoft Geospatial space and this I know because many people in OSGeo seem to be obsessed by it. This whole STAC and Earth Science wave dominating the market https://planetarycomputer.microsoft.com/docs/quickstarts/reading-stac/ Here you have the intersection of Microsoft, STAC, QGIS, Python, CloudNative (add another buzzword) as you can see they even have a whole section about QGIS and QGIS on the Hub https://planetarycomputer.microsoft.com/docs/overview/qgis-plugin/ Hope that helps, Regina ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS verse those in Azure Cloud
> Shaozhong SHI writes: > > > Geospatial capability has varied maturity in different systems, > > particular of interest is those in Azure Cloud as compared to PostGIS. > > > > Is there any publication on this topic? > > Postgis lives firmly in the open source world so I suspect you are getting a lot > of silent reactions like "huh, I never thought to use azure cloud - I wonder if > anyone knows." > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users Azure Cloud is a very vague thing. It's really no more informative than saying "Microsoft". You need to be more specific about what you are talking about and for what purpose. Azure Cloud is a whole suite of tools. They have cloud compute services. Most people I know using Azure, are running on Cloud compute using Ubuntu with their own installed PostgreSQL/PostGIS and they don't use any of the other Azure services aside from the blob storage. If you are looking at databases they have DbaaS for PostgreSQL, SQL Server, and MySQL. For example there is Azure Database for PostgreSQL And that I know does carry PostGIS and pgrouting Within that, they've got a thing they call Flexible Server https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/overview Which I think gives you more flexibility as to what you can install and still have a managed database. Then they have a thing called single server which was their flagship DbaaS. As far as single server goes, I'd probably stay clear of it, since the documentation they have lists near death versions of PostgreSQL and EOL'd versions of PostGIS and pgrouting Though it could be their documentation is just out of date. https://learn.microsoft.com/en-us/azure/postgresql/single-server/concepts-ex tensions Then there is their Azure Cosmos Db for PostgreSQL -- which is basically PostgreSQL Citus extension under the hood (folded in when they bought the CitusDb PostgreSQL company) https://learn.microsoft.com/en-us/azure/cosmos-db/postgresql/introduction -- I'm not sure what extensions besides Citus are offered in this. I'm pretty sure PostGIS is offered here (and at least 3.something version and PostgreSQL 13 or above) as I do recall one of my clients running this with PostGIS 3.0 or 3.1 installed. and then there is the whole Azure IOT Maps thing. I don't know anyone using this, but looks like a pay per call suite of api services. https://azure.microsoft.com/en-us/products/azure-maps Hope that helps, Regina ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS verse those in Azure Cloud
Shaozhong SHI writes: > Geospatial capability has varied maturity in different systems, particular > of interest is those in Azure Cloud as compared to PostGIS. > > Is there any publication on this topic? Postgis lives firmly in the open source world so I suspect you are getting a lot of silent reactions like "huh, I never thought to use azure cloud - I wonder if anyone knows." ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] PostGIS Known Issues : OEL7 to RHEL8 Upgrade
Got it, Thanks Regina! On Tue, Aug 29, 2023 at 6:55 PM Regina Obe wrote: > I doubt we’ll be ending support of 3.1 in 2024 but we haven’t voted on it. > > We still need to EOL 3.0 too which we haven’t voted on either. > > So 3.0 will most likely be in 2023. 3.1 probably not because we don’t > like to EOL two versions in 1 year. > > > > Also if there is no pressing reason to EOL, e.g the code hasn’t changed so > much that we can’t apply security patches without rewriting the patch, we > aren’t in that much of a rush. > > > > So too things that force us to EOL > > > >1. Patches are harder to Apply >2. We no longer have CI bots that can comfortably test all the PGs >that were supported by that minor > > > > Thanks, > > Regina > > > > *From:* Nikhil Shetty > *Sent:* Tuesday, August 29, 2023 8:56 AM > *To:* Regina Obe > *Cc:* PostGIS Users Discussion > *Subject:* Re: [postgis-devel] [postgis-users] PostGIS Known Issues : > OEL7 to RHEL8 Upgrade > > > > Hi Regina, > > > > One quick question, as per doc regd EOL > > > > The PostGIS project strives to support each minor version of PostGIS for > 2-4 years after initial release and at the very least until the lowest > PostgreSQL version supported by the PostGIS minor version is EOL’d > > > > For PostGIS 3.1(released in 2020), the support will end in 2024? or after > PostgreSQL 11 EOL i.e 2023(Next 2-3 months) ? > > > > I was thinking should we go for minor version upgrade (3.1.9) or move > directly to major version (3.2 or 3.3) (will require more stringent tests) > > > > Thanks, > > Nikhil > > > > > > On Fri, Aug 25, 2023 at 8:06 PM Nikhil Shetty > wrote: > > Hi Regina, Greg, > > > > Thank you for the detailed explanation > > > > We will upgrade PostGIS to latest release and then migrate to RHEL8 > > > > We will share with the community if there are any findings > > > > Thanks, > > Nikhil > > > > On Fri, 25 Aug 2023 at 19:37, Regina Obe wrote: > > Nikhil, > > > > As Greg had stated in a related post, I’d advice not using a micro we no > longer support. I already pointed at all the issues with those here - > https://git.osgeo.org/gitea/postgis/postgis/src/branch/stable-3.1/NEWS > > > > The BIG known issue is we can’t help you if anything goes wrong cause we > don’t support PostGIS 3.1.2 and I honestly can’t remember that far back in > time (2 years ago). > > That’s 7 unsupported micro releases ago paired with a PostgreSQL version > that is 7 micro releases behind the latest stable PostgreSQL 13. Yes I > suspect you’ll have lots of issues. > > > > Any issues you run into using PostGIS 3.1.2 instead of any of our > supported micros - https://postgis.net/development/source_code/ > > are on you. PostgreSQL group will have the same answer if you ask them if > you have issues with a micro they stopped supporting a while ago. > > If you report an issue to either PostGIS or PostgreSQL groups, our answer > will be “Upgrade to the latest micro to make sure it’s not an issue we > already solved” or go ask the group (e.g. vendor) > > who is asking you to run an out of date version of software. > > > > I also have no idea whether you are packaging your own or getting it from > a distribution. > > Most of the issues that arise are how you package the dependencies, which > versions you are running with and how it conflicts with stuff you already > have installed or applications that depend on it. > > > > I’m also unclear what version of PostGIS you are upgrading from. Telling > me you are coming from RHEL7 tells me nothing since I have much newer minor > versions of PostGIS running on RHEL 7 systems. > > > > You will need to report at the very least: > > > > SELECT version(), postgis_full_version(); > > > > From your RHEL7 system and if you are doing a pg_dump and pg_restore or > not. > > > > Generally running pg_dump / pg_restore is slowest way to upgrade but least > hassle of issues so easiest to go if you have few databases or a small > database. > > Doing a replication and then pg_upgrade after is the fastest way to > upgrade and what I do for databases in the high gigabytes or terabytes > (least amount of downtime), but there are lots of gotchas with that such as > > What I mentioned here: > https://www.bostongis.com/blog/index.php?/archives/273-Using-pg_upgrade-to-upgrade-PostgreSQL-9.3-PostGIS-2.1-to-PostgreSQL-11-2.5-on-Yum.html > > > > There are also often issues that arise with PROJ, but those should be less > so since you are not trying to upgrade on the same system. > > > > > > Hope that helps, > > Regina > > > > *From:* postgis-devel *On Behalf > Of *Nikhil Shetty > *Sent:* Friday, August 25, 2023 4:03 AM > *To:* PostGIS Users Discussion > *Cc:* PostGIS Development Discussion > *Subject:* Re: [postgis-devel] [postgis-users] PostGIS Known Issues : > OEL7 to RHEL8 Upgrade > > > > Hi Regina, > > > > Please let us know if upgrading to RHEL8 with 3.1.2 would be an issue or > any other known issues with PostGIS > > > > Thanks, > > Nikhil > > > >
[postgis-users] PostGIS verse those in Azure Cloud
Geospatial capability has varied maturity in different systems, particular of interest is those in Azure Cloud as compared to PostGIS. Is there any publication on this topic? Regards, David ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] PostGIS Known Issues : OEL7 to RHEL8 Upgrade
I doubt we’ll be ending support of 3.1 in 2024 but we haven’t voted on it. We still need to EOL 3.0 too which we haven’t voted on either. So 3.0 will most likely be in 2023. 3.1 probably not because we don’t like to EOL two versions in 1 year. Also if there is no pressing reason to EOL, e.g the code hasn’t changed so much that we can’t apply security patches without rewriting the patch, we aren’t in that much of a rush. So too things that force us to EOL 1. Patches are harder to Apply 2. We no longer have CI bots that can comfortably test all the PGs that were supported by that minor Thanks, Regina From: Nikhil Shetty Sent: Tuesday, August 29, 2023 8:56 AM To: Regina Obe Cc: PostGIS Users Discussion Subject: Re: [postgis-devel] [postgis-users] PostGIS Known Issues : OEL7 to RHEL8 Upgrade Hi Regina, One quick question, as per doc regd EOL The PostGIS project strives to support each minor version of PostGIS for 2-4 years after initial release and at the very least until the lowest PostgreSQL version supported by the PostGIS minor version is EOL’d For PostGIS 3.1(released in 2020), the support will end in 2024? or after PostgreSQL 11 EOL i.e 2023(Next 2-3 months) ? I was thinking should we go for minor version upgrade (3.1.9) or move directly to major version (3.2 or 3.3) (will require more stringent tests) Thanks, Nikhil On Fri, Aug 25, 2023 at 8:06 PM Nikhil Shetty mailto:nikhil.db...@gmail.com> > wrote: Hi Regina, Greg, Thank you for the detailed explanation We will upgrade PostGIS to latest release and then migrate to RHEL8 We will share with the community if there are any findings Thanks, Nikhil On Fri, 25 Aug 2023 at 19:37, Regina Obe mailto:l...@pcorp.us> > wrote: Nikhil, As Greg had stated in a related post, I’d advice not using a micro we no longer support. I already pointed at all the issues with those here - https://git.osgeo.org/gitea/postgis/postgis/src/branch/stable-3.1/NEWS The BIG known issue is we can’t help you if anything goes wrong cause we don’t support PostGIS 3.1.2 and I honestly can’t remember that far back in time (2 years ago). That’s 7 unsupported micro releases ago paired with a PostgreSQL version that is 7 micro releases behind the latest stable PostgreSQL 13. Yes I suspect you’ll have lots of issues. Any issues you run into using PostGIS 3.1.2 instead of any of our supported micros - https://postgis.net/development/source_code/ are on you. PostgreSQL group will have the same answer if you ask them if you have issues with a micro they stopped supporting a while ago. If you report an issue to either PostGIS or PostgreSQL groups, our answer will be “Upgrade to the latest micro to make sure it’s not an issue we already solved” or go ask the group (e.g. vendor) who is asking you to run an out of date version of software. I also have no idea whether you are packaging your own or getting it from a distribution. Most of the issues that arise are how you package the dependencies, which versions you are running with and how it conflicts with stuff you already have installed or applications that depend on it. I’m also unclear what version of PostGIS you are upgrading from. Telling me you are coming from RHEL7 tells me nothing since I have much newer minor versions of PostGIS running on RHEL 7 systems. You will need to report at the very least: SELECT version(), postgis_full_version(); >From your RHEL7 system and if you are doing a pg_dump and pg_restore or not. Generally running pg_dump / pg_restore is slowest way to upgrade but least hassle of issues so easiest to go if you have few databases or a small database. Doing a replication and then pg_upgrade after is the fastest way to upgrade and what I do for databases in the high gigabytes or terabytes (least amount of downtime), but there are lots of gotchas with that such as What I mentioned here: https://www.bostongis.com/blog/index.php?/archives/273-Using-pg_upgrade-to-upgrade-PostgreSQL-9.3-PostGIS-2.1-to-PostgreSQL-11-2.5-on-Yum.html There are also often issues that arise with PROJ, but those should be less so since you are not trying to upgrade on the same system. Hope that helps, Regina From: postgis-devel mailto:postgis-devel-boun...@lists.osgeo.org> > On Behalf Of Nikhil Shetty Sent: Friday, August 25, 2023 4:03 AM To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> > Cc: PostGIS Development Discussion mailto:postgis-de...@lists.osgeo.org> > Subject: Re: [postgis-devel] [postgis-users] PostGIS Known Issues : OEL7 to RHEL8 Upgrade Hi Regina, Please let us know if upgrading to RHEL8 with 3.1.2 would be an issue or any other known issues with PostGIS Thanks, Nikhil On Thu, 24 Aug 2023 at 08:24, Nikhil Shetty mailto:nikhil.db...@gmail.com> > wrote: Hi Regina, We do have plans to upgrade both Postgres and
Re: [postgis-users] [postgis-devel] PostGIS Known Issues : OEL7 to RHEL8 Upgrade
Hi Regina, One quick question, as per doc regd EOL The PostGIS project strives to support each minor version of PostGIS for 2-4 years after initial release and at the very least until the lowest PostgreSQL version supported by the PostGIS minor version is EOL’d For PostGIS 3.1(released in 2020), the support will end in 2024? or after PostgreSQL 11 EOL i.e 2023(Next 2-3 months) ? I was thinking should we go for minor version upgrade (3.1.9) or move directly to major version (3.2 or 3.3) (will require more stringent tests) Thanks, Nikhil On Fri, Aug 25, 2023 at 8:06 PM Nikhil Shetty wrote: > Hi Regina, Greg, > > Thank you for the detailed explanation > > We will upgrade PostGIS to latest release and then migrate to RHEL8 > > We will share with the community if there are any findings > > Thanks, > Nikhil > > On Fri, 25 Aug 2023 at 19:37, Regina Obe wrote: > >> Nikhil, >> >> >> >> As Greg had stated in a related post, I’d advice not using a micro we no >> longer support. I already pointed at all the issues with those here - >> https://git.osgeo.org/gitea/postgis/postgis/src/branch/stable-3.1/NEWS >> >> >> >> The BIG known issue is we can’t help you if anything goes wrong cause we >> don’t support PostGIS 3.1.2 and I honestly can’t remember that far back in >> time (2 years ago). >> >> That’s 7 unsupported micro releases ago paired with a PostgreSQL version >> that is 7 micro releases behind the latest stable PostgreSQL 13. Yes I >> suspect you’ll have lots of issues. >> >> >> >> Any issues you run into using PostGIS 3.1.2 instead of any of our >> supported micros - https://postgis.net/development/source_code/ >> >> are on you. PostgreSQL group will have the same answer if you ask them >> if you have issues with a micro they stopped supporting a while ago. >> >> If you report an issue to either PostGIS or PostgreSQL groups, our answer >> will be “Upgrade to the latest micro to make sure it’s not an issue we >> already solved” or go ask the group (e.g. vendor) >> >> who is asking you to run an out of date version of software. >> >> >> >> I also have no idea whether you are packaging your own or getting it from >> a distribution. >> >> Most of the issues that arise are how you package the dependencies, which >> versions you are running with and how it conflicts with stuff you already >> have installed or applications that depend on it. >> >> >> >> I’m also unclear what version of PostGIS you are upgrading from. Telling >> me you are coming from RHEL7 tells me nothing since I have much newer minor >> versions of PostGIS running on RHEL 7 systems. >> >> >> >> You will need to report at the very least: >> >> >> >> SELECT version(), postgis_full_version(); >> >> >> >> From your RHEL7 system and if you are doing a pg_dump and pg_restore or >> not. >> >> >> >> Generally running pg_dump / pg_restore is slowest way to upgrade but >> least hassle of issues so easiest to go if you have few databases or a >> small database. >> >> Doing a replication and then pg_upgrade after is the fastest way to >> upgrade and what I do for databases in the high gigabytes or terabytes >> (least amount of downtime), but there are lots of gotchas with that such as >> >> What I mentioned here: >> https://www.bostongis.com/blog/index.php?/archives/273-Using-pg_upgrade-to-upgrade-PostgreSQL-9.3-PostGIS-2.1-to-PostgreSQL-11-2.5-on-Yum.html >> >> >> >> There are also often issues that arise with PROJ, but those should be >> less so since you are not trying to upgrade on the same system. >> >> >> >> >> >> Hope that helps, >> >> Regina >> >> >> >> *From:* postgis-devel *On Behalf >> Of *Nikhil Shetty >> *Sent:* Friday, August 25, 2023 4:03 AM >> *To:* PostGIS Users Discussion >> *Cc:* PostGIS Development Discussion >> *Subject:* Re: [postgis-devel] [postgis-users] PostGIS Known Issues : >> OEL7 to RHEL8 Upgrade >> >> >> >> Hi Regina, >> >> >> >> Please let us know if upgrading to RHEL8 with 3.1.2 would be an issue or >> any other known issues with PostGIS >> >> >> >> Thanks, >> >> Nikhil >> >> >> >> On Thu, 24 Aug 2023 at 08:24, Nikhil Shetty >> wrote: >> >> >> >> Hi Regina, >> >> >> >> We do have plans to upgrade both Postgres and PostGIS to higher versions >> but currently we are focusing on OS upgrade to RHEL8 >> >> >> >> Do you think being on PostGIS 3.1.2 will impact OS upgrade? Are there any >> known issues >> >> >> >> Thanks, >> >> Nikhil >> >> >> >> On Wed, 23 Aug 2023 at 23:41, Regina Obe wrote: >> >> 3.1.2 is not the latest stable release for 3.1, so you really shouldn’t >> be using that. The 3.1.2 is before the PostgreSQL security vulnerability >> patches for example and numerous bugs have been fixed since then. >> >> >> >> Latest 3.1 is 3.1.9 >> >> >> >> >> https://postgis.net/2023/05/PostGIS-3.3.3-3.2.5-3.1.9-3.0.9-Patch-Releases/ >> >> News specific for 3.1 - >> https://git.osgeo.org/gitea/postgis/postgis/raw/tag/3.1.9/NEWS >> >> >> >> I would say the same holds true for PostgreSQL 13.12 is the latest >> stable,
[postgis-users] Possibly incorrect row count estimate for bounding box operators
I have performance related question as I noticed strange PostGIS behaviour
I can not explain, that degrades query performance.
I do have a table with North America continent split into 1mln irregular
sectors based on population density. Some of the sectors are 100mx100m
(cities) and some are 100kmx100km (north Canada) in size.
Table definition:
db=> \d+ table
Table "table”
Column |Type | Collation | Nullable |
Default | Storage | Compression | Stats target | Description
-+-+---+--+-+--+-+--+-
id | character(36) | | not null |
| extended | | |
polygon | geometry(MultiPolygon,4326) | | |
| main | | 1|
…
Indexes:
"table_pkey" PRIMARY KEY, btree (id)
"table_gist_idx" gist (polygon)
…
Access method: heap
db=> select version();
version
--
PostgreSQL 14.7 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1
20180712 (Red Hat 7.3.1-6), 64-bit
(1 row)
db=> select postgis_full_version();
postgis_full_version
--
-
POSTGIS="3.1.7 aafe1ff" [EXTENSION] PGSQL="120" (procs need upgrade for
use with PostgreSQL "140") GEOS="3.9.1-CAPI-1.14.2" PROJ="8.0.1" GDAL="GDAL
3.4.3, released 2022/04/22" LIBXML="2
.9.1" LIBJSON="0.15" LIBPROTOBUF="1.3.2" WAGYU="0.5.0 (Internal)" (core
procs from "3.1.5 c60e4e3" need upgrade) RASTER (raster procs from "3.1.5
c60e4e3" need upgrade)
(1 row)
db=> select count(*) from table;
count
-
1083887
(1 row)
db=> select oid::regclass, pg_size_pretty(pg_relation_size(oid)) from
pg_class WHERE oid::regclass::text like ‘table%’;
oid | pg_size_pretty
-+
table_pkey | 61 MB
table_gist_idx | 63 MB
table | 774 MB
(6 rows)
And now the problem - the first symptoms are visible with ST_Contains
function - the index is used correctly but the row count estimate (1075) is
way too high. I think it’s causing parallelism to kick off breaking query
performance:
db=> EXPLAIN ANALYZE SELECT id FROM table WHERE ST_Contains(polygon,
ST_PointFromText('POINT(-84.68998 39.1622)', 4326));
QUERY PLAN
-
Gather (cost=1044.35..20805.92 rows=1 width=36) (actual
time=0.479..52.232 rows=1 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Parallel Bitmap Heap Scan on table (cost=44.35..19805.82 rows=1
width=36) (actual time=0.154..0.154 rows=0 loops=2)
Filter: st_contains(polygon,
'010120E61039B9DFA1282C55C0A2B437F8C2944340'::geometry)
Rows Removed by Filter: 0
Heap Blocks: exact=1
-> Bitmap Index Scan on table_gist_idx (cost=0.00..44.35
rows=1075 width=0) (actual time=0.066..0.066 rows=2 loops=1)
Index Cond: (polygon ~
'010120E61039B9DFA1282C55C0A2B437F8C2944340'::geometry)
Planning Time: 0.287 ms
Execution Time: 52.282 ms
The root cause of the problem with ST_Contains row estimation sources from
bounding box ~ operator estimate (1075 as before). The parallelism doesn’t
kick in here however as the BB operation is much cheaper then ST_Contains
function:
db=> EXPLAIN ANALYZE SELECT id FROM table WHERE polygon ~
ST_PointFromText('POINT(-84.68998 39.1622)', 4326);
QUERY PLAN
---
Bitmap Heap Scan on table (cost=44.62..4004.37 rows=1075 width=36)
(actual time=0.068..0.069 rows=2 loops=1)
Recheck Cond: (polygon ~
'010120E61039B9DFA1282C55C0A2B437F8C2944340'::geometry)
Heap Blocks: exact=1
-> Bitmap Index Scan on table_gist_idx (cost=0.00..44.35 rows=1075
width=0) (actual time=0.063..0.063 rows=2 loops=1)
Index Cond: (polygon ~
'010120E61039B9DFA1282C55C0A2B437F8C2944340'::geometry)
Planning Time: 0.210 ms
Execution Time: 0.106 ms
(7 rows)
And here comes the surprise -
