Hi David,
That blog article is from 12 years ago, and was presenting the best
solution available at that time. Several new features have been included in
the meantime. For example, the usage of LATERAL JOIN would allow in your
case to avoid the loop over the records in your pl/pgsql procedure, whi
Hi,
On Tue, 1 Mar 2022, 12:55 Shaozhong SHI, wrote:
> Let's say that we have identified certain features and would like to draw
> special attention of others to look at these features.
>
> Highlighting or marking these features to stand out of the rest of
> features.
>
Can *ST_Buffer* (https://
That's really a fantastic news! Thanks Han again for your work on this, and
thanks to all the people that helped with this!
Giuseppe.
On Sun, 28 Nov 2021, 20:26 Regina Obe, wrote:
> I think Han and Kontur have some benchmarks.
>
> https://git.osgeo.org/gitea/postgis/postgis-performance
>
> In m
Hi Daniel,
Il giorno dom 24 gen 2021 alle ore 18:16 Daniel Baston
ha scritto:
> Hi Giuseppe,
>
> You can order the inputs by anything you like; OVER(ORDER BY feature_id)
> would work just as well. If you have an example that is not deterministic
> despite ordered inputs, I'd be curious to see it
Ciao Luca,
Il giorno mer 27 gen 2021 alle ore 11:15 Luca Bertoncello <
lucab...@lucabert.de> ha scritto:
>
> I tried now to add a SWAP file. I know, this is not the best, but right
> now I cannot add more RAM to the PC...
> It seems to help, since it runs and didn't crash yet...
>
Yeah, I was pr
Hi Luca,
>From what I understand from the logs you attached, the most likely scenario
is a failure at OS level, which made the Postmaster process crash, probably
stopped by the OS itself with an OOM kill.
This is compatible with what is said here (the postmaster process received
a Signal 9, a for
Hi Darafei,
Il giorno lun 25 gen 2021 alle ore 07:07 Darafei "Komяpa" Praliaskouski <
m...@komzpa.net> ha scritto:
> Yep, that's all pieces of multi-year cross-project improvement. We spent
> some years on algorithms, then we lacked infrastructure, now postgres added
> infrastructure and after th
Hi Daniel,
Il giorno ven 22 gen 2021 alle ore 18:07 Daniel Baston
ha scritto:
> It should be deterministic for most real data if the inputs are ordered
> consistently, using the OVER() clause as you suggest. It's possible that
> there may be a contrived situation involving duplicates in the inpu
Hi Marco,
Il giorno sab 23 gen 2021 alle ore 11:39 Marco Boeringa <
ma...@boeringa.demon.nl> ha scritto:
> Hi Darafei,
>
> How does this relate to e.g. PostGIS with polygon or line data?
>
It doesn't. The patch Darafei shared in his reply added the possibility to
define a further support functio
Hi Darafei,
Thank you for your answer!
Il giorno ven 22 gen 2021 alle ore 16:26 Darafei "Komяpa" Praliaskouski <
m...@komzpa.net> ha scritto:
> Hello,
>
> Cluster functions don't have cross-PostGIS-version stability guarantee.
> For many production applications that is equal to being non-determi
Hello,
I have a question about how the function ST_ClusterDBSCAN is implemented in
PostGIS: basically, the question is if I'm able to pass the same window of
geometries in input to the function, would it return the same clusters?
I tried to find an answer by myself having a look to the code: see
Hi Marco,
Il giorno dom 20 dic 2020 alle ore 10:03 Marco Boeringa <
ma...@boeringa.demon.nl> ha scritto:
> Hi,
>
> Reading through the PostGIS documentation, I noticed the
> "ST_ClusterDBSCAN" function takes a distance as one of the inputs. Now
> the docs suggest the current algorithm only takes
Hi David,
Il giorno dom 29 nov 2020 alle ore 01:20 Shaozhong SHI <
shishaozh...@gmail.com> ha scritto:
> Hi,
>
> I keep getting the following error when I use engine =
> create_engine(logging in details to postgres)
> df.to_sql('table_name', and etc.)
>
>
> OperationalError: (psycopg2.Operational
Hi Paco,
How is the query executed in the DB? Is it a prepared query, or are
parameters (x, y) passed on the client side and then the resolved query is
executed in the DB?
In case of prepared query, the planner uses a "general" plan to execute the
query: the plan is eventually corrected comparing
Hi,
Il giorno ven 6 nov 2020 alle ore 12:44 Shaozhong SHI <
shishaozh...@gmail.com> ha scritto:
> Any showcases of desktop applications such as Word, Excel, Power Point,
> Adobe Illustrator, Graphic packages loading data from PostgreSQL/PostGIS?
>
About Microsoft applications: potentially you co
Hi,
I'd suggest having a look as well at `bytea` data type, which has been
introduced after blobs. Here an useful link to
understand which is more suitable depending on your use case:
https://wiki.postgresql.org/wiki/BinaryFilesInDB
Cheers,
Giuseppe.
Il giorno ven 6 nov 20
ese packages access and retrieve image data (Bytea)
> from PostGIS?
>
> Regards,
>
> Shao
>
> On Fri, 18 Sep 2020 at 11:07, Giuseppe Broccolo
> wrote:
>
>> Hi Shao,
>>
>> What do you need to store, more specifically? Since this is a PostGIS
>> related t
Hi Shao,
What do you need to store, more specifically? Since this is a PostGIS
related thread, is it about raster data, etc.?
Anyway, as a general info about binary data: there are two general ways to
insert binaries into a PostgreSQL database, the hex format and the escape
format. I attach here
Hi Marco,
Il giorno mer 16 set 2020 alle ore 15:35 Marco Boeringa <
ma...@boeringa.demon.nl> ha scritto:
> [...]
> Yes, I know there are BRIN type spatial indexes for PostGIS, which are
> comparatively super fast to create and lead to very small indexes even
> for ultra large tables, but from the
Hi PostgAnn,
Why do you think bitmap heap scan is an issue? Checking the EXPLAIN ANALYSE
of the query, looks like the planner uses
the proper plan to execute the query (the order of magnitude of expected
fetched rows and actual fetched after the execution
is mostly the same). In fact, the index is
Hi PostgAnn,
This NOTICE is raised by PostGIS, while it's trying to build statistics for
geospatial attributes (columns)
of a specific relation (table) of the database, but it then realyses that
all the rows present null/empty geometries.
Check if geometries have been properly inserted in the tab
Hi Shao,
Il giorno ven 15 mag 2020 alle ore 11:56 Shaozhong SHI <
shishaozh...@gmail.com> ha scritto:
> Hi, Nicolas,
>
> Thanks.
>
> That is very interesting. I will test it.
>
> I could not find information on how distance is defined. 0.01 stands on
> what unit?
>
> https://postgis.net/docs/ST
Hi Acaymo,
In some way, it is expected: the algorithm needs to loop on all the pixels
for the interpolation,
and this can be time consuming in case of large rasters, specially if you
consider that st_invdistweight
is a function implemented in PL/pgSQL.
Anyway, did you consider to split the raster
Hi John,
I guess you are using Psycopg2 as driver to PostgreSQL here (please add
further details about your setup).
Assuming Psycopg2: I guess that is because actions are not committed in
your snippet. You may be interested to
set autocommit for the execution of the script, even better to manage
ot; || output || ")'";
> execute psqlstring;
>
>
> END $$;
>
>
> The other day when I composed a very long string to do something else, it
> worked.
>
> Now, I am trying to make update statement and then execute the string.
> But, I keep get error
Hi Shao,
Maybe you are looking about how to pass SQL statements via a shell
here-document:
psql [options] < ha scritto:
> It is quite appealing to wrap up a large block of psql codes as a string
> and execute the string.
>
> And, how to deal with quotes within quotes.
>
> I tried short text stri
oid <http://www.bluemail.me/r?b=15824>
> On Mar 17, 2020, at 12:07 PM, Giuseppe Broccolo
> wrote:
>>
>> Ok, as I told befire lambda functions should rely on RDS instances. Once
>> you open a connection
>> to the DB through psycopg, enable the Postgis extension wit
n and connection stuff
>
> cur.execute("select * from schema.udf_name (parm1,parm2)")
>
> The UDF invokes several PostGIS functions such as st_dwithin.
>
> Get BlueMail for Android <http://www.bluemail.me/r?b=15824>
> On Mar 17, 2020, at 9:04 AM, Giuseppe Broc
Hi Alexander,
can you add further details (snippet of code, etc.) about this?
Never tried doing this, but AFAIK AWS lambda runs on preconfigured RDS
environments,
maybe it's just enough to include the CREATE EXTENSION.
Giuseppe.
Il giorno mar 17 mar 2020 alle ore 12:55 Alexander Gataric
ha scr
Hi Alexander,
I'd suggest to check if there are differences in the execution of the query
in RDS or in the other box where the query is executed with higher
performances.
I'm specifically talking about to check the outputs of the EXPLAIN (BUFFER,
ANALYSE) of teh query executed in the two boxes.
E
true, Deforming
> true
> Timing: Generation 3.299 ms, Inlining 10.618 ms, Optimization 124.105
> ms, Emission 80.956 ms, Total 218.978 ms
> Execution Time: 44637.499 ms
>
> Interesting about the stats improvement in 10. If/when I gain more insight
> about this questi
Hi Shira,
On Fri, 10 Jan 2020, 21:44 Shira Bezalel, wrote:
> Hi Giuseppe,
>
> Thank you for your reply. I provided the EXPLAIN (ANALYZE ON, BUFFERS ON)
> output in the links in my initial email. Is that not what you meant?
>
I was meaning the vanilla output returned by the EXPLAIN, not just the
Hi Shira,
there's the number of shared buffers involved in the hash aggregation that
is different in the two execution plans, in PostgreSQL 9.6 the double of
the pages are hitten compared to PostgreSQL 12.0. Here I assume that the
configuration of the two DB engines is the same (same shared buffer
Hi Stefan,
I didn't try, but I'd create dynamical SQL inside the function, i.e.
building the string with the statement needed to create the FDW server, and
then executing it with EXECUTE.
Hope it can help,
Giuseppe.
Il giorno mar 29 gen 2019 alle ore 14:37 Stefan Keller
ha scritto:
> Sorry, no
Hi Wembo,
Il giorno sab 12 gen 2019 alle ore 16:29 Wenbo Tao
ha scritto:
> Hello,
>
> I was trying to build a GiST index on a geometry column in a table
> with 1 billion rows. It took an entire week to finish.
>
> Then I reduced the number of rows by grouping closer objects into one
> cl
Hi Nathan,
2018-06-07 16:15 GMT+01:00 Nathan Einstein :
> Hi all,
>
> I’d like to create Voronoi polygons for a set of about 11 million points.
> ST_VoronoiPolygons works fine for a subset of ~3 million of these points,
> though the function’s processing speed seems to scale non-linearly in the
>
Hi Frederick,
2018-03-01 14:30 GMT+01:00 Frederick Powers :
> Greetings,
>
> How is the bounding box determined when creating a spatial index for point
> geometries? It's my understanding that creating a spatial index utilizes
> bounding boxes, which makes sense to me for lines and polygons, bu
Hi Didier,
2018-02-05 16:08 GMT+01:00 didier peeters :
> Hi,
>
> Here’s the problem that puzzles me:
> I would like to determine if an object (a building, an antenna, …) can be
> seen from a specific point ‘somewhere'. I have to deal with distances
> ranging from a few meters to about 200 km or
Hi Vilem,
2018-01-08 16:26 GMT+01:00 Vilem Ded :
> Thanks,
> very nice indeed! I will definitely give that a try.
> But this is really very dependent on data distribution. My dataset is
> clustered so it will be better, but probably not good enough:/
> As I understood KNN in Postgis is implemente
Hi Антон,
2017-12-23 12:43 GMT+01:00 Антон Брюхов :
> Hi Giuseppe,
>
> Does PostGIS support seamless working with a set of tiles (small
> rasters) as a single raster?
>
Not sure I understood the point, but it should.
Giuseppe.
___
postgis-users mailing
Hi Антон,
2017-12-21 20:13 GMT+01:00 Антон Брюхов :
> Greetings,
>
> What is best way to import and work (in terms of performance
> and convenience) with large GeoTIFF rasters (~9000x9000). Is it better to
> register them as out-db rasters or not? Is it a good practice to cut big
> rasters in til
Hi Ben,
2017-11-02 20:15 GMT+01:00 Q S :
> Hi Giuseppe
>
> Apologies for the typo. The error is this:
>
> ERROR: could not find function "gserialized_gidx_geog_overlaps" in
> /usr/pgsql-9.2/lib/postgis-2.3.so
>
> I have updated at least two hosts without any issues. but this particular
> one give
Hi Ben,
2017-10-31 18:48 GMT+01:00 Q S :
> postgresql version: 9.2
>
> postgis version: 2.0.
>
>
>1. install postgis2.3
>2. login to postgresql database
>3. alter postgis update to '2.3.0';
>
> ERROR: could not find function "gserialized_gidx_geog_overlaps" in
> /usr/pgsql-9.2/lib/pos
Hi Antonio,
2017-10-29 12:31 GMT+01:00 Antonio Rodriges :
> Hello,
>
> Whether PostGIS allow importing 3-d, 4-d, etc. arrays or only 2-d arrays?
>
> Specifically, I have a 3-d array with axes (time, lat, lon).
> Does this mean that I need to split it onto 2-d bands (lat, lon) and
> import the num
Hi Dan,
2017-07-31 18:29 GMT+02:00 Dan Lyke :
>
> So over this weekend I exported the entire database out and imported it
> into a new instance. Now that one table size is down to 34GB (from
> 70GB before), which seems more in-line with the earlier database version
> (I guess something happened w
Hi Dan,
2017-07-26 19:15 GMT+02:00 Dan Lyke :
> On Wed, 26 Jul 2017 12:31:07 +0200
> Giuseppe Broccolo wrote:
> > On old dataset, the index is scanned through the bitmap: there are two
> > phases, in the first the index is used to build a bitmap, then the
> > quer
Hi Dan,
2017-07-25 18:47 GMT+02:00 Dan Lyke :
> I'm trying to figure out a performance issue between two quarters of
> map data. Both are using PosgreSQL 9.5.4 on Amazon RDS. The current
> quarter's data appears to be much slower than the old quarter's data.
>
> VACUUM ANALYZE has been run.
>
> E
Hi Denis,
2017-07-18 11:51 GMT+02:00 Denis Rouzaud :
> Hi all,
>
> I'm bumbing my head against the wall.
>
> I have an error "function st_x(geometry) does not exist" in an update
> statement which I don't understand. If I copy the same exact line in a
> select statement I get no error...
>
> Her
Hi Volker,
2017-07-05 15:39 GMT+02:00 :
> Hi,
>
> is it possible to lock a column of a table?
>
> > i edit data via qgis, but not every colum should be editable...
>
In PostgreSQL you can revoke permission at column table. This is possible
since (at least) the 8.4 version:
https://www.postgresql
blocks, filtering 400k rows from 1M rows.
Could you attach here the output of an EXPLAIN (ANALYSE, BUFFER) of the
query, if the execution does not take too long?
In any case, I'm posting here an useful link by Regina:
http://postgis.net/2014/03/14/tip_intersection_faster/
About how t
on overlapping (not just intersecting) geometries through the
condition on ST_Overlaps. This one is more
restrictive than the ST_Intersects one, so consider to use one or the other
basing on what you really need to filter.
Hope this can help.
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadran
ve just
the three intersecting geometries or the resulting intersection of these
(currently, you are selecting the intersection between part1 and part2
geometries)? Moreover, are you interested on overlapping or intersection (I
mean, is it fine if a part1 geometry contains a part2 or part3 ones)? I&#x
gPointCloud 1.1 in my Mac OS X Sierra laptop in this way.
Regards,
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it
___
postgis-us
fully-qualify functions and operators, so are not
recreated in the original schemas
when they are restored from a logical backup.
Which user is used to connect to the database when pg_restore is executed?
Regards,
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Train
th
(probably in the "postgis"
schema?).
Regards,
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
should then
been released with the next updates of PostGIS.
In the meantime, since the bug should not affect just-initiated indexes,
the workaround is to recreate
a new index everytime new entries are added in the indexed fields (consider
that BRINs can be
quickly rebuilt).
Regards,
Giuseppe
56 matches
Mail list logo
