Re: [postgis-users] planner missing indices on raster tables after upgrade to PostGIS-3

; > https://postgis.net/docs/manual-3.1/RT_ST_Intersects.html > > which tries to use the indices, but without guarantee. > > > > Can we do anything other than adding the cast in the request? > > Can an explicit warning be added in the manual for helping other users > maybe? >

Re: [postgis-users] planner missing indices on raster tables after upgrade to PostGIS-3

Pg12 added the capability of the planner breaking the CTE barrier and planning the whole query holistically. It's possible this is, in your case, actually leading to a worse plan, in the presence of an implicit cast. Check out the 'MATERIALIZED' keyword for example. On Fri, Jul 23, 2021 at 2:42

Re: [postgis-users] Axis switching in transform from SRID 4326 to 3006 (Sweref99 TM) with PostGIS 3.1.3

) On Wed, Jul 21, 2021 at 1:25 PM Paul Ramsey wrote: > The fix is not in any released version yet. If you pull the code from git > and build you can confirm it works for your case (I'm going to guess that > it will, your description matches the other N/E systems. > > P > > On Tue,

Re: [postgis-users] Axis switching in transform from SRID 4326 to 3006 (Sweref99 TM) with PostGIS 3.1.3

The fix is not in any released version yet. If you pull the code from git and build you can confirm it works for your case (I'm going to guess that it will, your description matches the other N/E systems. P On Tue, Jul 20, 2021 at 5:12 PM David Stenwall wrote: > Hi > Ok, so I _think_ I have

Re: [postgis-users] ownership and permissions on ogr_fdw shapefiles

ogr must be removing and re-writing the files, so you're getting the ownership of the writing process, which is the postgres daemon. No way to change that (except running your daemon under the ownership you want!! (don't)). A cron job may have to suffice. P > On Jul 13, 2021, at 2:26 PM,

Re: [postgis-users] Geometries returning zero area with PostGIS 3.1.2.

KT renderer, and it appeared OK. > > Marco > > Op 12-7-2021 om 21:46 schreef Paul Ramsey: >> Marco, >> Can you open a ticket and attach some WKT to said ticket? That way I can >> test it without having to learn how to extarct those relations from OSM >> myself.

Re: [postgis-users] Geometries returning zero area with PostGIS 3.1.2.

Marco, Can you open a ticket and attach some WKT to said ticket? That way I can test it without having to learn how to extarct those relations from OSM myself. Thanks! P > On Jul 11, 2021, at 3:42 AM, Marco Boeringa wrote: > > Hi all, > > I am running into an issue with two of OpenStreetMap's

Re: [postgis-users] Spatial Query (st_union) causing database to crash

Hi Mike, Nothing do you should ever cause a crash, so you've (a) found some data that exercises the code in a new and exciting way, or (b) encountered a system install issue, that is leading to a crash, and that's either (a) a bug or (b) a system issue on your end (probably (a)). The first

[postgis-users] PostGIS 3.1.2 Released

, postgistigergeocoder Better answers when no zip is provided (Regina Obe) • #4817, handle more complex compound coordinate dystems (Paul Ramsey) • #4842, Only do axis flips on CRS that have a “Lat” as the first column (Paul Ramsey) • Support recent Proj versions that have removed

Re: [postgis-users] Wrong format: PostGIS misses a " " in it's WKT output?

standard, > but the PostGIS is kind of the standard way to follow the standard. > > Remi > > > > Le ven. 23 avr. 2021 à 15:50, Paul Ramsey a écrit > : > Reference please? In general the OGC standards have a lot of room for > interpretation in them. > Certainly we've done

Re: [postgis-users] Wrong format: PostGIS misses a " " in it's WKT output?

Reference please? In general the OGC standards have a lot of room for interpretation in them. Certainly we've done it this way for 20 years, so :) P. > On Apr 23, 2021, at 12:44 PM, Rémi c wrote: > > Hey dear list, > I'm looking at ST_asWKT() function. > I noticed an issue here: > PostGIS

Re: [postgis-users] possible problem with PostGIS on RHEL8

ls on both versions of proj and when we build MapServer it picks up > both libraries. MapServer crashes due to the multiple proj libraries. > > There seem to be no gdal32-libs requiring proj 7. > > Can someone confirm this inconsistency and suggest a solution? > > Thanks,

Re: [postgis-users] possible problem with PostGIS on RHEL8

Please stick to the users list. You might need to build mapserver from source. Or other components. Unless a packager has build EVERYTHING you want, the kinds of cross deps you're seeing to happen. I generally use packages from the PGDG yum repo, which mostly work, most of the time. But that

Re: [postgis-users] Fast spatial join point to raster

The indexing of rasters is (and remains a pain in the butt) (maybe something I should look at shortly) because the index is bound to geometry and almost always declraed functionally. You've built functional indexes, GIST (ST_Transform(ST_Envelope(rast),26915)) But you aren't calling them with

Re: [postgis-users] Error: "Precision reduction requires GEOS-3.9 or higher"

We aren't the pgdg packers, so we cannot answer that for you, but there is a list you could ask... https://www.postgresql.org/about/news/pgdg-apt-repository-for-debianubuntu-1432/ It's probably just an oversight. P > On Apr 18, 2021, at 5:42 AM, Marco Boeringa wrote: > > Hi Sandro, > >

Re: [postgis-users] Error: "Precision reduction requires GEOS-3.9 or higher"

It's curious, but the implication is that the postgis on your machine was built against an earlier GEOS. The version number that postgis reports for GEOS is generated at run-time, while the test of whether to include support for various functions is determined at compile-time. Hence you can see

Re: [postgis-users] linux failed postgis 3.1.1 compile against proj 8.0.0 (could not find libproj)

3.1.1 doesn't include the proj8 fix, the 3.1.2 release will. You can use the front of the stable-3.1 branch or the front of main branch. P. On Sat, Apr 3, 2021 at 1:23 PM Saulteau Don wrote: > I'm building on arch linux and trying to see if postgis 3.1.1 will compile > against proj 8.0.0 and

Re: [postgis-users] Postgis & GEOS 3.9

> On Feb 26, 2021, at 10:40 AM, Frederic Leclercq wrote: > > Hi all, > > I've installed postgis via apt on ubuntu, and upgraded GEOS to 3.9 manually. > How can I force PostGis to use this updated version of GEOS? Do I need to > re-install postgis by compiling the source, or is there

[postgis-users] GEOS 3.9.1 Released

in createPolygon CAPI (#1050, Paul Ramsey) - Allow build on Apple ARM64 (Taras Zakharko) - Fix buffer to use largest enclosed area for invalid rings (#732, Paul Ramsey) - Preserve ordering of lines in overlay results (Martin Davis) - Fix overlay handling of flat interior lines (JTS-685, Martin

[postgis-users] PostGIS 3.1.1 Released

PostGIS 3.1.1 is a minor bugfix release, covering some critical issues in 3.1.0. https://postgis.net/2021/01/28/postgis-3.1.1/ - #4814, Crash passing collection with only empty components to ST_MakeValid - #4818, Make the VSICURL synthetic driver work as documented - #4825, Unstable

Re: [postgis-users] Postgis 3.1.0 st_makevalid crashes the PostgreSQL Server

My installation of master on both PostGIS and GEOS does not crash, but rolling back to PostGIS 3.1.0 and GEOS master crashes. I think we should push out a minor PostGIS release ASAP. On Tue, Jan 26, 2021 at 3:37 AM Nicolas Ribot wrote: > With geos-master (3.10.0dev-CAPI-1.15.0), the buffer

Re: [postgis-users] shp2pgsql-gui for Mac and/or Linux?

Yeah, it's platform independent, but the build can be a bit hairy on mac so I think most distributors don't bother. P > On Jan 13, 2021, at 11:18 AM, Regina Obe wrote: > > I thought you get it if you do something like > > apt postgis-gui > > That said, I don’t have a Linux with a gui to

Re: [postgis-users] Spatial Join Index Use

https://lists.osgeo.org/mailman/listinfo/postgis-users > or, via email, send a message with subject or body 'help' to > postgis-users-requ...@lists.osgeo.org > > You can reach the person managing the list at > postgis-users-ow...@lists.osgeo.org > > Wh

Re: [postgis-users] Spatial Join Index Use

Without more analysis if your data, it's hard to say, but an initial guess is that 0.1 degrees is actually quite a large distance so the st_dwithin join condition just isn't selective enough to run indexed in one of the inner filters. If you're *sure* you want it run first, you could try

Re: [postgis-users] Calculate the distance between a point and a line

Oh, perhaps your ways are already in mercator? ST_Distance('SRID=4326;POINT(14.0007583 50.8379138)'::geography, st_transform(way,4326)::geography) as dist > On Jan 6, 2021, at 8:38 AM, Luca Bertoncello wrote: > > Am 06.01.2021 um 16:50 schrieb Paul Ramsey: >> ST_Distance('

Re: [postgis-users] Calculate the distance between a point and a line

ST_Distance('SRID=4326;POINT(14.0007583 50.8379138)'::geography, way::geography) as dist > On Jan 6, 2021, at 7:49 AM, Luca Bertoncello wrote: > > Am 06.01.2021 16:45, schrieb Paul Ramsey: > >> Go ST_Distance(this::geography, that::geography) then. >> DistanceSpheroid(

Re: [postgis-users] Calculate the distance between a point and a line

> On Jan 6, 2021, at 7:33 AM, Luca Bertoncello wrote: > > Am 06.01.2021 16:27, schrieb Paul Ramsey: > > Hi Paul, > >> (a) you have the longitude and latitude reversed in your POINT > > Arg! OK, with the parameter in the correct way of co

Re: [postgis-users] Calculate the distance between a point and a line

(a) you have the longitude and latitude reversed in your POINT (b) use ST_DistanceSpheroid() or cast to geography instead of doing that heinous mercator hack P > On Jan 6, 2021, at 6:10 AM, Luca Bertoncello wrote: > > Hi list! > > I'm new here and I use Postgis with OpenStreetMap data to

[postgis-users] PostGIS 3.1.0 Released

The PostGIS Team is pleased to release the release [0] of PostGIS 3.1.0! This version exposes the new features of GEOS 3.9 [1] as well as numerous core performance enhancements for spatial joins, large object access, text format output and more. Performance [2] is a key feature of this

Re: [postgis-users] ERROR: invalid regular expression: parentheses () not balanced

A LIKE query just gets re-written into an equivalent regex in the backend. There is no free lunch. This can be fast, if you build an index using text_pattern_ops like CREATE INDEX ON footable (foo text_pattern_ops); SELECT * FROM footable WHERE foo LIKE 'bar%'; This will never be fast

Re: [postgis-users] How to fix double-encoded UTF8 characters

You could also skip the database entirely, use shp2pgsql to turn the shape file into text, then pass the text through iconv a couple times to do the conversion steps you need. > On Dec 16, 2020, at 7:30 PM, Hugo Nicolau Barbosa de Gusmão > wrote: > > I have a dataset (shapefile) with the

Re: [postgis-users] How to fix double-encoded UTF8 characters

There is a convert() function in PgSQL but it doesn't have the syntax you are using. https://www.postgresql.org/docs/current/functions-string.html > On Dec 16, 2020, at 7:30 PM, Hugo Nicolau Barbosa de Gusmão > wrote: > > I have a dataset (shapefile) with the same problem as the post below:

[postgis-users] postgis-3.1.0rc1 released

This is "release candidate" release, the final step before official release. Unless a show-stopping bug is found, the final release will be out at the end of this week. https://postgis.net/source/ https://github.com/postgis/postgis/blob/master/NEWS Thanks for your help in testing and ensuring

[postgis-users] postgis-3.1.0beta2 released

of POINT EMPTY to retain type (Sandro Santilli) - #4813, ST_MakeValid removing NaN coordinates (Sandro Santilli) > On Dec 9, 2020, at 4:25 PM, Paul Ramsey wrote: > > This is a beta1 release, for testing and quality assurance, to be followed > shortly by a final releas

[postgis-users] postgis-3.1.0beta1 released

, ST_ReducePrecision (GEOS 3.9+) allows valid precision reduction (Paul Ramsey) - #4805, _ST_SortableHash exposed to work around parallel soring performance issue in Postgres. If your table is huge, use ORDER BY _ST_SortableHash(geom) instead of ORDER BY geom to make parallel sort

Re: [postgis-users] Parallel spatial indexing for GiST?

> On Sep 16, 2020, at 7:35 AM, Marco Boeringa wrote: > > Hi all, > > This is probably more of a PostgreSQL question than a PostGIS one, but I have > wondered if there is actually any work going on in allowing PostgreSQL / > PostGIS to build GiST type spatial indexes in parallel, and / or if

Re: [postgis-users] ST_FromBinary not expected

Oracle uses non-standary WKB type numbers for curves. https://docs.oracle.com/cd/B19306_01/appdev.102/b14373/constant-values.html Instead of the preamble looking liks this (hex WKB) 00 - big endian 0009 - compound curve 0007 - 7 elements 00 - big endian 0002 - linestring 0002 -

Re: [postgis-users] ST_Buffer malfunction on LINESTRING

r with JTS testbuilder (from > master, I think). Martin could possibly confirm. > > On Thu, 27 Aug 2020 at 03:42, Paul Ramsey wrote: >> >> Try some more recent versions. I'm finding with my very-fresh GEOS I'm >> getting a poly-with-hole, just as you are. >>

Re: [postgis-users] ST_Buffer malfunction on LINESTRING

Try some more recent versions. I'm finding with my very-fresh GEOS I'm getting a poly-with-hole, just as you are. P. > On Aug 26, 2020, at 2:58 AM, Michal Seidl wrote: > > Hello, > I am facing this malfunction of ST_Buffer. The result of closed LINESTRING > buffer should be polygon with hole

Re: [postgis-users] Storing geometries in variables

On Sat, Aug 22, 2020 at 5:16 PM Simon Greener wrote: > > Python, unless I am wrong, is a mid/client tier language only; plpgsql can only be deployed in the data tier. Python can be used in the client side or on the server side via PL/Python. However: The server-side Python binding (create

Re: [postgis-users] ST_GeomFromKML error with LinearRing

It's not a bug, it's deliberate. LinearRing is not an instantiable type, it's a component of a Polygon. If you want to instantiate a closed area, use a Polygon. If you want to instantiate a close line, use a closed LineString. P > On May 13, 2020, at 12:04 AM, Teluk, Bill G > wrote: > > Hi,

Re: [postgis-users] Merging census block data in postgresql

Check for a GIST index on the geometry column of the address points in particular, but also the blocks. If missing, CREATE INDEX blocks_geom_x ON blocks USING GIST(geom); CREATE INDEX addresses_geom_x ON addresses USING GIST(geom); What SQL you run will depend to some extent on what you are

Re: [postgis-users] Fuzzy Address Matching - PostgreSql equivalent to FuzzyStringComparer using Python difflib module

There's a huge range of potential functionality/features that can reside under the banner "fuzzy address matching". At the simplest, you're trying to catch common lexicographic differences, like off-by-one addresses or alternate spellings. This is the realm of trigrams and levenstein

Re: [postgis-users] Fuzzy Address Matching - PostgreSql equivalent to FuzzyStringComparer using Python difflib module

It's not an easy problem. There is no one guaranteed magic bullet. Use the address_standardizer extension, particularly for north american addressing. https://postgis.net/docs/postgis_installation.html#installing_pagc_address_standardizer Or use an ML trained standardizer like this one.

Re: [postgis-users] Any robust regex for searching out addresses?

Also, https://docs.google.com/presentation/d/1Fgc_2dzWAzT--HdMEiWj2fFLJNnpxPXmnYXx9Js3xjE/edit#slide=id.g6404bf8f4e_0_0 > On May 3, 2020, at 11:49 AM, Paul Ramsey wrote: > > create extension address_standardizer > > https://postgis.net/docs/postgis_in

Re: [postgis-users] Any robust regex for searching out addresses?

create extension address_standardizer https://postgis.net/docs/postgis_installation.html#installing_pagc_address_standardizer > On May 3, 2020, at 11:43 AM, Jo wrote: > > It would help if you had a bigger sample of valid addresses. > > This may be a good starting point: > >

Re: [postgis-users] ERROR: distance returned negative!

> On May 1, 2020, at 1:19 PM, Christophe Pettus wrote: > > > >> On May 1, 2020, at 12:09, Christophe Pettus wrote: >> >>> On Apr 30, 2020, at 18:43, Paul Ramsey wrote: >>> >>> Yes, there's some magic geometry in there... if you can

Re: [postgis-users] ERROR: distance returned negative!

Yes, there's some magic geometry in there... if you can get it down to the offending pair, that would be amazing amazing P > On Apr 30, 2020, at 5:04 PM, Christophe Pettus wrote: > > > >> On Apr 30, 2020, at 17:03, Christophe Pettus wrote: >> >> I'm receiving an error that I am not

Re: [postgis-users] ST_Intersection returning offset point on two lines which intersect

You don't. If you introduce a vertex into the original line at the intersection point, then the vextexes can match up, but the new point is not necessarily representable as being on the mathematical intersection of the two inputs, it's at the closest representable point. This works, for

Re: [postgis-users] Is there a robust way to strip off special characters?

Add the ‘g’ option. See the PgSQL docs > On Mar 30, 2020, at 3:10 AM, Shaozhong SHI wrote: > > I ran the following code. > > ---strip special characters > area_name = regexp_replace(area_name, '[^\w]+',''); > > It only stripped away the first bracket - (. But, it left the closing > bracket

Re: [postgis-users] query a certain row from a table.

Very punny > On Mar 23, 2020, at 9:54 AM, Martin Davis wrote: > > Ah yes, good point! > > On Mon, Mar 23, 2020 at 8:30 AM Paul Ramsey wrote: > Challenge accepted > > pramsey=# select st_isvalid(st_makepoint('NaN'::float8,1)); > NOTICE: Invalid Coordi

Re: [postgis-users] query a certain row from a table.

Challenge accepted pramsey=# select st_isvalid(st_makepoint('NaN'::float8,1)); NOTICE: Invalid Coordinate at or near point nan 1 st_isvalid f (1 row) > On Mar 21, 2020, at 10:51 PM, Martin Davis wrote: > > Not surprising - I'm not sure it's possible to make an invalid

Re: [postgis-users] Implementation of postgis interoperability with R

proj_identify: https://proj.org/development/reference/functions.html#_CPPv413proj_identifyP10PJ_CONTEXTPK2PJPKcPPCKcPPi On Thu, Mar 12, 2020 at 7:35 PM Paul Ramsey wrote: > > (a) do you already know the authority and code? Then first use those > to look-up the SRID in spatial_ref_sy

Re: [postgis-users] Implementation of postgis interoperability with R

(a) do you already know the authority and code? Then first use those to look-up the SRID in spatial_ref_sys (select srid from spatial_ref_sys where auth_name = 'EPSG' and auth_code = 14234) (b) do you only have WKT input? Then, sInce you have access to proj on the R side, I'd suggest instantiating

Re: [postgis-users] gdal version for Postgis 2.4

> On Mar 9, 2020, at 8:51 AM, Zwettler Markus (OIZ) > wrote: > > QUESTIONS: > Does Postgis 2.4 depend on gdal23? Not at a source code level. It’s probably possible to compile PostGIS 2.4 against more modern GDAL. > Is there a bug with the rpm dependency? Yes. > How to fix this? Remove

Re: [postgis-users] ST_Distance provides unexpected results when using Geography type

If you would like to see what your “boxes” actually look like, try wrapping them in ST_Segmentize() before viewing them on a flat map. select st_asgeojson(st_segmentize('SRID=4326;POLYGON ((-167.51953125 8.841651120809145, 1.23046875 8.841651120809145, 1.23046875 63.35212928507874,

Re: [postgis-users] st_union

gt; > A segunda, 2/03/2020, 17:04, Paul Ramsey escreveu: >> >> No, if it’s returning the answer you want, that’s the way to do it, and >> there’s no way around it. >> >> > On Mar 2, 2020, at 6:25 AM, wrote: >> > >> > Hi list, >> > I wou

Re: [postgis-users] Changed intersection test speed after PG 11 -> 12 and PostGIS 2.5 -> 3 upgrade

> > >> Thanks for the report. I've ticketed as a bug - > >> https://trac.osgeo.org/postgis/ticket/4635 > > Thanks for filing the ticket Regina! > > On Tue, Feb 04, 2020 at 11:49:44AM -0800, Paul Ramsey wrote: > >OK, cleaning this out a little. > >Ti

Re: [postgis-users] How does PostGIS / PostgreSQL distribute parallel work to cores for GIS type data?

> On Mar 1, 2020, at 3:36 AM, Marco Boeringa wrote: > > Although it is hard to give figures here, because I do not have a fully > equivalent non-multi threaded processing flow, I do see significant benefits > from distributing records based on vertex complexity. Yes and no. The executor

Re: [postgis-users] st_union

No, if it’s returning the answer you want, that’s the way to do it, and there’s no way around it. > On Mar 2, 2020, at 6:25 AM, wrote: > > Hi list, > I would like to do this: > CREATE TABLE public."Areas_union" AS select > (st_dump(st_union("the_geom"))).geom from "Areas"; > > But it

Re: [postgis-users] 3D intersects and 2D doesn't. Is this normal?

Different code lines. The 3D function is evaluated via the 3d distance calculation internal to postgis, with a tolerance of 0. The 2D function is evaluated in GEOS. Your two lines are in fact very very very almost intersecting, but not quite. If I apply ST_Astext() to them before running your

[postgis-users] PostGIS 2.5.4 is Released

https://postgis.net/2020/02/28/postgis-2.5.4/ Just a bug fix release, but lots of bugs. https://download.osgeo.org/postgis/source/postgis-2.5.4.tar.gz Enjoy! * Bug fixes * - #4480, Geography Distance inconsistent with Intersects (Paul Ramsey) - #4481, Improve libprotobuf detection for old

Re: [postgis-users] Inconsistent results on ST_Intersects and ST_Distance

> On Feb 26, 2020, at 2:02 AM, Palmetzhofer Dietmar > wrote: > > I have a simple LINE-Geometry and a POINT, that I want to query, if it > intersects the line. > The result ist, that ST_Intersects gives „FALSE“, and ST_Distance gives „0“. > So, this is contradictory. An analysis with JTS

Re: [postgis-users] AWS RDS performance

A single query won’t parallelize, so you’ll usually only heat up one core. With pg12/ you should get more parallel behaviour and better utilitization of a box that size for analytical queries. P > On Feb 25, 2020, at 9:04 AM, Alexander Gataric wrote: > > I've been using PostGIS on AWS with

Re: [postgis-users] Performance Postgres12/Postgis3 vs Postgres10/Postgis2.4

s=1) >>> Group Key: osm_admin_areas.osm_id, osm_admin_areas.name, >>> osm_admin_areas.admin_level, osm_admin_areas.name_en, >>> osm_admin_areas.name_fr, osm_admin_areas.area, osm_admin_areas.iso, >>> osm_admin_areas.is_in >>> -> Sort

Re: [postgis-users] Performance Postgres12/Postgis3 vs Postgres10/Postgis2.4

If you can cut this down to a smaller, shorter query that shows the same characteristics, I would like to see it in a profiler to determine if the hot spots have moved. The explain seems more or less structurally the same, which leaves a couple possibilities: - the st_collect running in

Re: [postgis-users] Changed intersection test speed after PG 11 -> 12 and PostGIS 2.5 -> 3 upgrade

> On Feb 4, 2020, at 9:12 AM, Regina Obe wrote: > > Thanks for the report. I've ticketed as a bug - > https://trac.osgeo.org/postgis/ticket/4635 OK, cleaning this out a little. Ticket for reference https://trac.osgeo.org/postgis/ticket/4635#comment:2 First, drop all the rest of your

Re: [postgis-users] Changed intersection test speed after PG 11 -> 12 and PostGIS 2.5 -> 3 upgrade

> On Feb 2, 2020, at 7:37 PM, Regina Obe wrote: > > If it does, can you try changing the function to your old 11 definition and > see if that makes the answers the same. Then at least we'll know it's the > change in definition and can work from there. If this is the case, can you share

Re: [postgis-users] FOSS4G 2020 Early Paper Deadline is Feb 4!

ence for every topic. Sharing your learning journey, even the journey to just try open source software in the first place, is helpful for others in mapping out their journeys. I hope to see lots and lots of PostGIS-themed submissions by the 4th :) Thanks, P On Tue, Jan 28, 2020 at 10:49 A

[postgis-users] FOSS4G 2020 Early Paper Deadline is Feb 4!

FOSS4G 2020 will be in Calgary, Canada this year, from August 24-29. It seems so far away, and yet, the first submission deadline is almost here… https://2020.foss4g.org/early-acceptance/ If you submit your talk to the Call for Papers before February 4, you will be eligible for Early

Re: [postgis-users] Performance boost with ST_ValueCount() - wondering why

according to the explain the seqscan at the bottom of the plan is scanning and returning only 5x more rows in 9.6. are the tables really the same size? > On Jan 9, 2020, at 12:47 PM, Shira Bezalel wrote: > > Hi List, > > This is a "yeah, but why?" type of question. > > I'm testing an

Re: [postgis-users] Performance boost with ST_ValueCount() - wondering why

EXPLAIN ANALYZE the query. My data-free guess is that, since you’ve got an aggregate function in there, is you’re getting a parallel plan under the aggregate, that’s something you wouldn’t get in 9.6 but would in 12. I don’t think there’s been any substantial change in the PostGIS raster code,

Re: [postgis-users] Need better strategy for a query

No? http://postgis.net/docs/ST_Subdivide.html P > On Nov 19, 2019, at 1:33 PM, Stephen Woodbridge > wrote: > > Hi, > > I have a global dataset in SRS EPSG:4326 that are ocean depth contours. The > problem is that these tend to be long and have

Re: [postgis-users] Status of PostGIS 3 and PostgreSQL Yum Repos?

00) > > libdl.so.2 => /lib64/libdl.so.2 (0x7fd332399000) > > libz.so.1 => /lib64/libz.so.1 (0x7fd332182000) > > /lib64/ld-linux-x86-64.so.2 (0x7fd333c54000) > > > > The byte sizes of the .so files are different. It appears they both > > come from the

Re: [postgis-users] Status of PostGIS 3 and PostgreSQL Yum Repos?

net.agron.iastate.edu/pickup/callgrind.zip > > I then attempted to look at the file in kcachegrind and am unsure what > I am looking at :) > > It seems to show a lot of time being spent in sqlite3 functions. 38 > million calls to sqlite3VdbeSerialType ? > > daryl > &g

Re: [postgis-users] Status of PostGIS 3 and PostgreSQL Yum Repos?

> /usr/lib64/libboost_chrono.so.1.66.0 > > (0x7ff53693c000) > > libboost_date_time.so.1.66.0 => > > /usr/lib64/libboost_date_time.so.1.66.0 (0x7ff536729000) > > libboost_atomic.so.1.66.0 => /usr/lib64/libboost_atomic.so.1.66.0 > > (0x7ff53652700

Re: [postgis-users] Status of PostGIS 3 and PostgreSQL Yum Repos?

40x is shocking and it looks like the only difference is red hat 7 vs 8.? > On Nov 4, 2019, at 6:47 PM, Daryl Herzmann wrote: > > Thanks for the response Raúl, > > I tried PostGIS 3.0 GA now on RHEL7 and am still seeing the same > puzzling slow behaviour. My RHEL8 development laptop crunches

Re: [postgis-users] Query postgis GUC variables on a new connection

Until the library loads the system doesn’t know about the GUCs. And querying the GUCs doesn’t for a library load (the system doesn’t having a binding from GUC to library). Querying a function, on the other hand, forces a load. If this really annoys you, add postgis_raster.so to the ld_preload

Re: [postgis-users] Why is && operator causing Parallel to kick in ?

For fairly complex reasons having to do with the ST_Intersects() function being an inlined SQL function. https://carto.com/blog/postgres-parallel/ The issues are fixed in Pg12 / PostGIS 3, but older combinations will be fiddly to parallelize, as

Re: [postgis-users] Are there psql command for detecting geometry types stored in PostGIS?

Also better that geometry_typmod_out, some utility functions: postgis_typmod_dims(integer) RETURNS integer postgis_typmod_srid(integer) RETURNS integer postgis_typmod_type(integer) RETURNS text > On Sep 20, 2019, at 8:30 AM, Paul Ramsey wrote: > > Here’s a start

Re: [postgis-users] Are there psql command for detecting geometry types stored in PostGIS?

Here’s a starting point: select c.relname, t.typname, case when t.typname = 'geometry' then geometry_typmod_out(atttypmod) else '' end, a.attnum, a.attname, a.atttypid, a.atttypmod from pg_class c join pg_attribute a on c.oid = a.attrelid join pg_type t on a.atttypid = t.oid where

Re: [postgis-users] [postgis-devel] Upgrade issues

Trying to ensure that user-added epsg lines don’t collide with the system ones… but it’s probably tragically out of date by now as we’ve added a lot of new epsg codes. I’m not sure exactly what we’re getting from that line, but Regina knows. P > On Sep 3, 2019, at 8:11 PM, James Sewell

Re: [postgis-users] [postgis-devel] Upgrade issues

> On Sep 3, 2019, at 7:52 PM, Regina Obe wrote: > > 4) I think also there was a time when the geography SRIDs were cached in > a secret place, so geography didn’t rely on spatial_ref_sys. I forget when > this changed (might have been at 2.2 or 2.3). No, there was a time when geography

Re: [postgis-users] [postgis-devel] Upgrade issues

> On Sep 3, 2019, at 7:52 PM, Regina Obe wrote: > > Is this the first time you are running pg_upgrade? > 1) I think most users don’t even know they can use an srid other than > 4326 for geography, so this probably hasn’t been exercised by many users > 2) This would not be an issue

Re: [postgis-users] Upgrade issues

> On Sep 3, 2019, at 6:20 AM, Regina Obe wrote: > > Oh didn’t think of that one. That ‘s a bummer. PostGIS has supported other > long/lat spatial ref sys since PostGIS 2.2. > I think if you use anything other than 4326 though it has to look in the > spatial_ref_sys table for some things.

Re: [postgis-users] ArcGIS desktop accessing and reading from PostGIS

This is not actually so. There’s nothing special about the postgres database, drop it and see. The system information lives in system catalogs that are not attached to any particular database. The template0 and template1 databases are “special” in that new databases are created by copying the

Re: [postgis-users] Inside / Outside of geography polygons

;>255M square km. > > I can't speak for the implementation complexity though. Especially if such a > solution could open new issues, or break existing applications. > > Greetings > Christian > > >> On 13 August 2019 at 00:48 Paul Ramsey > <mailto:pram...@cleverel

Re: [postgis-users] Is postgres still free?

EDB does builds of both community and their special sauce, maybe you ended up with a download of their proprietary “EDB Postgres” by accident. All the builds at this page are fully free community builds: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Re: [postgis-users] [Longest distance between two from list of points?]

uld think would give you the answer if you are just dealing with points > and don't care which points are furthest. > > > -Original Message- > From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf > Of Paul Ramsey > Sent: Wednesday, August 14, 201

Re: [postgis-users] [Longest distance between two from list of points?]

--Original Message- > From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf > Of Paul Ramsey > Sent: Wednesday, August 14, 2019 11:18 AM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] [Longest distance between two from list of > points?] &

Re: [postgis-users] [Longest distance between two from list of points?]

Depends on how exact you need the answer. If you’re looking for the extent of the data set, using SELECT ST_Extent(geom) FROM mytable will pop put a nice bounds and you can easily pull the width/height of that to get something like the size. The actual two points that are the furthest, that’s

Re: [postgis-users] Results of ST_DWithin missing data around 0, 0 for large request areas

You may find that this problem is recently addressed (how does this bug exist for 10 years and get reported twice in two weeks?) in https://trac.osgeo.org/postgis/ticket/4480 While in general the rule “things that are larger than a hemisphere are

Re: [postgis-users] Inside / Outside of geography polygons

I’ve applied the patch back to 2.4. I think it’s an improvement, if not exactly an ideal solution. https://trac.osgeo.org/postgis/ticket/4480 <https://trac.osgeo.org/postgis/ticket/4480> ATB, P > On Aug 12, 2019, at 3:48 PM, Paul Ramsey wrote: > > So, I have a patch for th

Re: [postgis-users] Stuck with PostgreSQL/PostGIS Upgrade

Some more background information about the magic gears turning behind the upgrade process: http://blog.cleverelephant.ca/2016/08/postgis-upgrade.html <http://blog.cleverelephant.ca/2016/08/postgis-upgrade.html> P > On Aug 13, 2019, at 8:40 AM, Paul Ramsey wrote: > > B

Re: [postgis-users] Upgrading from 1.5

Nope, your version is so very old that you’ll have to do some work on it. You’re going to have to dump your database, filter out the postgis-specific parts, and then reload it into your new database, the so-called “hard upgrade” process:

Re: [postgis-users] Stuck with PostgreSQL/PostGIS Upgrade

B, You haven’t included quite enough information to make a full diagnosis (like what command generates the error message) but just guessing, your admin used apt-get to update both postgres and postgis packages and then ran pg_upgrade on the cluster. Since the old postgis version is gone at this

Re: [postgis-users] Interior rings

You’re going to want to chain a ST_DumpRings (turn polygons to rings) to an ST_Dump (turn mutlis to singletons) and then look at the dump ring composite type and omit ring 1 from the relation… Something like: WITH poly AS ( SELECT 'MULTIPOLYGON(((0 0, 0 10, 10 10, 10 0, 0 0), (4 4, 4 5, 5

Re: [postgis-users] Inside / Outside of geography polygons

orientation is starting to rear its head, but only for special cases. Lots of thinking left to do, P > On Aug 12, 2019, at 9:55 AM, Paul Ramsey wrote: > > You are exposing a limitation in the postgis geodetic implementation, namely > that we don’t handle things larger than a hem

Re: [postgis-users] Inside / Outside of geography polygons

You are exposing a limitation in the postgis geodetic implementation, namely that we don’t handle things larger than a hemisphere well. This is not unlike the SQL Server 2008 limitation, except we don’t toss errors at you.

Re: [postgis-users] PostGIS new geometry type with defined precision?

Also, what are the results of ST_Distance() when the distance is < tolerance? Zero? Tolerance? Basically tolerance is a cross-cutting concern that affects almost every function in the system, sometimes in ways that ordinary users might consider “surprising” or “counter intuitive”. Our current

<    1   2   3   4   >