RE: Segmentation fault restoring data to Azure VM

[Regina Obe]

Yah dropping the raster extension if you are not using it would be my first
suggestion.

 

If you do 

 

DROP EXTENSION postgis_raster;

 

And it successfully drops, then you definitely don't have data tied to it so
safe to drop before you do your pg_dump and restore.

 

I've seen people complain about random crashes with for example a bad netcdf
driver that gets loaded when gdal lib is loaded.  So if you are running into
one of those types of issues, not installing the raster extension will save
you from that pain.

 

I do see you have Azure postgis compiled with GEOS 3.10.2, but you are
running with 3.11.1.  In theory that shouldn't be an issue since 3.10.2 is
upward compatible with 3.11.

 

Everything else at a glance looks to be in shape.

 

Hope that helps,

Regina

 

From: Jeffrey Durrence  
Sent: Tuesday, April 23, 2024 1:21 PM
To: Regina Obe ; postgis-users@lists.osgeo.org
Subject: Re: Segmentation fault restoring data to Azure VM

 

Regina,

 

Thank you so much for your quick response (and for your contributions to the
rest of us in your writings)!

On the physical server I have:
 POSTGIS="3.4.2 c19ce56" [EXTENSION] PGSQL="150" GEOS="3.10.2-CAPI-1.16.0"
PROJ="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
USER_WRITABLE_DIRECTORY=/tmp/proj DATABASE_PATH=/usr/share/proj/proj.db"
GDAL="GDAL 3.4.3, released 2022/04/22" LIBXML="2.9.13" LIBJSON="0.15"
LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" (core procs from "3.4.1
ca035b9" need upgrade) RASTER (raster procs from "3.4.1 ca035b9" need
upgrade)

 

On the Azure server I have 
POSTGIS="3.4.2 c19ce56" [EXTENSION] PGSQL="150" GEOS="3.11.1-CAPI-1.17.1"
(compiled against GEOS 3.10.2) PROJ="8.2.1 NETWORK_ENABLED=OFF
URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj
DATABASE_PATH=/usr/share/proj/proj.db" GDAL="GDAL 3.4.3, released
2022/04/22" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0
(Internal)" RASTER

There are some differences, though I'm not sure of the best strategy to
reconcile this.  As for the raster extension, I don't use it for my
workflows.  I could drop the postgis_raster extension and see if that allows
my export/import to succeed without error.

 

-Jeffrey

 

Jeffrey Durrence

McLean Engineering Company

www.mcleanengineering.com <http://www.mcleanengineering.com/> 

Office: 229-985-1148

Mobile: 229-798-0480

 

  _  

From: Regina Obe mailto:l...@pcorp.us> >
Sent: Tuesday, April 23, 2024 1:09 PM
To: Jeffrey Durrence mailto:jeffrey.durre...@mcleanengineering.com> >;
postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
mailto:postgis-users@lists.osgeo.org> >
Subject: RE: Segmentation fault restoring data to Azure VM 

 

What does 

 

SELECT postgis_full_version();  

 

Run on your current setup vs. your Azure VM setup.

 

Might thinking is it might be a dependent library crashing.

 

Usually the issues are with postgis_raster extension since that drags in
GDAL which drags in several more libraries.

 

Could also be an issue with PROJ.  As I recall at least on windows, I used
to get crashes if PROJ_LIB path wasn't set, but I think that issue has gone
away with recent releases.

 

Not sure if it was ever an issue on Linux or Unix.

 

Hope that helps,

Regina

 

From: Jeffrey Durrence mailto:jeffrey.durre...@mcleanengineering.com> > 
Sent: Tuesday, April 23, 2024 11:42 AM
To: postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> 
Subject: Segmentation fault restoring data to Azure VM

 

Greetings,

 

I am migrating PostgreSQL/PostGIS databases from a physical server with
these specs:
Host OS: Ubuntu 22.04.4 LTS x64 (32GB RAM, 8GB Swap, 1TB disk space, XEON
E5-1660 CPU)
Postgres: 15.6.1-pgdg22.04+1
PostGIS: 3.4.2

 

To an Azure VM with 

Host OS: Ubuntu 22.04.4 LTS x64 (32GB RAM, 8GB Swap, 512GB disk space, XEON
8171M) (again, this is in Azure VM)
Postgres: 15.6.1-pgdg22.04+1
PostGIS: 3.4.2

 

 

In the past, I have migrated the same data from on the same physical
hardware for changes in host O/S, Postgres version, and/or PostGIS version.
I've also migrated from one physical host to another.  When doing so, I try
to make sure that my software packages are up to date and that the packages
in the destination environment match.  I did this for this planned
environment as well.  Because I know that some of my dbs have several years
of history in them, I export these dbs individually using pg_dump (using the
custom format when possible but in some cases I use the plain text option)
and pg_restore on the destination host.

 

During the process of migrating data in this manner, I observed that
restoration of many of my dbs failed.  I did lots of testing with this and
have made these additional observat

RE: Segmentation fault restoring data to Azure VM

[Regina Obe]

What does 

 

SELECT postgis_full_version();  

 

Run on your current setup vs. your Azure VM setup.

 

Might thinking is it might be a dependent library crashing.

 

Usually the issues are with postgis_raster extension since that drags in
GDAL which drags in several more libraries.

 

Could also be an issue with PROJ.  As I recall at least on windows, I used
to get crashes if PROJ_LIB path wasn't set, but I think that issue has gone
away with recent releases.

 

Not sure if it was ever an issue on Linux or Unix.

 

Hope that helps,

Regina

 

From: Jeffrey Durrence  
Sent: Tuesday, April 23, 2024 11:42 AM
To: postgis-users@lists.osgeo.org
Subject: Segmentation fault restoring data to Azure VM

 

Greetings,

 

I am migrating PostgreSQL/PostGIS databases from a physical server with
these specs:
Host OS: Ubuntu 22.04.4 LTS x64 (32GB RAM, 8GB Swap, 1TB disk space, XEON
E5-1660 CPU)
Postgres: 15.6.1-pgdg22.04+1
PostGIS: 3.4.2

 

To an Azure VM with 

Host OS: Ubuntu 22.04.4 LTS x64 (32GB RAM, 8GB Swap, 512GB disk space, XEON
8171M) (again, this is in Azure VM)
Postgres: 15.6.1-pgdg22.04+1
PostGIS: 3.4.2

 

 

In the past, I have migrated the same data from on the same physical
hardware for changes in host O/S, Postgres version, and/or PostGIS version.
I've also migrated from one physical host to another.  When doing so, I try
to make sure that my software packages are up to date and that the packages
in the destination environment match.  I did this for this planned
environment as well.  Because I know that some of my dbs have several years
of history in them, I export these dbs individually using pg_dump (using the
custom format when possible but in some cases I use the plain text option)
and pg_restore on the destination host.

 

During the process of migrating data in this manner, I observed that
restoration of many of my dbs failed.  I did lots of testing with this and
have made these additional observations:

1.  I am able to restore my export files to another physical host with
the same software environment without error.  (the errors occur when
importing to virtual environment).

2.  If I split my export/import into separate steps for the schema and
data, it is the schema import that produces the error, not the data import.

3.  When the error occurs, the db process terminates with a segmentation
vault (see log output below), and the db process restarts.

4.  With logging of all statements enabled, I can see that the
segmentation vault occurs at the absolute end of the import, when it seems
that all commands are completed.  So long as I wait on the server process to
recover, I have no errors when I import the data following the error.

5.  The db imports that produce these errors seem to be those with more
history (e.g., it's more likely that these were created with an older
version of postGIS and subsequently upgraded to the versions they have now).


6.  The errors do seem related to the installation of the PostGIS
extension.  If I purposely drop the extension from the source database
before I export, the resulting file can be imported in the target
environment without error.  This is obviously undesirable as it results in
my losing all of the geometry info from tables, views, etc. in the db.  I
only did this to experiment.

 

My typical restore command looks like this:
pg_restore --verbose --format=custom --dbname=my_db
/tmp/my_db_schema_only.backup 

(plus some options to capture the output) 

 

Here's a look at what the command line output looks like when this happens:

LOG:  server process (PID 52476) was terminated by signal 11: Segmentation
fault

LOG:  terminating any other active server processes

LOG:  all server processes terminated; reinitializing

LOG:  database system was interrupted; last known up at 2024-04-23 02:16:49
UTC

LOG:  database system was not properly shut down; automatic recovery in
progress

LOG:  redo starts at 5/51381960

WARNING:  could not open directory "base/696372": No such file or directory

CONTEXT:  WAL redo at 5/51381A48 for Database/DROP: dir 1663/696372

WARNING:  some useless files may be left behind in old database directory
"base/696372"

CONTEXT:  WAL redo at 5/51381A48 for Database/DROP: dir 1663/696372

LOG:  invalid record length at 5/52679558: expected at least 24, got 0

LOG:  redo done at 5/526794E0 system usage: CPU: user: 0.07 s, system: 0.01
s, elapsed: 0.10 s

LOG:  checkpoint starting: end-of-recovery immediate wait

LOG:  checkpoint complete: wrote 2699 buffers (16.5%); 0 WAL file(s) added,
1 removed, 0 recycled;

B; lsn=5/52679558, redo lsn=5/52679558

LOG:  database system is ready to accept connections

 

In the log file, the section written as the error occurs is something like
this...


[60419] [6627bcbb.ec03] [2024-04-23 13:50:54.813 UTC] [0]: LOG:  statement:
GRANT ALL ON TABLE my_schema.my_table TO my_user_name;

[60419] [6627bcbb.ec03] [2024-04-23 13:50:54.815 UTC] [0]: LOG:  statement:
-- 

RE: need help

[Regina Obe]

I'd check to make sure hibernate has usage rights and execute for all
functions.

 

I recall they changed that in PG 15 I think it was.

 

From: Bekir Niyaz  
Sent: Wednesday, April 3, 2024 4:52 AM
To: postgis-users@lists.osgeo.org
Subject: need help

 

Hello again I updated postgis from 3.0 to 3.3 but didn't execute "alter
extension postgis UPDATE;" and restart db. The query was working well but
after update it starts throwing errors below. 

 

ERROR:  type "geometry" does not exist at character 98
QUERY:  SELECT
ST_FlipCoordinates(st_setsrid(st_geogfromtext(replace(new.tesiskoordinat,','
,'.')),4326)::geometry)

 

it is a simple insert into sql, it throws only when inserted via hibernate, 

postgis installed in public schema and

user has search_path= public, another_schema 

table has select priviliges on public.spatial_ref_sys

 

 

when I check installed extention with \dx the output

  postgis| 3.0.4   | public | PostGIS geometry, geography,
and raster spatial types and functions

 

but when run select Postgis_Version();

 3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

RE: Buildbot

[Regina Obe]

Should be back up now.

 

From: Gandalf the Gray  
Sent: Thursday, April 4, 2024 4:32 AM
To: PostGIS Users Discussion 
Subject: Buildbot

 

Hi guys.

 

It seems Winnie is down again.

 

Pieter

RE: Continuing on clustering queries

[Regina Obe]

> [... deleting for the sake of brevity ...]
> 
> >> This SQL variant does not return errors, but it isn't visible in my
> >> QGIS
> > project
> >> (it's an ongoing project with about twenty layers built with PostGIS).
> >>
> >> Any advice?
> >>
> >> Should I direct this inquiry to the QGIS list?
> >>
> >> Thank you,
> >>
> >> Max
> > It might be because it returns a geometry collection.  I recall QGIS
> > not liking geometry collections.
> > Also depending on how you are creating this -- is this a view or an
> > inline SQL, you might need to force the type for it to show as a layer
option.
> 
> Regina: In these subsequent queries you are specifying SRS 4326; shouldn't
it
> be 2163, the SRS of all of the other layers?
> 
> 

No.  In subsequent queries, I used ST_Transform(geom,2163) only in the
clustering to get a good cluster number and so we could use meters for our
distance clustering.  So 2163 was just used for input to ST_ClusterDBScan
which returns a number.

The geometry itself should still be in it's original 4326 srid unless you
changed it. 4326 is still preferable for most visualization tools is
preferable.

RE: Continuing on clustering queries

[Regina Obe]

> The reason is that it doesn't seem that QGIS can work w TEMP tables.
> 
TEMP tables are only visible to the session that created them, so not a huge
surprise QGIS can't work with them.
I use them mostly to store intermediary results before dumping to another
table.


> From this, I want to create a layer; I use the following SQL:
> 
> SELECT cluster_id AS cid1 ,
> ST_Collect(ST_MinimumBoundingCircle(ST_Collect(geom)), ST_Collect(geom))
> AS geom, COUNT(*) as ct FROM shapefiles.filling_station_clusters GROUP BY
> 1;
> 
> (for some reason, it rejected the SQL if a result was returned with column
> name of cluster_id - possible reserved name?)
> 
Strange.  I should try that sometime.

> This SQL variant does not return errors, but it isn't visible in my QGIS
project
> (it's an ongoing project with about twenty layers built with PostGIS).
> 
> Any advice?
> 
> Should I direct this inquiry to the QGIS list?
> 
> Thank you,
> 
> Max
It might be because it returns a geometry collection.  I recall QGIS not
liking geometry collections.
Also depending on how you are creating this -- is this a view or an inline
SQL, you might need to force the type for it to show as a layer option.

Try:
   
   SELECT cluster_id AS cid1 ,
   ST_Collect(ST_MinimumBoundingCircle(ST_Collect(geom)),
ST_Collect(geom))::geometry(GEOMETRYCOLLECTION,4326) AS geom
   , COUNT(*) as ct FROM shapefiles.filling_station_clusters GROUP BY
   1;

Or having as two separate layers:
-- layer 1
SELECT cluster_id AS cid1 ,
ST_MinimumBoundingCircle(ST_Collect(geom)):geometry(POLYGON,4326) AS geom
, COUNT(*) as ct FROM shapefiles.filling_station_clusters GROUP BY
1;

-- layer 2
SELECT cluster_id AS cid1 ,  ST_Collect(geom):geometry(MULTIPOINT,4326) AS
geom
, COUNT(*) as ct FROM shapefiles.filling_station_clusters GROUP BY
1;

Could also be a permission issue.
If that doesn't help, then yes QGIS mailing list might have more useful
answers.

Hope this helps,
Regina

RE: Continuing on clustering queries

[Regina Obe]

> Thank you again; results are starting to come in.
> 
> Apologies for starting a new thread; but the other one was getting messy.
> 
> Here are the queries that I am testing:
> SELECT city, state, ST_ClusterDBScan( ST_Transform(geom,2163), eps :=
> 0.3048*5000, minpoints :=1) OVER () AS cluster_id INTO TEMP aa_zz FROM
> shapefiles.atp_filling_stations aa WHERE aa.amenity = 'fuel'
> AND aa.ctry_code = 'US' AND aa.geom IS NOT NULL;
> 
> SELECT cluster_id, COUNT(*) FROM aa_zz GROUP BY 1 ORDER BY 2 DESC
> LIMIT 10;
> 
> SELECT state,  cluster_id, COUNT(*) FROM aa_zz GROUP BY 2, 1 ORDER BY 3
> DESC LIMIT 20;
> 
> SELECT city, state,  cluster_id, COUNT(*) FROM aa_zz GROUP BY 3, 2, 1
> ORDER BY 4 DESC LIMIT 20;
> 
> 
> I receive the following results:
> SELECT 76023
>   cluster_id | count
> +---
> 8467 |   422
> 3888 |   371
> 5701 |   249
> 3351 |   177
>  |   141
> 8352 |   120
> 1610 |   107
> 1812 |90
> 1824 |86
>14232 |81
> (10 rows)
> 
>   state | cluster_id | count
> ---++---
>   MI|   8467 |   422
>   NY|   3888 |   371
>   IL|   5701 |   245
>   NY|   3351 |   177
>   CO|   8352 |   110
>   TX|   1610 |   101
>   NY|   1824 |86
>   DC|    |82
>   IN|  14748 |78
>   TX|  14297 |75
>   MO|   3661 |74
>   CA|  11272 |74
>   MI|462 |71
>   TX|  14232 |70
>   TX|  19326 |69
>   TX|   1812 |68
>   FL|  13693 |68
>   TX|  16824 |68
>   CA|  14006 |66
>   IL|   2727 |64
> (20 rows)
> 
>   city | state | cluster_id | count
> --+---++---
>   DETROIT  | MI|   8467 |   147
>   CHICAGO  | IL|   5701 |   104
>   Austin   | TX|   1610 |   101
>   Detroit  | MI|   8467 |85
>   BROOKLYN | NY|   3888 |77
>   Chicago  | IL|   5701 |75
>   Fresno   | CA|  11272 |73
>   Brooklyn | NY|   3888 |71
>   WASHINGTON   | DC|    |66
>   HOUSTON  | TX|  14297 |64
>   St. Louis| MO|   3661 |63
>   HOUSTON  | TX|  14232 |63
>   DENVER   | CO|   8352 |54
>   BRONX| NY|   3351 |53
>   LOS ANGELES  | CA|  14006 |50
>   Bakersfield  | CA|   1777 |49
>   INDIANAPOLIS | IN|  14748 |44
>   Bronx| NY|   3351 |42
>   Amarillo | TX|622 |41
>   Austin   | TX|   1601 |38
> (20 rows)
> 
> 
> Obviously, the case-based aspect of the city name can be cancelled with a
> function such as UPPER(city).
> 
> I also am generous in the radius dimension setting it at 5k feet.
> 
> But my next question is how do position the cluster_id on a map w/o
> referencing the city, and then tying that out to a geometry that I have
> stored for the city?
> 
> Thank you again,
> 
> Max

Depends how you want to show the cluster. If you want to show like a bubble
to represent the cluster, you can probably using ST_MinimumBoundingCircle
https://postgis.net/docs/en/ST_MinimumBoundingCircle.html  
First by keeping the point geometry

CREATE TEMP TABLE tmp_aa_zz AS 
SELECT city, state, ST_ClusterDBScan( ST_Transform(geom,2163), eps :=
 0.3048*5000, minpoints :=1) OVER () AS cluster_id, aa.geom 
FROM
 shapefiles.atp_filling_stations aa WHERE aa.amenity = 'fuel'
AND aa.ctry_code = 'US' AND aa.geom IS NOT NULL;


SELECT cluster_id , ST_MinimumBoundingCircle(ST_Collect(geom)) AS geom,
COUNT(*)
FROM tmp_aa_zz
GROUP BY cluster_id;

Or you might want to show the individual points in which case you can
dispense with the ST_MinimumBoundingCircle call or have it in addition to
like: 

SELECT cluster_id , ST_Collect(ST_MinimumBoundingCircle(ST_Collect(geom)),
ST_Collect(geom)) AS geom, COUNT(*)
FROM tmp_aa_zz
GROUP BY cluster_id;

Other alternative options you can try besides the minimum bounding circle
might be ST_ConvexHull  https://postgis.net/docs/en/ST_ConvexHull.html
, ST_ConcaveHull https://postgis.net/docs/en/ST_ConcaveHull.html, or maybe
even ST_GeometricMedian https://postgis.net/docs/en/ST_GeometricMedian.html
(if you want to show only one point to represent all the points or
ST_Centroid ( I think ST_GeometricMedian would be a more accurate
representation though, think of centroid as the average so would be highly
affected by outlier points where as the median would be less so)

RE: Running clustering queries

[Regina Obe]

> Depends where you got the data, but generally if they give you longitude
> latitude Columns, then 4326 is a safe assumption and looks like you created
> the geometry right too.
> 
> To Gary's point, 4326 is degree based, so no wonder you aren't getting any
> meaningful answers since 1000 would cover the whole world.
> 
> SELECT city, state, ST_ClusterDBScan(geom, eps := 1000, minpoints :=
> > >> 1) OVER () AS cluster FROM shapefiles.atp_filling_stations aa WHERE
> > >> aa.amenity = 'fuel'
> > >> AND aa.ctry_code = 'US' AND aa.geom IS NOT NULL;
> 
> For US data, I have pretty good luck with US National Atlas Equal Area --
> SRID=2163 which is a meter based coordinate system generally good enough
> for accurate measurement.
> Web Mercator is another but that is really bad for measure preserving
> 
> But SRID=2163 is not common for display on maps etc, so I usually only use it
> for good enough measurement preserving or as a functional index, not for
> storage.
> 
> I think your dataset is small enough that you can get away with on the fly
> projection like below
> 
> 
> SELECT city, state, ST_ClusterDBScan( ST_Transform(geom,2163), eps :=
> 0.3048*500, minpoints :=1) OVER () AS cluster FROM
> shapefiles.atp_filling_stations aa
>   WHERE aa.amenity = 'fuel'
> AND aa.ctry_code = 'US' AND aa.geom IS NOT NULL;
> 

Slight amendment.  I guess 2163 is deprecated these days 
https://spatialreference.org/ref/epsg/2163/ , so the replacement is  9311

https://spatialreference.org/ref/epsg/9311/

Which I can't find in our spatial_ref_sys table (guess our table needs an 
update), but you can add with the below
as long as you are using PostGIS 2.5 or higher and PROJ 7 or higher, it's going 
to relegate all the details to PROJ anyway
So the other columns will not be needed except these 3.

INSERT INTO spatial_ref_sys(srid, auth_name, auth_srid)
VALUES(9311, 'EPSG', 9311);


But anyway either should work I think with 2163 there was some issue with one 
of the proj settings which wouldn't impact
your measurement but would affect proper display.

RE: Running clustering queries

[Regina Obe]

> On Thu, 28 Mar 2024, Gary Turner wrote:
> 
> >
> > On 28/03/2024 12:32 pm, Max Pyziur wrote:
> >> On Wed, 27 Mar 2024, Regina Obe wrote:
> >>
> >>> That's the function I would have suggested.
> >>>
> >>> What query are you trying to run and what is the spatial reference
> >>> system id for your data.
> >>>
> >>> I'm guessing the issue might be your units of measure.  If you want
> >>> to do
> >>> 500 ft you'd be best using a meter or mile based spatial reference system.
> >>
> >> Here are two examples that I tried:
> >> SELECT city, state, ST_ClusterDBScan(geom, eps := 1000, minpoints :=
> >> 1) OVER () AS cluster FROM shapefiles.atp_filling_stations aa WHERE
> >> aa.amenity = 'fuel'
> >> AND aa.ctry_code = 'US' AND aa.geom IS NOT NULL;
> >>
> >>
> >> SELECT ST_ClusterKMeans(geom, 1000) OVER () as cluster_id FROM
> >> shapefiles.atp_filling_stations GROUP BY cluster_id;
> >
> > But what's the SRS of 'geom'?
> >
> > If it's say WGS84, which is a degree based SRS, 1000 will be 1000 degrees!
> >
> > I don't use archaic units, sorry, so I don' know if there are any foot
> > based spatial reference systems.
> 
> So, I create a table from this data that I scraped (about 80k U.S. filling
> stations). The lat and long fields are floats.
> 
> I then issue these two commands:
> ALTER TABLE shapefiles.atp_filling_stations ADD COLUMN geom
> geometry(Point, 4326);
> 
> UPDATE shapefiles.atp_filling_stations SET geom =
> ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
> 
> So the SRS is 4326, right?
> 
> Max

Depends where you got the data, but generally if they give you longitude 
latitude 
Columns, then 4326 is a safe assumption and looks like you created the geometry 
right too.

To Gary's point, 4326 is degree based, so no wonder you aren't getting any 
meaningful answers since 1000 would cover the whole world.

SELECT city, state, ST_ClusterDBScan(geom, eps := 1000, minpoints :=
> >> 1) OVER () AS cluster FROM shapefiles.atp_filling_stations aa WHERE
> >> aa.amenity = 'fuel'
> >> AND aa.ctry_code = 'US' AND aa.geom IS NOT NULL;

For US data, I have pretty good luck with US National Atlas Equal Area -- 
SRID=2163 which is a meter based coordinate system generally good enough for 
accurate measurement.
Web Mercator is another but that is really bad for measure preserving

But SRID=2163 is not common for display on maps etc, so I usually only use it 
for good enough measurement preserving or as a functional index, not for 
storage.

I think your dataset is small enough that you can get away with on the fly 
projection like below


SELECT city, state, ST_ClusterDBScan( ST_Transform(geom,2163), eps := 
0.3048*500, minpoints :=1) OVER () AS cluster FROM 
shapefiles.atp_filling_stations aa 
WHERE aa.amenity = 'fuel'
AND aa.ctry_code = 'US' AND aa.geom IS NOT NULL;

RE: Running clustering queries

[Regina Obe]

That's the function I would have suggested.

What query are you trying to run and what is the spatial reference system id
for your data.

I'm guessing the issue might be your units of measure.  If you want to do
500 ft you'd be best using a meter or mile based spatial reference system.

> -Original Message-
> From: Max Pyziur 
> Sent: Wednesday, March 27, 2024 5:33 PM
> To: postgis-users@lists.osgeo.org
> Subject: Running clustering queries
> 
> 
> Greetings,
> 
> I have a dataset of filling stations with point geometries (about 80k U.S.
filling
> stations)
> 
> I'm trying to find/make clusters of this data (with a criteria like say
minimum
> of 3 stations w/in 500 feet of each other.
> 
> I've found these webpages; but as yet I can't produce any sort of
meaningful
> results.
> 
> I would appreciate any and all guidence. Thank you.
> 
> Max Pyziur
> p...@brama.com
> 
> 
> ##
> Webpages:
> Examples Of Spatial Clustering With PostGIS - February 28, 2024
> https://mapscaping.com/examples-of-spatial-clustering-with-postgis/
> 
> ST_ClusterDBSCAN
> https://postgis.net/docs/ST_ClusterDBSCAN.html
> 
> PostGIS Clustering with DBSCAN | Crunchy Data Blog
> https://www.crunchydata.com/blog/postgis-clustering-with-dbscan
> 
> clustering - PostGIS nest ST_ClusterKMeans on results of ST_ClusterKMeans
> - Geographic Information Systems Stack Exchange
> https://gis.stackexchange.com/questions/396029/postgis-nest-st-
> clusterkmeans-on-results-of-st-clusterkmeans

RE: How to properly rotate a raster

[Regina Obe]

The only reason I can think why QGIS and DBeaver would show something different 
is how they are projecting.

 

I’m guessing they are both projecting to Web Mercator or some such thing  to 
overlay your geometry on the map, and their assumptions about SRID 28992  are 
different.

 

I’d try transforming both your geometries to 4326

 

ST_Transform( ST_Polygon(…), 4326)

 

I’m guessing whatever spatial projecting they do from 4326 should be consistent.

 

Alternatively ditch the map overlay and then maybe they won’t try to reproject.

 

From: Eloi Ribeiro  
Sent: Thursday, March 21, 2024 11:47 AM
To: PostGIS Users Discussion 
Subject: RE: How to properly rotate a raster

 

Hi Regina and all,

 

I' seeing different things depending on what application I use. On QGIS the 
rotated raster appears smaller in width and height. On DBeaver, both rotate, 
although in different directions.

 

-- Create raster from polygon (black line)

DROP TABLE IF EXISTS public.rast_not_rotated;

CREATE TABLE public.rast_not_rotated AS

SELECT ST_AsRaster(t.geom,1000,1000,'8BUI',1,0) AS rast

FROM (SELECT ST_GeomFromText('POLYGON((166989.36462303242 
505764.77048559673,

 166996.25788353998 505576.476318614,

 167265.54134611748 505585.94966528506,

 167258.67088940175 505774.2438322678,

 166989.36462303242 
505764.77048559673))',28992) geom) t;

ALTER TABLE public.rast_not_rotated ADD COLUMN rid SERIAL PRIMARY KEY;

CREATE INDEX rast_not_rotated_rast_gist ON public.rast_not_rotated USING gist 
(st_convexhull(rast));

SELECT AddRasterConstraints('public'::name, 
'rast_not_rotated'::name,'rast'::name);

 

-- rotate previous raster (blue)

DROP TABLE IF EXISTS public.rast_rotated_from_raster;

CREATE TABLE public.rast_rotated_from_raster AS SELECT ST_SetRotation(rast, 
-0.5) rast FROM public.rast_not_rotated;

ALTER TABLE public.rast_rotated_from_raster ADD COLUMN rid SERIAL PRIMARY KEY;

CREATE INDEX rast_rotated_from_raster_rast_gist ON 
public.rast_rotated_from_raster USING gist (st_convexhull(rast));

SELECT AddRasterConstraints('public'::name, 
'rast_rotated_from_raster'::name,'rast'::name);

 

-- rotate polygon and then save as raster (red)

DROP TABLE IF EXISTS public.rast_rotated_from_vector;

CREATE TABLE public.rast_rotated_from_vector AS

SELECT ST_AsRaster(t.geom,1000,1000,'8BUI',1,0) AS rast

FROM (SELECT ST_Rotate(

ST_GeomFromText('POLYGON((166989.36462303242 
505764.77048559673,

166996.25788353998 505576.476318614,

167265.54134611748 505585.94966528506,

167258.67088940175 505774.2438322678,

166989.36462303242 
505764.77048559673))',28992)

,-0.5, 166989.36462303242, 
505764.77048559673) geom) t;

ALTER TABLE public.rast_rotated_from_vector ADD COLUMN rid SERIAL PRIMARY KEY;

CREATE INDEX rast_rotated_from_vector_rast_gist ON 
public.rast_rotated_from_vector USING gist (st_convexhull(rast));

SELECT AddRasterConstraints('public'::name, 
'rast_rotated_from_vector'::name,'rast'::name);

 

On QGIS.

black line - raster from polygon

red - the rotated polygon and then converted to raster

blue - rotated raster, different from how it is displayed in DBeaver.

 

 


On DBeaver this is how it shows the rotated raster.



 

On DBeaver this is how it shows the rotated polygon and then converted to 
raster.

 

 

 

 

 

On Tuesday, March 19th, 2024 at 14:58, Regina Obe mailto:l...@pcorp.us> > wrote:



An easier way to compare what is happening is to look at the ST_Polygon output 
of the two. https://postgis.net/docs/en/RT_ST_Polygon.html

 

I’m thinking the confusion (which I had thought myself) is that the width and 
height is measured against the X y axis, but now I think about it, I think it’s 
measured along the skew so width and height would remain the same in both 
cases. In the case of your polygon you rotate and then save as raster, the new 
raster is axis aligned and so there you do see the width and height change.

 

So when I run this query:

 

 

SELECT ST_Polygon(r1.rast) AS orig_geom_rast, ST_Polygon(r2.rast) AS 
rotated_geom_rast, ST_Width(r1.rast) AS orig_width, ST_Width(r2.rast) AS 
rotated_width

FROM (SELECT rast FROM rast_not_rotated) AS r1,

(SELECT ST_SetRotation(rast, -0.5 ) AS rast FROM rast_not_rotated) AS r2;

 

The dashed polygon is the original polygon and the filled in one is after you 
rotate the raster. In both cases the ST_Width and ST_Height do not change so 
that’s not a good measure of what is going on.

 

 



 

 

 

 

From: Eloi Ribeiro mailto:m...@eloiribeiro.eu> > 
Sent: Tuesday, March 19, 2024 6:56 AM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: RE: How to proper

RE: Determining postgis version from pg_dump

[Regina Obe]

I don’t think so.  As I recall when you do a pg_dump, it just stores the 

 

CREATE EXTENSION … line without a VERSION so you can’t tell.

 

I could be mistaken though.

 

From: B H  
Sent: Friday, March 22, 2024 2:50 PM
To: PostGIS Users Discussion 
Subject: Determining postgis version from pg_dump

 

Hi, 

I have a year old snapshot of postgresql that had an older version of postgis 
installed at that time.

Is it possible for me to figure out from the dump what version of postgis was 
installed at that time?

#command used to dump the db.

pg_dump  -Fc -b -v -f  

 

I am hoping it is stored in some data/function that pg_dump .

I know it had 2.4.8 to 2.5.9 version (I just don't know which one).

Best,

HB

RE: How to properly rotate a raster

[Regina Obe]

h(rast), ST_Height(rast), ST_SkewX(rast) FROM plot_3;

--  st_width | st_height | st_skewx 

-- --+---+--

--  1000 |  1000 |0

 

-- output raster of ST_SetRotation(rast, -0.035162353342492736)

SELECT ST_Width(rast), ST_Height(rast), ST_SkewX(rast) FROM 
plot_3_rotated_from_raster;

--  st_width | st_height |   st_skewx   

-- --+---+--

--  1000 |  1000 | 0.006952538312005269

 

-- output raster of ST_SetRotation(rast, -0.035162353342492736)

SELECT ST_Width(rast), ST_Height(rast), ST_SkewX(rast) FROM 
plot_3_rotated_from_vect;

--  st_width | st_height | st_skewx 

-- --+---+--

--   977 |   964 |0

 

-- output raster of ST_SetRotation(rast, -0.5)

SELECT ST_Width(rast), ST_Height(rast), ST_SkewX(rast) FROM 
plot_3_rotated_from_raster_05;

--  st_width | st_height |  st_skewx   

-- --+---+-

--  1000 |  1000 | 0.09481479675190897

 

 

Cheers,

Eloi

 

 

On Monday, March 18th, 2024 at 14:01, Regina Obe mailto:l...@pcorp.us> > wrote:



By size, what do you mean exactly? It would change the width and height.

 

What are you expecting rotation to do. Perhaps you can give example out of the 
below and some sample

 

ST_Width, ST_Height, ST_SkewX before and after and what you were expecting.

 

https://postgis.net/docs/en/RT_ST_SetRotation.html

 

From: Eloi Ribeiro mailto:m...@eloiribeiro.eu> > 
Sent: Monday, March 18, 2024 4:57 AM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: How to properly rotate a raster

 

Hi all,

 

I need to rotate a raster, for that I'm using the function ST_SetRotation, like 
so: 

CREATE TABLE plot_3_rotated AS 
SELECT ST_SetRotation(rast, 0.03516235334249185) rast 
FROM plot_3;

 

But I see that is not producing the expected results and is changing size of 
the raster. What am I doing wrong?

Cheers,

 

Eloi

 

 

 

RE: How to properly rotate a raster

[Regina Obe]

By size, what do you mean exactly?  It would change the width and height.

 

What are you expecting rotation to do.  Perhaps you can give example out of the 
below and some sample

 

ST_Width, ST_Height, ST_SkewX  before and after and what you were expecting.

 

https://postgis.net/docs/en/RT_ST_SetRotation.html

 

From: Eloi Ribeiro  
Sent: Monday, March 18, 2024 4:57 AM
To: PostGIS Users Discussion 
Subject: How to properly rotate a raster

 

Hi all,

 

I need to rotate a raster, for that I'm using the function ST_SetRotation, like 
so: 

CREATE TABLE plot_3_rotated AS 
SELECT ST_SetRotation(rast, 0.03516235334249185) rast 
FROM plot_3;

 

But I see that is not producing the expected results and is changing size of 
the raster. What am I doing wrong?

Cheers,

 

Eloi

 

RE: Is there a guide on what and how to do with PostGIS configuration to speed up operations like interecting

[Regina Obe]

There isn’t a single guide I can think of. 

 

Generally the key factors I can think of for speeding intersects, are make sure 
you have spatial indexes in place, parallelization, and memory.

 

Here is one that details how to config params for parallelization

 

http://blog.cleverelephant.ca/2019/05/parallel-postgis-4.html

 

here talks about general params, sadly some is a bit dated

 

 

https://postgis.net/docs/en/using_postgis_dbmanagement.html#build-indexes

https://postgis.net/docs/en/postgis_administration.html#database_tuning_configuration

 

 

From: Shaozhong SHI  
Sent: Monday, March 18, 2024 8:21 AM
To: PostGIS Users Discussion 
Subject: Is there a guide on what and how to do with PostGIS configuration to 
speed up operations like interecting

 

Is there a guide on what  and how to do with PostGIS configuration to speed up 
operations like interecting?

 

Regards,

 

David

RE: PostGIS performance issue

[Regina Obe]

And the query?

 

From: Shaozhong SHI  
Sent: Monday, March 18, 2024 7:57 AM
To: Regina Obe 
Cc: PostGIS Users Discussion 
Subject: Re: PostGIS performance issue

 

product_assurance=# SELECT version(),  postgis_full_version();
 version
  | 
   postgis_full_version

 

--+---
--
PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 
(Red Hat 8.5.0-20), 64-bit | POSTGIS="3.1.10 4e93a1a" [EXTENSION] PGSQL="130" 
GEOS="3.12.1-CAPI-1.18.1" SFCGAL="1.4.1" PROJ="9.2.1" LIB
XML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)"

 

 

On Thu, 22 Feb 2024 at 14:38, Regina Obe mailto:l...@pcorp.us> 
> wrote:

Possible or one of the dependencies is different, or planner differences such 
as the CTE changes that came in PostgreSQL 12, but hard to tell given the 
information you have provided

Or you didn’t reindex your data after the upgrade to a newer version of 
PostgreSQL and such a reindex was required.

 

I have had to change some of my CTEs queries after upgrading to PostgreSQL 12+ 
to get back to same speed or better as prior runs for example.

 

First of all?

 

What does the below return now (and if you can remember the key bits like GEOS, 
PROJ, PostgreSQL Version what did you have before)

 

SELECT version(),  postgis_full_version();

 

 

Two,  If you can provide the query you are trying to run,  that would help, as 
we may see a pattern there.

 

 

 

From: Shaozhong SHI mailto:shishaozh...@gmail.com> > 
Sent: Thursday, February 22, 2024 9:23 AM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: PostGIS performance issue

 

My system has recently been upgraded from Linux 7 to Linux 8.

There are performance issues with PostGIS 

"A process used to take 3.9 hours.  Now, it has taken more than 2 days and yet 
it has not completed running.  No errors have been flagged up."

 

Is possible the PostGIS not configured properly?

 

Regards,

 

David

RE: Database crash when creating raster_in function

[Regina Obe]

Most of that fix should already be in the PG15 3.3.5 except for the parts that 
have PG16 guards, cause they wouldn’t work in PG15 or lower.

So If you find trying to reapply to 3.3.5 works I’d be surprised and be 
interested in which portion of patch you applied.

 

 

 

From: Nikhil Shetty  
Sent: Sunday, March 17, 2024 9:49 AM
To: Regina Obe 
Cc: PostGIS Users Discussion 
Subject: Re: Database crash when creating raster_in function

 

I am  are using raster extension 

 

The creation of raster_in function breaks on PG15 as well, JFYI, I have created 
package for 3.3.5 on rhel7(this is not available in Postgres repo)

 

Will the fix given in 

  <https://trac.osgeo.org/postgis/ticket/5277> 
https://trac.osgeo.org/postgis/ticket/5277

 solve the issue? if yes, I can add the same fix and create new rpms for 3.3.5 
on rhel7

 

Thanks,

Nikhil

 

On Sun, 17 Mar 2024 at 10:07, Regina Obe mailto:l...@pcorp.us> 
> wrote:

Yap that was fixed in PostGIS 3.3.3.

The change there was to support a change in PG16, so you shouldn’t need it for 
upgrading from PG13 to PG15.

 

Are you using postgis_raster or you just have it installed?

 

If you don’t need postgis_raster, I would suggest uninstalling it before 
upgrading. 

 

From: Nikhil Shetty mailto:nikhil.db...@gmail.com> > 
Sent: Saturday, March 16, 2024 10:55 AM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: Database crash when creating raster_in function

 

Hi Team,

 

I am trying to upgrade the postgres database from PG13 to PG15. pg_upgrade is 
crashing with below error when creating the raster_in function

 

pg_restore: creating FUNCTION "public.raster_in("cstring")"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 394; 1255 16439 FUNCTION raster_in("cstring") 
postgres
pg_restore: error: could not execute query: server closed the connection 
unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Command was: CREATE FUNCTION "public"."raster_in"("cstring") RETURNS 
"public"."raster"
LANGUAGE "c" IMMUTABLE STRICT PARALLEL SAFE
AS '$libdir/postgis_raster-3', 'RASTER_in';

 

 

PostGIS version : 3.3.5

 

This looks similar to the issue mentioned in 
https://trac.osgeo.org/postgis/ticket/5277. Is this fixed in 3.3.5 ?

 

Thanks,

Nikhil

RE: Database crash when creating raster_in function

[Regina Obe]

Yap that was fixed in PostGIS 3.3.3.

The change there was to support a change in PG16, so you shouldn’t need it for 
upgrading from PG13 to PG15.

 

Are you using postgis_raster or you just have it installed?

 

If you don’t need postgis_raster, I would suggest uninstalling it before 
upgrading. 

 

From: Nikhil Shetty  
Sent: Saturday, March 16, 2024 10:55 AM
To: PostGIS Users Discussion 
Subject: Database crash when creating raster_in function

 

Hi Team,

 

I am trying to upgrade the postgres database from PG13 to PG15. pg_upgrade is 
crashing with below error when creating the raster_in function

 

pg_restore: creating FUNCTION "public.raster_in("cstring")"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 394; 1255 16439 FUNCTION raster_in("cstring") 
postgres
pg_restore: error: could not execute query: server closed the connection 
unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Command was: CREATE FUNCTION "public"."raster_in"("cstring") RETURNS 
"public"."raster"
LANGUAGE "c" IMMUTABLE STRICT PARALLEL SAFE
AS '$libdir/postgis_raster-3', 'RASTER_in';

 

 

PostGIS version : 3.3.5

 

This looks similar to the issue mentioned in 
https://trac.osgeo.org/postgis/ticket/5277. Is this fixed in 3.3.5 ?

 

Thanks,

Nikhil

RE: Error during gdal 3.5.3 Installation dependency libarmadillo

[Regina Obe]

> Hi,
> 
> On Tue, 2024-03-12 at 15:12 +0530, Nikhil Shetty wrote:
> > I have a requirement to go with v3.3.3 for RHEL8 as of now.
> 
> What forces you to install and old and buggy version?
> 

Yah PostGIS 3.3.6 is latest for 3.3 minor and 3.3 is not the latest stable 
minor.
I would push back on that requirement or at least more of an explanation from 
those making these demands.

RE: Error when parsing GML with st_geomfromgml

[Regina Obe]

You are missing double-quotes, on the xmlns schemes, but even putting those
in, I still get an ERROR: invalid GML representation 

 

 

I haven't tried ST_GeomFromGML with curved geometries before or added
namespaces, so it's possible curves are not supported since there is no
mention on the page of their support.

 

https://postgis.net/docs/en/ST_GeomFromGML.html

 

 

Has this ever worked for you or is this your first time trying?

 

 

 

 

From: Pavel ?tilip  
Sent: Sunday, March 10, 2024 12:35 PM
To: postgis-users@lists.osgeo.org
Subject: Error when parsing GML with st_geomfromgml

 

Hello,

I get an error when trying to parse GML data with ST-GeomFromGML().

I have this GML data and PostGis version POSTGIS="3.3.6 cfe89b3" [EXTENSION]
PGSQL="150" GEOS="3.12.1-CAPI-1.18.1" PROJ="9.0.1" LIBXML="2.9.7"
LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.5.0 (Internal)" (core procs
from "3.3.2 4975da8" need upgrade)

 

select st_geomfromgml('http://www.opengis.net/gml/3.2
xmlns=http://www.opengis.net/gml/3.2>
  

  

  

  -804451.98 -1013551.00 -804451.27 -1013556.10
-804448.51 -1013567.60 -804446.29 -1013566.43 -804440.31 -1013563.31
-804439.61 -1013562.94 -804436.02 -1013561.07 -804414.65 -1013549.08
-804403.56 -1013542.50 -804398.63 -1013539.59 -804393.24 -1013536.78
-804389.23 -1013534.90 -804381.17 -1013531.13 -804381.00 -1013531.02
-804377.85 -1013529.00 -804344.54 -1013507.25 -804342.45 -1013505.89
-804338.49 -1013510.14 -804334.58 -1013514.33 -804290.01 -1013562.14
-804259.12 -1013592.20 -804250.26 -1013600.83 -804234.33 -1013616.33
-804206.72 -1013641.52 -804205.04 -1013640.05 -804202.24 -1013637.60
-804151.82 -1013593.39 -804112.28 -1013561.29 -804092.72 -1013545.40
-804060.98 -1013523.90 -804048.76 -1013513.32 -804043.93 -1013509.14
-804040.27 -1013506.25 -804034.55 -1013501.04 -804025.64 -1013491.83
-804023.40 -1013489.52 -804020.69 -1013487.10 -804015.58 -1013482.55
-803999.72 -1013465.88 -803998.71 -1013464.81 -803981.68 -1013447.91
-803977.37 -1013443.64 -803969.51 -1013403.02 -803949.20 -1013257.77
-803945.89 -1013258.26 -803906.04 -1013264.27 -803881.76 -1013267.93
-803867.87 -1013271.58 -803826.53 -1013282.45 -803817.88 -1013284.73
-803814.66 -1013277.08 -803812.16 -1013271.13 -803793.54 -1013226.87
-803789.80 -1013217.95 -803788.69 -1013215.31 -803750.38 -1013232.06
-803743.67 -1013234.99 -803680.84 -1013256.62 -803647.08 -1013268.24
-803647.63 -1013270.92 -803652.39 -1013294.13 -803610.49 -1013303.28
-803601.17 -1013305.51 -803556.52 -1013316.17 -803518.70 -1013322.96
-803484.74 -1013329.12 -803475.14 -1013330.86 -803474.84 -1013313.85
-803474.59 -1013284.11 -803474.38 -1013281.30 -803471.71 -1013243.16
-803471.08 -1013237.20 -803469.11 -1013218.35 -803464.85 -1013184.13
-803407.46 -1013189.92 -803366.21 -1013188.84 -803352.14 -1013188.73
-803309.68 -1013187.47 -803296.28 -1013186.92 -803285.30 -1013185.85
-803265.90 -1013183.95 -803220.24 -1013179.76 -803219.49 -1013189.01
-803231.89 -1013191.20 -803286.34 -1013200.81 -803394.02 -1013228.85
-803395.23 -1013267.46 -803392.95 -1013299.88 -803397.95 -1013362.71
-803396.52 -1013403.28 -803396.46 -1013405.06 -803365.80 -1013400.44
-803318.95 -1013392.53 -803255.59 -1013376.23 -803236.14 -1013373.84
-803211.65 -1013374.60 -803193.72 -1013378.62 -803192.02 -1013379.23
-803146.03 -1013395.54 -803116.71 -1013403.37 -803079.93 -1013408.53
-803082.72 -1013410.17 -803081.50 -1013411.61 -803108.32 -1013426.02
-803156.41 -1013459.10 -803288.03 -1013558.20 -803327.60 -1013589.93
-803340.61 -1013601.43 -803383.09 -1013635.80 -803411.33 -1013659.27
-803450.92 -1013692.17 -803479.89 -1013718.54 -803511.33 -1013752.53
-803516.82 -1013756.95 -803521.32 -1013760.58 -803524.77 -1013755.94
-803525.47 -1013753.55 -803531.35 -1013733.31 -803540.09 -1013708.40
-803543.28 -1013694.24 -803551.32 -1013658.58 -803549.76 -1013633.82
-803552.06 -1013633.11 -803568.77 -1013627.99 -803579.16 -1013624.80
-803630.95 -1013608.61 -803645.39 -1013603.11 -803720.39 -1013578.34
-803769.79 -1013567.75 -803808.37 -1013565.23 -803803.42 -1013636.22
-803802.46 -1013650.05 -803802.28 -1013652.59 -803801.43 -1013655.06
-803774.02 -1013654.37 -803750.34 -1013660.72 -803730.35 -1013667.06
-803679.87 -1013686.58 -803645.98 -1013701.71 -803607.79 -1013722.05
-803569.11 -1013742.64 -803566.49 -1013744.21 -803549.88 -1013754.15
-803544.85 -1013757.16 -803535.98 -1013759.82 -803529.68 -1013761.70
-803526.39 -1013762.69 -803528.01 -1013769.44 -803531.07 -1013774.09
-803533.30 -1013777.47 -803537.45 -1013783.78 -803545.19 -1013791.18
-803552.93 -1013798.57 -803553.15 -1013798.74 -803567.05 -1013809.42
-803581.03 -1013818.55 -803579.04 -1013821.32 -803572.53 -1013830.35
-803559.33 -1013848.69 -803558.56 -1013849.76 -803555.25 -1013853.89
-803553.00 -1013856.69 -803543.75 -1013868.20 -803533.43 -1013881.06
-803519.06 -1013897.00 -803509.18 -1013907.96 -803485.97 -1013933.72
-803479.68 -1013941.75 -803469.56 -1013954.69 -803520.19 -1013983.05

RE: The intersect function does not utilize indexes

[Regina Obe]

Dapeng,

 

Is there a reason they found a need to hack their PostgreSQL 12.  Can they 
maybe use a newer version of PostgreSQL for their windows testing.

 

I haven’t had time to update instructions for building on windows.  It’s very 
complicated with all the dependencies needed.

 

The steps our windows ci uses for building are here, but it’s very path based 
on how the windows Jenkins build bot is configured and my current mingw64 build 
chain

 

https://git.osgeo.org/gitea/postgis/postgis/src/branch/master/ci/winnie/build_postgis.sh

 

But requires a mingw64 configured.  Currently I’m using gcc 8.1.0, but in 
middle of upgrading to a newer GCC.  So that’s what my focus has been on right 
now and once I have that working, I’ll have build instructions for that.  But 
I’ve still got a ways to go.

 

Thanks, 

Regina

 

 

 

From: Dapeng Wang  
Sent: Tuesday, February 27, 2024 1:05 AM
To: Regina Obe 
Cc: PostGIS Users Discussion 
Subject: Re: The intersect function does not utilize indexes

 

I am very happy to have found the reason.

The client's development database is based on pg12. They modified the nodes.h 
file and added two enumerations above T_SupportRequestIndexCondition. Since the 
client downloaded pg12-postgis from postgis.org <http://postgis.org> , the 
installation file is based on the pg12 version, and the corresponding .h file 
value is 416. However, the client's version is 418 (because they customized two 
enumerations). Adjusting the order of the enumerations and recompiling should 
resolve the issue.

I would like to ask, how to compile postgis on Windows, and if you can provide 
a batch file or instructions.

Thanks,
Dapeng

 

Regina Obe mailto:l...@pcorp.us> > 于2024年2月26日周一 03:06写道：

But do the version()  numbers agree?

 

SELECT version();

 

I recall that issue being in PostgreSQL itself.  So a difference in version 
there might explain things.

 

The other place I’ve see that kind of issue is with foreign tables, but I 
assume you are not using any of those.

 

From: Dapeng Wang mailto:wangdapeng20191...@gmail.com> > 
Sent: Sunday, February 25, 2024 9:09 AM
To: Regina Obe mailto:l...@pcorp.us> >
Cc: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: Re: The intersect function does not utilize indexes

 

The version installed is 3.4.1, which is already the latest version. I'll 
navigate upwards from the "Cache Lookup Error" location to identify the cause. 
Since it's a client development version of the database, any scenario could 
have occurred.
After identifying the cause, I will reply to the email.

 

Regina Obe mailto:l...@pcorp.us> > 于2024年2月24日周六 10:18写道：

I’ve seen that cached lookup error before but can’t recall when.  It might have 
been a bug in a micro release fixed in a later.

 

Verify that

 

SELECT version();

 

Returns the same or higher version number on your windows system than your 
Linux.

 

Also make sure

 

SELECT postgis_full_version(); 

 

Doesn’t say you need updates.  If it does make sure to run

 

SELECT postgis_extensions_upgrade();

 

 

 

From: Dapeng Wang mailto:wangdapeng20191...@gmail.com> > 
Sent: Friday, February 23, 2024 8:25 PM
To: Regina Obe mailto:l...@pcorp.us> >
Cc: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: Re: The intersect function does not utilize indexes

 

The executed SQL is very simple, and I executed the command explain (analyze, 
Buffers, verbose) select a.id <http://a.id>  from random_2point_lines a join t1 
b on public.st_intersects(a.geom, b.geom);. 
While printing the planner's data in the PostgreSQL database, I encountered an 
error message "cache lookup failed for operator family 0."
In a PostgreSQL database, it would calculate the time for sequential scans and 
index scans to choose the one with minimal time consumption. However, in the 
customer's development database, there was an error encountered while scanning 
the index, displaying the mentioned error message.

 

Regina Obe mailto:l...@pcorp.us> > 于2024年2月23日周五 10:23写道：

You have an example query where you are seeing this behavior?

 

From: Dapeng Wang mailto:wangdapeng20191...@gmail.com> > 
Sent: Thursday, February 22, 2024 8:57 PM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: The intersect function does not utilize indexes

 

Hello everyone,

 

I have a client with a database developed on PostgreSQL 12, with both Windows 
and Linux versions. Everything is normal with the Linux version, but there are 
anomalies with the Windows version.

While the Windows database can have the PostGIS extension installed, create 
indexes, and execute SQL queries correctly, spatial functions do not utilize 
indexes, such as intersection and containment.

Here's what I've investigated so far (using the same DDL):
On the Windows system, PostgreSQL 12 is installed along with P

RE: Options to rollback PostGIS upgrade

[Regina Obe]

Correct.  Well that is the safest way cause we don’t support downgrades.

 

In theory if you just ran with the older binaries with newer scripts,  what 
you’d have is all the new functions added between 3.1 and 3.3 broken.

 

You could then just manually run the 3.1 script, but you’d need to hack the 
version checks in postgis_extensions_upgrade().

 

 

 

From: Nikhil Shetty  
Sent: Monday, February 26, 2024 11:20 AM
To: Regina Obe 
Cc: PostGIS Users Discussion 
Subject: Re: Options to rollback PostGIS upgrade

 

Hi Regina,

 

We are upgrading from 3.1.2 to 3.3.3.

 

So, after we run below command the only option to rollback to previous PostGIS 
version is to install old binaries and restore data from backup?

 

SELECT postgis_extensions_upgrade();

 

Thanks,

Nikhil

 

On Mon, 26 Feb 2024 at 20:23, Regina Obe mailto:l...@pcorp.us> 
> wrote:

Which version of PostGIS are you coming from again?

 

In theory, as long as your old is running a newish PostGIS 3+, it should be 
safe enough to just NOT run

 

SELECT postgis_extensions_upgrade();

 

If you don’t run that, then your database is just running with a newer lib 
file, but old scripts.  So then just installing the binaries of older should 
work fine.

 

One caveat, I believe we did make sure changes that caused some functions not 
to work, but I can’t remember what those were.

 

All I know is if you run

 

SELECT postgis_full_version();

 

And you get an error after upgrading your binaries, then not running 
postgis_extensions_ugprade is not an option.

 

From: Nikhil Shetty mailto:nikhil.db...@gmail.com> > 
Sent: Monday, February 26, 2024 3:30 AM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: Options to rollback PostGIS upgrade

 

Hi Team,

 

We are looking at options available to rollback a PostGIS upgrade in case there 
are issues after the upgrade.

 

Apart from removing new packages and installing old ones and then restoring the 
database from backup , do we have any other option?

 

Thanks,

Nikhil

RE: Options to rollback PostGIS upgrade

[Regina Obe]

Which version of PostGIS are you coming from again?

 

In theory, as long as your old is running a newish PostGIS 3+, it should be 
safe enough to just NOT run

 

SELECT postgis_extensions_upgrade();

 

If you don’t run that, then your database is just running with a newer lib 
file, but old scripts.  So then just installing the binaries of older should 
work fine.

 

One caveat, I believe we did make sure changes that caused some functions not 
to work, but I can’t remember what those were.

 

All I know is if you run

 

SELECT postgis_full_version();

 

And you get an error after upgrading your binaries, then not running 
postgis_extensions_ugprade is not an option.

 

From: Nikhil Shetty  
Sent: Monday, February 26, 2024 3:30 AM
To: PostGIS Users Discussion 
Subject: Options to rollback PostGIS upgrade

 

Hi Team,

 

We are looking at options available to rollback a PostGIS upgrade in case there 
are issues after the upgrade.

 

Apart from removing new packages and installing old ones and then restoring the 
database from backup , do we have any other option?

 

Thanks,

Nikhil

RE: PostGIS 3.3 latest releases unavailable for rhel7

[Regina Obe]

yum.postgresql.org  has already discontinued support for rhel-7 so I would 
presume so.

 

From: Nikhil Shetty  
Sent: Monday, February 26, 2024 6:27 AM
To: PostGIS Users Discussion 
Subject: PostGIS 3.3 latest releases unavailable for rhel7

 

Hi Team,

 

I was not able to find the rpm packages for the latest releases of PostGIS 3.3 
for rhel7 

 

The latest available version is 3.3.3 

https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-7-x86_64/

 

whereas for rhel8, all the latest releases(3.3.4, 3.3.5 and 3.3.6) are 
available. Is it because support for rhel7 is ending soon?

 

Thanks,

Nikhil

RE: The intersect function does not utilize indexes

[Regina Obe]

But do the version()  numbers agree?

 

SELECT version();

 

I recall that issue being in PostgreSQL itself.  So a difference in version 
there might explain things.

 

The other place I’ve see that kind of issue is with foreign tables, but I 
assume you are not using any of those.

 

From: Dapeng Wang  
Sent: Sunday, February 25, 2024 9:09 AM
To: Regina Obe 
Cc: PostGIS Users Discussion 
Subject: Re: The intersect function does not utilize indexes

 

The version installed is 3.4.1, which is already the latest version. I'll 
navigate upwards from the "Cache Lookup Error" location to identify the cause. 
Since it's a client development version of the database, any scenario could 
have occurred.
After identifying the cause, I will reply to the email.

 

Regina Obe mailto:l...@pcorp.us> > 于2024年2月24日周六 10:18写道：

I’ve seen that cached lookup error before but can’t recall when.  It might have 
been a bug in a micro release fixed in a later.

 

Verify that

 

SELECT version();

 

Returns the same or higher version number on your windows system than your 
Linux.

 

Also make sure

 

SELECT postgis_full_version(); 

 

Doesn’t say you need updates.  If it does make sure to run

 

SELECT postgis_extensions_upgrade();

 

 

 

From: Dapeng Wang mailto:wangdapeng20191...@gmail.com> > 
Sent: Friday, February 23, 2024 8:25 PM
To: Regina Obe mailto:l...@pcorp.us> >
Cc: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: Re: The intersect function does not utilize indexes

 

The executed SQL is very simple, and I executed the command explain (analyze, 
Buffers, verbose) select a.id <http://a.id>  from random_2point_lines a join t1 
b on public.st_intersects(a.geom, b.geom);. 
While printing the planner's data in the PostgreSQL database, I encountered an 
error message "cache lookup failed for operator family 0."
In a PostgreSQL database, it would calculate the time for sequential scans and 
index scans to choose the one with minimal time consumption. However, in the 
customer's development database, there was an error encountered while scanning 
the index, displaying the mentioned error message.

 

Regina Obe mailto:l...@pcorp.us> > 于2024年2月23日周五 10:23写道：

You have an example query where you are seeing this behavior?

 

From: Dapeng Wang mailto:wangdapeng20191...@gmail.com> > 
Sent: Thursday, February 22, 2024 8:57 PM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: The intersect function does not utilize indexes

 

Hello everyone,

 

I have a client with a database developed on PostgreSQL 12, with both Windows 
and Linux versions. Everything is normal with the Linux version, but there are 
anomalies with the Windows version.

While the Windows database can have the PostGIS extension installed, create 
indexes, and execute SQL queries correctly, spatial functions do not utilize 
indexes, such as intersection and containment.

Here's what I've investigated so far (using the same DDL):
On the Windows system, PostgreSQL 12 is installed along with PostGIS versions 
3.3.0 to 3.4.1, and everything works fine. Spatial functions can utilize 
indexes.
On the Windows system, the client's developed database is installed with 
PostGIS versions 3.3.0 to 3.4.1. Queries using the && operator utilize indexes, 
but spatial functions (such as st_intersects and ST_Contains) do not utilize 
indexes.
Vacuum analyze has been performed, and I've attempted to disable sequential 
scans and force index scans, but to no avail.
Do you have any good suggestions?

 

Thanks,

Dapeng

RE: The intersect function does not utilize indexes

[Regina Obe]

I’ve seen that cached lookup error before but can’t recall when.  It might have 
been a bug in a micro release fixed in a later.

 

Verify that

 

SELECT version();

 

Returns the same or higher version number on your windows system than your 
Linux.

 

Also make sure

 

SELECT postgis_full_version(); 

 

Doesn’t say you need updates.  If it does make sure to run

 

SELECT postgis_extensions_upgrade();

 

 

 

From: Dapeng Wang  
Sent: Friday, February 23, 2024 8:25 PM
To: Regina Obe 
Cc: PostGIS Users Discussion 
Subject: Re: The intersect function does not utilize indexes

 

The executed SQL is very simple, and I executed the command explain (analyze, 
Buffers, verbose) select a.id <http://a.id>  from random_2point_lines a join t1 
b on public.st_intersects(a.geom, b.geom);. 
While printing the planner's data in the PostgreSQL database, I encountered an 
error message "cache lookup failed for operator family 0."
In a PostgreSQL database, it would calculate the time for sequential scans and 
index scans to choose the one with minimal time consumption. However, in the 
customer's development database, there was an error encountered while scanning 
the index, displaying the mentioned error message.

 

Regina Obe mailto:l...@pcorp.us> > 于2024年2月23日周五 10:23写道：

You have an example query where you are seeing this behavior?

 

From: Dapeng Wang mailto:wangdapeng20191...@gmail.com> > 
Sent: Thursday, February 22, 2024 8:57 PM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: The intersect function does not utilize indexes

 

Hello everyone,

 

I have a client with a database developed on PostgreSQL 12, with both Windows 
and Linux versions. Everything is normal with the Linux version, but there are 
anomalies with the Windows version.

While the Windows database can have the PostGIS extension installed, create 
indexes, and execute SQL queries correctly, spatial functions do not utilize 
indexes, such as intersection and containment.

Here's what I've investigated so far (using the same DDL):
On the Windows system, PostgreSQL 12 is installed along with PostGIS versions 
3.3.0 to 3.4.1, and everything works fine. Spatial functions can utilize 
indexes.
On the Windows system, the client's developed database is installed with 
PostGIS versions 3.3.0 to 3.4.1. Queries using the && operator utilize indexes, 
but spatial functions (such as st_intersects and ST_Contains) do not utilize 
indexes.
Vacuum analyze has been performed, and I've attempted to disable sequential 
scans and force index scans, but to no avail.
Do you have any good suggestions?

 

Thanks,

Dapeng

RE: The intersect function does not utilize indexes

[Regina Obe]

You have an example query where you are seeing this behavior?

 

From: Dapeng Wang  
Sent: Thursday, February 22, 2024 8:57 PM
To: PostGIS Users Discussion 
Subject: The intersect function does not utilize indexes

 

Hello everyone,

 

I have a client with a database developed on PostgreSQL 12, with both Windows 
and Linux versions. Everything is normal with the Linux version, but there are 
anomalies with the Windows version.

While the Windows database can have the PostGIS extension installed, create 
indexes, and execute SQL queries correctly, spatial functions do not utilize 
indexes, such as intersection and containment.

Here's what I've investigated so far (using the same DDL):
On the Windows system, PostgreSQL 12 is installed along with PostGIS versions 
3.3.0 to 3.4.1, and everything works fine. Spatial functions can utilize 
indexes.
On the Windows system, the client's developed database is installed with 
PostGIS versions 3.3.0 to 3.4.1. Queries using the && operator utilize indexes, 
but spatial functions (such as st_intersects and ST_Contains) do not utilize 
indexes.
Vacuum analyze has been performed, and I've attempted to disable sequential 
scans and force index scans, but to no avail.
Do you have any good suggestions?

 

Thanks,

Dapeng

RE: PostGIS performance issue

[Regina Obe]

Possible or one of the dependencies is different, or planner differences such 
as the CTE changes that came in PostgreSQL 12, but hard to tell given the 
information you have provided

Or you didn’t reindex your data after the upgrade to a newer version of 
PostgreSQL and such a reindex was required.

 

I have had to change some of my CTEs queries after upgrading to PostgreSQL 12+ 
to get back to same speed or better as prior runs for example.

 

First of all?

 

What does the below return now (and if you can remember the key bits like GEOS, 
PROJ, PostgreSQL Version what did you have before)

 

SELECT version(),  postgis_full_version();

 

 

Two,  If you can provide the query you are trying to run,  that would help, as 
we may see a pattern there.

 

 

 

From: Shaozhong SHI  
Sent: Thursday, February 22, 2024 9:23 AM
To: PostGIS Users Discussion 
Subject: PostGIS performance issue

 

My system has recently been upgraded from Linux 7 to Linux 8.

There are performance issues with PostGIS 

"A process used to take 3.9 hours.  Now, it has taken more than 2 days and yet 
it has not completed running.  No errors have been flagged up."

 

Is possible the PostGIS not configured properly?

 

Regards,

 

David

RE: GDAL dependencies during PostGIS upgrade

[Regina Obe]

Removing shouldn’t be a problem as I never install those. I suspect it contains 
the commandline tools, not something postgresql needs to run.  Those probably 
get installed in the system bin instead of the postgresql bin, which is why you 
can have only one of them.

 

From: Nikhil Shetty  
Sent: Tuesday, February 20, 2024 11:33 PM
To: Regina Obe 
Cc: PostGIS Users Discussion 
Subject: Re: GDAL dependencies during PostGIS upgrade

 

Hi,

 

I tired installing the single package but still the same error

 

yum install -y postgis33_15-client-3.3.5-1PGDG.rhel8.x86_64.rpm
Last metadata expiration check: 0:01:18 ago on Wed 21 Feb 2024 02:51:19 AM UTC.
Dependencies resolved.
==
 PackageArchitecture
  Version   
Repository   Size
==
Installing:
 postgis33_15-clientx86_64  
  3.3.5-1PGDG.rhel8 
@commandline289 k
Transaction Summary
==
Install  1 Package
Total size: 289 k
Installed size: 1.4 M
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Error: Transaction test error:
  file /usr/lib/.build-id/b6/89201f4430759c8544cd168dc6ba3e24f7d912 from 
install of postgis33_15-client-3.3.5-1PGDG.rhel8.x86_64 conflicts with file 
from package postgis33_13-client-3.3.5-1PGDG.rhel8.x86_64

 

Alternatively just remove the postgis_33_13_client

-- Will this create a problem with the existing installation of PostGIS?.  I am 
asking because we are just installing the new packages for now and we will do 
the actual postgres upgrade during a downtime window

 

Thanks,

Nikhil

 

On Tue, Feb 20, 2024 at 11:30 PM Regina Obe mailto:l...@pcorp.us> > wrote:

I’m guessing you can’t have both clients installed.  Not sure what is in the 
client package. 

 

What if you don’t do 

 

yum install -y postgis_33_15*

 

But instead just 

 

yum install -y postgis_33_15

 

I’m guessing whatever that client is, they probably get installed in the same 
place, so you only need one of them.

 

Alternatively just remove the postgis_33_13_client

 

From: Nikhil Shetty mailto:nikhil.db...@gmail.com> > 
Sent: Tuesday, February 20, 2024 12:07 PM
To: Regina Obe mailto:l...@pcorp.us> >
Cc: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: Re: GDAL dependencies during PostGIS upgrade

 

Hi Regina,

 

I upgraded the existing PostGIS 3.1.2 to 3.3.5 on PG13 but now when I am trying 
to install PostGIS 3.3.5 for PG15 , I am getting below error

 

Error: Transaction test error:

  file /usr/lib/.build-id/b6/89201f4430759c8544cd168dc6ba3e24f7d912 from 
install of postgis33_15-client-3.3.5-1PGDG.rhel8.x86_64 conflicts with file 
from package postgis33_13-client-3.3.5-1PGDG.rhel8.x86_64

 

Do you know how we can resolve this ?

 

 

Complete install o/p

 

yum install -y postgis33_15*
Last metadata expiration check: 0:01:21 ago on Tue 20 Feb 2024 05:01:03 PM UTC.
Dependencies resolved.
==
 PackageArchitecture
  Version   
Repository   Size
==
Installing:
 postgis33_15   x86_64  
  3.3.5-1PGDG.rhel8 
@commandline4.8 M
 postgis33_15-clientx86_64  
  3.3.5-1PGDG.rhel8 
@commandline 

RE: GDAL dependencies during PostGIS upgrade

[Regina Obe]

I’m guessing you can’t have both clients installed.  Not sure what is in the 
client package. 

 

What if you don’t do 

 

yum install -y postgis_33_15*

 

But instead just 

 

yum install -y postgis_33_15

 

I’m guessing whatever that client is, they probably get installed in the same 
place, so you only need one of them.

 

Alternatively just remove the postgis_33_13_client

 

From: Nikhil Shetty  
Sent: Tuesday, February 20, 2024 12:07 PM
To: Regina Obe 
Cc: PostGIS Users Discussion 
Subject: Re: GDAL dependencies during PostGIS upgrade

 

Hi Regina,

 

I upgraded the existing PostGIS 3.1.2 to 3.3.5 on PG13 but now when I am trying 
to install PostGIS 3.3.5 for PG15 , I am getting below error

 

Error: Transaction test error:

  file /usr/lib/.build-id/b6/89201f4430759c8544cd168dc6ba3e24f7d912 from 
install of postgis33_15-client-3.3.5-1PGDG.rhel8.x86_64 conflicts with file 
from package postgis33_13-client-3.3.5-1PGDG.rhel8.x86_64

 

Do you know how we can resolve this ?

 

 

Complete install o/p

 

yum install -y postgis33_15*
Last metadata expiration check: 0:01:21 ago on Tue 20 Feb 2024 05:01:03 PM UTC.
Dependencies resolved.
==
 PackageArchitecture
  Version   
Repository   Size
==
Installing:
 postgis33_15   x86_64  
  3.3.5-1PGDG.rhel8 
@commandline4.8 M
 postgis33_15-clientx86_64  
  3.3.5-1PGDG.rhel8 
@commandline289 k
 postgis33_15-devel x86_64  
  3.3.5-1PGDG.rhel8 
@commandline8.3 k
 postgis33_15-utils x86_64  
  3.3.5-1PGDG.rhel8 
@commandline 44 k
Transaction Summary
==
Install  4 Packages
Total size: 5.1 M
Installed size: 45 M
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Error: Transaction test error:
  file /usr/lib/.build-id/b6/89201f4430759c8544cd168dc6ba3e24f7d912 from 
install of postgis33_15-client-3.3.5-1PGDG.rhel8.x86_64 conflicts with file 
from package postgis33_13-client-3.3.5-1PGDG.rhel8.x86_64

 

Thanks,

Nikhil

 

On Fri, Feb 16, 2024 at 11:16 AM Nikhil Shetty mailto:nikhil.db...@gmail.com> > wrote:

Hi Regina,

 

We are upgrading from PG 13 3.1.2  to  PG 15 3.3.5

 

I forget if pg_upgrade checks the old .so to see if it can still load it or if 
it only checks if the new .so it is expecting in the new cluster is still 
available.

pg_upgrade is checking in the PG13 lib whether the .so file exists or not. 

 

If it does check, I’d try upgrading to a newer PostGIS on your older cluster, 
doesn’t need to be the same, just one where the gdals are compatible.

We might have to go with this approach because it checks the .so file in old 
version

 

 

Error:

pg_dump: error: query failed: ERROR:  could not access file 
"$libdir/postgis-3": No such file or directory

pg_dump: detail: Query was: EXECUTE dumpFunc('23873')

 

 

Missing .so files:

 

-0-root@host:/usr/pgsql-13/lib # ls -l /usr/pgsql-13/lib/postgis*

ls: cannot access '/usr/pgsql-13/lib/postgis*': No such file or directory

 

-2-root@host:/usr/pgsql-13/lib # ls -l /usr/pgsql-15/lib/postgis*

/usr/pgsql-15/lib/postgis-3.so

/usr/pgsql-15/lib/postgis_raster-3.so

/usr/pgsql-15/lib/postgis_sfcgal-3.so

/usr/pgsql-15/lib/postgis_topology-3.so

 

Thanks,

Nikhil

 

On Fri, Feb 16, 2024 at 10:53 AM Regina Obe mailto:l...@pcorp.us> > wrote:

Nikhil,

 

Which version of PostgreSQL are you upgrading from.

 

I forget if pg_upgrade checks the old .so to see if it can still load it or if 
it only checks if the new .so it is expecting in the new cluster is still 
available.

 

If it doesn’

RE: GDAL dependencies during PostGIS upgrade

[Regina Obe]

Nikhil,

 

Which version of PostgreSQL are you upgrading from.

 

I forget if pg_upgrade checks the old .so to see if it can still load it or if 
it only checks if the new .so it is expecting in the new cluster is still 
available.

 

If it doesn’t check the old to try to load the lib, dropping postgis-3.1.2 
should work just fine.

Since 3.0, the .so file didn’t change, so pg_upgrading from say a PG 13 3.1.2 
to a PG 15 3.3.5 should work just fine.

 

If it does check, I’d try upgrading to a newer PostGIS on your older cluster, 
doesn’t need to be the same, just one where the gdals are compatible.

 

From: Nikhil Shetty  
Sent: Thursday, February 15, 2024 11:37 PM
To: PostGIS Users Discussion 
Subject: GDAL dependencies during PostGIS upgrade

 

Hi Team,

 

We are planning to upgrade postgres to 15 and postgis from 3.1.2 to 3.3.5.

 

While installing postgis 3.3.5 we found some dependency issues with gdal36, we 
were able to workaround by removing postgis 3.1.2 and gdal32 packages but for 
postgres upgrade we need both postgis (3.1.2 and 3.3.5) packages to be present.

 

Below is the issue when installing gdal36 which is required for postgis 3.3.5

 

-0-root@:/tmp # yum install gdal36-libs-3.6.4-6PGDG.rhel8.x86_64.rpm
Last metadata expiration check: 0:00:58 ago on Wed 31 Jan 2024 02:01:35 PM UTC.
Error: 
Problem: problem with installed package gdal32-libs-3.2.3-1.rhel8.x86_64
- package gdal32-libs-3.2.3-1.rhel8.x86_64 requires libarmadillo.so.9()(64bit), 
but none of the providers can be installed
- package gdal32-libs-3.2.2-13.rhel8.x86_64 requires 
libarmadillo.so.9()(64bit), but none of the providers can be installed
- cannot install both armadillo-12.6.6-1.el8.x86_64 and 
armadillo-9.700.2-1.el8.x86_64
- cannot install both armadillo-9.700.2-1.el8.x86_64 and 
armadillo-12.6.6-1.el8.x86_64
- package gdal36-libs-3.6.4-6PGDG.rhel8.x86_64 requires 
libarmadillo.so.12()(64bit), but none of the providers can be installed
- conflicting requests
(try to add '—allowerasing' to command line to replace conflicting packages or 
'—skip-broken' to skip uninstallable packages or '—nobest' to use not only best 
candidate packages)

 

 

Thanks,

Nikhil 

RE: pgr_alphashape is returning a geometry collection?

[Regina Obe]

Can you give us an example you are getting this for?  It should be returning a 
polygon.

 

From: Alexandre Neto  
Sent: Monday, February 5, 2024 7:14 AM
To: PostGIS Users Discussion 
Subject: pgr_alphashape is returning a geometry collection?

 

I am trying to use pgr_alphashape to convert a set of nodes into a polygon. The 
documentation description says:

 

Returns the polygon part of an alpha shape.

 

But instead I am getting a geometry_collection instead. I can solve it with 
st_CollectionExtract, but I m wondering if this is the expected behaviour.

 

Alexandre Neto

 

Sent with  

 Shortwave 

RE: Postgres segfaults on raster query

[Regina Obe]

> This seems… odd to me? In what context is putting postgis into the preload a
> requirement?
> 
> P
> 
> > On Jan 18, 2024, at 8:20 PM, Scott  wrote:
> >
> > Bam!
> >
> > That was it. Thanks Regina, you rock!
> >
> > On 1/18/24 20:16, Regina Obe wrote:
> >> ALTER SYSTEM  SET shared_preload_libraries = 'postgis-3';

The only time I've seen it be a requirement is with mobilitydb, where they are 
relying on the postgis-3 library to be already loaded and are dynamically 
linking to it.
I recall I used to see reference to the postgis-3 when I'd do

ldd /usr/lib/postgresql/16/lib/libMobilityDB-1.1.so
When it's not preloaded, mobilitydb crashes in a similar fashion which is why I 
thought about this.
Though I'm not seeing reference to that anymore though (or maybe I only saw is 
on windows), but it still relies on postgis-3 to load.

So when Scott said it works if he runs postgis_full_version() before hand, got 
me thinking, it must be relying on postgis already loaded and 
postgis_full_version() loads postgis lib first.

But for postgis_raster that should not be required, since postgis_raster has 
the liblwgeom baked into it so it never links directly to postgis-3 or at least 
shouldn't be.

@Scott,  

Can you run an ldd check on your postgis_raster-3?

If you don't know where it is

pg_config | grep PKGLIBDIR

Should tell you the folder

Mine looks like below -- verify you don't have reference to postgis-3.so

ldd /usr/lib/postgresql/16/lib/postgis_raster-3.so

linux-vdso.so.1 (0x7ffd7efda000)
libgdal.so.34 => /lib/x86_64-linux-gnu/libgdal.so.34 
(0x7f73f3b59000)
libgeos_c.so.1 => /usr/local/lib/libgeos_c.so.1 (0x7f73f3b0c000)
libproj.so.25 => /lib/x86_64-linux-gnu/libproj.so.25 
(0x7f73f370b000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x7f73f362c000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x7f73f344a000)
libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x7f73f342b000)
libcurl-gnutls.so.4 => /lib/x86_64-linux-gnu/libcurl-gnutls.so.4 
(0x7f73f336e000)
libodbc.so.2 => /lib/x86_64-linux-gnu/libodbc.so.2 (0x7f73f32fd000)
libodbcinst.so.2 => /lib/x86_64-linux-gnu/libodbcinst.so.2 
(0x7f73f32e5000)
libxml2.so.2 => /lib/x86_64-linux-gnu/libxml2.so.2 (0x7f73f3133000)
libcrypto.so.3 => /lib/x86_64-linux-gnu/libcrypto.so.3 
(0x7f73f2cac000)
liblzma.so.5 => /lib/x86_64-linux-gnu/liblzma.so.5 (0x7f73f2c7c000)
libdeflate.so.0 => /lib/x86_64-linux-gnu/libdeflate.so.0 
(0x7f73f2c66000)
liblz4.so.1 => /lib/x86_64-linux-gnu/liblz4.so.1 (0x7f73f2c4)
libblosc.so.1 => /lib/x86_64-linux-gnu/libblosc.so.1 
(0x7f73f2c3)
libarmadillo.so.12 => /lib/libarmadillo.so.12 (0x7f73f2c1e000)
libqhull_r.so.8.0 => /lib/x86_64-linux-gnu/libqhull_r.so.8.0 
(0x7f73f2ba8000)
libxerces-c-3.2.so => /lib/x86_64-linux-gnu/libxerces-c-3.2.so 
(0x7f73f280d000)
libjpeg.so.62 => /lib/x86_64-linux-gnu/libjpeg.so.62 
(0x7f73f2778000)
libtiff.so.6 => /lib/x86_64-linux-gnu/libtiff.so.6 (0x7f73f26eb000)
libgeotiff.so.5 => /lib/x86_64-linux-gnu/libgeotiff.so.5 
(0x7f73f26b5000)
libheif.so.1 => /lib/x86_64-linux-gnu/libheif.so.1 (0x7f73f25dc000)
libpng16.so.16 => /lib/x86_64-linux-gnu/libpng16.so.16 
(0x7f73f25a6000)
libzstd.so.1 => /lib/x86_64-linux-gnu/libzstd.so.1 (0x7f73f24e5000)
libaec.so.0 => /lib/x86_64-linux-gnu/libaec.so.0 (0x7f73f24d9000)
libpoppler.so.126 => /lib/x86_64-linux-gnu/libpoppler.so.126 
(0x7f73f2144000)
libgif.so.7 => /lib/x86_64-linux-gnu/libgif.so.7 (0x7f73f2139000)
libnetcdf.so.19 => /lib/x86_64-linux-gnu/libnetcdf.so.19 
(0x7f73f1f3d000)
libcfitsio.so.10 => /lib/x86_64-linux-gnu/libcfitsio.so.10 
(0x7f73f1c21000)
libhdf5_serial.so.103 => /lib/x86_64-linux-gnu/libhdf5_serial.so.103 
(0x7f73f1868000)
libwebp.so.7 => /lib/x86_64-linux-gnu/libwebp.so.7 (0x7f73f17e9000)
libsqlite3.so.0 => /lib/x86_64-linux-gnu/libsqlite3.so.0 
(0x7f73f1679000)
libpq.so.5 => /lib/x86_64-linux-gnu/libpq.so.5 (0x7f73f1622000)
libopenjp2.so.7 => /lib/x86_64-linux-gnu/libopenjp2.so.7 
(0x7f73f15be000)
libkmlbase.so.1 => /lib/x86_64-linux-gnu/libkmlbase.so.1 
(0x7f73f15a1000)
libkmldom.so.1 => /lib/x86_64-linux-gnu/libkmldom.so.1 
(0x7f73f14ff000)
libkmlengine.so.1 => /lib/x86_64-linux-gnu/libkmlengine.so.1 
(0x7f73f14c5000)
libfyba.so.0 => /lib/x86_64-linux-gnu/libfyba.so.0 (0x7f73f146d000)
libpcre2-8.so.0 => /lib/x86_64-linux-gnu/libpcre2-8.so.0 
(0x7f73f13d2000)
libspatialite.so.8 

RE: Postgres segfaults on raster query

[Regina Obe]

> Here's what's happening:
> 
> psql -d mydb
> 
> select rid from rastertable where rid = 1;
> 
> Psql connection drops, postgres segfaults and restarts.
> 
> BUT, if I do a query such as this FIRST:
> 
> select postgis_full_version();
> 
> Then do other raster queries, it works fine. I suspect I broke something but 
> I'm
> not sure where to start.
> 
> Thanks for any help.
> Scott

What other extensions do you have loaded.

Also what does your output return for

SELECT postgis_full_version();

Only thing I can think of is calling postgis_full_version() is causing the 
raster and postgis extensions to preload,

I'd check to see if you have anything in 

SHOW shared_preload_libraries;


You might actually want to try doing something like:

ALTER SYSTEM  SET shared_preload_libraries = 'postgis-3';

And then restart your service to see if it has the same effect as the SELECT 
postgis_full_version();

RE: Current postgis builds broken???

[Regina Obe]

Looks like you might be missing epel-release

 

Did you already do 

 

dnf -y install epel-release

 

 

as detailed here -
https://postgis.net/documentation/getting_started/install_red_hat/

 

 

You might get more answers on the
https://www.postgresql.org/list/pgsql-pkg-yum/  mailing list.

 

Hope that helps,

Regina

 

From: Scott Pasnikowski  
Sent: Friday, January 12, 2024 2:24 PM
To: 'PostGIS Users Discussion' 
Subject: Current postgis builds broken???

 

 

Yesterday I was doing a fresh install of our server package and quickly
recieved error messages that postgis wont install...

 

This appears to have happened late afternoon (PST).

 

Does anyone know whats up?

 

 

 

[rocky@sai-int-test-cortex-airflow-test4 singlenode]$ sudo dnf -y install
postgis34_15

Last metadata expiration check: 0:02:48 ago on Fri 12 Jan 2024 07:15:57 PM
UTC.

Error:

 Problem: package postgis34_15-3.4.1-1PGDG.rhel8.x86_64 from pgdg15 requires
gdal36-libs >= 3.6.3, but none of the providers can be installed

  - package postgis34_15-3.4.1-1PGDG.rhel8.x86_64 from pgdg15 requires
libgdal.so.32()(64bit), but none of the providers can be installed

  - cannot install the best candidate for the job

  - nothing provides libarmadillo.so.10()(64bit) needed by
gdal36-libs-3.6.4-4PGDG.rhel8.x86_64 from pgdg-common

  - nothing provides libarmadillo.so.10()(64bit) needed by
gdal36-libs-3.6.4-5PGDG.rhel8.x86_64 from pgdg-common

(try to add '--skip-broken' to skip uninstallable packages or '--nobest' to
use not only best candidate packages)

[postgis-users] OSGeo Discourse - Mirroring of the mailing lists and eventual move to discourse

[Regina Obe via postgis-users]

As some of you may have noticed, we have set up discourse on OSGeo infra.

https://discourse.osgeo.org/

The initial plan is just import the lists and mirror the mailing lists, so
we have a more searchable archive of results (similar to what the now
defunct Nabble provided which many people preferred over interacting with he
mailing lists).

Eventually we'd like to move the mailing lists to discourse. The results
will be

1) People can still interact with discourse via email as if it were just a
mailing list, and subscribe to a whole list = category in discourse speak
As they do now with mailing lists
2) People have the option of only watching threads they are interested in =
topics in discourse terminology
3) People can login and do posts instead of email if they prefer
4) Easier cross-pollination between OSGeo mailing lists.

Right now our discourse instance supports logging in using an OSGeo LDAP
account, GitHub Account, or direct registration on our discourse.

I know this is a very divisive topic, so I'd like to hear people's inputs.

What do you like / not like about the plan?

I'll start with mine.  

* I know a lot of folks don't want to be on mailing lists, they just want to
ask a question and just monitor answers to their questions or if their name
is mentioned.
* I do not enjoy interacting with mailing lists, because I don't care much
for email so I only subscribe to the mailing lists I have deep interest in.
To me email is something I can't easily shut off.  I much prefer going to a
site when I have a spare 15 minutes here and there. Which is why I try to
turn off all notifications from all sites except possibly digest.  And
Sandro -- yah I know I can configure my email to be less invasive, but I
honestly don't care too.

Paul had mentioned he'd be okay with the idea if we did not host discourse
ourselves, cause he thinks OSGeo shouldn't be managing any of its own
infrastructure.
I still feel hosting our own is the way to go, cause if no one wants to move
to discourse, there is no point in paying hosting fees for it, and moving a
discourse instance to a hosted discourse instance, is still way cheaper than
piecemeal migration (which cost $250/hr for migration in addition to the
$300 or more /mth of hosting).

Thanks,
Regina

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

Re: [postgis-users] Operator does not exist: <<#>>

[Regina Obe via postgis-users]

See answers below.  No trouble at all.  Thanks for catching all these loose 
ends in the docs.

 

Much appreciated,

Regina

 

From: Dapeng Wang  
Sent: Tuesday, December 26, 2023 10:26 PM
To: Regina Obe 
Cc: PostGIS Users Discussion 
Subject: Re: [postgis-users] Operator does not exist: <<#>>

 

I get it, <<#>> is deprecated.
<<->>Supports 2D and 3D operations, shortest distance.
I also verified via SQL that <<->> is indeed the shortest distance.

There are 2 questions here,
1. The centroid algorithm disappears.

[Regina Obe]   Yes this disappears, and is replaced with true distance
2. How to calculate bounding box distance used in 3D? Initially use <<#>>.

[Regina Obe]  You’d have to cast, if you really wanted to, using geom::box3d, 
but in practice I don’t think it’s that useful to have bounding box distance.  
We had it because that was all we could get out of KNN at the time.

 

(Please don't increase the team's development time because of my question, I 
have no real application, just learning).

SELECT ST_GeomFromText('MULTIPOINT(1 3 2, 0 0 0)') <<->> 
ST_GeomFromText('LINESTRING(1 1 1, 5 5 5)') returns 1.4142135623730951
SELECT ST_3DDistance(ST_GeomFromText('MULTIPOINT(1 3 2, 0 0 0)'), 
ST_GeomFromText('LINESTRING(0 0 0, 5 5 5)'))
Haha, the LINESTRING data of the ST_3DDistance function should be LINESTRING(1 
1 1, 5 5 5), which is unintentional.

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

Re: [postgis-users] Operator does not exist: <<#>>

[Regina Obe via postgis-users]

Just remembered <<#>> is for ND boxes, and we did take it out, recalling a 
convo pramsey had a long time ago.

 

But I think the comment still remains the same

 

This one - 
https://postgis.net/docs/manual-dev/en/geometry_distance_centroid_nd.html  used 
to just do centroid of the boxes and the other one was for distance of the 
boxes.

When we changed to true KNN, distance would always be better so we scrapped all 
the box distance variants and the centroid ones then all became true distance.  
I think the comment about <<->> is now wrong and it should be true distance

 

e.g

 

 

SELECT ST_GeomFromText('MULTIPOINT(1 3 2, 0 0 0)') <<->> 
ST_GeomFromText('LINESTRING(1 1 1, 5 5 5)')  returns 1.4142135623730951

 

Which is the same answer as 

 

SELECT ST_3DDistance(ST_GeomFromText('MULTIPOINT(1 3 2, 0 0 0)'), 
ST_GeomFromText('LINESTRING(0 0 0, 5 5 5)'))

 

From: Regina Obe  
Sent: Tuesday, December 26, 2023 2:46 PM
To: 'PostGIS Users Discussion' 
Cc: 'Dapeng Wang' 
Subject: RE: [postgis-users] Operator does not exist: <<#>>

 

I concur doesn’t seem to exist.  I’m trying to think how that would ever have 
been different from <#>.

 

I recall there was a time before we had true KNN for geometry that one was for 
bounding box distance and one was for the centroid bounding box distance.

 

Anyway I’ll remove from the docs.  Thanks for catching.

 

Regina

 

From: postgis-users mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Dapeng Wang via 
postgis-users
Sent: Monday, December 25, 2023 10:31 PM
To: postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> 
Cc: Dapeng Wang mailto:wangdapeng20191...@gmail.com> >
Subject: [postgis-users] Operator does not exist: <<#>>

 

Hello everyone,

SELECT st_distance(geom, 'SRID=4326;POINT(1 2)'::geometry)
FROM ne_110m_admin_0_countries
ORDER BY (ST_GeomFromText('POINT(1 2)') <<#>> geom)
operator does not exist: geometry <<#>> geometry
Reference https://postgis.net/docs/manual-dev/en/geometry_distance_box_nd.html


Checked the postgis.sql.in <http://postgis.sql.in>  source code definition and 
did not find this operator. Has it been abandoned?

Thanks,
Dapeng

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

Re: [postgis-users] Operator does not exist: <<#>>

[Regina Obe via postgis-users]

I concur doesn’t seem to exist.  I’m trying to think how that would ever have 
been different from <#>.

 

I recall there was a time before we had true KNN for geometry that one was for 
bounding box distance and one was for the centroid bounding box distance.

 

Anyway I’ll remove from the docs.  Thanks for catching.

 

Regina

 

From: postgis-users  On Behalf Of Dapeng 
Wang via postgis-users
Sent: Monday, December 25, 2023 10:31 PM
To: postgis-users@lists.osgeo.org
Cc: Dapeng Wang 
Subject: [postgis-users] Operator does not exist: <<#>>

 

Hello everyone,

SELECT st_distance(geom, 'SRID=4326;POINT(1 2)'::geometry)
FROM ne_110m_admin_0_countries
ORDER BY (ST_GeomFromText('POINT(1 2)') <<#>> geom)
operator does not exist: geometry <<#>> geometry
Reference https://postgis.net/docs/manual-dev/en/geometry_distance_box_nd.html


Checked the postgis.sql.in   source code definition and 
did not find this operator. Has it been abandoned?

Thanks,
Dapeng

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

Re: [postgis-users] Sample SQL statement showing error

[Regina Obe via postgis-users]

I’ve ticketed the issue here

 

https://trac.osgeo.org/postgis/ticket/5645

 

 

From: Regina Obe  
Sent: Friday, December 15, 2023 5:47 PM
To: 'PostGIS Users Discussion' ; 'Sandro 
Santilli' 
Cc: 'Dapeng Wang' 
Subject: RE: [postgis-users] Sample SQL statement showing error

 

Ah sorry about that.

 

At  <mailto:s...@kbt.io> @Sandro Santilli I think this is caused by the logic 
we have maybe to try to do something with XML entities

 

I had put in the PostgreSQL   touched => true

 

To allow setting defaults while skipping other defaults.

 

It seems for the translations it converted those to 

 

=

> 

 

Is this our doing or something in weblate you think?  I didn’t check out pot 
file to see if it has a similar issue.

 

Thanks,

Regina

 

From: postgis-users mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Dapeng Wang via 
postgis-users
Sent: Wednesday, December 13, 2023 1:45 AM
To: postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> 
Cc: Dapeng Wang mailto:wangdapeng20191...@gmail.com> >
Subject: [postgis-users] Sample SQL statement showing error

 

Hello everyone,

 

https://postgis.net/docs/manual-dev/RT_ST_Clip.html
https://postgis.net/docs/manual-dev/zh_Hans/RT_ST_Clip.html

The sample sql statements in English and Chinese are displayed differently.

Examples: Comparing selecting all touched vs. not all touched.

One is "=>", the other is "= >",direct copying results in a runtime error.

This is true for all languages except "en".

 

Thanks,

Dapeng

 

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

Re: [postgis-users] Sample SQL statement showing error

[Regina Obe via postgis-users]

Ah sorry about that.

 

At   @Sandro Santilli I think this is caused by the logic 
we have maybe to try to do something with XML entities

 

I had put in the PostgreSQL   touched => true

 

To allow setting defaults while skipping other defaults.

 

It seems for the translations it converted those to 

 

=

> 

 

Is this our doing or something in weblate you think?  I didn’t check out pot 
file to see if it has a similar issue.

 

Thanks,

Regina

 

From: postgis-users  On Behalf Of Dapeng 
Wang via postgis-users
Sent: Wednesday, December 13, 2023 1:45 AM
To: postgis-users@lists.osgeo.org
Cc: Dapeng Wang 
Subject: [postgis-users] Sample SQL statement showing error

 

Hello everyone,

 

https://postgis.net/docs/manual-dev/RT_ST_Clip.html
https://postgis.net/docs/manual-dev/zh_Hans/RT_ST_Clip.html

The sample sql statements in English and Chinese are displayed differently.

Examples: Comparing selecting all touched vs. not all touched.

One is "=>", the other is "= >",direct copying results in a runtime error.

This is true for all languages except "en".

 

Thanks,

Dapeng

 

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

Re: [postgis-users] out db: not able to query the raster

[Regina Obe via postgis-users]

Yah the data directory is less likely to have screwed up permissions that the 
postgres account can’t access.  So I would go with Jorge’s idea.

 

That said I would think that having permission with r r r  should do the trick 
and the fact you can get a directory listing suggests the postgres process can 
at least read the list of files.

 

The only thing I can think of is maybe something is locking those files in some 
write mode preventing any other process from reading.  I’m assuming you are 
done with loading so shouldn’t be that.

 

To rule out postgis raster is at fault, maybe try restarting the service and do 
that pg_stat_file check again immediately after restarting.

 

Regarding your question about “Why is it offline? It's there.”

 

It’s offline because it’s not in your database. Out-db / offline are equivalent 
terminology in postgis raster.  We should probably change that terminology to 
out-db cause offline means not reachable in other contexts and just confusing.  
I think calling it out-db is a lot clearer.

 

 

From: postgis-users  On Behalf Of Jorge 
Gustavo Rocha via postgis-users
Sent: Wednesday, December 6, 2023 5:25 PM
To: postgis-users@lists.osgeo.org
Cc: Jorge Gustavo Rocha 
Subject: Re: [postgis-users] out db: not able to query the raster

 

Hi Andrea,

I always put the raster somewhere below Postgresql data_directory.

psql
show data_directory;

I hope it helps.

Saluti,

Jorge

On 04/12/23 22:23, andy via postgis-users wrote:

Hi, 

if I run "SELECT (ST_BandMetaData(rast)).* FROM rasters_outdb LIMIT 1;", in the 
field path I have "/directory_docker/ou_s6_01_01_stack.tif"

 

If I run "ls -l /directory_docker/ou_s6_01_01_stack.tif" I have

-rwxr--r-- 1 1000 1000 2100227 Dec  4 08:32 
/directory_docker/ou_s6_01_01_stack.tif

 

But when I run "SELECT ST_Value(rast, 1, 1) FROM rasters_outdb;" I get 

ERROR:  rt_band_load_offline_data: Cannot open offline raster: 
/directory_docker/ou_s6_01_01_stack.tif

 

Why is it offline? It's there.

I'm using postgis/postgis:16-master

 

I have created the sql file to load it in postgres, running

 

raster2pgsql -I -C -M -F -t auto -R /directory_docker/ou_s6_01_01_stack.tif 
public.rasters_outdb

 

How to read the pixel values using a PG SQL query?

 

Thank you


 

-- 

___

 

Andrea Borruso
website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E, EPSG:4326
___

"cercare e saper riconoscere chi e cosa,
 in mezzo all’inferno, non è inferno, 
e farlo durare, e dargli spazio"

Italo Calvino





___
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] out db: not able to query the raster

[Regina Obe via postgis-users]

Are you running postgres inside the docker container or are you using your own?

 

I wasn’t clear if you are just using the raster2pgsql from docker or you are 
using the postgresql as well.

 

It’s possible inside the container it doesn’t know the path as such.

 

Try this query in your postgresql, to see if your postgresql can read that path

 

SELECT *

FROM pg_ls_dir('/directory_docker');

 

And can read the file

 

SELECT *

FROM pg_stat_file('/directory_docker/ou_s6_01_01_stack.tif ');

 

 

From: postgis-users  On Behalf Of andy 
via postgis-users
Sent: Monday, December 4, 2023 5:24 PM
To: PostGIS Users Discussion 
Cc: andy 
Subject: [postgis-users] out db: not able to query the raster

 

Hi,

if I run "SELECT (ST_BandMetaData(rast)).* FROM rasters_outdb LIMIT 1;", in the 
field path I have "/directory_docker/ou_s6_01_01_stack.tif"

 

If I run "ls -l /directory_docker/ou_s6_01_01_stack.tif" I have

-rwxr--r-- 1 1000 1000 2100227 Dec  4 08:32 
/directory_docker/ou_s6_01_01_stack.tif

 

But when I run "SELECT ST_Value(rast, 1, 1) FROM rasters_outdb;" I get 

ERROR:  rt_band_load_offline_data: Cannot open offline raster: 
/directory_docker/ou_s6_01_01_stack.tif

 

Why is it offline? It's there.

I'm using postgis/postgis:16-master

 

I have created the sql file to load it in postgres, running

 

raster2pgsql -I -C -M -F -t auto -R /directory_docker/ou_s6_01_01_stack.tif 
public.rasters_outdb

 

How to read the pixel values using a PG SQL query?

 

Thank you


 

-- 

___

 

Andrea Borruso
website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E, EPSG:4326
___

"cercare e saper riconoscere chi e cosa,
 in mezzo all’inferno, non è inferno, 
e farlo durare, e dargli spazio"

Italo Calvino

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

Re: [postgis-users] raster2pgsql gives me segmentation fault

[Regina Obe via postgis-users]

What does SELECT version() return?

 

Also have you tried it with in-db. (without the -R switch).  Could be some sort 
of permission issue.  If it still breaks with in-db, that would rule out out-db 
as the culprit.

 

I know the out-db logic we have is still not that popular so much less tested 
than in-db.  If we can isolate it down a bit more to being an out-db or 
permission issue that would help.

 

 

 

From: andy  
Sent: Monday, December 4, 2023 10:08 AM
To: Regina Obe 
Cc: PostGIS Users Discussion 
Subject: Re: [postgis-users] raster2pgsql gives me segmentation fault

 

Thank you Regina.

 

I'm using "POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="130" 
GEOS="3.9.4-CAPI-1.14.4" PROJ="7.2.1" GDAL="GDAL 3.2.2, released 2021/03/05" 
LIBXML="2.9.10" LIBJSON="0.15" LIBPROTOBUF="1.4.1" WAGYU="0.5.0 (Internal)" 
TOPOLOGY RASTER"

 

-- 

___

 

Andrea Borruso
website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E, EPSG:4326
___

"cercare e saper riconoscere chi e cosa,
 in mezzo all’inferno, non è inferno, 
e farlo durare, e dargli spazio"

Italo Calvino

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

Re: [postgis-users] raster2pgsql gives me segmentation fault

[Regina Obe via postgis-users]

Which version of PostGIS and PostgreSQL are you running?

 

SELECT postgis_full_version(),  version();

 

Also is the data publicly accessible so we can test it out? 

If you can try it with https://github.com/postgis/docker-postgis  as Imre 
suggested, that would be great too.

 

I also assume you are not having issues with all tif files, and it’s this one 
specifically? 

 

From: postgis-users  On Behalf Of andy 
via postgis-users
Sent: Monday, December 4, 2023 7:52 AM
To: postgis-users@lists.osgeo.org
Cc: andy 
Subject: [postgis-users] raster2pgsql gives me segmentation fault

 

Hi,

I'm using RELEASE: 3.3.2 GDAL_VERSION=36 (4975da8) in debian.

 

When I run this command

 

raster2pgsql -I -C -M -F -t auto -R ou_s6_01_01_stack.tif public.rasters_outdb

 

I have in the stdout the first part without errors, but after about 80 records 
I have error.

Below are some output example lines.

 

These are the tif metadata: 
https://gist.github.com/aborruso/caa16e5eb5d5b4756035f68bee191ed4

 

What can the problem be? How to solve it?

 

Thank you 

 

INSERT INTO "public"."rasters_outdb" ("rast","filename") VALUES 
('010400392BA226FA7C743F392BA226FA7C74BFAE4A22FB20BF304098E315889EBA4240E611E8008A006F755F73365F30315F30315F737461636B2E746966008A016F755F73365F30315F30315F737461636B2E746966008A026F755F73365F30315F30315F737461636B2E746966008A036F755F73365F30315F30315F737461636B2E74696600'::raster,'ou_s6_01_01_stack.tif');
INSERT INTO "public"."rasters_outdb" ("rast","filename") VALUES 
('010400392BA226FA7C743F392BA226FA7C74BF626D8C9DF006324098E315889EBA4240E610C400E8008A006F755F73365F30315F30315F737461636B2E746966008A016F755F73365F30315F30315F737461636B2E746966008A026F755F73365F30315F30315F737461636B2E746966008A036F755F73365F30315F30315F737461636B2E74696600'::raster,'ou_s6_01_01_stack.tif');
CREATE INDEX ON "public"."rasters_outdb" USING gist (st_convexhull("rast"));
ANALYZE "public"."rasters_outdb";
SELECT 
AddRasterConstraints('public','rasters_outdb','rast',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE);
END;
VACUUM ANALYZE "public"."rasters_outdb";
[1]52988 segmentation fault  raster2pgsql -I -C -M -F -t auto -R 
ou_s6_01_01_stack.tif public.rasters_outd

 




 

-- 

___

 

Andrea Borruso
website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E, EPSG:4326
___

"cercare e saper riconoscere chi e cosa,
 in mezzo all’inferno, non è inferno, 
e farlo durare, e dargli spazio"

Italo Calvino

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

Re: [postgis-users] ST_DWithin slow if query is joined to another table, but fast if ST_DWithin cast to an integer

[Regina Obe via postgis-users]

I didn’t realize you are building the geography from longitude / latitude of 
your building.

Is there a reason you don’t have a geography column in your building table, 
instead of building it from scratch each time?

Right now you query can’t use a spatial index at all.

 

If you had the geography in your building table:

 

ALTER TABLE building ADD geog geography(POINT, 4326);

UPDATE building SET geog = ST_Point(longitude, latitude)::geography;

CREATE INDEX ix_building_geog ON building USING gist(geog);

 

SELECT  c.*

FROM buildings AS b INNER JOIN c ON b.id = c.building_id

WHERE ST_DWithin(b.geog, st_makepoint(-96.7804060, 33.2471770)::geography, 
5);

 

Then that should use a spatial index and be much faster

 

Now why one of your queries is running at 5000 ms vs. 35 ms, since I can’t see 
the TEST EXPRESSIOn  you are using, I’m not sure if those are equivalent.  I 
assume the test expression is true.

 

If for some reason, you can’t have a geography column in your building table, 
you could try to force the planners behavior with a subselect AS

 

FROM (SELECT *, ST_MakePoint(longitude, latitude) AS geog FROM building ) AS b 
INNER JOIN customer AS c ON b.id = c.building_id

 

 

Also make sure you have a primary key on your building.id  column and an index 
on your customer.building_id.  Your plans don’t appear to be using a 
building_id index either, which might be the correct thing to do, or could be 
cause you don’t have an index on those columns.

 

 

From: Cameron McCloud  
Sent: Tuesday, November 28, 2023 9:45 AM
To: Regina Obe 
Subject: Re: [postgis-users] ST_DWithin slow if query is joined to another 
table, but fast if ST_DWithin cast to an integer

 

This is what I get from that query:

 

IMMUTABLE PARALLEL SAFE STRICT COST 1 SUPPORT postgis_index_supportfn

 

Putting the ST_DWithin into the join gives the same result.

 

On Mon, Nov 27, 2023 at 5:15 PM Regina Obe mailto:l...@pcorp.us> > wrote:

I’m guessing it has to do with the costing we have on ST_DWithin.

I recall we increased that back recently because of some complaints but can’t 
recall the version.

 

What does your costing read from ST_DWithin(geography, geography, …) read?

 

Run this query to see the code:

SELECT pg_get_functiondef(oid)

FROM pg_proc

WHERE proname = 'st_dwithin' AND prosrc = 'geography_dwithin';

 

Mine reads

COST 5000

IMMUTABLE STRICT PARALLEL SAFE SUPPORT postgis_index_supportf

 

Also what happens if you switch your query to put the ST_Dwithin in the JOIN 
clause instead of the WHERE?

 

from building b join customer c ON (b.id <http://b.id>  = c.building_id  AND  
st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, 
st_makepoint(-96.7804060, 33.2471770)::geography, 5) );

 

From: postgis-users mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Cameron McCloud 
via postgis-users
Sent: Monday, November 27, 2023 8:32 AM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Cc: Cameron McCloud mailto:cameron.mccl...@gmail.com> >
Subject: Re: [postgis-users] ST_DWithin slow if query is joined to another 
table, but fast if ST_DWithin cast to an integer

 

Hi,

 

Just pinging this again to see if anyone has an idea of what's going on. >From 
the EXPLAIN output of the second query in the DBFiddle example it does seem 
that there is a cross-join going on:

 

 Rows Removed by Join Filter: 101705643

 

, even though there is an "ON" clause in the join.

 

Cam.

 

On Thu, Nov 9, 2023 at 10:27 AM Cameron McCloud mailto:cameron.mccl...@gmail.com> > wrote:

https://dbfiddle.uk/qJDW-DjP

 

 

On Thu, Nov 9, 2023 at 9:11 AM Cameron McCloud mailto:cameron.mccl...@gmail.com> > wrote:

Here's the EXPLAIN for the second query with the join and WHERE on ST_DWithin:

 

 Nested Loop  (cost=0.00..403093.00 rows=2 width=20)
   Join Filter: (b.id <http://b.id>  = c.building_id)
   ->  Seq Scan on customer c  (cost=0.00..231.00 rows=16000 width=4)
   ->  Materialize  (cost=0.00..402382.01 rows=2 width=20)
 ->  Seq Scan on building b  (cost=0.00..402382.00 rows=2 width=20)
   Filter: st_dwithin((st_makepoint((longitude)::double precision, 
(latitude)::double precision))::geography, 
'010120E6108AE6012CF23158C065A9F57EA39F4040'::geography, 
'5'::double precision, true)
 JIT:
   Functions: 7
   Options: Inlining false, Optimization false, Expressions true, Deforming true

 

Here's the EXPLAIN ANALYZE:

 Nested Loop  (cost=0.00..403093.00 rows=2 width=20) (actual 
time=29.391..5196.713 rows=6453 loops=1)
   Join Filter: (b.id <http://b.id>  = c.building_id)
   Rows Removed by Join Filter: 82424169
   ->  Seq Scan on customer c  (cost=0.00..231.00 rows=16000 width=4) (actual 
time=0.004..2.484 rows=16000 loops=1)
   ->  Materialize  (cost=0.00..402382.01 rows=2 width=20) (actual 
time=0.000..0.149 rows=5152 loops=16000)
   

Re: [postgis-users] ST_DWithin slow if query is joined to another table, but fast if ST_DWithin cast to an integer

[Regina Obe via postgis-users]

ches: 1  Memory Usage: 691kB
 ->  Seq Scan on customer c  (cost=0.00..231.00 rows=16000 width=4) 
(actual time=0.007..0.931 rows=16000 loops=1)
 Planning Time: 0.132 ms
 JIT:
   Functions: 12
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.311 ms, Inlining 0.000 ms, Optimization 0.184 ms, 
Emission 4.085 ms, Total 4.580 ms
 Execution Time: 34.239 ms

 

 

 

 

On Wed, Nov 8, 2023 at 6:10 PM Regina Obe mailto:l...@pcorp.us> 
> wrote:

First of all is that really your join clause?  You seem to be missing an ON

 

from building b

join customer c

 

 

Also please output

 

EXPLAIN 

 

And the 

 

EXPLAIN ANALYZE of each query

 

From: postgis-users mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Cameron McCloud 
via postgis-users
Sent: Wednesday, November 8, 2023 5:38 AM
To: postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> 
Cc: Cameron McCloud mailto:cameron.mccl...@gmail.com> >
Subject: Re: [postgis-users] ST_DWithin slow if query is joined to another 
table, but fast if ST_DWithin cast to an integer

 

Test code is here: 
https://github.com/cameronmccloud/postgis_slow_st_dwithin/blob/main/test.sql

 

On Wed, Nov 8, 2023 at 9:25 AM Cameron McCloud mailto:cameron.mccl...@gmail.com> > wrote:

Hi,

 

Postgis Version: 3.3.4

Postgres Version: 14.9

 

We have 2 tables, "building" with lat/long and "customer" with a FK to 
building. There's a 1:1 relationship between the two.

 

The test tables we're using have 16K rows each. Our production data has a lot 
more, but we could reproduce this on a smaller dataset.

 

We found some odd behaviour when using ST_DWITHIN in a WHERE clause but only 
when the "building" table is joined to the "customer" table.

 

We also found that converting the result of ST_DWITHIN to an integer (1/0 for 
true/false) and using the integer in the WHERE is fast.

 

-- This query works as expected and takes 60ms returning 6K rows.

select b.*

from building b

where

  st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, 
st_makepoint(-96.7804060, 33.2471770)::geography, 5);

 

-- This query is orders of magnitude slower - 3000ms, even though joining the 
two tables without the WHERE takes 30ms

select b.*

from building b

join customer c

where

  st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, 
st_makepoint(-96.7804060, 33.2471770)::geography, 5);

 

-- This query converts the result of ST_DWITHIN to an integer. It's fast and 
takes 80ms

select b.*

from building b

join customer c

where

  case st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, 
st_makepoint(-96.7804060, 33.2471770)::geography, 5)

when true then 1

else 0

  end = 1;

 

We have no idea why this is the case, but we're curious. In our production 
scenario using the "case...when" brought a query down from 6 minutes to 6 
seconds. We'd love to know why this might be and if there are other scenarios 
like this that could increase the performance of our Postgis queries.

 

Thanks,

 

Cam.

 

 

 

 

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

[postgis-users] PostGIS 3.4.1 Windows Bundle for PostGIS 3.4.1 for PostgreSQL 12-16

[Regina Obe via postgis-users]

The PostGIS 3.4.1 bundle windows installers and binaries are available now
on Application Stackbuilder as well as osgeo downloads.

Details at:
https://postgis.net/documentation/getting_started/install_windows/released_v
ersions/

Key highlights since PostGIS 3.4.1

1) pgRouting updated from 3.5.0 to 3.6.0
https://github.com/pgRouting/pgrouting/releases/tag/v3.6.0

2) MobilityDb updated from 1.1.0alpha to 1.1.0beta1   -
https://github.com/MobilityDB/MobilityDB/releases/tag/v1.1.0-beta1


3) PostGIS 3.4.1 -
https://git.osgeo.org/gitea/postgis/postgis/src/tag/3.4.1/NEWS - with GEOS
3.12.1 (updated from GEOS 3.12.0) and SFCGAL 1.5.0 (updated from 1.4.1)

Feel free to reply to this email if you run into install issues.

Thanks,
Regina


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

[postgis-users] Patch releases available for 3.4.1, 3.3.5, 3.2.6, 3.1.10, and 3.0.10

[Regina Obe via postgis-users]

Patch releases available for 3.4.1, 3.3.5, 3.2.6, 3.1.10, and 3.0.10

Details and download links can be found at:

https://postgis.net/2023/11/PostGIS-Patch-Releases/

Thanks,
PostGIS Development Team

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

Re: [postgis-users] Windows compile instructions

[Regina Obe via postgis-users]

Cool and I see they had a release 5 days ago.  A quick attempt at compile I 
wasn’t successful even using the 

 

make USE_HEALPIX=0

 

I get error:

In file included from src/sparse.c:136:

src/sparse.h:69:5: error: 'INT' redeclared as different kind of symbol

   69 | INT = 270, /* INT  */

  | ^~~

In file included from C:/ming64/mingw64/include/minwindef.h:163,

 from C:/ming64/mingw64/include/windef.h:9,

 from C:/ming64/mingw64/include/windows.h:69,

 from C:/ming64/mingw64/include/winsock2.h:23,

 from 
C:/ming64/projects/POSTGR~1/rel/pg16w64/include/server/port/win32_port.h:60,

 from 
C:/ming64/projects/POSTGR~1/rel/pg16w64/include/server/port.h:24,

 from 
C:/ming64/projects/POSTGR~1/rel/pg16w64/include/server/c.h:1375,

 from 
C:/ming64/projects/POSTGR~1/rel/pg16w64/include/server/postgres.h:45,

 from src/sparse.y:4:

C:/ming64/mingw64/include/winnt.h:299:15: note: previous declaration of 'INT' 
with type 'INT' {aka 'int'}

  299 |   typedef int INT;

  |   ^~~

src/sparse.h:70:5: error: 'FLOAT' redeclared as different kind of symbol

   70 | FLOAT = 271,   /* FLOAT  */

 

 

I didn’t look too deeply to see how to fix the issue, or if it’s just something 
about my setup. 

 

Normally these kinds of issues need some conditional handling for mingw64 
and/or windows.  PostGIS has a couple of special handlings.

 

If you have any luck let me know.  I plan to take a look at it later either way.

 

Even though it doesn’t rely on PostGIS, I’d be willing to include it as part of 
the PostGIS Bundle, or as a separate package under Windows Application Stack 
Builder -> Spatial Extensions.

 

Thanks,

Regina

 

 

 

From: Gandalf the Gray  
Sent: Sunday, November 19, 2023 9:53 AM
To: Regina Obe 
Subject: Re: Windows compile instructions

 

Hi Regina

 

There is actually a newer repository:

 

https://github.com/postgrespro/pgsphere

 

On Sat, Nov 18, 2023 at 7:05 PM Regina Obe mailto:l...@pcorp.us> > wrote:

Gandalf,

 

Sorry I keep on meaning to put up my compile instructions and latest PostGIS 
build chain up, but everytime I start, I create a mess of things and put it 
off, cause I’m midway upgrading my chain to a newer and haven’t swapped out my 
mingw setup.

 

I had written these instructions not too long ago for building pgRouting under 
msys/mingw, 
https://github.com/pgRouting/pgrouting/wiki/Building-on-windows-with-msys2-and-mingw64-(WIP)

but got stuck when trying to get pgTap Perl dependencies to finish installing 
(thus why it still has WIP in the name), so stuck with my old configured chain.

But the idea here, was to have something a bit simpler for people to understand 
than my “build everything from scratch” approach.

 

Just ignore the cmake instructions, and follow the pgSphere instructions for 
building.

The key thing here, is using pacman (the mingw64 package manager) to install 
the development tool chain.

 

I have never tried to build pgSphere (except like 15 years ago where I failed 
miserably for some reason).  But just a warning, at a glance it looks like 
pgSphere hasn’t been updated in sometime https://github.com/akorotkov/pgsphere 
(and looks like it might only be set to work only thru PG12).  So it’s quite 
possible it may need some patches to work with newer PostgreSQL versions.

 

Since it doesn’t appear to have dependencies then you can probably do it with 
mingw/msys just fine and using the pacman packaged PostgreSQL.

If the version offered by pacman installer doesn’t suit you, compiling 
PostgreSQL from source is fairly trivial as described here  
https://www.cybertec-postgresql.com/en/building-postgresql-with-msys2-and-mingw-under-windows/

 

 

Regardless whatever you build should be compatible with both mingw64 and 
PostgreSQL 64 Windows EDB as long as you include the libgcc_s_seh-1.dll and 
libstdc++-6.dll that comes with the mingw64 install and any other dependencies 
not shipped with PostgreSQL windows EDB.

 

I should also note that the pacman packager in that ships with msys2 mingw64, 
has pretty much every dependency one would need.  Heck it even has postgis, 
geos, gdal., but I don’t use those since I tend to name (for example more than 
one version of PostgreSQL), and I generally need to test out in development 
geos.

 

Hope that helps,

Regina

 

 

 

From: Gandalf the Gray mailto:pjduplooy@gmail.com> > 
Sent: Saturday, November 18, 2023 8:10 AM
To: postgis-de...@lists.osgeo.org <mailto:postgis-de...@lists.osgeo.org> 
Subject: Windows compile instructions

 

Hi guys (although I think this is for Regina)

 

Where is the latest compile instructions for Windows located at.

 

I have a PG extension that I would like to try and compile for Windows.  It is 
PG Sphere.

 

Thanks a whole bunch for any

Re: [postgis-users] Generating new random points throughout an update

[Regina Obe via postgis-users]

I’m guessing the reason why your example doesn’t work is because the planner is 
doing some serious short-circuiting cause it sees two subselects that are using 
no variables from the updated table. 

I suspect this is a bug or some intentional stuff that makes no sense to me.  
Cause I’m pretty sure I’ve done something like  UPDATE sometable SET geom = 
ST_GeneratePoints(somestaticgeom,1);

And gotten different answers.  So I suspect it’s the subselect throwing it off 
or it is intentionally treating like a constant because that subselect doesn’t 
involve the table being updated.

 

So the trick I see is to incorporate some value from your events table into 
your routine.

 

Try computing first and then updating like so the below gives me different 
answers for each row.

 

WITH a AS (

SELECT e.id, ST_Makeline(

ST_GeometryN(

  ST_GeneratePoints(

ST_Buffer(

  ST_POINTN(s.std_track,1),

  0.01),

  1),

1), ST_GeometryN(

  ST_GeneratePoints(

ST_Buffer(

  ST_POINTN(s.std_track,2),

  0.01),

1),

  1)

) AS geom

FROM events AS e, std_tow AS s

)

UPDATE events

set jittered = a.geom

FROM a

WHERE a.id = events.id;

select ST_AsText(jittered) from events;

 

the other way to do it, making the planner realize that just cause the code is 
exactly the same and doesn’t involve the table being updated, doesn’t mean you 
want all your values to be the same, is to incorporate your event id in your 
randomize like so

 

update events

set jittered = ST_Makeline(

(select ST_GeometryN(

  ST_GeneratePoints(

ST_Buffer(

  ST_POINTN(std_track,1),

  0.01),

  1,

  (random()*1000)::int + events.id),

   1)

 from std_tow),

(select ST_GeometryN(

  ST_GeneratePoints(

ST_Buffer(

  ST_POINTN(std_track,2),

  0.01),

1,

(random()*1000)::int + events.id),

1)

 from std_tow));

select ST_AsText(jittered) from events;

 

 

 

 

From: Brent Wood  
Sent: Saturday, November 18, 2023 4:35 PM
To: Regina Obe ; PostGIS Users Discussion 

Subject: Re: [postgis-users] Generating new random points throughout an update

 

Thanks for your time & advice Regina, I appreciate it.

 

 

I still can't get this to work as I think it should, so have included actual 
SQL's to show what I'm doing, using ST_GeneratePoints() this time...

 

I create a db & add the postgis extension

 

Then create the two tables to test, inserting 3 empty geometries in one & a 
simple linestring in the other:

 

create table events (idinteger,

 jittered  geometry(LINESTRING,4326));

 

insert into events (id) values (1);

insert into events (id) values (2);

insert into events (id) values (3);

 

create table std_tow (idinteger,

  std_track geometry(LINESTRING,4326));

 

insert into std_tow values (1, ST_SetSRID(

 ST_MakeLine(

    ST_MakePoint(176,-47),

    ST_MakePoint(177,-48)

 ),

     4326)); 



 

I want to update the empty linestrings in one table (events) with slightly 
randomised versions of the linestring in the other (std_tow).

ST_GeneratePoints() supposedly generates random points (in a polygon created by 
buffering the vertices in the standard linestring) without a seed, so I run it 
with no seed & view the results:

 

update events
set jittered = ST_Makeline(
(select ST_GeometryN(
  ST_GeneratePoints(
ST_Buffer(
  ST_POINTN(std_track,1),
  0.01),
  1),
1)
 from std_tow),
(select ST_GeometryN(
  ST_GeneratePoints(
ST_Buffer(
  ST_POINTN(std_track,2),
  0.01),
1),
  1)

 from std_tow));

select ST_AsText(jittered) from events;

 

LINESTRING(175.99658281229873 -46.99893493622685,177.0081812507064 
-47.99873318845546)
LINESTRING(175.99658281229873 -46.99893493622685,177.0081812507064 
-47.99873318845546)
LINESTRING(175.99658281229873 -46.99893493622685,177.0081812507064 
-47.99873318845546)
(3 rows)

I get three identical linestrings. 

 

I figured I'd use a different integer random seed (between 0 and 1000) in 
ST_GeneratePoints() to force a different result each time:

 

update events
set jittered = ST_Makeline(
(sel

Re: [postgis-users] Generating new random points throughout an update

[Regina Obe via postgis-users]

Well when I run random()  I do get a different answer for each run so random 
behaves as I would expect.  I didn’t look that closely at your query with 
random.

 

e.g.

 

SELECT random()

FROM generate_series(1,100);

 

Even if within the same row, the random numbers are different:

 

SELECT random(), random()

FROM generate_series(1,10);

 

If you were doing random()::integer as input into ST_GeneratePoints, I thought 
maybe that was a typo on your end.  Then your random number would only be 0 or 
1, which is not that random.

 

So if you really were doing ST_GeneratePoints(geom, random()::integer) then 
that would explain why you got much less than random results with 
ST_GeneratePoints.

 

 

From: Brent Wood  
Sent: Saturday, November 18, 2023 1:29 AM
To: Regina Obe ; PostGIS Users Discussion 

Subject: Re: [postgis-users] Generating new random points throughout an update

 

Hi Regina,

 

The seed was an int generated from random(), so I'd expected to generate a 
different result every time. This didn't happen.

 

Do I understand that if I omit the seed, I'll get a different point each time 
by default?

 

 

Thanks,

 

   Brent 

 

On Saturday, November 18, 2023 at 06:01:37 PM GMT+13, Regina Obe mailto:l...@pcorp.us> > wrote: 

 

 

If you want the answer different each time, you don’t want to feed a seed to 
ST_GeneratePoints.  

The seed argument was added because some people wanted to generate the same 
answer for each run.

 

https://postgis.net/docs/ST_GeneratePoints.html  (note the sentence: The 
optional seed is used to regenerate a deterministic sequence of points, and 
must be greater than zero.)

 

 

From: postgis-users mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Brent Wood via 
postgis-users
Sent: Friday, November 17, 2023 11:53 PM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Cc: Brent Wood mailto:pcr...@yahoo.com> >
Subject: [postgis-users] Generating new random points throughout an update

 

Hopefully someone can help with a problem I'm having.

 

I have a table with simple linestrings that I need to create a randomly 
modified version of.

 

The linestrings represent vessel tracks. I can identify a set of "similar" 
tracks & create a single "average" linestring that is somewhat representative.

 

Many of the records don't have a linestring, but for statistical purposes I 
need to assign a linestring to each - by creating a jittered version of the 
average linestring so they are not all identical.

 

The simplest approach I have tried is to use an update with ST_Project() given 
a random() distance & random() direction applied to each vertex in the average 
line.

 

I use the first two vertices with ST_Makeline(), then append a vertex for the 
third point, as in the SQL below. 

 

My problem is that every new line is identical. From some Googled hints, I 
figure the optimiser has decided to run random() once & re-use the value 
instead of running the function for every iteration (but I could be wrong!).

 

Any suggestions as to how I can force a different random result for each record 
that is updated?

I also tried using ST_GeneratePoints() in a buffer around each point, but need 
to use something like (random()::int as the seed, and this seems to do exactly 
the same - valid linestrings are generated, but they are identical, so I'm 
assuming the seed is not being recalculated for each record.

 

 

update events
set jittered = ST_MakeLine(
   (select ST_Project(
 ST_POINTN(std_track,1),
 (random()*5000),
 radians(random()*360))::geometry
   from std_tow),
  (select ST_Project(
 ST_PointN(std_track,2),
 (random()*5000),
   radians(random()*360))::geometry
   from std_tow)
  );

 

 

Thanks,

 

  Brent Wood

 

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

Re: [postgis-users] Generating new random points throughout an update

[Regina Obe via postgis-users]

If you want the answer different each time, you don’t want to feed a seed to 
ST_GeneratePoints.  

The seed argument was added because some people wanted to generate the same 
answer for each run.

 

https://postgis.net/docs/ST_GeneratePoints.html  (note the sentence: The 
optional seed is used to regenerate a deterministic sequence of points, and 
must be greater than zero.)

 

 

From: postgis-users  On Behalf Of Brent 
Wood via postgis-users
Sent: Friday, November 17, 2023 11:53 PM
To: PostGIS Users Discussion 
Cc: Brent Wood 
Subject: [postgis-users] Generating new random points throughout an update

 

Hopefully someone can help with a problem I'm having.

 

I have a table with simple linestrings that I need to create a randomly 
modified version of.

 

The linestrings represent vessel tracks. I can identify a set of "similar" 
tracks & create a single "average" linestring that is somewhat representative.

 

Many of the records don't have a linestring, but for statistical purposes I 
need to assign a linestring to each - by creating a jittered version of the 
average linestring so they are not all identical.

 

The simplest approach I have tried is to use an update with ST_Project() given 
a random() distance & random() direction applied to each vertex in the average 
line.

 

I use the first two vertices with ST_Makeline(), then append a vertex for the 
third point, as in the SQL below. 

 

My problem is that every new line is identical. From some Googled hints, I 
figure the optimiser has decided to run random() once & re-use the value 
instead of running the function for every iteration (but I could be wrong!).

 

Any suggestions as to how I can force a different random result for each record 
that is updated?

I also tried using ST_GeneratePoints() in a buffer around each point, but need 
to use something like (random()::int as the seed, and this seems to do exactly 
the same - valid linestrings are generated, but they are identical, so I'm 
assuming the seed is not being recalculated for each record.

 

 

update events
set jittered = ST_MakeLine(
   (select ST_Project(
 ST_POINTN(std_track,1),
 (random()*5000),
 radians(random()*360))::geometry
   from std_tow),
  (select ST_Project(
 ST_PointN(std_track,2),
 (random()*5000),
   radians(random()*360))::geometry
   from std_tow)
  );

 

 

Thanks,

 

  Brent Wood

 

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

Re: [postgis-users] Extracting variable information from netcdf, imported as raster to a table

[Regina Obe via postgis-users]

One more optimization.  I think you can get rid of the FOREACH band_number too 
and reduce all that to this query

 

So all this goes

 

band_numbers := ARRAY(SELECT generate_series(1, 366));

-- Loop through all bands within the file
FOREACH band_number IN ARRAY band_numbers LOOP

:

LOOP

 

Use ST_NumBands instead of relying on your rasters all having 366 bands 
https://postgis.net/docs/RT_ST_NumBands.html

So replace all the above with this:

 

INSERT INTO extracted_data_bbox (year, year_day, lat, lon, prcp, 
tmax, tmin, observation_time)
SELECT year_from_filename, n.band_number, ST_Y(sc.geom), 
ST_X(sc.geom), CASE WHEN variable_name = 'prcp' THEN sc.val ELSE NULL END,
CASE WHEN variable_name = 'tmax' THEN sc.val ELSE NULL END,
CASE WHEN variable_name = 'tmin' THEN sc.val ELSE NULL END, 
observation_time

   FROM  generate_series(1, ST_NumBands(raster_record.clipped_raster) ) 
AS n(band_number), ST_PixelAsCentroids(raster_record.clipped_raster, 
n.band_number, true) AS sc;

 

That will solve the issue of you going over the band count of your raster, 
which might be an issue you are running into 

 

 

From: Regina Obe  
Sent: Thursday, November 16, 2023 9:46 PM
To: 'Manaswini Ganjam' 
Cc: 'PostGIS Users Discussion' 
Subject: RE: [postgis-users] Extracting variable information from netcdf, 
imported as raster to a table

 

I don’t know much about netCDF but I would assume GDAL would handle the packing 
unraveling behind the scenes.

 

Some things I notice wrong

 

https://postgis.net/docs/RT_ST_PixelAsCentroids.html  is a set returning 
function, so you can’t just stuff it in a record variable.  It has to go in a 
table.

So I’d get rid of this line

 

  SELECT * FROM ST_PixelAsCentroids(raster_record.clipped_raster, 
band_number, true) INTO pixel_data;

I’d also scrap this, because ultimately you want to work on all centroids not 
the first one that falls out of the tree

EXECUTE format('SELECT ST_Value($1, $2, $3, true)', 
raster_record.clipped_raster, band_number, centroid_point) INTO variable_value 
USING raster_record.clipped_raster, band_number, centroid_point;

 

I’d change this


-- Insert the extracted data into the extracted_data_bbox table
INSERT INTO extracted_data_bbox (year, year_day, lat, lon, prcp, 
tmax, tmin, observation_time)
VALUES (year_from_filename, band_number, ST_Y(centroid_point), 
ST_X(centroid_point), CASE WHEN variable_name = 'prcp' THEN variable_value ELSE 
NULL END,
CASE WHEN variable_name = 'tmax' THEN variable_value ELSE 
NULL END,
CASE WHEN variable_name = 'tmin' THEN variable_value ELSE 
NULL END, observation_time);

 

To this:

 

INSERT INTO extracted_data_bbox (year, year_day, lat, lon, prcp, 
tmax, tmin, observation_time)
SELECT year_from_filename, band_number, ST_Y(sc.geom), 
ST_X(sc.geom), CASE WHEN variable_name = 'prcp' THEN sc.val ELSE NULL END,
CASE WHEN variable_name = 'tmax' THEN sc.val ELSE NULL END,
CASE WHEN variable_name = 'tmin' THEN sc.val ELSE NULL END, 
observation_time

   FROM  ST_PixelAsCentroids(raster_record.clipped_raster, band_number, 
true) AS sc;

 

 

 

 

From: Manaswini Ganjam mailto:manu.gan...@gmail.com> > 
Sent: Thursday, November 16, 2023 1:19 PM
To: Regina Obe mailto:l...@pcorp.us> >
Cc: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] Extracting variable information from netcdf, 
imported as raster to a table

 

Thank you, Regina,

 

I apologize, I have shared the older version of my code, I have tried 
ST_pixelascentroid and ST_value and the issue was I messed up the parameters of 
centroids and that caused the errors now I corrected that as well. I think the 
code below is a good representation of my approach, I would like to iterate for 
every raster in a table, for every band and for every lat and lon and extract 
st value, and insert it to a table, 

 

psql:/home/manaswini/MEGA/bbgc_uw_rpackage/rproject/raster2pgsql/extract_table_quer.sql:66:
 NOTICE:  Invalid band index (must use 1-based). Returning empty set for all 
the coordinates and bands. What could be the issue. 

 

Is this because my data is packed? the readme file of the data has this 
information: Details about the data packing:

The data has been packed into short integers (2 bytes instead of 4 byte reals) 
to save space. You must unpack that data to get the correct floating point 
representation of the data. Each netCDF variable that has been packed has an 
add_offset and scale_factor attribute associated with it. Some software 
automatically unpacks the data when it is read.
 
The formula to unpack the data is:
 
unpacked value = add_offset + ( (packed value) * scale_factor )
 
For more information see here:
https://www.unidata

Re: [postgis-users] Extracting variable information from netcdf, imported as raster to a table

[Regina Obe via postgis-users]

I don’t know much about netCDF but I would assume GDAL would handle the packing 
unraveling behind the scenes.

 

Some things I notice wrong

 

https://postgis.net/docs/RT_ST_PixelAsCentroids.html  is a set returning 
function, so you can’t just stuff it in a record variable.  It has to go in a 
table.

So I’d get rid of this line

 

  SELECT * FROM ST_PixelAsCentroids(raster_record.clipped_raster, 
band_number, true) INTO pixel_data;



I’d also scrap this, because ultimately you want to work on all centroids not 
the first one that falls out of the tree

EXECUTE format('SELECT ST_Value($1, $2, $3, true)', 
raster_record.clipped_raster, band_number, centroid_point) INTO variable_value 
USING raster_record.clipped_raster, band_number, centroid_point;



 

I’d change this


-- Insert the extracted data into the extracted_data_bbox table
INSERT INTO extracted_data_bbox (year, year_day, lat, lon, prcp, 
tmax, tmin, observation_time)
VALUES (year_from_filename, band_number, ST_Y(centroid_point), 
ST_X(centroid_point), CASE WHEN variable_name = 'prcp' THEN variable_value ELSE 
NULL END,
CASE WHEN variable_name = 'tmax' THEN variable_value ELSE 
NULL END,
CASE WHEN variable_name = 'tmin' THEN variable_value ELSE 
NULL END, observation_time);

 

To this:

 

INSERT INTO extracted_data_bbox (year, year_day, lat, lon, prcp, 
tmax, tmin, observation_time)
SELECT year_from_filename, band_number, ST_Y(sc.geom), 
ST_X(sc.geom), CASE WHEN variable_name = 'prcp' THEN sc.val ELSE NULL END,
CASE WHEN variable_name = 'tmax' THEN sc.val ELSE NULL END,
CASE WHEN variable_name = 'tmin' THEN sc.val ELSE NULL END, 
observation_time

   FROM  ST_PixelAsCentroids(raster_record.clipped_raster, band_number, 
true) AS sc;

 

 

 

 

From: Manaswini Ganjam  
Sent: Thursday, November 16, 2023 1:19 PM
To: Regina Obe 
Cc: PostGIS Users Discussion 
Subject: Re: [postgis-users] Extracting variable information from netcdf, 
imported as raster to a table

 

Thank you, Regina,

 

I apologize, I have shared the older version of my code, I have tried 
ST_pixelascentroid and ST_value and the issue was I messed up the parameters of 
centroids and that caused the errors now I corrected that as well. I think the 
code below is a good representation of my approach, I would like to iterate for 
every raster in a table, for every band and for every lat and lon and extract 
st value, and insert it to a table, 

 

psql:/home/manaswini/MEGA/bbgc_uw_rpackage/rproject/raster2pgsql/extract_table_quer.sql:66:
 NOTICE:  Invalid band index (must use 1-based). Returning empty set for all 
the coordinates and bands. What could be the issue. 

 

Is this because my data is packed? the readme file of the data has this 
information: Details about the data packing:

The data has been packed into short integers (2 bytes instead of 4 byte reals) 
to save space. You must unpack that data to get the correct floating point 
representation of the data. Each netCDF variable that has been packed has an 
add_offset and scale_factor attribute associated with it. Some software 
automatically unpacks the data when it is read.
 
The formula to unpack the data is:
 
unpacked value = add_offset + ( (packed value) * scale_factor )
 
For more information see here:
https://www.unidata.ucar.edu/software/netcdf/workshops/2010/bestpractices/Packing.html
 
There's also another attribute called "missing_value". In this case all the 
-32768 values you see are missing. Only the grid points outside the downscaling 
domain is given the missing data value.
 
The packing saves a lot of space, that is why the data is packed.

 

 

-- Create the precipitation_temperature_data table
DROP TABLE IF EXISTS extracted_data_bbox;
CREATE TABLE extracted_data_bbox (
id SERIAL PRIMARY KEY,
year integer,
year_day integer,
lat double precision,
lon double precision,
prcp double precision,
tmax double precision,
tmin double precision,
observation_time timestamp
);

-- Loop through all records in gfdl_03_bbox
DO $$
DECLARE
raster_record RECORD;
year_from_filename integer;
observation_time timestamp;
centroid_point geometry;
variable_name text;
variable_value double precision;
band_number integer;
band_numbers integer[];
pixel_data RECORD;
BEGIN
FOR raster_record IN (SELECT * FROM gfdl_03_bbox) LOOP
SELECT regexp_replace(raster_record.filename, '.*_(\d{4})[^0-9]+', 
'\1')::integer INTO year_from_filename;

-- Determine the variable name based on the filename
IF raster_record.filename ~ 'prcp' THEN
variable_name := 'prcp';
ELSIF raster_record.filename ~ 'tmax' THEN
variable_name := 'tmax';
ELSIF raster_record.filename ~ 'tmin' THEN
variable_name := 'tmin';
ELSE
   

Re: [postgis-users] Extracting variable information from netcdf, imported as raster to a table

[Regina Obe via postgis-users]

I didn’t realize that netCDF also has a vector component.

 

https://gdal.org/drivers/vector/netcdf.html#vector-netcdf

 

To read the vector component, you’d use the ogr_fdw extension to read that 
rather than postgis_raster extension.

 

Details here -  https://github.com/pramsey/pgsql-ogr-fdw

 

So perhaps that is what your python is doing reading the vector component.  I 
don’t know if netcdf mixes those in one data set or not since I have no 
experience using netcdf.

 

I recall you said you are using the OSGeo Live 16 distribution.  I just checked 
the OSGeoLive 16 does include ogr_fdw 

 

So do 

 

CREATE EXTENSION ogr_fdw;

 

 

The list of supported formats you can see with this query:

 

SELECT name FROM unnest(ogr_fdw_drivers()) AS f(name) ORDER BY name;

 

Which for osgeolive 16, I see netCDF listed

 

 

 

 

 

From: Regina Obe  
Sent: Wednesday, November 15, 2023 6:19 PM
To: 'PostGIS Users Discussion' 
Cc: 'Manaswini Ganjam' 
Subject: RE: [postgis-users] Extracting variable information from netcdf, 
imported as raster to a table

 

Just confirming some stuff, since I’m not completely following:

 

Raster_record.rast column is of type raster correct?  IF so ST_X and ST_Y won’t 
work since those are for geometry types.

 

Also ST_Value(raster_record.rast, band_number), won’t work either since that 
expects as input a geometry or x,y on the raster you want the value you.

I would think you would have gotten an error with that, which makes me feel I’m 
missing something critical.

 

If you want to extract all the pixels in a raster, you’d do something like 
https://postgis.net/docs/RT_ST_PixelAsPoints.html

 

SELECT pp.x, pp.y, pp.val, ST_X(pp.geom) AS lon, ST_Y(pp.geom) AS lat

FROM raster_record, 

ST_PixelAsPoints(raster_record.rast, 1) AS pp

 


   

 

From: postgis-users mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Manaswini Ganjam 
via postgis-users
Sent: Wednesday, November 15, 2023 2:01 PM
To: postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> 
Cc: Manaswini Ganjam mailto:manu.gan...@gmail.com> >
Subject: [postgis-users] Extracting variable information from netcdf, imported 
as raster to a table

 

Hi, 

I have been trying to download s3 cloud stored gridded climate data and 
generate tables with variables, lat, lon and timestamp (year, yearday). To 
achieve this I used raster2pgsql and imported multiple netcdf files into a 
database table. 

 

Question: How to achieve the extraction of variables using postgis? I tried 
using ST_value, ST_pixelaspoints but I was getting errors, mainly due to the 
format in which netcdfs are stored in the database (the error says can't load 
some characters like 00E30100082...), I even tried changing the datatype to 
float but still did not work. I mean it is probably not simple like selecting a 
variable from the netcdf. I have enclosed my sql query below: 

 

  -- Iterate through all raster files in the table
FOR raster_record IN (SELECT * FROM gfdl_03_prcp) LOOP
-- Determine the year from the raster file name, assuming the format is 
'prcp_03_1950.nc <http://prcp_03_1950.nc> '
SELECT
regexp_replace(raster_record.filename, '.*_(\d{4})\.nc', 
'\1')::integer
INTO
year;

-- Calculate the start date of the year
year_start := (year || '-01-01')::date;

-- Determine if the year is a leap year
is_leap_year := EXTRACT(ISODOW FROM (year_start + interval '1 year')) = 
7;

-- Set the number of bands for the year (365 for non-leap years, 366 
for leap years)
FOR band_number IN 1..(CASE WHEN is_leap_year THEN 366 ELSE 365 END) 
LOOP
-- Calculate the observation_time using the year and band number
observation_time := year_start + (band_number - 1) * interval '1 
day';

-- Extract X (lon) and Y (lat) coordinates from the raster
SELECT
ST_X(raster_record.rast) AS lon,
ST_Y(raster_record.rast) AS lat
INTO
lon,
lat;

-- Insert the lat, lon, prcp, and observation_time into the 
extracted_values table
INSERT INTO extracted_values (lat, lon, prcp, observation_time)
VALUES
(lat, lon, ST_Value(raster_record.rast, band_number), 
observation_time);

-- Increment the counter
counter := counter + 1;

-- Commit the transaction periodically in batches
IF counter % batch_size = 0 THEN
COMMIT;
END IF;
END LOOP;
END LOOP;


 

The metadata for the two files is as follows: 

 

File from database:

{'NC_GLOBAL#Conventions': 'CF-1.5',
 'NC_GLOBAL#GDAL': 'GDAL 3.6.4, released 2023/04/17',
 'NC_GLOBAL#history': 'Wed Nov 15 13:32:13 2023: GDAL CreateCopy( 
n

Re: [postgis-users] Extracting variable information from netcdf, imported as raster to a table

[Regina Obe via postgis-users]

Just confirming some stuff, since I’m not completely following:

 

Raster_record.rast column is of type raster correct?  IF so ST_X and ST_Y won’t 
work since those are for geometry types.

 

Also ST_Value(raster_record.rast, band_number), won’t work either since that 
expects as input a geometry or x,y on the raster you want the value you.

I would think you would have gotten an error with that, which makes me feel I’m 
missing something critical.

 

If you want to extract all the pixels in a raster, you’d do something like 
https://postgis.net/docs/RT_ST_PixelAsPoints.html

 

SELECT pp.x, pp.y, pp.val, ST_X(pp.geom) AS lon, ST_Y(pp.geom) AS lat

FROM raster_record, 

ST_PixelAsPoints(raster_record.rast, 1) AS pp

 


   

 

From: postgis-users  On Behalf Of 
Manaswini Ganjam via postgis-users
Sent: Wednesday, November 15, 2023 2:01 PM
To: postgis-users@lists.osgeo.org
Cc: Manaswini Ganjam 
Subject: [postgis-users] Extracting variable information from netcdf, imported 
as raster to a table

 

Hi, 

I have been trying to download s3 cloud stored gridded climate data and 
generate tables with variables, lat, lon and timestamp (year, yearday). To 
achieve this I used raster2pgsql and imported multiple netcdf files into a 
database table. 

 

Question: How to achieve the extraction of variables using postgis? I tried 
using ST_value, ST_pixelaspoints but I was getting errors, mainly due to the 
format in which netcdfs are stored in the database (the error says can't load 
some characters like 00E30100082...), I even tried changing the datatype to 
float but still did not work. I mean it is probably not simple like selecting a 
variable from the netcdf. I have enclosed my sql query below: 

 

  -- Iterate through all raster files in the table
FOR raster_record IN (SELECT * FROM gfdl_03_prcp) LOOP
-- Determine the year from the raster file name, assuming the format is 
'prcp_03_1950.nc  '
SELECT
regexp_replace(raster_record.filename, '.*_(\d{4})\.nc', 
'\1')::integer
INTO
year;

-- Calculate the start date of the year
year_start := (year || '-01-01')::date;

-- Determine if the year is a leap year
is_leap_year := EXTRACT(ISODOW FROM (year_start + interval '1 year')) = 
7;

-- Set the number of bands for the year (365 for non-leap years, 366 
for leap years)
FOR band_number IN 1..(CASE WHEN is_leap_year THEN 366 ELSE 365 END) 
LOOP
-- Calculate the observation_time using the year and band number
observation_time := year_start + (band_number - 1) * interval '1 
day';

-- Extract X (lon) and Y (lat) coordinates from the raster
SELECT
ST_X(raster_record.rast) AS lon,
ST_Y(raster_record.rast) AS lat
INTO
lon,
lat;

-- Insert the lat, lon, prcp, and observation_time into the 
extracted_values table
INSERT INTO extracted_values (lat, lon, prcp, observation_time)
VALUES
(lat, lon, ST_Value(raster_record.rast, band_number), 
observation_time);

-- Increment the counter
counter := counter + 1;

-- Commit the transaction periodically in batches
IF counter % batch_size = 0 THEN
COMMIT;
END IF;
END LOOP;
END LOOP;


 

The metadata for the two files is as follows: 

 

File from database:

{'NC_GLOBAL#Conventions': 'CF-1.5',
 'NC_GLOBAL#GDAL': 'GDAL 3.6.4, released 2023/04/17',
 'NC_GLOBAL#history': 'Wed Nov 15 13:32:13 2023: GDAL CreateCopy( 
not_clipped_prcp.nc  , ... )'}
File before loading into the database:
{'lat#units': 'degrees_north',
 'lon#units': 'degrees_east',
 'NC_GLOBAL#title': 'Daily statistically downscaled CMIP5 data for the United 
States and southern Canada east of the Rocky Mountains, version 1.0, 
realization 1, 0.1x0.1 degree spatial resolution.',
 'NETCDF_DIM_EXTRA': '{time}',
 'NETCDF_DIM_time_DEF': '{366,4}',
 'NETCDF_DIM_time_VALUES': 
'{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14362,363,364,365}',
 'prcp#add_offset': '819.17499',
 'prcp#long_name': 'daily precipitation accumulation',
 'prcp#missing_value': '-32768',
 'prcp#scale_factor': '0.025',
 'prcp#units': 'mm',
 'prcp#_FillValue': '-32768',
 'time#units': 'days since 1952-1-1 0:0:0.0'}
 
In case this information is useful: Previously I used python to extract 
variable information and generate a csv or table using this variable 
information, and the code is enclosed below. In the code I extracted variable 
values using lon = dataset.variables['lon'][:] and iterated for loops to write 
them all in csv. 

Python code:

import netCDF4 as nc

# Step 1: Read the NetCDF file
filename = "/home/manaswini/prcp_03_1950.nc  "
dataset = 

Re: [postgis-users] par_psql code for queries

[Regina Obe via postgis-users]

Didn’t notice you were talking about this -  https://github.com/gbb/par_psql

 

New to me, but yes that as a driver should work fine.

 

I’d still use a procedure  and just use par_sql to drive the runs of that 
procedure.

 

Generate the driver query something like

 

SELECT 'CALL clip_rasters(' || i::text || ', ' || (i + j - 1)::text || '); -- &'

FROM (SELECT 10) AS f(j), generate_series((SELECT min(id) FROM gfdl_03_temp ), 
(SELECT max(id) FROM gfdl_03_temp ), j) AS I;

 

And then copy that into your par_sql script.

 

Replace the number 10 with whatever iteration you prefer to use.

 

 

 

From: Regina Obe  
Sent: Tuesday, November 14, 2023 11:07 PM
To: 'PostGIS Users Discussion' 
Cc: 'Manaswini Ganjam' 
Subject: RE: [postgis-users] par_psql code for queries

 

I’m guessing your intention is to run this in separate connections cause 
otherwise sad to say you can’t do this in parallel without having some sort of 
background processor.

 

Something also seems incorrectly shuffled in your query.

First of all, you want a COMMIT before your end LOOP otherwise not much point 
to doing this in a loop since it wouldn’t only commit when all is done.

 

Also may be okay, but looks like you are relying on filename only appearing 
once in gfdl_03_temp?  Might be safe, but just in case, I’d add the ID in your 
routine

 

I also think it would be better to just have this in a stored procedure instead 
of a DO command, so that you can easily call it in separate connections.

 

So here is my revised

 

CREATE TABLE clipped_rasters(

id serial PRIMARY KEY,

clipped_raster raster,

filename text, id_tile integer);

 

CREATE INDEX ix_clipped_rasters_id_tile ON clipped_rasters(id_tile);

 

CREATE OR REPLACE PROCEDURE clip_rasters(param_start integer, param_end 
integer) AS

$body$

DECLARE 

min_lon numeric := -98;  -- Minimum Longitude

raster_row record;

min_lat numeric := 23;   -- Minimum Latitude

max_lon numeric := -74;  -- Maximum Longitude

crs integer := 4326; -- Coordinate Reference System (CRS)

max_lat numeric := 41;   -- Maximum Latitude

 

BEGIN

FOR raster_row IN (SELECT id, filename, rast FROM gfdl_03_temp AS t WHERE id 
BETWEEN param_start AND param_end 

AND NOT EXISTS(SELECT 1 FROM clipped_rasters AS n WHERE 
n.id_tile = t.id ) )

LOOP

RAISE NOTICE 'Starting filename: %, row: %, time: %', raster_row.file_name, 
raster_row.id, clock_timestamp();

INSERT INTO clipped_rasters (filename, clipped_raster_f, id_tile)

SELECT raster_row.filename, ST_Clip(rast, ST_MakeEnvelope(min_lon, min_lat, 
max_lon, max_lat, crs)), raster_row.id

FROM gfdl_03_temp

WHERE id = raster_row.id;

COMMIT;

RAISE NOTICE 'Ending filename: %, row: %, time: %', raster_row.file_name, 
raster_row.id, clock_timestamp();

END LOOP;

RETURN;

END $body$ language plpgsql;

 

-- then to run for tiles 1 to 10 do below. You want to run a different range in 
different sessions so they don’t run the risk of conflicting each other.

 

CALL clip_rasters(1,10);

 

From: postgis-users mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Manaswini Ganjam 
via postgis-users
Sent: Tuesday, November 14, 2023 1:44 PM
To: postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> 
Cc: Manaswini Ganjam mailto:manu.gan...@gmail.com> >
Subject: [postgis-users] par_psql code for queries

 

Hi, I want to do parallel processing for this code below:

CREATE TABLE clipped_rasters_f (
id serial PRIMARY KEY,
clipped_raster raster
filename text,
DECLARE
DO $$
);
min_lon numeric := -98;  -- Minimum Longitude
raster_row record;
min_lat numeric := 23;   -- Minimum Latitude
max_lon numeric := -74;  -- Maximum Longitude
crs integer := 4326; -- Coordinate Reference System (CRS)
max_lat numeric := 41;   -- Maximum Latitude

BEGIN
FOR raster_row IN (SELECT id, filename, rast FROM gfdl_03_temp)

LOOP
INSERT INTO clipped_rasters (filename, clipped_raster_f)
SELECT raster_row.filename, ST_Clip(rast, ST_MakeEnvelope(min_lon, min_lat, 
max_lon, max_lat, crs))
FROM gfdl_03_temp
WHERE id = raster_row.id <http://raster_row.id> ;

END LOOP;

END $$; --&

Can someone guide me on understanding the mandatory edits in changing this code 
or any postgis functions used in a query to convert to a par_psql compatible 
query?

 

Thank you,

Manaswini Ganjam

 

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

Re: [postgis-users] par_psql code for queries

[Regina Obe via postgis-users]

I’m guessing your intention is to run this in separate connections cause 
otherwise sad to say you can’t do this in parallel without having some sort of 
background processor.

 

Something also seems incorrectly shuffled in your query.

First of all, you want a COMMIT before your end LOOP otherwise not much point 
to doing this in a loop since it wouldn’t only commit when all is done.

 

Also may be okay, but looks like you are relying on filename only appearing 
once in gfdl_03_temp?  Might be safe, but just in case, I’d add the ID in your 
routine

 

I also think it would be better to just have this in a stored procedure instead 
of a DO command, so that you can easily call it in separate connections.

 

So here is my revised

 

CREATE TABLE clipped_rasters(

id serial PRIMARY KEY,

clipped_raster raster,

filename text, id_tile integer);

 

CREATE INDEX ix_clipped_rasters_id_tile ON clipped_rasters(id_tile);

 

CREATE OR REPLACE PROCEDURE clip_rasters(param_start integer, param_end 
integer) AS

$body$

DECLARE 

min_lon numeric := -98;  -- Minimum Longitude

raster_row record;

min_lat numeric := 23;   -- Minimum Latitude

max_lon numeric := -74;  -- Maximum Longitude

crs integer := 4326; -- Coordinate Reference System (CRS)

max_lat numeric := 41;   -- Maximum Latitude

 

BEGIN

FOR raster_row IN (SELECT id, filename, rast FROM gfdl_03_temp AS t WHERE id 
BETWEEN param_start AND param_end 

AND NOT EXISTS(SELECT 1 FROM clipped_rasters AS n WHERE 
n.id_tile = t.id ) )

LOOP

RAISE NOTICE 'Starting filename: %, row: %, time: %', raster_row.file_name, 
raster_row.id, clock_timestamp();

INSERT INTO clipped_rasters (filename, clipped_raster_f, id_tile)

SELECT raster_row.filename, ST_Clip(rast, ST_MakeEnvelope(min_lon, min_lat, 
max_lon, max_lat, crs)), raster_row.id

FROM gfdl_03_temp

WHERE id = raster_row.id;

COMMIT;

RAISE NOTICE 'Ending filename: %, row: %, time: %', raster_row.file_name, 
raster_row.id, clock_timestamp();

END LOOP;

RETURN;

END $body$ language plpgsql;

 

-- then to run for tiles 1 to 10 do below. You want to run a different range in 
different sessions so they don’t run the risk of conflicting each other.

 

CALL clip_rasters(1,10);

 

From: postgis-users  On Behalf Of 
Manaswini Ganjam via postgis-users
Sent: Tuesday, November 14, 2023 1:44 PM
To: postgis-users@lists.osgeo.org
Cc: Manaswini Ganjam 
Subject: [postgis-users] par_psql code for queries

 

Hi, I want to do parallel processing for this code below:

CREATE TABLE clipped_rasters_f (
id serial PRIMARY KEY,
clipped_raster raster
filename text,
DECLARE
DO $$
);
min_lon numeric := -98;  -- Minimum Longitude
raster_row record;
min_lat numeric := 23;   -- Minimum Latitude
max_lon numeric := -74;  -- Maximum Longitude
crs integer := 4326; -- Coordinate Reference System (CRS)
max_lat numeric := 41;   -- Maximum Latitude

BEGIN
FOR raster_row IN (SELECT id, filename, rast FROM gfdl_03_temp)

LOOP
INSERT INTO clipped_rasters (filename, clipped_raster_f)
SELECT raster_row.filename, ST_Clip(rast, ST_MakeEnvelope(min_lon, min_lat, 
max_lon, max_lat, crs))
FROM gfdl_03_temp
WHERE id = raster_row.id  ;

END LOOP;

END $$; --&

Can someone guide me on understanding the mandatory edits in changing this code 
or any postgis functions used in a query to convert to a par_psql compatible 
query?

 

Thank you,

Manaswini Ganjam

 

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

Re: [postgis-users] Grouping points based on distance

[Regina Obe via postgis-users]

Use https://postgis.net/docs/ST_ClusterDBSCAN.html

 

 

 

From: postgis-users  On Behalf Of
liglio.pessoal--- via postgis-users
Sent: Monday, November 13, 2023 2:38 PM
To: postgis-users@lists.osgeo.org
Cc: liglio.pess...@nexxa.com.br
Subject: [postgis-users] Grouping points based on distance

 

Hi,

 

I want to group a set of one million points within 50km each other and a
minimal of 100 points per group. 

How I do that.

 

Regards,

 

Liglio 

 

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

Re: [postgis-users] How to migrate to new server when PostGIS generations differ

[Regina Obe via postgis-users]

> Troels,
> 
> > I am working on getting rid of an old CentOS 7 server which runs
> > Postgres
> > 10+PostGIS 2.4.8, installed from the yum repository at
> > https://download.postgresql.org/pub/repos/yum/
> >
> > The server's databases are to be moved to a new Ubuntu 22 server
> > running Postgres 15+PostGIS 3.4, installed from the apt repo at
> > http://apt.postgresql.org/pub/repos/apt/
> >
> > I was hoping I could simply perform a pg_dump on the old server and a
> > pg_restore on the new one, but I run into some errors, example:
> >
> >pg_restore: error: could not execute query: ERROR:  type 
> > "public.pgis_abs"
> > does not exist
> >Command was: CREATE AGGREGATE public.accum(public.geometry) (
> >SFUNC = public.pgis_geometry_accum_transfn,
> >STYPE = public.pgis_abs,
> >FINALFUNC = public.pgis_geometry_accum_finalfn
> >);
> >
> > Is my pg_dump->pg_restore plan simply not viable? If not, what other
> > strategy should I employ?
> >
> > --
> > Regards,
> > Troels Arvin
> 
> Your plan of doing a pg_dump of your old Centos 7 PostGIS 2.4.8 and
> pg_restore on PostgreSQL 15 + PostGIS 3.4 should work just fine.
> 
> How did you install your PostGIS 2.4.8, if you installed using extensions, you
> shouldn't even have CREATE AGGREGATE public.accum in your install.
> My guess is maybe you have remnants of older postgis in there even pre-
> 2.4.8, cause I vaguely recall we got rid of public.accum even before PostGIS
> 2.4.8
> 
> The public.accum function we got rid of since it was supplanted by the built 
> in
> PostgreSQL array_agg function.
> So that error about public.accum you can ignore unless you built user
> functions around it.  If you have functions around it, you could create a
> public.accum, which would be just a copy of the array_agg definition, perhaps
> just forcing use of geometry, before you do the pg_dump
> 
> That said, once you do all that, many people run into issues with how the
> planner planned things back then and PostGIS use of new planner stuff from
> PG 12 on.
> So you'll probably want to test your apps for performance degradation, as you
> may have to rewrite some of those queries.
> 
> Hope that helps,
> Regina
> 

I forgot to mention one more thing.  In PostGIS 2.4,  the postgis extension 
included both geometry and raster types.
In PostGIS 3+, these were split into the postgis extension and the 
postgis_raster extension.

Now if you are not using raster, functionality and you had created your postgis 
in your old version using

CREATE EXTENSION postgis;


there is nothing extra to do.  Because your backup should just have a CREATE 
EXTENSION postgis;  in it, and thus 
No raster stuff will be restored.

However if you were using postgis raster functionality, then in your PostgreSQL 
15 / 3.4, you should do 

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_raster;

That way your raster tables will come back cleanly.



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

Re: [postgis-users] How to migrate to new server when PostGIS generations differ

[Regina Obe via postgis-users]

Troels,

> I am working on getting rid of an old CentOS 7 server which runs Postgres
> 10+PostGIS 2.4.8, installed from the yum repository at
> https://download.postgresql.org/pub/repos/yum/
> 
> The server's databases are to be moved to a new Ubuntu 22 server running
> Postgres 15+PostGIS 3.4, installed from the apt repo at
> http://apt.postgresql.org/pub/repos/apt/
> 
> I was hoping I could simply perform a pg_dump on the old server and a
> pg_restore on the new one, but I run into some errors, example:
> 
>pg_restore: error: could not execute query: ERROR:  type "public.pgis_abs"
> does not exist
>Command was: CREATE AGGREGATE public.accum(public.geometry) (
>SFUNC = public.pgis_geometry_accum_transfn,
>STYPE = public.pgis_abs,
>FINALFUNC = public.pgis_geometry_accum_finalfn
>);
> 
> Is my pg_dump->pg_restore plan simply not viable? If not, what other
> strategy should I employ?
> 
> --
> Regards,
> Troels Arvin

Your plan of doing a pg_dump of your old Centos 7 PostGIS 2.4.8
and pg_restore on PostgreSQL 15 + PostGIS 3.4 should work just fine.

How did you install your PostGIS 2.4.8, if you installed using extensions, you 
shouldn't even have CREATE AGGREGATE public.accum in your install.
My guess is maybe you have remnants of older postgis in there even pre-2.4.8, 
cause I vaguely recall we got rid of public.accum even before PostGIS 2.4.8

The public.accum function we got rid of since it was supplanted by the built in 
PostgreSQL array_agg function.
So that error about public.accum you can ignore unless you built user functions 
around it.  If you have functions around it, you could create a public.accum, 
which would be just a copy of the array_agg definition, perhaps just forcing 
use of geometry, before you do the pg_dump

That said, once you do all that, many people run into issues with how the 
planner planned things back then and PostGIS use of new planner stuff from PG 
12 on.
So you'll probably want to test your apps for performance degradation, as you 
may have to rewrite some of those queries.

Hope that helps,
Regina



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

Re: [postgis-users] ST_DWithin slow if query is joined to another table, but fast if ST_DWithin cast to an integer

[Regina Obe via postgis-users]

First of all is that really your join clause?  You seem to be missing an ON

 

from building b

join customer c

 

 

Also please output

 

EXPLAIN 

 

And the 

 

EXPLAIN ANALYZE of each query

 

From: postgis-users  On Behalf Of 
Cameron McCloud via postgis-users
Sent: Wednesday, November 8, 2023 5:38 AM
To: postgis-users@lists.osgeo.org
Cc: Cameron McCloud 
Subject: Re: [postgis-users] ST_DWithin slow if query is joined to another 
table, but fast if ST_DWithin cast to an integer

 

Test code is here: 
https://github.com/cameronmccloud/postgis_slow_st_dwithin/blob/main/test.sql

 

On Wed, Nov 8, 2023 at 9:25 AM Cameron McCloud mailto:cameron.mccl...@gmail.com> > wrote:

Hi,

 

Postgis Version: 3.3.4

Postgres Version: 14.9

 

We have 2 tables, "building" with lat/long and "customer" with a FK to 
building. There's a 1:1 relationship between the two.

 

The test tables we're using have 16K rows each. Our production data has a lot 
more, but we could reproduce this on a smaller dataset.

 

We found some odd behaviour when using ST_DWITHIN in a WHERE clause but only 
when the "building" table is joined to the "customer" table.

 

We also found that converting the result of ST_DWITHIN to an integer (1/0 for 
true/false) and using the integer in the WHERE is fast.

 

-- This query works as expected and takes 60ms returning 6K rows.

select b.*

from building b

where

  st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, 
st_makepoint(-96.7804060, 33.2471770)::geography, 5);

 

-- This query is orders of magnitude slower - 3000ms, even though joining the 
two tables without the WHERE takes 30ms

select b.*

from building b

join customer c

where

  st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, 
st_makepoint(-96.7804060, 33.2471770)::geography, 5);

 

-- This query converts the result of ST_DWITHIN to an integer. It's fast and 
takes 80ms

select b.*

from building b

join customer c

where

  case st_dwithin(st_makepoint(b.longitude, b.latitude)::geography, 
st_makepoint(-96.7804060, 33.2471770)::geography, 5)

when true then 1

else 0

  end = 1;

 

We have no idea why this is the case, but we're curious. In our production 
scenario using the "case...when" brought a query down from 6 minutes to 6 
seconds. We'd love to know why this might be and if there are other scenarios 
like this that could increase the performance of our Postgis queries.

 

Thanks,

 

Cam.

 

 

 

 

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

Re: [postgis-users] postgis_raster undefined symbol error

[Regina Obe via postgis-users]

This problem usually happens if for some reason you have two versions of GEOS 
installed  and the older one is being loaded.

Might be a packaging issue.  I’ve cc’d the pgsql-yum mailing list for guidance.

 

Thanks,

Regina

 

From: postgis-users  On Behalf Of Andy 
Knight via postgis-users
Sent: Tuesday, October 10, 2023 12:34 AM
To: postgis-users@lists.osgeo.org
Cc: Andy Knight 
Subject: [postgis-users] postgis_raster undefined symbol error

 

I'm trying to create a postgis_raster extension and I'm getting the following 
error:

 

CREATE EXTENSION postgis_raster;

ERROR:  could not load library "/usr/pgsql-13/lib/postgis_raster-3.so": 
/usr/libspatialite50/lib/libspatialite.so.8: undefined symbol: GEOSHilbertCode_r

 

The instance is running on Rocky 8 linux details as below;:

NAME="Rocky Linux"

VERSION="8.8 (Green Obsidian)"

postgis31_13-utils-3.1.9-1.rhel8.x86_64

postgis31_13-devel-3.1.9-1.rhel8.x86_64

postgis31_13-client-3.1.9-1.rhel8.x86_64

postgis31_13-3.1.9-1.rhel8.x86_64

postgis31_13-docs-3.1.9-1.rhel8.x86_64

 

postgresql13-server-13.12-1PGDG.rhel8.x86_64

 

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

Re: [postgis-users] Rocky 9 - libhull missing errors

[Regina Obe via postgis-users]

Are you using yum.postgresql.org.  If you are best to ask the question on
https://www.postgresql.org/list/pgsql-pkg-yum/

I know there have been some issues recently with gdal/geos as you can see -
https://www.postgresql.org/list/pgsql-pkg-yum/2023-09/

 

Though I thought most had been resolved recently except possibly for Fedora
38

 

GDAL is sadly always an issue cause so many dependencies are brought in and
many have to come thru the EPEL repo.

 

In your case it sounds like you probably have 2 GDAL installs perhaps one
coming from main repo and another from yum.postgresql.org

 

I'll try to take a look later if I run into any issues installing on a clean
rocky 9 system.

 

Regina

 

 

From: postgis-users  On Behalf Of
Scott Pasnikowski via postgis-users
Sent: Monday, October 2, 2023 3:00 PM
To: postgis-users@lists.osgeo.org
Cc: Scott Pasnikowski 
Subject: [postgis-users] Rocky 9 - libhull missing errors

 

 

Anyone know if postgis is good to go on Rocky 9?

 

I am being forced to migrate our app from Rocky 8 to 9 and the postgis
install fails.

The root cause seems tobe gdal is missing a "libhull.so"

 

I tried consulting the install matrix of what OS's are to be known good and
working... but the grid is 

a bit confusing.

 

For rocky 8 ... postgis installs without any issues... 

 

 

Scott

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

Re: [postgis-users] Purge of old released notes

[Regina Obe via postgis-users]

Are you talking about changelog or NEWS?

 

The changelog was just a rehash of the git log.

If you badly wanted to look at all the history, you can generate one yourself 
or just search thru the commit log here:

 

https://git.osgeo.org/gitea/postgis/postgis/commits/branch/master

 

The NEWS file we discussed purging too, but we’ve only purged it from the 
release notes docs.  It’s still part of the NEWS

 

https://git.osgeo.org/gitea/postgis/postgis/src/branch/master/NEWS

 

 

 

 

 

From: postgis-users  On Behalf Of p 
Valdés via postgis-users
Sent: Wednesday, September 27, 2023 1:52 PM
To: PostGIS Users Discussion 
Cc: p Valdés 
Subject: Re: [postgis-users] Purge of old released notes

 

You could look for an older package in the debian stable distro for example and 
extract the relevant changelog file.

 

El mié, 27 sept 2023 a la(s) 10:01, Arthur Bazin via postgis-users 
(postgis-users@lists.osgeo.org  ) 
escribió:

Hi !

 

I was wondering why the old released notes have been removed from the changelog 
?

I often use this document to find when changes happenned.

 

I understand that it can be really heavy by the time but is it possible to find 
the old changelogs in other files ? maybe one file by version ?

Is it something that you want to do ?

 

Thank you for your answer.




Have a nice day !

Arthur Bazin

___
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

[postgis-users] FOSS4GNA 2023 Baltimore Maryland USA, Oct 23rd - 25th

[Regina Obe via postgis-users]

The Free and Open Source 4 Geospatial North American conference will be
taking place in a little over a month in Baltimore, MD USA

https://foss4gna.org/

There will be 15 workshops happening on the Oct 23rd each is 3 hrs long

https://foss4gna.org/schedule.html#schedule

There will be some pgRouting, PostGIS, and QGIS workshops.

I'd like to draw your attention to:

Using PostGIS (which I will be a co-trainer on)
pgRouting Workshop (which Vicky, lead pgRouting developer) will be the
trainer

If you know someone or you are someone who wants some pointers on these
topics, please spread the word and also register.

Thanks,
Regina

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

Re: [postgis-users] JSONC compilation issues (Regina Obe)

[Regina Obe via postgis-users]

I use environment variables too, I’m just saying stuff it in a PKG_CONFIG_PATH 
environment variable instead.  That is the more modern way of doing things and 
things like PROJ (the proj-dir is deprecated) not even sure it works anymore.  
So I stuff PROJ in my PKG_CONFIG_PATH environment variable as I do SQLite etc.

 

From: postgis-users  On Behalf Of Light 
via postgis-users
Sent: Thursday, September 21, 2023 7:18 PM
To: postgis-users@lists.osgeo.org
Cc: Light 
Subject: Re: [postgis-users] JSONC compilation issues (Regina Obe)

 

Regina,

 

Environment variables are a great way to build things.
I used json-c version=0.13 in the past, using --with-jsondir and had no 
compilation failures.
But after I upgraded to json-c=0.15, the folder changed and became lib64.
I tried to compile json-c-0.13, 0.14, and 0.15, and found that only 0.13 is 
lib, and the others are lib64.
Summary, json-c-0.14>=lib64.

Thanks,
Wang Dapeng

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

Re: [postgis-users] JSONC compilation issues

[Regina Obe via postgis-users]

Have you tried using pkg config instead.

I don’t have mine in lib64 issue but I think using pkg-config should fix it

 

export 
PKG_CONFIG_PATH=/usr/local/json-c-0.15//lib64/pkgconfig:${PKG_CONFIG_PATH}

 

From: postgis-users  On Behalf Of Light
Sent: Thursday, September 21, 2023 5:24 AM
To: postgis-users@lists.osgeo.org
Subject: [postgis-users] JSONC compilation issues

 

Hi,

postgis 3.3.4
json-c 0.15

When I compiled 334, jsonc did not use environment variables. I used 
--with-jsondir=/usr/local/json-c-0.15. I confirmed that json-c-0.15 is 
installed.
configure record:
---
...
Check "/usr/local/json-c-0.15/include/json-c/json.h"...yes
Check json_object_get... in -ljson-c no
...
JSON-C support: no
...
---


Check line 985 of configure.ac  , 
`JSON_LDFLAGS="-L$JSONDIR/lib"`. The reason here is that json_object_get cannot 
be found in jsonc later.
My json-c-0.15 folder is not called "lib", but "lib64".

Is it necessary to make a judgment here, for 32-bit and 64-bit?

When I modify 
`JSON_LDFLAGS="-L$JSONDIR/lib"`==>`JSON_LDFLAGS="-L$JSONDIR/lib64"` everything 
works fine.

 

Thanks,

Wang Dapeng

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

[postgis-users] pgRouting Code Sprint 2023 in Boston October 27th - October 29th

[Regina Obe]

The pgRouting Code Sprint is going to be held in Person in Boston from
October 27th through October 29th

If you'd like to attend or Sponsor, please add you name / Company to the
list on the Wiki page

https://wiki.osgeo.org/wiki/PgRouting_Code_Sprint_2023_Boston#In_Person


Thanks,
Regina Obe
pgRouting and PostGIS PSC Member

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

Re: [postgis-users] ArcGIS Online without ArcGIS server

[Regina Obe]

Thanks everyone who chimed in on this.  It’s sad to hear but it is what it is.

 

From: postgis-users  On Behalf Of 
Marcelo Marques
Sent: Friday, September 15, 2023 1:59 PM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] ArcGIS Online without ArcGIS server

 

Hello, 

The answer is "No". ArcGIS Online is not able to connect to a PostGIS enabled 
database directly.


ArcGIS Online is a SAS Software As a Service, and you need to store your data 
into the ArcGIS Online Feature Data Store.

 

Data Management in ArcGIS Online - 
https://doc.arcgis.com/en/arcgis-online/manage-data/data-in-online.htm

 

FAQ ArcGIS Online - https://doc.arcgis.com/en/arcgis-online/reference/faq.htm


However as mentioned ArcGIS Online allows you to Add a Service from a URL.

 

https://doc.arcgis.com/en/arcgis-online/manage-data/add-item-from-url.htm

 

This means if you have an ArcGIS Geodatabase on PostgreSQL you can store the 
spatial data with esri st_geometry or with postgis pg_geometry.

 

Then with ArcGIS Server you can publish a Map Service or Feature Service using 
the layer or layers from the PostgreSQL Geodatabase.

 

Then, you can use the Map Service / Feature Service URL with ArcGIS Online.

 

The ArcGIS Server VM and PostgreSQL Geodatabase can reside in the Cloud, e.g. 
Amazon or Azure.

I hope this clarifies.

 

Thanks,

 

| Marcelo Marques | Esri PS Products | Principal Product Engineer |  

| Cloud & Database Administrator | OCP – Oracle Certified Professional |

| Esri | 380 New York St | Redlands, CA 92373 | USA |

| T +909-369-1787 | M +909-255-2079 |  <http://esri.com/> esri.com |

 | Esri Enterprise Mapping and Charting Databases Best Practices 
<https://community.esri.com/groups/mapping-and-charting-enterprise-databases/blog/2014/12/18/enterprise-mcs-databases-best-practices>
  |

|  <https://www.linkedin.com/in/mmarquesbr/> 
https://www.linkedin.com/in/mmarquesbr/ |

 

 

On Fri, Sep 15, 2023 at 9:20 AM Phil Bartie mailto:philbar...@gmail.com> > wrote:

ArcGIS online supports WMS, WMTS, and WFS OGC web services. 

 

https://doc.arcgis.com/en/arcgis-online/manage-data/add-item-from-url.htm

 

Best,

Phil

 

On Fri, 15 Sept 2023 at 16:43, Martin Davis mailto:mtncl...@gmail.com> > wrote:

It's my educated guess that the answer is no.  The AGOL client talks via web 
services to the backend, so it can't talk directly to a database server.

 

It *might* be able to talk to a non-AGOL web service (probably using OGC 
protocols)?

 

On Fri, Sep 15, 2023 at 8:26 AM Regina Obe mailto:l...@pcorp.us> > wrote:

This may be a question to just people using ArcGIS Online or at least
familiar with it.  Is it possible to have an ArcGIS online connect to a
PostGIS enabled database (assuming you have the port open etc), without an
ArcGIS Server license?
If so are their limitations in terms of what features you can use of ArcGIS
Online like their javascript apis etc?

Thanks,
Regina

___
postgis-users mailing list
postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@lists.osgeo.org <mailto: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

[postgis-users] ArcGIS Online without ArcGIS server

[Regina Obe]

This may be a question to just people using ArcGIS Online or at least
familiar with it.  Is it possible to have an ArcGIS online connect to a
PostGIS enabled database (assuming you have the port open etc), without an
ArcGIS Server license?
If so are their limitations in terms of what features you can use of ArcGIS
Online like their javascript apis etc?

Thanks,
Regina

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

Re: [postgis-users] generate a geometry column of random point, line and polygon

[Regina Obe]

You need to do 

 

CREATE EXTENSION postgis_sfcgal;

 

It’s not part of the postgis extension.  If you don’t have that extension, then 
you can’t use this function.

 

 

You next best bet is using  ST_ConcaveHull

 

https://postgis.net/docs/ST_ConcaveHull.html

 

 

From: postgis-users  On Behalf Of 
Shaozhong SHI
Sent: Monday, September 11, 2023 3:36 AM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] generate a geometry column of random point, line 
and polygon

 

Hi, Regina,

 

It did not work.

ERROR: function st_optimalalphashape(geometry) does not exist LINE 1: SELECT 
min(gs) , max(gs), st_optimalalphashape(st_collect(S... ^ HINT: No function 
matches the given name and argument types. You might need to add explicit type 
casts. SQL state: 42883 Character: 28

 

On Mon, 11 Sept 2023 at 08:31, Regina Obe mailto:l...@pcorp.us> 
> wrote:

You are using the wrong version of ST_Collect.  You want to use the aggregate 
form.  You are using the two point form  - 
https://postgis.net/docs/en/ST_Collect.html

 

Also when you aggregate, you can’t include the gs in there.

 

Try: 

 

SELECT  min(gs) , max(gs), 
st_optimalalphashape(st_collect(ST_MakePoint(random()*10,random()*10)) )

from generate_series(1,100) gs;

 

From: postgis-users mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Shaozhong SHI
Sent: Monday, September 11, 2023 3:21 AM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] generate a geometry column of random point, line 
and polygon

 

I tested St_optimalphashape and it does not work.

 

select gs, 
st_optimalalphashape(st_collect(ST_MakePoint(random()*10,random()*10),ST_MakePoint(random()*10,random()*10))::geometry)

from generate_series(1,100) gs;

 

Example given here does not work either.

ST_OptimalAlphaShape (postgis.net) 
<https://postgis.net/docs/en/ST_OptimalAlphaShape.html> 

 

Regards,

 

David

 

On Fri, 8 Sept 2023 at 16:16, Regina Obe mailto:l...@pcorp.us> 
> wrote:

David,

 

The example query in the docs, is as simple as it gets.  It’s a self-contained 
example you can just run, but it does return a multipoint and I realize now the 
docs don’t make it clear ST_GeneratePoints returns a single geometry that is a 
multipoint.

 

IF you want individual points, you’d combine with ST_DumpPoints.

I’ll add such an example to the docs.

 

So here is an example you can apply to a table of polygons

 

For polygons you can do something like below where p is the table name and geom 
is the polygon column.

This will generate 100 random points for each polygon

 

 

SELECT p.id <http://p.id> , dp.path[1], dp.geom 

FROM p, ST_DumpPoints(ST_GeneratePoints(p.geom, 100)) AS dp;

 

The p.id <http://p.id>  and path I just threw in cause I find them useful, but 
you could leave them out. 

 

 

Here is a self-contained using the example table in docs:

 

WITH p AS (  SELECT 1 AS id, ST_Buffer(

ST_GeomFromText(

'LINESTRING(50 50,150 150,150 50)'),

10, 'endcap=round join=round') AS geom)

SELECT p.id <http://p.id> , dp.path[1], dp.geom 

FROM p, ST_DumpPoints(ST_GeneratePoints(p.geom, 100)) AS dp;

 

 

From: postgis-users mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Shaozhong SHI
Sent: Friday, September 8, 2023 10:54 AM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] generate a geometry column of random point, line 
and polygon

 

The simplicity is beautiful.  Any simple script to do so?  I think that will be 
an useful addition.

Regards,

David

On Friday, 8 September 2023, Regina Obe mailto:l...@pcorp.us> > 
wrote:

David,

 

For this are you needing to generate random points from a line or polygon or 
are you trying to generate random polygons, lines, and points?

 

If you need to generate random points from a polygon:

 

Use ST_GeneratePoints:  https://postgis.net/docs/en/ST_GeneratePoints.html

Note there is a option seed argument, that will give you the same exact answer 
if you give it the same seed, but without that the generated points will be 
different each time.

 

As I recall, ST_GeneratePoints only works with areals so won’t work with a 
line, however you can buffer a line very thinly to do the same. Use a flat 
buffer:

 

https://postgis.net/docs/en/ST_Buffer.html

 

SELECT ST_GeneratePoints(ST_Buffer(

ST_GeomFromText(

  'LINESTRING(50 50,150 150,150 50)'

), 0.5, 'endcap=square join=round'), 1000);

 

 

If you want to generate random polygons, you could use ST_ConcaveHull or 
ST_AlphaShape around the section of a polygon you did a ST_GeneratePoints on

 

And then use something like https://postgis.net/docs/en/ST_Subdivide.html to 
chop up the polygons.

 

To get a linestring out of that (It will be closed), you can take the boundary 
of any of the above

 

https://postgis.net/docs/ST_Boundary.html

 

Hope tha

Re: [postgis-users] generate a geometry column of random point, line and polygon

[Regina Obe]

You are using the wrong version of ST_Collect.  You want to use the aggregate 
form.  You are using the two point form  - 
https://postgis.net/docs/en/ST_Collect.html

 

Also when you aggregate, you can’t include the gs in there.

 

Try: 

 

SELECT  min(gs) , max(gs), 
st_optimalalphashape(st_collect(ST_MakePoint(random()*10,random()*10)) )

from generate_series(1,100) gs;

 

From: postgis-users  On Behalf Of 
Shaozhong SHI
Sent: Monday, September 11, 2023 3:21 AM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] generate a geometry column of random point, line 
and polygon

 

I tested St_optimalphashape and it does not work.

 

select gs, 
st_optimalalphashape(st_collect(ST_MakePoint(random()*10,random()*10),ST_MakePoint(random()*10,random()*10))::geometry)

from generate_series(1,100) gs;

 

Example given here does not work either.

ST_OptimalAlphaShape (postgis.net) 
<https://postgis.net/docs/en/ST_OptimalAlphaShape.html> 

 

Regards,

 

David

 

On Fri, 8 Sept 2023 at 16:16, Regina Obe mailto:l...@pcorp.us> 
> wrote:

David,

 

The example query in the docs, is as simple as it gets.  It’s a self-contained 
example you can just run, but it does return a multipoint and I realize now the 
docs don’t make it clear ST_GeneratePoints returns a single geometry that is a 
multipoint.

 

IF you want individual points, you’d combine with ST_DumpPoints.

I’ll add such an example to the docs.

 

So here is an example you can apply to a table of polygons

 

For polygons you can do something like below where p is the table name and geom 
is the polygon column.

This will generate 100 random points for each polygon

 

 

SELECT p.id <http://p.id> , dp.path[1], dp.geom 

FROM p, ST_DumpPoints(ST_GeneratePoints(p.geom, 100)) AS dp;

 

The p.id <http://p.id>  and path I just threw in cause I find them useful, but 
you could leave them out. 

 

 

Here is a self-contained using the example table in docs:

 

WITH p AS (  SELECT 1 AS id, ST_Buffer(

ST_GeomFromText(

'LINESTRING(50 50,150 150,150 50)'),

10, 'endcap=round join=round') AS geom)

SELECT p.id <http://p.id> , dp.path[1], dp.geom 

FROM p, ST_DumpPoints(ST_GeneratePoints(p.geom, 100)) AS dp;

 

 

From: postgis-users mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Shaozhong SHI
Sent: Friday, September 8, 2023 10:54 AM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] generate a geometry column of random point, line 
and polygon

 

The simplicity is beautiful.  Any simple script to do so?  I think that will be 
an useful addition.

Regards,

David

On Friday, 8 September 2023, Regina Obe mailto:l...@pcorp.us> > 
wrote:

David,

 

For this are you needing to generate random points from a line or polygon or 
are you trying to generate random polygons, lines, and points?

 

If you need to generate random points from a polygon:

 

Use ST_GeneratePoints:  https://postgis.net/docs/en/ST_GeneratePoints.html

Note there is a option seed argument, that will give you the same exact answer 
if you give it the same seed, but without that the generated points will be 
different each time.

 

As I recall, ST_GeneratePoints only works with areals so won’t work with a 
line, however you can buffer a line very thinly to do the same. Use a flat 
buffer:

 

https://postgis.net/docs/en/ST_Buffer.html

 

SELECT ST_GeneratePoints(ST_Buffer(

ST_GeomFromText(

  'LINESTRING(50 50,150 150,150 50)'

), 0.5, 'endcap=square join=round'), 1000);

 

 

If you want to generate random polygons, you could use ST_ConcaveHull or 
ST_AlphaShape around the section of a polygon you did a ST_GeneratePoints on

 

And then use something like https://postgis.net/docs/en/ST_Subdivide.html to 
chop up the polygons.

 

To get a linestring out of that (It will be closed), you can take the boundary 
of any of the above

 

https://postgis.net/docs/ST_Boundary.html

 

Hope that helps,

Regina

 

From: postgis-users mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Shaozhong SHI
Sent: Friday, September 8, 2023 7:56 AM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: [postgis-users] generate a geometry column of random point, line and 
polygon

 

Is a simple way to do this?

 

Regards,

 

David

___
postgis-users mailing list
postgis-users@lists.osgeo.org <mailto: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] generate a geometry column of random point, line and polygon

[Regina Obe]

David,

 

The example query in the docs, is as simple as it gets.  It’s a self-contained 
example you can just run, but it does return a multipoint and I realize now the 
docs don’t make it clear ST_GeneratePoints returns a single geometry that is a 
multipoint.

 

IF you want individual points, you’d combine with ST_DumpPoints.

I’ll add such an example to the docs.

 

So here is an example you can apply to a table of polygons

 

For polygons you can do something like below where p is the table name and geom 
is the polygon column.

This will generate 100 random points for each polygon

 

 

SELECT p.id, dp.path[1], dp.geom 

FROM p, ST_DumpPoints(ST_GeneratePoints(p.geom, 100)) AS dp;

 

The p.id and path I just threw in cause I find them useful, but you could leave 
them out. 

 

 

Here is a self-contained using the example table in docs:

 

WITH p AS (  SELECT 1 AS id, ST_Buffer(

ST_GeomFromText(

'LINESTRING(50 50,150 150,150 50)'),

10, 'endcap=round join=round') AS geom)

SELECT p.id, dp.path[1], dp.geom 

FROM p, ST_DumpPoints(ST_GeneratePoints(p.geom, 100)) AS dp;

 

 

From: postgis-users  On Behalf Of 
Shaozhong SHI
Sent: Friday, September 8, 2023 10:54 AM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] generate a geometry column of random point, line 
and polygon

 

The simplicity is beautiful.  Any simple script to do so?  I think that will be 
an useful addition.

Regards,

David

On Friday, 8 September 2023, Regina Obe mailto:l...@pcorp.us> > 
wrote:

David,

 

For this are you needing to generate random points from a line or polygon or 
are you trying to generate random polygons, lines, and points?

 

If you need to generate random points from a polygon:

 

Use ST_GeneratePoints:  https://postgis.net/docs/en/ST_GeneratePoints.html

Note there is a option seed argument, that will give you the same exact answer 
if you give it the same seed, but without that the generated points will be 
different each time.

 

As I recall, ST_GeneratePoints only works with areals so won’t work with a 
line, however you can buffer a line very thinly to do the same. Use a flat 
buffer:

 

https://postgis.net/docs/en/ST_Buffer.html

 

SELECT ST_GeneratePoints(ST_Buffer(

ST_GeomFromText(

  'LINESTRING(50 50,150 150,150 50)'

), 0.5, 'endcap=square join=round'), 1000);

 

 

If you want to generate random polygons, you could use ST_ConcaveHull or 
ST_AlphaShape around the section of a polygon you did a ST_GeneratePoints on

 

And then use something like https://postgis.net/docs/en/ST_Subdivide.html to 
chop up the polygons.

 

To get a linestring out of that (It will be closed), you can take the boundary 
of any of the above

 

https://postgis.net/docs/ST_Boundary.html

 

Hope that helps,

Regina

 

From: postgis-users mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Shaozhong SHI
Sent: Friday, September 8, 2023 7:56 AM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: [postgis-users] generate a geometry column of random point, line and 
polygon

 

Is a simple way to do this?

 

Regards,

 

David

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

Re: [postgis-users] generate a geometry column of random point, line and polygon

[Regina Obe]

David,

 

For this are you needing to generate random points from a line or polygon or 
are you trying to generate random polygons, lines, and points?

 

If you need to generate random points from a polygon:

 

Use ST_GeneratePoints:  https://postgis.net/docs/en/ST_GeneratePoints.html

Note there is a option seed argument, that will give you the same exact answer 
if you give it the same seed, but without that the generated points will be 
different each time.

 

As I recall, ST_GeneratePoints only works with areals so won’t work with a 
line, however you can buffer a line very thinly to do the same. Use a flat 
buffer:

 

https://postgis.net/docs/en/ST_Buffer.html

 

SELECT ST_GeneratePoints(ST_Buffer(

ST_GeomFromText(

  'LINESTRING(50 50,150 150,150 50)'

), 0.5, 'endcap=square join=round'), 1000);

 

 

If you want to generate random polygons, you could use ST_ConcaveHull or 
ST_AlphaShape around the section of a polygon you did a ST_GeneratePoints on

 

And then use something like https://postgis.net/docs/en/ST_Subdivide.html to 
chop up the polygons.

 

To get a linestring out of that (It will be closed), you can take the boundary 
of any of the above

 

https://postgis.net/docs/ST_Boundary.html

 

Hope that helps,

Regina

 

From: postgis-users  On Behalf Of 
Shaozhong SHI
Sent: Friday, September 8, 2023 7:56 AM
To: PostGIS Users Discussion 
Subject: [postgis-users] generate a geometry column of random point, line and 
polygon

 

Is a simple way to do this?

 

Regards,

 

David

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

Re: [postgis-users] Is there a simple way to generate polygon from random points?

[Regina Obe]

David,

 

There are some examples in the manual.  There is a ST_ConcaveHull function and 
an ST_AlphaShape.  Both do essentially the same thing but use different engines.

 

Part of postgis extension, uses GEOS under the hood

https://postgis.net/docs/en/ST_ConcaveHull.html

 

Part of postgis_sfcgal – uses CGAL under the hood.

The ST_AlphaShape one is only available if you have postgis_sfcgal extension 
installed.

 

https://postgis.net/docs/en/ST_AlphaShape.html

 

 

To Barend,

 

Thanks for your consideration of our time, but I think it’s better for people 
to not fear asking questions and worry the question they are asking is 
something they should have looked up in the manual.  I’d rather they ask, and 
we point them to the section of the manual where it is detailed.

 

This mailing list isn’t so crowded that we can’t simply ignore questions we’d 
rather not bother answering.  David asks very useful questions that I think a 
lot of people could benefit from searching our mail list archives for and that 
to me is a great contribution, so please don’t discourage him.

 

Thanks,

Regina

 

 

 

From: postgis-users  On Behalf Of 
Kobben, Barend (UT-ITC) via postgis-users
Sent: Friday, September 8, 2023 9:57 AM
To: PostGIS Users Discussion 
Cc: Kobben, Barend (UT-ITC) 
Subject: Re: [postgis-users] Is there a simple way to generate polygon from 
random points?

 

Really Shaozhong Shi (David)...? After all these years of open questions 
without any focus you cannot even lookup the functions the respondent mentioned 
yourself, in the excellent PostGIS  manual...?

 

Please have some consideration for the people putting in their time to help 
others. RTFM.

-- 

Barend Köbben

 

 

On 08/09/2023, 15:37, "postgis-users" mailto:postgis-users-boun...@lists.osgeo.org> > wrote:


Is there a sample script?

Regards，

David

On Friday, 8 September 2023, Erik Wienhold mailto:e...@ewie.name> > wrote:

On 08/09/2023 13:34 CEST Shaozhong SHI mailto:shishaozh...@gmail.com> > wrote:

> Is there a simple way to generate polygon from random points?

Compute the convex or concave hull or an alpha shape.  PostGIS provides
functions for all three of them.

--
Erik

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

Re: [postgis-users] Postgis_full_version(): WAGYU="0.5.0 (Internal)" (core procs from "3.3.4 3.3.4" need upgrade)

[Regina Obe]

I don’t think the complain is about WAGYU. That version info just happens to 
sit next to the message.

 

Have you tried doing

 

SELECT postgis_extensions_upgrade();

 

Already and what does it say?

 

From: postgis-users  On Behalf Of Bo Guo
Sent: Thursday, September 7, 2023 4:52 PM
To: PostGIS Users Discussion 
Subject: [postgis-users] Postgis_full_version(): WAGYU="0.5.0 (Internal)" (core 
procs from "3.3.4 3.3.4" need upgrade)

 

Hi there,

 

I am trying to get all three environments to have the same version of Postgis.  

 

Could someone on the list provide any advice on how I may update the WAGYU core 
procs on one of the servers to match with the rest?

 

I appreciate your help!

 

Cheers,

 

Bo   

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

Re: [postgis-users] ERROR: extension "postgis" has no update path from version "3.2.1" to version "3.2.0"

[Regina Obe]

No.  Well not advisable.  People have run into a lot of issues, primarily 
around PROJ dependency.

The other reason why you might not want to is because most PostGIS dependency 
checks are done at runtime.

This isn’t an issue for GDAL so much, but more for GEOS.

 

So even though UbuntuGIS generally ships with a newer GEOS than main ubuntu 
repos, just because it says say GEOS 3.11, PostGIS will still only allow you to 
use functions allowed for the GEOS that apt postgresql org was compiled with.

 

 

 

From: postgis-users  On Behalf Of 
Gandalf the Gray
Sent: Wednesday, September 6, 2023 6:38 AM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] ERROR: extension "postgis" has no update path from 
version "3.2.1" to version "3.2.0"

 

Hi guys

 

Sorry to hijack this thread a bit, but my question is very similar. 

 

Is it possible to use PostGIS from the postgresql apt repo, while using gdal 
etc (for qgis and grass) from ubuntugis-unstable ppa?

 

Pieter

 

On Tue, Sep 5, 2023 at 4:00 PM Bo Guo mailto:bo@gisticinc.com> > wrote:

Hi, Regina!  I solved the problem.

 

It turned out I missed the step of "Adds pgdg keys and main repo" as referenced 
in https://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS3UbuntuPGSQLApt  

 

Thank you again!

 

Bo

 

On Tue, Sep 5, 2023 at 5:52 AM Bo Guo mailto:bo@gisticinc.com> > wrote:

Hi, Regina!

 

I do not know why the new stall Postgresql 14 with Postgis-3 on the newly built 
Ubuntu server 22.04 would have Postgis 3.2.0.  This was the command I used for 
the postgis install:

   sudo apt install postgresql-14-postgis-3

 

I went to the postgis folder (/usr/share/postgresql/14/extension) but could 
ONLY see files with version of 3.2.0 and lower.  So it appeared that the 
installation did not bring postgis to the 3.2.1 or above.  Please let me know 
alternative ways to install higher version.

 

Cheers,

 

Bo

 

 

On Mon, Sep 4, 2023 at 5:06 PM Regina Obe mailto:l...@pcorp.us> 
> wrote:

Bo,

 

At a glance, it looks like maybe your PostgreSQL 13 had a 3.2.1 installed and 
your PostgreSQL 14 has 3.2.0 installed (or at least the postgis.control file 
probably has 3.2.0 instead of 3.2.1).

I’d double check to make sure you did install 3.2.1 on your PostgreSQL 14.  
Downgrades are not supported which is essentially what it’s telling you.

 

 

That said, the preferable way for upgrading PostGIS 3 is to use:

 

SELECT postgis_extensions_upgrade();

 

Though that will probably result in the same error since it seems your 
PostgreSQL 14 is running with 3.2.0 instead of 3.2.1.

 

The ALTER EXTENSION approach has a couple of downsides, that 
postgis_extensions_upgrade can handle

 

1.  It will only update postgis and not your other extensions like for 
example your postgis_topology.  Postgis_extensions_upgrade()  will upgrade any 
postgis_ extension.
2.  If you are moving from a PostgreSQL major to another PostgreSQL major 
on same version, you still need to upgrade, but because  ALTER EXTENSION relies 
on a version bump, it won’t be able to upgrade.

 

If you still see a mismatch after please let us know.  I know we’ve made 
improvements in postgis_extensions_upgrade so 3.2.0 might not have all the new 
features.

 

Thanks,

Regina

 

From: postgis-users mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Bo Guo
Sent: Monday, September 4, 2023 7:40 PM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: [postgis-users] ERROR: extension "postgis" has no update path from 
version "3.2.1" to version "3.2.0"

 

Hi all,

 

I do not know how it happened, but after I upgraded from Postgresql 13- 14, I 
had a problem updating extensions, including postgis by using: 

 

ALTER EXTENSION "postgis" UPDATE; 

 

Here is the version info:

 

SELECT version();

PostgreSQL 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled 
by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

 

SELECT postgis_full_version();

POSTGIS="3.2.0 c3e3cc0" [EXTENSION] PGSQL="130" (procs need upgrade for use 
with PostgreSQL "140") GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.12" 
LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" (core procs from 
"3.2.1 5fae8e5" need upgrade) TOPOLOGY (topology procs from "3.2.1 5fae8e5" 
need upgrade)

 

I really appreciate any help you can provide.

 

Bo

 

 

Bo Guo, PhD, PE
President
  <https://gisticinc.com/wp-content/uploads/2019/03/GLogoSquareName.png> 
2820 S Alma School Rd #18-671, Chandler, AZ 85286

p 480-656-9962 c 602-570-4697
www.gisticinc.com <http://www.gisticinc.com> 

youtube.com/linearbench <http://youtube.com/linearbench> 

 

___
postgis-users maili

Re: [postgis-users] ERROR: extension "postgis" has no update path from version "3.2.1" to version "3.2.0"

[Regina Obe]

Where did you get your ubuntu package.  Usually I get from apt.postgresql.org 
but I think for 14 the default for that is newer than 3.2 and even then 3.2.0 
and 3.2.1 are very old micros.

Latest micro for PostGIS 3.2 is postgis-3.2.5.

 

Did you run :

 

apt update && apt upgrade

 

before installing?

 

If that doesn’t help then it’s a packaging issue that you should talk with your 
packager group about.

 

 

 

From: postgis-users  On Behalf Of Bo Guo
Sent: Tuesday, September 5, 2023 8:53 AM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] ERROR: extension "postgis" has no update path from 
version "3.2.1" to version "3.2.0"

 

Hi, Regina!

 

I do not know why the new stall Postgresql 14 with Postgis-3 on the newly built 
Ubuntu server 22.04 would have Postgis 3.2.0.  This was the command I used for 
the postgis install:

   sudo apt install postgresql-14-postgis-3

 

I went to the postgis folder (/usr/share/postgresql/14/extension) but could 
ONLY see files with version of 3.2.0 and lower.  So it appeared that the 
installation did not bring postgis to the 3.2.1 or above.  Please let me know 
alternative ways to install higher version.

 

Cheers,

 

Bo

 

 

On Mon, Sep 4, 2023 at 5:06 PM Regina Obe mailto:l...@pcorp.us> 
> wrote:

Bo,

 

At a glance, it looks like maybe your PostgreSQL 13 had a 3.2.1 installed and 
your PostgreSQL 14 has 3.2.0 installed (or at least the postgis.control file 
probably has 3.2.0 instead of 3.2.1).

I’d double check to make sure you did install 3.2.1 on your PostgreSQL 14.  
Downgrades are not supported which is essentially what it’s telling you.

 

 

That said, the preferable way for upgrading PostGIS 3 is to use:

 

SELECT postgis_extensions_upgrade();

 

Though that will probably result in the same error since it seems your 
PostgreSQL 14 is running with 3.2.0 instead of 3.2.1.

 

The ALTER EXTENSION approach has a couple of downsides, that 
postgis_extensions_upgrade can handle

 

1.  It will only update postgis and not your other extensions like for 
example your postgis_topology.  Postgis_extensions_upgrade()  will upgrade any 
postgis_ extension.
2.  If you are moving from a PostgreSQL major to another PostgreSQL major 
on same version, you still need to upgrade, but because  ALTER EXTENSION relies 
on a version bump, it won’t be able to upgrade.

 

If you still see a mismatch after please let us know.  I know we’ve made 
improvements in postgis_extensions_upgrade so 3.2.0 might not have all the new 
features.

 

Thanks,

Regina

 

From: postgis-users mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Bo Guo
Sent: Monday, September 4, 2023 7:40 PM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: [postgis-users] ERROR: extension "postgis" has no update path from 
version "3.2.1" to version "3.2.0"

 

Hi all,

 

I do not know how it happened, but after I upgraded from Postgresql 13- 14, I 
had a problem updating extensions, including postgis by using: 

 

ALTER EXTENSION "postgis" UPDATE; 

 

Here is the version info:

 

SELECT version();

PostgreSQL 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled 
by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

 

SELECT postgis_full_version();

POSTGIS="3.2.0 c3e3cc0" [EXTENSION] PGSQL="130" (procs need upgrade for use 
with PostgreSQL "140") GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.12" 
LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" (core procs from 
"3.2.1 5fae8e5" need upgrade) TOPOLOGY (topology procs from "3.2.1 5fae8e5" 
need upgrade)

 

I really appreciate any help you can provide.

 

Bo

 

 

Bo Guo, PhD, PE
President
  <https://gisticinc.com/wp-content/uploads/2019/03/GLogoSquareName.png> 
2820 S Alma School Rd #18-671, Chandler, AZ 85286

p 480-656-9962 c 602-570-4697
www.gisticinc.com <http://www.gisticinc.com> 

youtube.com/linearbench <http://youtube.com/linearbench> 

 

___
postgis-users mailing list
postgis-users@lists.osgeo.org <mailto: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] ERROR: extension "postgis" has no update path from version "3.2.1" to version "3.2.0"

[Regina Obe]

Bo,

 

At a glance, it looks like maybe your PostgreSQL 13 had a 3.2.1 installed and 
your PostgreSQL 14 has 3.2.0 installed (or at least the postgis.control file 
probably has 3.2.0 instead of 3.2.1).

I’d double check to make sure you did install 3.2.1 on your PostgreSQL 14.  
Downgrades are not supported which is essentially what it’s telling you.

 

 

That said, the preferable way for upgrading PostGIS 3 is to use:

 

SELECT postgis_extensions_upgrade();

 

Though that will probably result in the same error since it seems your 
PostgreSQL 14 is running with 3.2.0 instead of 3.2.1.

 

The ALTER EXTENSION approach has a couple of downsides, that 
postgis_extensions_upgrade can handle

 

1.  It will only update postgis and not your other extensions like for 
example your postgis_topology.  Postgis_extensions_upgrade()  will upgrade any 
postgis_ extension.
2.  If you are moving from a PostgreSQL major to another PostgreSQL major 
on same version, you still need to upgrade, but because  ALTER EXTENSION relies 
on a version bump, it won’t be able to upgrade.

 

If you still see a mismatch after please let us know.  I know we’ve made 
improvements in postgis_extensions_upgrade so 3.2.0 might not have all the new 
features.

 

Thanks,

Regina

 

From: postgis-users  On Behalf Of Bo Guo
Sent: Monday, September 4, 2023 7:40 PM
To: PostGIS Users Discussion 
Subject: [postgis-users] ERROR: extension "postgis" has no update path from 
version "3.2.1" to version "3.2.0"

 

Hi all,

 

I do not know how it happened, but after I upgraded from Postgresql 13- 14, I 
had a problem updating extensions, including postgis by using: 

 

ALTER EXTENSION "postgis" UPDATE; 

 

Here is the version info:

 

SELECT version();

PostgreSQL 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled 
by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

 

SELECT postgis_full_version();

POSTGIS="3.2.0 c3e3cc0" [EXTENSION] PGSQL="130" (procs need upgrade for use 
with PostgreSQL "140") GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.12" 
LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" (core procs from 
"3.2.1 5fae8e5" need upgrade) TOPOLOGY (topology procs from "3.2.1 5fae8e5" 
need upgrade)

 

I really appreciate any help you can provide.

 

Bo

 

 

Bo Guo, PhD, PE
President
   
2820 S Alma School Rd #18-671, Chandler, AZ 85286

p 480-656-9962 c 602-570-4697
www.gisticinc.com  

youtube.com/linearbench  

 

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

Re: [postgis-users] Weblate closed registration

[Regina Obe]

Weblate.osgeo.org  requires an osgeo account.

 

Which you can get here:

 

https://id.osgeo.org/ldap/create

 

I sent you the mantra code offlist so you don’t need to fill in the top form.

 

We don’t publicize the mantra because we’ve gotten a lot of spam bots in the 
past signup and that account gets you into a lot of places in the OSGeo 
infrastructure.

 

You can use it to:

 

1.  File bug reports in PostGIS bug tracker https://trac.osgeo.org/postgis
2.  Edit OSGeo translations on https://weblate.osgeo.org (this is currently 
PostGIS, postgis-workshop, GRASS, pgrouting, QGIS, and OSGeoLive)
3.  Create a profile for yourself on the www.osgeo.org 
 
4.  Edit our wiki:  https://wiki.osgeo.org (though we are having some 
issues with this necessitating SAC team (which I am a member of) to fix 
accounts when someone first tries to log in
5.  Publish QGIS plugins
6.  Store files on our nextcloud instance -- https://nextcloud.osgeo.org/
7.  Store videos on our PeerTube instance https://video.osgeo.org
8.  Login into matrix using your osgeo account at – 
https://matrix.osgeo.org (you need to specify that as your home server instead 
of the default matrix.org)

 

I’ll look at your pull requests shortly.

 

Thanks,

Regina

From: postgis-users  On Behalf Of Light
Sent: Thursday, August 31, 2023 11:00 PM
To: postgis-users@lists.osgeo.org
Subject: [postgis-users] Weblate closed registration

 

How should I register for Weblate?
I submitted 2 PRs about Chinese translation.

Thanks.
Wangdapeng

___
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

[Regina Obe]

Imre,

Wow that’s a great summary covers Azure whole Geospatial space 

 

David,

 

I think Imre has provided you with a good starting point to ask your questions 
and to Greg’s point, many of these

questions you’ll want to ask on Azure social systems.

 

We just happened to be a much nicer group of people than all of those 

 

Thanks,

Regina

 

From: postgis-users  On Behalf Of Imre 
Samu
Sent: Wednesday, August 30, 2023 10:02 AM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] PostGIS verse those in Azure Cloud

 

> As compared with PostGIS spatial operations, what are the advantages and 
> disadvantages 

> of various systems in Azure Cloud in terms of supporting further development.

 

see: 
https://learn.microsoft.com/en-us/azure/orbital/geospatial-reference-architecture

 

The Azure Cloud has many features related to GeoSpatial. 

One small part of this is "Azure Database for PostgreSQL (+ PostGIS)."

However, migrating to Azure Cloud can mean different things. 
If someone specifically means MS SQL Server, then it's not ideal for PostGIS.   
:-(

Regards,
 Imre



 

Shaozhong SHI mailto:shishaozh...@gmail.com> > ezt 
írta (időpont: 2023. aug. 30., Sze, 14:31):

I am trying to review the underlying geospatial capability of these systems as 
the geospatial capability maturity have been developed unevenly.  I am 
interested in the following:

 

As compared with PostGIS spatial operations, what are the advantages and 
disadvantages of various systems in Azure Cloud in terms of supporting further 
development.

 

In my mind and from my experience, PostGIS has a lot of useful underlying 
functions that support development of other applications.

 

My initial glance of what other systems offer in the Azure, the potential for 
this is not great.

 

I would like to find information on this and confirm or reject this 
understanding.

 

Secondary programming/development relies on what the underlying functionality 
of a system provides.   Add-on development is based on the underlying 
technology offered.  From my experience, PostGIS offers a versatile range of 
functions and capability.  This is important to any other add-on development 
for forming robust and useful geospatial applications.

 

Am I right?

 

Regards,

 

David

 

On Wed, 30 Aug 2023 at 01:31, David Haynes mailto:hayne...@gmail.com> > wrote:

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 mailto:shishaozh...@gmail.com> > 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

___
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] Stuck with upgrade from postgresql 9.6 to postgresql 14 due to postgis2.3

[Regina Obe]

Marcelius,

 

So you installed PostGIS from scripts right? 

I think your best bet is to upgrade your PostgreSQL 9.6 using scripts

 

1) Get a PostGIS 3.0  or higher for PostgreSQL 9.6 (it can be from a yum repo 
or compiled yourself).  Preferable a PostGIS 3.1 (I think 3.1 is the last 
version supported for PostgreSQL 9.6) if you can get it.

2) Run the postgis_upgrade.sql  script, which should be in the 
contrib/postgis-  folder of your install 

As detailed here: 
https://postgis.net/docs/postgis_administration.html#soft_upgrade_sql_script

 

You can call pg_config --sharedir

To figure out the path of the shared folder if you don’t know it.

 

You may need to run the raster and other upgrade scripts too. To confirm do 
something like

 

SELECT * FROM raster_columns;

 

If that doesn’t error out, you have raster support in there too that needs to 
be upgraded

 

3) Once you’ve upgraded your PostgreSQL 9.6 using the scripts, run

 

SELECT postgis_extensions_upgrade();

 

That should if you are running PostGIS 3+ or higher to an extension based 
install.

 

Sandro, remember which version of this we started flipping everyone to 
extension based install?  I think 3.1 was after that time

 

Hope that helps,

Regina

 

From: postgis-users  On Behalf Of atanga 
MARCELIUS via postgis-users
Sent: Wednesday, August 30, 2023 5:51 AM
To: PostGIS Users Discussion ; Sandro Santilli 

Cc: atanga MARCELIUS 
Subject: Re: [postgis-users] Stuck with upgrade from postgresql 9.6 to 
postgresql 14 due to postgis2.3

 

Hello Sandro, Thanks for your support.

 Please here are the out put to the queries you suggested.

I have also attached the output in text document in a clearer format.

My main concern here is how to  manage the upgrade path from postgis 2.3 in 
postgresql 9.6  to  a compactible version of postgis  in postgresql14  given 
that postgis 2.3 was hard install(using source code) and given my environmental 
challenges I do not want to do a dump and restore?

 


1.

postgres=# SELECT postgis_full_version();


postgis_full_version   

 

---

-

 POSTGIS="2.3.7 r16523" PGSQL="100" GEOS="3.9.2-CAPI-1.14.3" PROJ="Rel. 4.9.3, 
15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJS

ON="0.11" RASTER

(1 row)

 

2.

postgres=# SELECT oid::regprocedure,probin

  FROM pg_catalog.pg_proc

  WHERE proname LIKE 'postgis_%version';

 oid  |probin 

--+---

 postgis_version()| $libdir/postgis-2.3

 postgis_liblwgeom_version()  | $libdir/postgis-2.3

 postgis_proj_version()   | $libdir/postgis-2.3

 postgis_lib_version()| $libdir/postgis-2.3

 postgis_geos_version()   | $libdir/postgis-2.3

 postgis_svn_version()| $libdir/postgis-2.3

 postgis_libxml_version() | $libdir/postgis-2.3

 postgis_full_version()   | 

 postgis_libjson_version()| $libdir/postgis-2.3

 postgis_raster_lib_version() | $libdir/rtpostgis-2.3

 postgis_gdal_version()   | $libdir/rtpostgis-2.3

(11 rows)

 

postgres=# 

 

 

 

-  

Marcelius Atanga

Tel: +13025384841


   

Call

Send SMS

Call from mobile

Add to Skype

You'll need Skype CreditFree via Skype

 

 

On Tuesday, August 29, 2023 at 02:55:37 AM EDT, atanga MARCELIUS 
mailto:marx...@yahoo.ca> > wrote: 

 

 

Hello Sandro, Thanks for your support.

 Please here are the out put to the queries you suggested.

I have also attached the output in text document in a clearer format.

My main concern here is how to  manage the upgrade path from postgis 2.3 in 
postgresql 9.6  to  a compactible version of postgis  in postgresql14  given 
that postgis 2.3 was hard install(using source code) and given my environmental 
challenges I do not want to do a dump and restore?

 


1.

postgres=# SELECT postgis_full_version();


postgis_full_version   

 

---

-

 POSTGIS="2.3.7 r16523" PGSQL="100" GEOS="3.9.2-CAPI-1.14.3" PROJ="Rel. 4.9.3, 
15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJS

ON="0.11" RASTER

(1 row)

 

2.

postgres=# SELECT oid::regprocedure,probin

  FROM pg_catalog.pg_proc

  WHERE proname LIKE 'postgis_%version';

 oid  |probin 

Re: [postgis-users] PostGIS verse those in Azure Cloud

[Regina Obe]

Are you right about what? 

 

I still think you need to focus on what exactly you are trying to compare and 
for who.

 

You are trying to compare apples and oranges is all I see.

 

I don’t think you will find any single review about Azure cloud geospatial 
offerings, cause it’s a huge landscape.

 

You need to pinpoint more what problem you are trying to solve and who is 
tasked to solve them.

You need to define exactly what set of Azure geospatial technologies you are 
talking about.

 

 

 

From: postgis-users  On Behalf Of 
Shaozhong SHI
Sent: Wednesday, August 30, 2023 8:31 AM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] PostGIS verse those in Azure Cloud

 

I am trying to review the underlying geospatial capability of these systems as 
the geospatial capability maturity have been developed unevenly.  I am 
interested in the following:

 

As compared with PostGIS spatial operations, what are the advantages and 
disadvantages of various systems in Azure Cloud in terms of supporting further 
development.

 

In my mind and from my experience, PostGIS has a lot of useful underlying 
functions that support development of other applications.

 

My initial glance of what other systems offer in the Azure, the potential for 
this is not great.

 

I would like to find information on this and confirm or reject this 
understanding.

 

Secondary programming/development relies on what the underlying functionality 
of a system provides.   Add-on development is based on the underlying 
technology offered.  From my experience, PostGIS offers a versatile range of 
functions and capability.  This is important to any other add-on development 
for forming robust and useful geospatial applications.

 

Am I right?

 

Regards,

 

David

 

On Wed, 30 Aug 2023 at 01:31, David Haynes mailto:hayne...@gmail.com> > wrote:

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 mailto:shishaozh...@gmail.com> > 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

___
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

[Regina Obe]

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

[Regina Obe]

> 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-devel] PostGIS Known Issues : OEL7 to RHEL8 Upgrade

[Regina Obe]

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

Re: [postgis-users] [postgis-devel] PostGIS Known Issues : OEL7 to RHEL8 Upgrade

[Regina Obe]

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 mailto:nikhil.db...@gmail.com> > 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 mailto:l...@pcorp.us> 
> 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, so 
not sure why you are trying to use a version several micro updates behind

 

https://www.postgresql.org/about/news/postgresql-154-149-1312-1216-1121-and-postgresql-16-beta-3-released-2689/

 

 

Hope that helps,

Regina

 

 

From: postgis-devel [mailto:postgis-devel-boun...@lists.osgeo.org 
<mailto:postgis-devel-boun...@lists.osgeo.org> ] On Behalf Of Nikhil Shetty
Sent: Wednesday, August 23, 2023 2:02 PM
To: PostGIS Development Discussion mailto:postgis-de...@lists.osgeo.org> >; postgis-users@lists.osgeo.org 
<mailto:postgis-users@lists.osgeo.org> 
Subject: [postgis-devel] PostGIS Known Issues : OEL7 to RHEL8 Upgrade

 

Hi Team, 

 

We want to know if there are any open/known issues that we may face 
post-migration of the Postgres database  instance  with PostGIS enabled from 
centos7/RHEL7/OEL7 to RHEL8. 

 

We came across one issue regarding 
collation(https://wiki.postgresql.org/wiki/Locale_data_changes), the solution 
for which is to reindex or use logical replication. This is not related to 
PostGIS but still a concern causing index corruption 

 

In a similar way, we want to understand if there ar

Re: [postgis-users] [postgis-devel] PostGIS Known Issues : OEL7 to RHEL8 Upgrade

[Regina Obe]

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, so 
not sure why you are trying to use a version several micro updates behind

 

https://www.postgresql.org/about/news/postgresql-154-149-1312-1216-1121-and-postgresql-16-beta-3-released-2689/

 

 

Hope that helps,

Regina

 

 

From: postgis-devel [mailto:postgis-devel-boun...@lists.osgeo.org] On Behalf Of 
Nikhil Shetty
Sent: Wednesday, August 23, 2023 2:02 PM
To: PostGIS Development Discussion ; 
postgis-users@lists.osgeo.org
Subject: [postgis-devel] PostGIS Known Issues : OEL7 to RHEL8 Upgrade

 

Hi Team, 

 

We want to know if there are any open/known issues that we may face 
post-migration of the Postgres database  instance  with PostGIS enabled from 
centos7/RHEL7/OEL7 to RHEL8. 

 

We came across one issue regarding 
collation(https://wiki.postgresql.org/wiki/Locale_data_changes), the solution 
for which is to reindex or use logical replication. This is not related to 
PostGIS but still a concern causing index corruption 

 

In a similar way, we want to understand if there are any other known issues 
related to PostGIS that should be taken care of

 

PostgreSQL : 13.6

PostGIS : 3.1.2

 

Thanks and Regards,

Nikhil

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

Re: [postgis-users] Help with Upgrade using YUM

[Regina Obe]

Atanga,

 

Please DO NOT EVER reply back to an unrelated question of your issue.  It just 
derails the conversation and is bad etiquette for any list.

Not only that but you annoyed other people in other lists since this was sent 
to more than PostGIS users.

 

I’ve changed the subject so as not to confuse people. 

 

Your best bet is to symlink or copy  the new .so files to the old name as I 
have described 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

 

Hope that helps,

Regina

 

From: atanga MARCELIUS [mailto:marx...@yahoo.ca] 
Sent: Sunday, August 20, 2023 8:49 AM
To: PostGIS Users Discussion ; Regina Obe 

Cc: 'PostGIS Development Discussion' ; 
'pgRouting users mailing list' ; 'pgRouting 
developers mailing list' ; 
mobilitydb-us...@lists.osgeo.org
Subject: Re: [postgis-users] PostGIS 3.4.0 binaries and Installers for Windows 
PostgreSQL 12-16

 

Hello Everyone,

we am trying to upgrade postgresql9.6 to postgresql 14 and postgis with below 
steps following

the postgis compartibility with postgis.

 

postgres 9.6( postgis 2.3) -> postgres 10 ( postgis 2.3) ( postgres upgrade)

 

postgres 10 (postgis 2.3) -> postgres 10 (postgis 2.5) ( postgis upgrade)

 

postgres 10 ( postgis 2.5) -> postgres 11 ( postgis 2.5) ( postgres upgrade)

 

postgres 11 ( postgis 2.5) -> postgres 11 ( postgis 3.1) ( postgis upgrade)

 

postgres 11 ( postgis 3.1) -> postgres 14 ( postgis 3.1) ( postgres upgrade)

 

 

So what i have done so far is .

 

1) First i have upgrade postgres 9.6 with postgis 2.3 to postgres 10 with same 
postgis 2.3.

 i installed postgres 10 and installed postgres contrib, then for postgis, i 
copied the postgis

 libraries from postgres 9.6 to postgres 10 and also installed "yum install 
postgis23_10"

 and i think its the compartible postgis 2.3 for version 10 i installed made 
the pg_upgrade successful

 and as postgres 9.6 and 10 postgis functions are compartible.

 >>>>>>So from postgres 9.6 to 10, i was able to run pg_upgrade successfully.

 

2) Upgrading from postgres 10 to postgres 11 but have to upgrade postgis 2.3 to 
postgis 2.5 as those are compartible.

 

So far postgres 10 already has postgis23_10 installed as i did when i wanted to 
upgrade from 9.6 to 10.

So i went ahead and installed "yum install postgresql11-contrib and yum install 
postgis25_11" so as to see if i can

have an upgrade path from postgis 2.3 to 2.5 as postgres 11 is compartible with 
postgis 2.5.

 

Upgrade of Postgis will fail and reading from postgis website i see not all can 
be upgraded using the alter command.

 

\dx that list entensions will not bring up postgis.

 

x=# \dx

   List of installed extensions

Name | Version | Schema | Description  

+-++--

 hstore | 1.4 | public | data type for storing sets of (key, value) pairs

 pg_repack | 1.4.6 | public | Reorganize tables in PostgreSQL databases with 
minimal locks

 pg_stat_statements | 1.6 | public | track execution statistics of all SQL 
statements executed

 pgcrypto | 1.3 | public | cryptographic functions

 pgstattuple | 1.5 | public | show tuple-level statistics

 plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

 uuid-ossp | 1.1 | public | generate universally unique identifiers (UUIDs)

(7 rows)

 

 

x=# SELECT postgis_full_version();

NOTICE: Function postgis_gdal_version() not found. Is raster support enabled 
and rtpostgis.sql installed?

NOTICE: Function postgis_raster_scripts_installed() not found. Is raster 
support enabled and rtpostgis.sql installed?

NOTICE: Function postgis_raster_lib_version() not found. Is raster support 
enabled and rtpostgis.sql installed?

 
postgis_full_version
  

---

 POSTGIS="2.3.7 r16523" GEOS="3.9.2-CAPI-1.14.3" PROJ="Rel. 4.9.3, 15 August 
2016" LIBXML="2.9.1" LIBJSON="0.11" (core procs from "2.3.2 r15302" need 
upgrade)

(1 row)

 

 

x=# SELECT postgis_extensions_upgrade();

ERROR: function postgis_extensions_upgrade() does not exist

LINE 1: SELECT postgis_extensions_upgrade();

   ^

HINT: No function matches the given name and argument types. You might need to 
add explicit type casts.

 

 

x=# ALTER EXTENSION postgis UPDATE TO '2.5.5';

ERROR: extension "postgis" does not exist

x=# ALTER EXTENSION postgis UPDATE TO '2.5';

ERROR: extension "postgis"

[postgis-users] PostGIS 3.4.0 binaries and Installers for Windows PostgreSQL 12-16

[Regina Obe]

I've published the installers for PostGIS 3.4.0 bundle for Windows
PostgreSQL 12, 13, 14, 15, and 16

 

Details on this page:  

 

https://postgis.net/documentation/getting_started/install_windows/released_v
ersions/

 

Extensions included are:

 

PostGIS 3.4.0 project: postgis, postgis_raster, postgis_topology,
postgis_tiger_geocoder, postgis_sfcgal, address_standardizer,
address_standardizer_data_us

pgRouting: pgrouting 3.5.0

h3-pg 4.1.3: h3, h3_postgis 

pgpointcloud : 1.2.4 pointcloud, pointcloud_postgis

ogr_fdw: 1.1.4

mobilitydb: 1.1.0alpha

 

enabling in databases detailed here -
https://postgis.net/documentation/getting_started/install_windows/enabling_p
ostgis/

 

Let me know if you run into any issues.

 

Thanks,

Regina

 

 

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

[postgis-users] PostGIS 3.4.0 released

[Regina Obe]

The PostGIS Development team is happy to release PostGIS 3.4.0 release.

Details at:  https://postgis.net/2023/08/PostGIS-3.4.0/

Happy testing,
PostGIS Development Team


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

Re: [postgis-users] Windows installer for 3.3.4

[Regina Obe]

1) I do packaging for windows on my free time so it gets done when I have
time to get it done.

If you want to contribute to my PostGIS packaging efforts, you can fund the
PostGIS project
Here:

https://postgis.net/community/support/

or you can fund me directly: https://www.patreon.com/reginaobe

2) For 3.3.4, I'm planning not to package that except for PG11  and will
move PG12 - 16 to 3.4.0

The binaries are always available on the windows buildbot and usually
available fairly immediately
Specifically you can find the PG12 3.3.4 binaries here:

https://winnie.postgis.net/download/windows/pg12/buildbot/


3.4.0 installers I'm planning to get out this weekend.  PostGIS 3.4.0 will
likely be released today or tomorrow.

Thanks,
Regina


> -Original Message-
> From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On
> Behalf Of Erik Wienhold
> Sent: Tuesday, August 15, 2023 6:20 AM
> To: postgis-users@lists.osgeo.org
> Subject: [postgis-users] Windows installer for 3.3.4
> 
> I want to upgrade PostGIS from 3.3.3 to 3.3.4 on a Windows server but the
> installer for 3.3.4 is not yet available [1].
> 
> Is there a schedule for when Windows builds are made available?  In the
past
> I already noticed that Windows builds are not available right away
compared
> to e.g. Debian packages.  Any reasons for the delay?
> 
> [1] https://download.osgeo.org/postgis/windows/pg12/
> 
> --
> Erik
> ___
> 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 3.4 on PG 16 observations

[Regina Obe]

The h3_postgis issue should be fixed now.  Note that it is now a tagged build 
instead of using the main repo.

 

https://winnie.postgis.net/download/windows/pg16/buildbot/extras/h3-pg_pg16-binaries-v4.1.3w64.zip

 

Thanks for testing,

Regina

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Gandalf the Gray
Sent: Monday, August 14, 2023 10:12 AM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] PostGIS 3.4 on PG 16 observations

 

Hi, 

 

Yes, I mean CREATE EXTENSION

 

On Mon, Aug 14, 2023 at 4:09 PM Regina Obe mailto:l...@pcorp.us> > wrote:

Command line.  You mean CREATE EXTENSION?  Just making sure.  I see it in my 
extension drop down but could be a refresh issue or something I’m missing cause 
I’m using a slightly earlier build.

 

H3_postgis ah yes I get the same error.  I’ll let you know once fixed.

 

Thanks,

Regina

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org 
<mailto:postgis-users-boun...@lists.osgeo.org> ] On Behalf Of Gandalf the Gray
Sent: Monday, August 14, 2023 6:57 AM
To: PostGIS Users Discussion mailto:postgis-users@lists.osgeo.org> >
Subject: [postgis-users] PostGIS 3.4 on PG 16 observations

 

Hi

 

I have made the following observations regarding PostGIS 3.4 on PG 16 using the 
archives on buildbot for Winnie.

 

PostGIS proper works perfectly.

 

PgRouting does not appear in the list of extensions while using PGAdmin, 
although it is installable using the command line.

 

H3_postgis is not installable at all, and gives me the following error:

 

could not access file "h3_postgis": No such file or directory

 

H3 is however , installable.

 

Regards

 

Pieter

 

 

___
postgis-users mailing list
postgis-users@lists.osgeo.org <mailto: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 3.4 on PG 16 observations

[Regina Obe]

Command line.  You mean CREATE EXTENSION?  Just making sure.  I see it in my 
extension drop down but could be a refresh issue or something I’m missing cause 
I’m using a slightly earlier build.

 

H3_postgis ah yes I get the same error.  I’ll let you know once fixed.

 

Thanks,

Regina

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Gandalf the Gray
Sent: Monday, August 14, 2023 6:57 AM
To: PostGIS Users Discussion 
Subject: [postgis-users] PostGIS 3.4 on PG 16 observations

 

Hi

 

I have made the following observations regarding PostGIS 3.4 on PG 16 using the 
archives on buildbot for Winnie.

 

PostGIS proper works perfectly.

 

PgRouting does not appear in the list of extensions while using PGAdmin, 
although it is installable using the command line.

 

H3_postgis is not installable at all, and gives me the following error:

 

could not access file "h3_postgis": No such file or directory

 

H3 is however , installable.

 

Regards

 

Pieter

 

 

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

Re: [postgis-users] PostGIS 3.4 on PG 16 observations

[Regina Obe]

It’s a PostGIS/Proj invention.

The reason is ST_Transform behaves differently with some transformations or 
doesn’t work at all of those settings for network enabled off.  I haven’t tried 
turning network on on windows and just realized I don’t think I’m packaging the 
proj.ini or some such thing you can use to control that.

 

The Database Path we also put in because on systems with multiple proj.db 
present, it’s hard to tell if the proj your postgis was compiled with is using 
the right database and sometimes proj db changes across major versions of proj.

 

I guess I should add to the release annoucement explaining those.

 

USER_WRITABLE_DIRECTORY is where proj writes grid shift files if it pulls from 
the internet.

 

Thanks,

Regina

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Gandalf the Gray
Sent: Monday, August 14, 2023 7:02 AM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] PostGIS 3.4 on PG 16 observations

 

Hi

 

Sorry, I also meant to ask what is the purpose of the extra info when one does 
a select postgis_full_version(), like NETWORK_ENABLED=OFF, URL_ENDPOINT, 
USER_WRITEABLE_DIRECTORY?  DATABASE_PATH I get.  Or should I ask postgres 
mailing list?

 

Pieter

 

On Mon, Aug 14, 2023 at 12:56 PM Gandalf the Gray mailto:pjduplooy@gmail.com> > wrote:

Hi

 

I have made the following observations regarding PostGIS 3.4 on PG 16 using the 
archives on buildbot for Winnie.

 

PostGIS proper works perfectly.

 

PgRouting does not appear in the list of extensions while using PGAdmin, 
although it is installable using the command line.

 

H3_postgis is not installable at all, and gives me the following error:

 

could not access file "h3_postgis": No such file or directory

 

H3 is however , installable.

 

Regards

 

Pieter

 

 

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

Re: [postgis-users] PgPointcloud for PG 16 Windows

[Regina Obe]

For the pgpointcloud, I did build against my patched fork but maybe you saw it 
already https://winnie.postgis.net/download/windows/pg16/buildbot/extras/

 

For the osm2pgrouting just had to build it.  Just built it  - 
https://winnie.postgis.net/download/windows/pg16/buildbot/

 

Let me know if you run into any issues.

 

Thanks,

Regina

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Gandalf the Gray
Sent: Monday, August 14, 2023 2:49 AM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] PgPointcloud for PG 16 Windows

 

Hi Regina

 

Thanks for the archive in Winnie.  I will see if it works.

 

On a side note, are there any problems with osm2pgrouting binaries, or do you 
still have to build it?

 

On Sun, Aug 13, 2023 at 7:11 PM Regina Obe mailto:l...@pcorp.us> > wrote:

Nope, pgPointcloud doesn’t build cleanly against PG16 yet.

 

See https://github.com/pgpointcloud/pointcloud/issues/338

 

I’ll get a patch for this (assuming someone doesn’t beat me to it) and submit 
and urge them to push out a tagged version.

 

Thanks,

Regina

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org 
<mailto:postgis-users-boun...@lists.osgeo.org> ] On Behalf Of Gandalf the Gray
Sent: Sunday, August 13, 2023 12:30 PM
To: postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> 
Subject: [postgis-users] PgPointcloud for PG 16 Windows

 

Hi guys

 

Is there a pgpointcloud archive for Windows PG 16?

___
postgis-users mailing list
postgis-users@lists.osgeo.org <mailto: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] PgPointcloud for PG 16 Windows

[Regina Obe]

Nope, pgPointcloud doesn’t build cleanly against PG16 yet.

 

See https://github.com/pgpointcloud/pointcloud/issues/338

 

I’ll get a patch for this (assuming someone doesn’t beat me to it) and submit 
and urge them to push out a tagged version.

 

Thanks,

Regina

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Gandalf the Gray
Sent: Sunday, August 13, 2023 12:30 PM
To: postgis-users@lists.osgeo.org
Subject: [postgis-users] PgPointcloud for PG 16 Windows

 

Hi guys

 

Is there a pgpointcloud archive for Windows PG 16?

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

Re: [postgis-users] Winnie/Buildbot for Windows not available

[Regina Obe]

Should be back now.

 

Thanks,

Regina

 

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Gandalf the Gray
Sent: Thursday, August 10, 2023 4:27 AM
To: postgis-users@lists.osgeo.org
Subject: [postgis-users] Winnie/Buildbot for Windows not available

 

Hi guys

 

Unreleased versions for Windows (Winnie/Buildbot) has been unreachable for a 
couple of days (I think since the weekend) now.

 

Has the url perhaps changed?  I have tried it directly, and from Geting 
Started/Windows unreleased versions page on your website.

 

Regards

 

Pieter

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