from:"Paul Ramsey"

Re: [postgis-users] ST_Area(geog): ptarray_area_spheroid: cannot handle ptarray that crosses equator

2017-08-25 Thread Paul Ramsey

I think at proj 4.9 we flip over to using the geodetic functions in proj,
which are more robust and have fewer failure modes.

P

On Mon, Aug 21, 2017 at 2:33 PM, Peter Schmitt  wrote:

> Thanks Regina!  Yes, it must be something with the version of GEOS, PROJ
> or GDAL.  The query works fine on a AWS RDS instance with
>
> POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08
> September 2015" GDAL="GDAL 2.0.3, released 2016/07/01" LIBXML="2.9.1"
> LIBJSON="0.12" RASTER
>
> but failed with this Docker image: https://hub.docker.com/
> r/mdillon/postgis/
>
> POSTGIS="2.3.2 r15302" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6
> March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1"
> LIBJSON="0.11.99" RASTER
>
> Thanks again!
> Pete
>
> On Mon, Aug 21, 2017 at 3:05 PM, Regina Obe  wrote:
>
>> Hmm something seems a little fishy with this.  I'm running 2.3.3 so I
>> guess it's possible it's something we fixed in PostGIS 2.3.3.
>>
>>
>>
>> When I run:
>>
>>
>>
>> SELECT ST_Area(ST_GeographyFromText('POLYGON((40 0.009,40.1 0.009,40.1
>> 0,40 0,40 0.009))'));
>>
>>
>>
>> I get:
>>
>>
>>
>> 11078167.65815
>>
>>
>>
>> I think since version 2.2.0 or 2.3, I forget which, the geographicLab
>> library (which requires Proj 4.9+) kicks in so it's possible I'm not having
>> the same issue because I'm using a newer Proj version so I'm using the
>> improved calc stuff.
>>
>>
>>
>> This is the version I'm using.
>>
>>
>>
>> POSTGIS="2.3.3 r15473" GEOS="3.6.1-CAPI-1.10.1 r4317" PROJ="Rel. 4.9.1,
>> 04 March 2015" GDAL="GDAL 2.2.1, released 2017/06/23" LIBXML="2.7.8"
>> LIBJSON="0.12" RASTER
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On
>> Behalf Of *Peter Schmitt
>> *Sent:* Monday, August 21, 2017 4:31 PM
>> *To:* postgis-users@lists.osgeo.org
>> *Subject:* [postgis-users] ST_Area(geog): ptarray_area_spheroid: cannot
>> handle ptarray that crosses equator
>>
>>
>>
>> Hi,
>>
>>
>>
>> The following query:
>>
>>
>>
>> >  SELECT ST_Area(ST_GeographyFromText('POLYGON((40 0.009,40.1
>> 0.009,40.1 0,40 0,40 0.009))'));
>>
>>
>>
>> returns the error:
>>
>>
>>
>> > ptarray_area_spheroid: cannot handle ptarray that crosses equator
>>
>>
>>
>> I am using Postgis-2.3 provided by https://hub.docker.com/r/md
>> illon/postgis/
>>
>>
>>
>> postgis_full_version =  POSTGIS="2.3.2 r15302" GEOS="3.4.2-CAPI-1.8.2
>> r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released
>> 2013/08/26" LIBXML="2.9.1" LIBJSON="0.11.99" RASTER
>>
>>
>>
>> I believe the geometry is valid.  I can pass the error by
>> setting use_spheroid=false, like so:
>>
>>
>>
>> > SELECT ST_Area(ST_GeographyFromText('POLYGON((40 0.009,40.1 0.009,40.1
>> 0,40 0,40 0.009))'), false);
>>
>>
>>
>> However, I would like to use the sphere everywhere else in my
>> application.  Can anyone provide some insight?  I found something reporting
>> this should not hapen on Postgis >= 2.2: https://gis.stackexchange
>> .com/questions/169436/postgis-polygons-lying-on-the-equator/169482#169482
>>
>>
>>
>> Thanks!
>>
>> Pete
>>
>>
>>
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>
>
>
> --
> Pete
>
> ___
> 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] TR: strange behavior of ST_Area(geography)

2017-10-16 Thread Paul Ramsey

Werks fer me also:

 st_equals | eq_area | geog1 | geog2 | geog3 | geom
---+-+---+---+---+--
 t | t   | t | t | t | t


On Sun, Oct 15, 2017 at 6:07 AM, IIDA Tetsushi  wrote:

> thank you, Olivier.
>
> sorry, i forgot to describe my environment.
> 
>  PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian
> 4.9.2-10) 4.9.2, 64-bit
>  POSTGIS="2.3.2 r15302" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel.
> 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1,
>  released 2013/08/26" LIBXML="2.9.1" LIBJSON="0.11.99" RASTER
>
> (docker image "mdillon/postgis")
>
>
> 2017-10-15 21:34 GMT+09:00 Olivier Leprêtre :
> > 9.6 and
> > "POSTGIS="2.3.2 r15302" GEOS="3.6.1-CAPI-1.10.1 r4317" PROJ="Rel. 4.9.1,
> 04 March 2015" GDAL="GDAL 2.1.3, released 2017/20/01" LIBXML="2.7.8"
> LIBJSON="0.12" TOPOLOGY RASTER"
> >
> > -Message d'origine-
> > De : Olivier Leprêtre [mailto:o.lepre...@gmail.com]
> > Envoyé : dimanche 15 octobre 2017 14:33
> > À : 'PostGIS Users Discussion'
> > Objet : RE: [postgis-users] strange behavior of ST_Area(geography)
> >
> > I tested your query with 9.6 all tests return true.
> >
> > Olivier
> >
> > -Message d'origine-
> > De : postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] De la
> part de IIDA Tetsushi Envoyé : dimanche 15 octobre 2017 13:42 À :
> postgis-users@lists.osgeo.org Objet : [postgis-users] strange behavior of
> ST_Area(geography)
> >
> > Hi,
> >
> > These polygons have the same shape but orientation.
> >
> > --- my query -
> > WITH poly AS (
> >  SELECT
> >   ST_SetSRID(ST_GeomFromText(
> >'POLYGON((138.002 37.001, 138.003 37.0010, 138.003 37.0013, 138.002
> 37.001))'
> >),4326) g1,
> >   ST_SetSRID(ST_GeomFromText(
> >'POLYGON((138.002 37.001, 138.003 37.0013, 138.003 37.0010, 138.002
> 37.001))'
> >),4326) g2
> > )
> > SELECT ST_Equals(g1,g2),
> >  ST_Area(g1) = ST_Area(g2) AS eq_area,
> >  ST_Area(g1::geography)  = ST_Area(g2::geography) AS geog1,
> >  ST_Area(g1::geography, false)   = ST_Area(g2::geography, false) AS
> geog2,
> >  ST_Area(g1::geography)  = ST_Area(ST_Reverse(g2)::geography)
> AS geog3,
> >  ST_Area(ST_Transform(g1,32654)) = ST_Area(ST_Transform(g2,32654)) AS
> geom  FROM poly;
> >
> > - output -
> >
> >  st_equals | eq_area | geog1 | geog2 | geog3 | geom
> > ---+-+---+---+---+--
> >  t | t   | f | t | t | t
> >
> >
> > I expected all these tests return true.
> > Is this behavior by spec?
> >
> > Thanks
> >
> > --
> > hogeman
> > ___
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
> >
> >
> > ---
> > L'absence de virus dans ce courrier électronique a été vérifiée par le
> logiciel antivirus Avast.
> > https://www.avast.com/antivirus
> >
> > ___
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
> --
> Iida, Tetsushi
> 飯田 哲士
> ___
> 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] PostGIS 2.2.6, 2.3.4, and 2.4.1

2017-10-18 Thread Paul Ramsey

Hey PostGIS users and developers,

There are new patch releases for the supported stable branches available.
Please avail yourself of them, for the latest in fixes and tweaks. For some
insight into the particular changes, check out the respective NEW files:

https://svn.osgeo.org/postgis/tags/2.2.6/NEWS
https://svn.osgeo.org/postgis/tags/2.3.4/NEWS
https://svn.osgeo.org/postgis/tags/2.4.1/NEWS

Thanks as always for your support and encouragement,
Yours,
  the PostGIS development folks
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Handling N-d arrays in PostGIS

2017-10-30 Thread Paul Ramsey

As others have noted, a POINTM or MULTIPOINTM will serve to store your data
just fine, but what you plan to *do* with that data after will determine
whether a relational database is really the correct tool for you.

ATB,
P

On Mon, Oct 30, 2017 at 4:56 AM, Antonio Rodriges 
wrote:

> I suppose it has a bit different purpose (like an efficient handling of
> sparsity which is not the case with dense climate data) and I hope there is
> an easier solution (I just have 3 dimensions...)
>
> Antonio
>
> 2017-10-30 14:52 GMT+03:00 Stephen V. Mather 
> :
>
>> Ya, I’m not sure point clouds are at all the fix. They just address the
>> dimensionality question well, though not the gridded data requirement.
>>
>> Cheers,
>> Best,
>> Steve
>>
>>
>> [image: http://sig.cmparks.net/cmp-ms-90x122.png]*Stephen V. Mather*
>> GIS Manager
>> (216) 635-3243 (Work)
>> (216) 339-6347 (Cell)
>> --sent from phone--
>>
>>
>>
>> On Oct 30, 2017, at 07:49, Antonio Rodriges 
>> wrote:
>>
>> Thank you for pointing to this tool.
>>
>> However, I thought that since PostGIS uses GDAL it may be easier to
>> import such arrays, e.g. just split them onto individual 2-d grids (since
>> PostGIS mainly understands 2-d grids).
>>
>> 2017-10-30 14:34 GMT+03:00 Stephen V. Mather > >:
>>
>>> I don’t know if it’s the ideal tool for the job, as it’s more flexible
>>> than you need, not being a regularized grid but a point cloud, but you
>>> might look to the pgPointCloud extension: https://github.com/
>>> pgpointcloud/pointcloud
>>>
>>> Cheers,
>>> Best,
>>> Steve
>>>
>>>
>>> [image: http://sig.cmparks.net/cmp-ms-90x122.png]*Stephen V. Mather*
>>> GIS Manager
>>> (216) 635-3243 (Work)
>>> (216) 339-6347 (Cell)
>>> --sent from phone--
>>>
>>>
>>>
>>> On Oct 30, 2017, at 07:09, Antonio Rodriges 
>>> wrote:
>>>
>>> Hello,
>>>
>>> Thank you for the reply, however my data is slightly different. Sorry
>>> that I did not make it clearer at the very beginning.
>>>
>>> Actually I would like to import a dense, 3-d array of wind speed (a
>>> time series of grids, each grid point contains the wind speed value)
>>>
>>> The array is stored as a NetCDF file
>>> FIles are here https://www.esrl.noaa.gov/psd/
>>> cgi-bin/db_search/DBListFiles.pl?did=61&tid=59909&vid=4298
>>>
>>> The size of the array and its dimensions are below
>>>
>>> dimensions:
>>>lat = 94 ;
>>>lon = 192 ;
>>>time = 1460;
>>>
>>> The array
>>>
>>> short uwnd(time,lat,lon) ;
>>>  uwnd:long_name = "6-Hourly Forecast of U-wind at 10 m" ;
>>>  uwnd:valid_range = -32765s, -8765s ;
>>>  uwnd:unpacked_valid_range = -120.f, 120.f ;
>>>  uwnd:actual_range = -38.2f, 38.07f ;
>>>  uwnd:units = "m/s" ;
>>>  uwnd:add_offset = 207.65f ;
>>>  uwnd:scale_factor = 0.01f ;
>>>  uwnd:missing_value = 32766s ;
>>>  uwnd:_FillValue = -32767s ;
>>>  uwnd:precision = 2s ;
>>>  uwnd:least_significant_digit = 1s ;
>>>  uwnd:GRIB_id = 33s ;
>>>  uwnd:GRIB_name = "U GRD" ;
>>>  uwnd:var_desc = "u-wind" ;
>>>  uwnd:dataset = "NCEP/DOE AMIP-II Reanalysis (Reanalysis-2)" ;
>>>  uwnd:level_desc = "10 m" ;
>>>  uwnd:statistic = "Individual Obs" ;
>>>  uwnd:parent_stat = "Other" ;
>>>  uwnd:standard_name = "eastward_wind" ;
>>>
>>> 2017-10-30 11:04 GMT+03:00 Giuseppe Broccolo :
>>>
>>> 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 number of bands that is equal to the number of time steps
>>>
>>> in the 3-d array?
>>>
>>>
>>>
>>> Which is the data source from which you import the data (e.g. textual,
>>>
>>> etc.)?
>>>
>>>
>>> If I've correctly understood, you have arrays where geospatial and
>>>
>>> non-geospatial information
>>>
>>> is present, each one providing a "dimension" of the array.
>>>
>>>
>>> Just FYI, in PostGIS is possible to define mixed, structured data with
>>>
>>> constructors like POINTM
>>>
>>> and POINT, that allow to add a further dimension to the 2D/3D
>>> (respectively)
>>>
>>> geospatial ones, that
>>>
>>> includes a scalar information.
>>>
>>>
>>> Hope this can help in your import, otherwise provide more information
>>> about
>>>
>>> source data and how
>>>
>>> you'd like to import.
>>>
>>>
>>> Giuseppe.
>>>
>>>
>>>
>>> ___
>>>
>>> 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/mail

Re: [postgis-users] [XX000] ERROR: AddToPROJ4SRSCache: could not parse proj4 string

2017-11-09 Thread Paul Ramsey

I think "unknown elliptical parameter name" is the giveaway. I'm guessing
you haven't installed the NAD grids in your new install, so it's unable to
dereference "NAD83" into something it can use.

P

On Thu, Nov 9, 2017 at 1:44 PM, Andrew Joseph  wrote:

> I receive the following error when running st_transform(geom,4326) on an
> ogr_fdw foreign table:
>
> [XX000] ERROR: AddToPROJ4SRSCache: could not parse proj4 string '+proj=lcc
> +lat_1=31.88 +lat_2=30.116667 +lat_0=29.67
> +lon_0=-100.3 +x_0=69.9998983998 +y_0=300 +datum=NAD83
> +units=us-ft +no_defs ' unknown elliptical parameter name
>
> This does not occur when I import the data into postgis manually using
> ogr2ogr and then run st_transform() on the concrete table.
>
> This issue was not present on the following configuration:
>
> POSTGIS="2.3.2 r15302" GEOS="3.6.1-CAPI-1.10.1 r0" SFCGAL="1.3.0"
> PROJ="Rel.
> 4.8.0, 6 March 2012" GDAL="GDAL 2.2.0dev, released 2016/99/99"
> LIBXML="2.9.1" LIBJSON="0.11.99" TOPOLOGY RASTER
>
> But appears on the configuration below with the same dataset:
>
> POSTGIS="2.4.1 r16012" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d6"
> SFCGAL="1.3.0" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.3.0dev,
> released 2017/99/99" LIBXML="2.9.3" LIBJSON="0.11.99" TOPOLOGY RASTER
>
> gdalsrsinfo output
> 
> gdalsrsinfo -e ESRI::testshp.prj
>
> EPSG:2277
>
> PROJ.4 : +proj=lcc +lat_1=31.88 +lat_2=30.116667
> +lat_0=29.67 +lon_0=-100.3 +x_0=69.9998983998
> +y_0=300 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=us-ft +no_defs
>
> OGC WKT :
> PROJCS["NAD83 / Texas Central (ftUS)",
> GEOGCS["NAD83",
> DATUM["North_American_Datum_1983",
> SPHEROID["GRS 1980",6378137,298.257222101,
> AUTHORITY["EPSG","7019"]],
> TOWGS84[0,0,0,0,0,0,0],
> AUTHORITY["EPSG","6269"]],
> PRIMEM["Greenwich",0,
> AUTHORITY["EPSG","8901"]],
> UNIT["degree",0.0174532925199433,
> AUTHORITY["EPSG","9122"]],
> AUTHORITY["EPSG","4269"]],
> PROJECTION["Lambert_Conformal_Conic_2SP"],
> PARAMETER["standard_parallel_1",31.88],
> PARAMETER["standard_parallel_2",30.116667],
> PARAMETER["latitude_of_origin",29.67],
> PARAMETER["central_meridian",-100.3],
> PARAMETER["false_easting",2296583.333],
> PARAMETER["false_northing",9842500.2],
> UNIT["US survey foot",0.3048006096012192,
> AUTHORITY["EPSG","9003"]],
> AXIS["X",EAST],
> AXIS["Y",NORTH],
> AUTHORITY["EPSG","2277"]]
>
>
>
>
> --
> Sent from: http://postgis.17.x6.nabble.com/PostGIS-User-f3516033.html
> ___
> 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] PostGIS 2.3.5 & 2.4.2 Released

2017-11-15 Thread Paul Ramsey

In order to correct a gap in the upgrade path (primarily) and add a few
more bug fixes into public release, patch releases for 2.3.5 and 2.4.2 have
been made available.

https://svn.osgeo.org/postgis/tags/2.3.5/NEWS
https://svn.osgeo.org/postgis/tags/2.4.2/NEWS

Thanks!
The PostGIS Global Domination Group
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] How can I update proj4 without recompile

2017-11-28 Thread Paul Ramsey

Hi Guido,
Knowing your use case ("wanting to avoid bad area calculations in
geodetics"), I can say definitively that you're out of luck. Just
replacing the library file won't do the trick, as the code in PostGIS
that uses the new proj geodetic area calculations won't be activated
with anything less than a rebuild.
ATB,
P

On Tue, Nov 28, 2017 at 9:59 AM, Guido Stein  wrote:
> Hey folks,
>
> I am trying to update the proj4 libraries on my postgis because I think the
> older version of proj4 is giving me faulty areas (known bug). I would like
> to go from 4.8 to 4.9.
>
> In talking to some friends I found out that I can make a sym link from the
> existing proj4 to a newly compiled proj4 without an issue. But, I can't seem
> to figure that out completely and need a little help.
>
> # Here is what is running on my system:
>
> select version();
> PostgreSQL 9.5.10 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
>
> select postgis_full_version();
> POSTGIS="2.3.3 r15473"
> GEOS="3.4.2-CAPI-1.8.2 r3921"
> PROJ="Rel. 4.8.0, 6 March 2012"
> GDAL="GDAL 1.10.1, released 2013/08/26"
> LIBXML="2.9.1"
> LIBJSON="0.11.99"
> RASTER
>
> # here is what I have done so far:
>
> - locate the directory of the postgis
> pg_congif --pkglibdir
> - get information about what libraries are associated with postgis
> ldd postgis-2.3
> libproj.so.0 => /usr/lib/libproj.so.0 (0x7ff19e1dd000)
>
> # this is where I loose the thread
>
> I don't know if when I make install if there should be a file I need to link
> here?? Yeah I need a clue and would be grateful for any assistance on this.
>
> -guido
>
> This e-mail message and any attachments may contain confidential or legally
> privileged information. If you are not an intended recipient or otherwise
> authorized to receive this message, you should not use, copy, distribute,
> disclose or take any action based on the information contained in this
> e-mail or any attachments. If you have received this message and material in
> error, please advise the sender immediately by reply e-mail and delete this
> message. Thank you on behalf of Applied Geographics, Inc. (AppGeo).
> ___
> 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] Bad plan without && operator

2017-12-06 Thread Paul Ramsey

Just invert the arguments in your call to ST_Intersects().
I don't think we have any way to tell the planner anything about which
side of the condition should drive the nested loop join. And yet it
seems to get the "right" one most of the time. I assume you've already
run 'ANALYZE' and the stats are all up-to-date.
P.

On Wed, Dec 6, 2017 at 9:57 AM, Nicolas Ribot  wrote:
> Hi,
>
> I have 2 tables, indexed and analyzed.
> One big (batiment) with 45M records, one small (tmp.tp1), with 83k records
> (UNLOGGED table)
>
> When performing a spatial join, the planner chooses the smallest table for
> seq scan only if I add a && condition with st_intersects:
>
> EXPLAIN SELECT p.id, b.id
> FROM tmp.tp1 p join ref.batiment b on st_intersects(b.geom, p.geom);
>
> QUERY PLAN
> Nested Loop  (cost=0.15..21819347.50 rows=50019 width=35)
>   ->  Seq Scan on batiment b  (cost=0.00..2238826.04 rows=45794904
> width=193)
>   ->  Index Scan using tp1_geom_gist on tp1 p  (cost=0.15..0.42 rows=1
> width=276)
> Index Cond: (b.geom && geom)
> Filter: _st_intersects(b.geom, geom)
>
> vs
>
> EXPLAIN SELECT p.id, b.id
> FROM tmp.tp1 p join ref.batiment b on p.geom && b.geom and
> st_intersects(b.geom, p.geom);
>
> QUERY PLAN
> Nested Loop  (cost=0.42..118443.25 rows=1 width=35)
>   ->  Seq Scan on tp1 p  (cost=0.00..842.75 rows=13675 width=276)
>   ->  Index Scan using batiment_geom_gist on batiment b  (cost=0.42..8.59
> rows=1 width=193)
> Index Cond: ((p.geom && geom) AND (geom && p.geom))
> Filter: _st_intersects(geom, p.geom)
>
> PG:
> PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
>
> Postgis:
> POSTGIS="2.3.3 r15473" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.2, 08
> September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3"
> LIBJSON="0.11.99" TOPOLOGY (topology procs from "2.3.2 r15302" need upgrade)
> RASTER
>
> Thanks for any hint.
>
> Nicolas
>
> ___
> 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] Bad plan without && operator

2017-12-06 Thread Paul Ramsey

If you change the small table into a normal table (not unlogged) does
the problem go away? Could be an interesting side effect of
"unlogged"?

On Wed, Dec 6, 2017 at 1:14 PM, Nicolas Ribot  wrote:
> Yes, tables are analyzed.
>
> I tried reverting argument, same plan generated:
>
> EXPLAIN SELECT p.id, b.id
> FROM tmp.tp1 p join ref.batiment b on st_intersects(p.geom, b.geom);
>
> QUERY PLAN
> Nested Loop  (cost=0.15..21819347.50 rows=50019 width=35)
>   ->  Seq Scan on batiment b  (cost=0.00..2238826.04 rows=45794904
> width=193)
>   ->  Index Scan using tp1_geom_gist on tp1 p  (cost=0.15..0.42 rows=1
> width=276)
> Index Cond: (geom && b.geom)
> Filter: _st_intersects(geom, b.geom)
>
> Nico
>
> On 6 December 2017 at 19:14, Paul Ramsey  wrote:
>>
>> Just invert the arguments in your call to ST_Intersects().
>> I don't think we have any way to tell the planner anything about which
>> side of the condition should drive the nested loop join. And yet it
>> seems to get the "right" one most of the time. I assume you've already
>> run 'ANALYZE' and the stats are all up-to-date.
>> P.
>>
>> On Wed, Dec 6, 2017 at 9:57 AM, Nicolas Ribot 
>> wrote:
>> > Hi,
>> >
>> > I have 2 tables, indexed and analyzed.
>> > One big (batiment) with 45M records, one small (tmp.tp1), with 83k
>> > records
>> > (UNLOGGED table)
>> >
>> > When performing a spatial join, the planner chooses the smallest table
>> > for
>> > seq scan only if I add a && condition with st_intersects:
>> >
>> > EXPLAIN SELECT p.id, b.id
>> > FROM tmp.tp1 p join ref.batiment b on st_intersects(b.geom, p.geom);
>> >
>> > QUERY PLAN
>> > Nested Loop  (cost=0.15..21819347.50 rows=50019 width=35)
>> >   ->  Seq Scan on batiment b  (cost=0.00..2238826.04 rows=45794904
>> > width=193)
>> >   ->  Index Scan using tp1_geom_gist on tp1 p  (cost=0.15..0.42 rows=1
>> > width=276)
>> > Index Cond: (b.geom && geom)
>> > Filter: _st_intersects(b.geom, geom)
>> >
>> > vs
>> >
>> > EXPLAIN SELECT p.id, b.id
>> > FROM tmp.tp1 p join ref.batiment b on p.geom && b.geom and
>> > st_intersects(b.geom, p.geom);
>> >
>> > QUERY PLAN
>> > Nested Loop  (cost=0.42..118443.25 rows=1 width=35)
>> >   ->  Seq Scan on tp1 p  (cost=0.00..842.75 rows=13675 width=276)
>> >   ->  Index Scan using batiment_geom_gist on batiment b
>> > (cost=0.42..8.59
>> > rows=1 width=193)
>> > Index Cond: ((p.geom && geom) AND (geom && p.geom))
>> > Filter: _st_intersects(geom, p.geom)
>> >
>> > PG:
>> > PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
>> > 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
>> >
>> > Postgis:
>> > POSTGIS="2.3.3 r15473" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.2,
>> > 08
>> > September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3"
>> > LIBJSON="0.11.99" TOPOLOGY (topology procs from "2.3.2 r15302" need
>> > upgrade)
>> > RASTER
>> >
>> > Thanks for any hint.
>> >
>> > Nicolas
>> >
>> > ___
>> > 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] Bad plan without && operator

2017-12-07 Thread Paul Ramsey

Are you sure that's what SET STATISTICS means? I thought it was the
size of the sample drawn to build the statistics. The histogram size
is somewhat hardcoded,
https://github.com/postgis/postgis/blob/svn-trunk/postgis/gserialized_estimate.c#L1446-L1453

P.

On Thu, Dec 7, 2017 at 12:37 AM, Darafei "Komяpa" Praliaskouski
 wrote:
> Hi Nicolas,
>
> I've seen that behavior for tables that have same, rather sparse but
> worldwide coverage for points and polygons.
> In my case it was due to statistic histogram having not enough resolution to
> properly estimate selectivity.
> When you add more && to request, you basically replace selectivity by
> selectivity^2 in planner.
>
> Another way to change this may be
>
> ALTER TABLE tmp.tp1 SET STATISTICS 1;
> ALTER TABLE ref.batimet SET STATISTICS 1;
> ANALYZE tmp.tp1;
> ANALYZE ref.batimet;
>
> By default table is estimated by histogram of 100 cells, basically grid
> 10x10. 1 changes it to 100x100, allowing
>
> чт, 7 дек. 2017 г. в 11:24, Nicolas Ribot :
>>
>> Same with a logged table.
>>
>> It seems the && condition have to be duplicated for the right plan to be
>> chosen:
>>
>> EXPLAIN SELECT p.id, b.id
>> FROM tmp.tp1 p join ref.batiment b
>> on b.geom && p.geom and p.geom && b.geom and _st_intersects(b.geom,
>> p.geom);
>>
>>
>> On 6 December 2017 at 22:36, Paul Ramsey 
>> wrote:
>>>
>>> If you change the small table into a normal table (not unlogged) does
>>> the problem go away? Could be an interesting side effect of
>>> "unlogged"?
>>>
>>> On Wed, Dec 6, 2017 at 1:14 PM, Nicolas Ribot 
>>> wrote:
>>> > Yes, tables are analyzed.
>>> >
>>> > I tried reverting argument, same plan generated:
>>> >
>>> > EXPLAIN SELECT p.id, b.id
>>> > FROM tmp.tp1 p join ref.batiment b on st_intersects(p.geom, b.geom);
>>> >
>>> > QUERY PLAN
>>> > Nested Loop  (cost=0.15..21819347.50 rows=50019 width=35)
>>> >   ->  Seq Scan on batiment b  (cost=0.00..2238826.04 rows=45794904
>>> > width=193)
>>> >   ->  Index Scan using tp1_geom_gist on tp1 p  (cost=0.15..0.42 rows=1
>>> > width=276)
>>> > Index Cond: (geom && b.geom)
>>> > Filter: _st_intersects(geom, b.geom)
>>> >
>>> > Nico
>>> >
>>> > On 6 December 2017 at 19:14, Paul Ramsey 
>>> > wrote:
>>> >>
>>> >> Just invert the arguments in your call to ST_Intersects().
>>> >> I don't think we have any way to tell the planner anything about which
>>> >> side of the condition should drive the nested loop join. And yet it
>>> >> seems to get the "right" one most of the time. I assume you've already
>>> >> run 'ANALYZE' and the stats are all up-to-date.
>>> >> P.
>>> >>
>>> >> On Wed, Dec 6, 2017 at 9:57 AM, Nicolas Ribot
>>> >> 
>>> >> wrote:
>>> >> > Hi,
>>> >> >
>>> >> > I have 2 tables, indexed and analyzed.
>>> >> > One big (batiment) with 45M records, one small (tmp.tp1), with 83k
>>> >> > records
>>> >> > (UNLOGGED table)
>>> >> >
>>> >> > When performing a spatial join, the planner chooses the smallest
>>> >> > table
>>> >> > for
>>> >> > seq scan only if I add a && condition with st_intersects:
>>> >> >
>>> >> > EXPLAIN SELECT p.id, b.id
>>> >> > FROM tmp.tp1 p join ref.batiment b on st_intersects(b.geom, p.geom);
>>> >> >
>>> >> > QUERY PLAN
>>> >> > Nested Loop  (cost=0.15..21819347.50 rows=50019 width=35)
>>> >> >   ->  Seq Scan on batiment b  (cost=0.00..2238826.04 rows=45794904
>>> >> > width=193)
>>> >> >   ->  Index Scan using tp1_geom_gist on tp1 p  (cost=0.15..0.42
>>> >> > rows=1
>>> >> > width=276)
>>> >> > Index Cond: (b.geom && geom)
>>> >> > Filter: _st_intersects(b.geom, geom)
>>> >> >
>>> >> > vs
>>> >> >
>>> >> > EXPLAIN SELECT p.id, b.id
>>> >> > FROM tmp.tp1 p join ref.batiment b on p.geom && b.geom and
>>> &

Re: [postgis-users] Bad plan without && operator

2017-12-07 Thread Paul Ramsey

I don't think so, the comment says 1*ndims is the max number of
cells, and the code underneath does seem to enforce that, so max cells
would be 20,000 for a 2d index.

P

On Thu, Dec 7, 2017 at 8:02 AM, Sandro Santilli  wrote:
> On Thu, Dec 07, 2017 at 06:48:05AM -0800, Paul Ramsey wrote:
>> Are you sure that's what SET STATISTICS means? I thought it was the
>> size of the sample drawn to build the statistics. The histogram size
>> is somewhat hardcoded,
>> https://github.com/postgis/postgis/blob/svn-trunk/postgis/gserialized_estimate.c#L1446-L1453
>
> As you can read in that comment, each side of the grid
> is composed by stats->attr->attstattarget cells, so 100x100
> by default and can be raised by SET STATISTICS.
>
> You both make the grid bigger *and* the sample bigger.
>
> --strk;
> ___
> 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] FOREIGN TABLES and spatial_ref_sys

2017-12-18 Thread Paul Ramsey

You don't know what the problem *is* yet :)
You can "explain (analyze, verbose) select ..." to see the SQL being
sent over the wire.
You can also turn the statement logging on your foreign host to see
what SQL it being run over there.
That should at least clarify what's going wrong.
P

On Mon, Dec 18, 2017 at 12:04 AM, Martin Høgh  wrote:
> I'm using a postgres_fdw to wrap PostGIS tables in a remote server:
>
> CREATE SERVER gc2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
> '172.30.0.41', dbname 'dk', port '5432', extensions 'postgis'); (The last
> option is needed for performance)
>
> When running something like this (where foo.bar is the foreign table):
>
> SELECT
>   *
> FROM foo.bar
> WHERE ST_Distance(ST_Transform("the_geom", 3857),
> ST_GeomFromText('POINT(1082546.5790602104 7346842.908572207)', 3857)) < 23;
>
> I get this error: "spatial_ref_sys" does not exist
>
> I believe this happens because ST_Distance is not using the schema qualified
> name for "spatial_ref_sys". But again I thought all calls in PostGIS
> functions was schema qualified in version 2.3+?
>
> Is there a work-a-round for this problem?
>
> --
> Martin Høgh
> MapCentia ApS
>
>
> ___
> 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] WORKAROUND: Restore of PostgreSQL/PostGIS Database fails with functional Index on ST_Transform

2018-01-17 Thread Paul Ramsey

Remember to

ALTER FUNCTION ST_Transform(geometry, INTEGER) RESET search_path;

when you are done your restore, or all your calls to ST_Transform()
will have a 4x performance penalty.

P


On Wed, Jan 17, 2018 at 8:56 AM, schild  wrote:
> Hi all,
>
>
> Migration of a PostGIS database via pg_restore induces an error, if the 
> database includes a functional index on ST_Transform:
>
>
> [postgres ~] $ pg_restore --username=postgres --dbname=mydb --verbose  
> --no-tablespaces  --index=mygeom_etrs89laea_gist "mydb.backup"
>
> => Error Message (see below)
>
>
> Workaround:
>
> mydb=# ALTER FUNCTION ST_Transform(geometry, INTEGER) SET search_path=public;
>
>
> pg_restore: connecting to database for restore
> pg_restore: creating INDEX "myschema.mygeom_etrs89laea_gist "
>
>
> Greetings from Vienna,
>
> Andreas
>
>
> * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
> * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
> * * * * * * * * * * * * * * * * * * * *
>
> Error Message:
>
> pg_restore: connecting to database for restore
> pg_restore: creating INDEX "myschema.mygeom_etrs89laea_gist"
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 17844; 1259 443698 INDEX 
> mygeom_etrs89laea_gist  postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  relation 
> "spatial_ref_sys" does not exist
> LINE 1: SELECT proj4text FROM spatial_ref_sys WHERE srid = 31287 LIM...
>
> ^
> QUERY:  SELECT proj4text FROM spatial_ref_sys WHERE srid = 31287 LIMIT 1
> Command was: CREATE INDEX mygeom_etrs89laea_gist ON mygeom USING gist 
> (public.st_transform(geom, 93035));
>
>
> * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
> * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
> * * * * * * * * * * * * * * * * * * * *
>
>
> mydb=# SELECT version();
>
> PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.2.0, 64-bit
>
>
> mydb=# SELECT postgis_full_version();
>
> POSTGIS="2.4.2 r16113" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d6"
>
> PROJ="Rel.4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20"
>
> LIBXML="2.9.7" LIBJSON="0.13" LIBPROTOBUF="1.3.0" TOPOLOGY RASTER
> ___
> 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] When PostGIS is used with pg_store_plans, OutOfMemory occurs.

2018-03-05 Thread Paul Ramsey

Confirmed.
Ticketed at https://trac.osgeo.org/postgis/ticket/4036
I recommend you carry the problem over to pg_store_plans, as the OOM
is happening in their code, not in ours.
P.

On Sun, Mar 4, 2018 at 9:29 PM, Kariatsumari, Kazuki
 wrote:
> Dear all.
>
> To the DB of the environment where pg_store_plans is enabled,
> Attempting to enable PostGIS will result in OutOfMemory.
>
> Is this phenomenon specification?
>
> The steps that occurred are described below.
>
> 1. Install PostGIS.
> 2. Install pg_store_plans.
> 3. Write pg_store_plans in shared_preload_libraries in postgresql.conf.
> 4. Restart Postgresql.
> 5. Connect to DB.
> 6. Execute "CREATE EXTENTION postgis".
>
> Thanks,
> Kazuki Kariatsumari
>
> ___
> 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] Status and plans regarding circular arcs support?

2018-03-13 Thread Paul Ramsey

Hi Andrea,
In terms of "native" support for arcs, I believe the answer is "area,
length, distance, indexes and supportive output formats", full stop.
Everything else that accepts them does linearization.
In terms of future improvements, I know of no ongoing work,
particularly in the realm of things like constructive geometry or full
predicate support. Unfortunately it's always been a somewhat narrow
niche.
ATB,
P


On Mon, Mar 12, 2018 at 8:24 AM, Andrea Aime
 wrote:
> Hi,
> is there any document outlining the current status of arc support in
> PostGIS?
> In particular, a list of functions that can those that work off the native
> curves, and those that  linear-ization instead (eventually, those that do
> not work at all against curvilinear geometries)?
> Maybe (and I might be dreaming here :-) ) with some indication of whether
> they will be improvements in that regard?
>
> Cheers
> Andrea
>
> ==
> GeoServer Professional Services from the experts! Visit http://goo.gl/it488V
> for more information.
> ==
>
> Ing. Andrea Aime
> @geowolf
> Technical Lead
>
> GeoSolutions S.A.S.
> Via di Montramito 3/A
> 55054  Massarosa (LU)
> phone: +39 0584 962313
> fax: +39 0584 1660272
> mob: +39  339 8844549
>
> http://www.geo-solutions.it
> http://twitter.com/geosolutions_it
>
> AVVERTENZE AI SENSI DEL D.Lgs. 196/2003
>
> Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i
> file/s allegato/i sono da considerarsi strettamente riservate. Il loro
> utilizzo è consentito esclusivamente al destinatario del messaggio, per le
> finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio
> senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia
> via e-mail e di procedere alla distruzione del messaggio stesso,
> cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo
> anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per
> finalità diverse, costituisce comportamento contrario ai principi dettati
> dal D.Lgs. 196/2003.
>
> The information in this message and/or attachments, is intended solely for
> the attention and use of the named addressee(s) and may be confidential or
> proprietary in nature or covered by the provisions of privacy act
> (Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
> Code).Any use not in accord with its purpose, any disclosure, reproduction,
> copying, distribution, or either dissemination, either whole or partial, is
> strictly forbidden except previous formal approval of the named
> addressee(s). If you are not the intended recipient, please contact
> immediately the sender by telephone, fax or e-mail and delete the
> information in this message that has been received in error. The sender does
> not give any warranty or accept liability as the content, accuracy or
> completeness of sent messages and accepts no responsibility  for changes
> made after they were sent or for other risks which arise as a result of
> e-mail transmission, viruses, etc.
>
>
>
> ___
> 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] recommended Postgres version using PostGIS 2.2

2018-03-23 Thread Paul Ramsey

The PostGIS version matrix is here
https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS
You'll want to use the latest version you can, consistent with the
third party software you're using.
In my experience, unfortunately, the Esri softwares tend to be the
rate limiting step.
P.

On Fri, Mar 23, 2018 at 6:11 AM, Bistrais, Bob  wrote:
> We are looking for recommendations on which version of Postgres is best
> suited for use with PostGIS 2.2.  we anticipate clients to include ArcGIS
> desktop and pro, QGIS including Boundless desktop version, and Mapinfo Pro
> 16.  Any advice, experiences, recommendations are appreciated.
>
>
> ___
> 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-devel] EOL page

2018-03-27 Thread Paul Ramsey

Some more visibility around initial release dates for various versions
would help improve communications around EOL. I can kind of do the
math and say that it's probably 3-4 years old, but it would be nice to
have than solid in the matrix, that the 2.2.0 release was on date X.
P.

On Tue, Mar 27, 2018 at 12:08 PM, Regina Obe  wrote:
> Come to think of it maybe that page is sufficient for our versioning policy
> and we just need to make more prominent and grey out all versions EOL'd.
>
>
>
> Not sure how to grey out cells in Wiki format, but for starters I put in EOL
> in header and will link to website on home page.
>
>
>
>
>
>
>
> From: postgis-devel [mailto:postgis-devel-boun...@lists.osgeo.org] On Behalf
> Of Darafei "Kom?pa" Praliaskouski
> Sent: Tuesday, March 27, 2018 2:49 PM
> To: PostGIS Users Discussion 
> Cc: PostGIS Development Discussion 
> Subject: Re: [postgis-devel] [postgis-users] EOL page
>
>
>
> Can we add more visibility to this page and gray out unsupported
> configurations in matrix?
> https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS
>
>
>
> вт, 27 мар. 2018 г. в 19:34, Regina Obe :
>
> Someone pointed out on IRC there is no easy way to determine when a PostGIS
> version is EOL'd.  He was looking for us to patch 2.1 and I said NO cause
> that's EOL'd.
>
> Anyway to make it clear, I was going to create an EOL on postgis.net page
> similar to what
>
> https://www.postgresql.org/support/versioning/
>
> In addition, just like PostgreSQL group  I'm going to mandate a future EOL
> for those we still support.
>
> Anyone have issues with me setting up such a page and making such godly
> decisions about future EOLS :)
>
> +1 if you are okay with it -1 if you are not.
>
> And of course I expect at least PSC folks to vote, others can vote too.
> I'll jump as soon as I see 2 +1s from PSC and no -1s.
>
> Thanks,
> Regina
>
> ___
> 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] overlaps and intersections strange behavior

2018-05-28 Thread Paul Ramsey

Does knowing that an overlaps test is only true if the interiors of
both objects interact, but there is no full containment relationship
help you understand a little?

On Mon, May 28, 2018 at 8:03 AM, franco base  wrote:
> Hi all,
>
>
> question 1)
> why ST_Overlaps doesn't work with geometry a and b?
> The first 2 point of the 2 line (bold) are the same.
> ST_Intersection works
>
> question 2)
> if I extend line b it becomes c
> (sorry i write text geometry because I have a lot of point coord),
> ST_Overlaps return false and also
> ST_Intersection return a point (length =0)
>
> What's happen?
>
> "POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08
> September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3"
> LIBJSON="0.11.99" TOPOLOGY RASTER"
>
>
> WITH geom AS
> (
> SELECT
> st_setsrid(st_makeline(
> st_makepoint(9.18284350788197,45.4441804010334),
> st_makepoint(9.18334214253396,45.4443310195606)
> ),4326) a,
>
> st_setsrid(st_makeline(ARRAY[
> st_makepoint(9.18334214253396,45.4443310195606),
> st_makepoint(9.18284350788197,45.4441804010334),
> st_makepoint(9.18274220788201,45.4441483010334),
> st_makepoint(9.18274220788201,45.4441483010334),
> st_makepoint(9.18271080788202,45.4441388010335),
> st_makepoint(9.18271080788202,45.4441388010335) ]),4326) b,
>
> '010220E6102700E8527405DF5D224039CEBED6DFB846402A470DAA9D5D22403CFB43E7DAB8464073E7FC62905D22409DC1FDD9D9B8464073E7FC62905D22409DC1FDD9D9B8464040F560458C5D224065A94C8AD9B8464040F560458C5D224065A94C8AD9B84640577DD232875D224085179BAFDCB84640577DD232875D224085179BAFDCB84640C43B5CAF795D2240BB5EA7A0DBB84640BB366DB9795D2240D5A0759CDBB84640CB05C9E4095D2240CC685208D3B84640CB05C9E4095D2240CC685208D3B8464088EC7DE7A75C224074794263CBB8464088EC7DE7A75C224074794263CBB84640280A729E8B5C224050769668C9B84640280A729E8B5C224050769668C9B8464031A63554665C224097E12591C7B8464031A63554665C224097E12591C7B846409A92E968405C22406A63C10CC6B846409A92E968405C22406A63C10CC6B84640164A8CEC245C22402356E826C5B84640164A8CEC245C22402356E826C5B84640D4962A44065C22406FC51DC8C4B84640D4962A44065C22406FC51DC8C4B84640A5F78473E75B2240380991C0C4B84640A5F78473E75B2240380991C0C4B84640B08F6F2CB85B2240449DA518C5B84640B08F6F2CB85B2240449DA518C5B84640087D054BAA5B224050D0A632C5B84640087D054BAA5B224050D0A632C5B84640213C1ECBA65B2240F2CC5C39C5B84640213C1ECBA65B2240F2CC5C39C5B846406BDBF78AA35B2240F0C76D43C5B846406BDBF78AA35B2240F0C76D43C5B84640D731EDD8985B2240C676D265C5B84640D731EDD8985B2240C676D265C5B84640EFDF4074875B224027DB2F9DC5B846404443B89A605B2240369D5619C6B84640B521C808865B22404765B9A1C5B84640'::geometry
> as c
> )
>
>
> SELECT
>  question 1
> st_overlaps(a,b),
> st_length(st_intersection(a,b)::geography)::int,
>  question 2
> st_overlaps(a,c),
> st_length(st_intersection(a,c)::geography)::int
>
> FROM geom
>
>
> return
>
> f;42;f;0
>
>
>
> thanks
> ft
>
>
>
>
>
>
>
> ___
> 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] overlaps and intersections strange behavior

2018-05-28 Thread Paul Ramsey

Also knowing that the end points of a line string are considered the
boundary of the linestring, not the interior...

On Mon, May 28, 2018 at 12:21 PM, Paul Ramsey  wrote:
> Does knowing that an overlaps test is only true if the interiors of
> both objects interact, but there is no full containment relationship
> help you understand a little?
>
> On Mon, May 28, 2018 at 8:03 AM, franco base  wrote:
>> Hi all,
>>
>>
>> question 1)
>> why ST_Overlaps doesn't work with geometry a and b?
>> The first 2 point of the 2 line (bold) are the same.
>> ST_Intersection works
>>
>> question 2)
>> if I extend line b it becomes c
>> (sorry i write text geometry because I have a lot of point coord),
>> ST_Overlaps return false and also
>> ST_Intersection return a point (length =0)
>>
>> What's happen?
>>
>> "POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08
>> September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3"
>> LIBJSON="0.11.99" TOPOLOGY RASTER"
>>
>>
>> WITH geom AS
>> (
>> SELECT
>> st_setsrid(st_makeline(
>> st_makepoint(9.18284350788197,45.4441804010334),
>> st_makepoint(9.18334214253396,45.4443310195606)
>> ),4326) a,
>>
>> st_setsrid(st_makeline(ARRAY[
>> st_makepoint(9.18334214253396,45.4443310195606),
>> st_makepoint(9.18284350788197,45.4441804010334),
>> st_makepoint(9.18274220788201,45.4441483010334),
>> st_makepoint(9.18274220788201,45.4441483010334),
>> st_makepoint(9.18271080788202,45.4441388010335),
>> st_makepoint(9.18271080788202,45.4441388010335) ]),4326) b,
>>
>> '010220E6102700E8527405DF5D224039CEBED6DFB846402A470DAA9D5D22403CFB43E7DAB8464073E7FC62905D22409DC1FDD9D9B8464073E7FC62905D22409DC1FDD9D9B8464040F560458C5D224065A94C8AD9B8464040F560458C5D224065A94C8AD9B84640577DD232875D224085179BAFDCB84640577DD232875D224085179BAFDCB84640C43B5CAF795D2240BB5EA7A0DBB84640BB366DB9795D2240D5A0759CDBB84640CB05C9E4095D2240CC685208D3B84640CB05C9E4095D2240CC685208D3B8464088EC7DE7A75C224074794263CBB8464088EC7DE7A75C224074794263CBB84640280A729E8B5C224050769668C9B84640280A729E8B5C224050769668C9B8464031A63554665C224097E12591C7B8464031A63554665C224097E12591C7B846409A92E968405C22406A63C10CC6B846409A92E968405C22406A63C10CC6B84640164A8CEC245C22402356E826C5B84640164A8CEC245C22402356E826C5B84640D4962A44065C22406FC51DC8C4B84640D4962A44065C22406FC51DC8C4B84640A5F78473E75B2240380991C0C4B84640A5F78473E75B2240380991C0C4B84640B08F6F2CB85B2240449DA518C5B84640B08F6F2CB85B2240449DA518C5B84640087D054BAA5B224050D0A632C5B84640087D054BAA5B224050D0A632C5B84640213C1ECBA65B2240F2CC5C39C5B84640213C1ECBA65B2240F2CC5C39C5B846406BDBF78AA35B2240F0C76D43C5B846406BDBF78AA35B2240F0C76D43C5B84640D731EDD8985B2240C676D265C5B84640D731EDD8985B2240C676D265C5B84640EFDF4074875B224027DB2F9DC5B846404443B89A605B2240369D5619C6B84640B521C808865B22404765B9A1C5B84640'::geometry
>> as c
>> )
>>
>>
>> SELECT
>>  question 1
>> st_overlaps(a,b),
>> st_length(st_intersection(a,b)::geography)::int,
>>  question 2
>> st_overlaps(a,c),
>> st_length(st_intersection(a,c)::geography)::int
>>
>> FROM geom
>>
>>
>> return
>>
>> f;42;f;0
>>
>>
>>
>> thanks
>> ft
>>
>>
>>
>>
>>
>>
>>
>> ___
>> 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] Oddity in _ST_Expand(geography) ?

2018-07-03 Thread Paul Ramsey

Ug. So the issue with XL is an interesting side-effect of the way
expand is implemented in geography...

The first part to understand is that the bounding box of a geography
is expressed in 3-space. So imagine the world, it's a big sphere. Now,
imagine a line on that world. In 3-space, it's a curved arc. Now wrap
a bounding volume around that arc. That is the extent, the bounding
box, of that arc, as understood by geography. (Now you also know how
we implement a spatial index on geography, it's an r-tree in 3-space)

So, what does _ST_Expand(geography) do? It expands that 3-space
bounding volume, and that is stored in the memory representation of
the geography (lwgeom->bbox). It'll also get stored on the disk
(lwgeom to gserialized). Unfortunately it will *now* be stored in the
canonical text format (hex-encoded EWKB) because that representation
has no space for the bbox. This was never really a problem in geometry
because the bounds were implicit in the object itself. For geography
because we change the box but do not change the underlying coordinates
of the object, that homeomorphism is broken.

When I look at what we do for geometry:

select st_astext(st_expand('LINESTRING(0 0, 1 1)'::geometry, 1));

POLYGON((-1 -1,-1 2,2 2,2 -1,-1 -1))

Huh, we morph a linestring into a polygon, odd, right? But it's a
polygon that represents the bounds.

I start to think that perhaps the existing behavior is broken, and
geography expand should rewrite the object so that it becomes
something that has the 3-space bounds we want. Maybe an appropriately
chosen multipoint, for example. It will be very confusing to people,
though, since they tend to think rectangularly about bounds, even when
working with spherical coordinates, which are very decidedly not
rectangular.

Interested in thoughts,

P

On Mon, Jul 2, 2018 at 10:47 PM, Pavan Deolasee
 wrote:
> Hello,
>
> I came across what looks like a little puzzling behaviour of using
> _ST_Expand on a geography data type. This is with PostGIS version 2.2.7,
> running on PostgreSQL 9.5
>
> The following queries return the same output. Does that mean _ST_Expand() is
> not doing its job correctly or is the out function is somehow broken or is
> this an expected behaviour of _ST_Expand on a geography type?
>
>
> postgres=# SELECT _ST_Expand(ST_GeomFromText('POINT(-74.028349 40.737980)',
> 4326), 0);
>  _st_expand
> 
>  010120E610F5F75278D08152C0118DEE20765E4440
> (1 row)
>
> postgres=# SELECT _ST_Expand(ST_GeomFromText('POINT(-74.028349 40.737980)',
> 4326), 100);
>  _st_expand
> 
>  010120E610F5F75278D08152C0118DEE20765E4440
> (1 row)
>
> postgres=# SELECT _ST_Expand(ST_GeomFromText('POINT(-74.028349 40.737980)',
> 4326), 1);
>  _st_expand
> 
>  010120E610F5F75278D08152C0118DEE20765E4440
> (1 row)
>
>
> So irrespective of the quantum by which I extend the value, the result
> remains the same.
>
> I started looking at it because of another problem I noticed. See the output
> of the following query:
>
> postgres=# explain analyze select * from test_geog se where
> ST_DWithin(se.shape, ST_GeomFromText('POINT(-74.028349 40.737980)',
> 4326),1611 * 1);
>
> QUERY PLAN
>
> --
> ---
>  Seq Scan on test_geog se  (cost=0.00..32.20 rows=1 width=60) (actual
> time=0.107..0.221 rows=80 loops=1)
>Filter: ((shape &&
> '010120E610F5F75278D08152C0118DEE20765E4440'::geography) AND
> ('010120E610F5F75278D08152C0118DEE20765E4440'::geography &&
> _st_expand(shape, '1611'::double precision)) AN
> D _st_dwithin(shape,
> '010120E610F5F75278D08152C0118DEE20765E4440'::geography,
> '1611'::double precision, true))
>  Planning time: 0.101 ms
>  Execution time: 0.236 ms
> (4 rows)
>
> The query returns 80 rows. But if I simply replace the WHERE  clause with
> the first filter condition shown in the EXPLAIN output above, no rows are
> returned.
>
>
> postgres=# explain analyze select * from test_geog se where (shape &&
> '010120E610F5F75278D08152C0118DEE20765E4440'::geography);
>   QUERY PLAN
> ---
>  Seq Scan on test_geog se  (cost=0.00..2.00 rows=1 width=60) (actual
> time=0.040..0.040 rows=0 loops=1)
>Filter: (shape &&
> '010120E610F5F75278D08152C0118DEE20765E4440'::geography)
>Rows Removed by Filter: 80
>  Planning time: 0.042 ms
>  Execution time: 0.052 ms
>

Re: [postgis-users] [postgis-devel] Oddity in _ST_Expand(geography) ?

2018-07-03 Thread Paul Ramsey

That's the stupidest idea I ever heard.

/JK

Actually it's just a misunderstanding. We can't return the three-space
box, we have to return a lon/lat shape that, when plotted on the
surface of the earth, has the expected 3-space bounding box. So, it
could get tricky.

P.

On Tue, Jul 3, 2018 at 1:17 PM, Regina Obe  wrote:
>>
>> I start to think that perhaps the existing behavior is broken, and geography
>> expand should rewrite the object so that it becomes something that has the
>> 3-space bounds we want. Maybe an appropriately chosen multipoint, for
>> example. It will be very confusing to people, though, since they tend to 
>> think
>> rectangularly about bounds, even when working with spherical coordinates,
>> which are very decidedly not rectangular.
>>
>> Interested in thoughts,
>>
>> P
>>
>
> A  polyhedral surface.  When you cast box3d to geometry now it gives you a 
> polyhedral surface.
>
> SELECT ST_AsText('BOX3D(1 2 3,5 6 5)'::box3d::geometry)
>
> POLYHEDRALSURFACE Z (((1 2 3,1 6 3,5 6 3,5 2 3,1 2 3)),((1 2 5,5 2 5,5 6 5,1 
> 6 5,1 2 5)),((1 2 3,1 2 5,1 6 5,1 6 3,1 2 3)),((5 2 3,5 6 3,5 6 5,5 2 5,5 2 
> 3)),((1 2 3,5 2 3,5 2 5,1 2 5,1 2 3)),((1 6 3,1 6 5,5 6 5,5 6 3,1 6 3)))
>
> Though I guess we don't support polyhedral surfaces in geography so perhaps 
> that's a pipe dream.
>
>  Regina hops away before Paul has a chance to say
>
> "That's the stupidest idea I ever heard."
>
>
>
>
>
> ___
> 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] no non-null/empty features, unable to compute statistics when running ANALYSE

2018-08-01 Thread Paul Ramsey

Well, first thing just bump up to your relevant latest patch release (2.3.7)
and see if that makes things better.
It might.
Second of all, confirm that the error message is in fact wrong: do those
tables causing the message in fact have some non-null/non-empty features?
Third of all, attach gdb to the process and run analyze on those suspect
tables, and watch what it does. If you don't want to do #3, dump one of the
tables and attach it to a ticket. Before doing so, though, load it into a
new database and see if it *still* complains during analyze.
P

On Tue, Jul 31, 2018 at 4:43 PM, Nathan Woodrow 
wrote:

> Because I was a bit quick on the send this is the version details:
>
> PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3,
> 64-bit POSTGIS="2.3.4 r16009" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel.
> 4.9.3, 15 August 2016" GDAL="GDAL 2.1.3, released 2017/20/01"
> LIBXML="2.9.3" LIBJSON="0.12" (core procs from "2.3.2 r15302" need upgrade)
> RASTER (raster procs from "2.3.2 r15302" need upgrade)
>
> - Nathan
>
> On Wed, Aug 1, 2018 at 9:39 AM Nathan Woodrow 
> wrote:
>
>> Hey,
>>
>> When I run  ANALYSE on some of our tables we seem to be getting the
>> following:
>>
>> [2018-08-01 09:20:31] [0] no non-null/empty features, unable to compute 
>> statistics
>> [2018-08-01 09:20:31] [0] no non-null/empty features, unable to compute 
>> statistics
>>
>>
>> I found an older ticket that was meant to be fixed related to 3D data and
>> large values on the z and m, but from what I can see we don't have any of
>> that in our database.  Dumping the WKT doesn't return any POINTZ or
>> anything like that.
>>
>> Any hints on how to debug this one.  I'm going to check other tables we
>> have but so far 2 are showing it happen and it seems to make the stats not
>> update correctly and given the table is going to be quite large I would
>> like to make sure they stay updated if possible.
>>
>> Regards,
>> Nathan
>>
>>
> ___
> 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] 2.4.5 Released

2018-09-12 Thread Paul Ramsey

The PostGIS development team is pleased to provide bug fix release 2.4.5
for the 2.4 stable branch.

  - #4031, Survive to big MaxError tolerances passed to ST_CurveToLine
   (Sandro Santilli)
  - #4058, Fix infinite loop in linearization of a big radius small arc
   (Sandro Santilli)
  - #4071, ST_ClusterKMeans crash on NULL/EMPTY fixed (Darafei
Praliaskouski)
  - #4079, ensure St_AsMVTGeom outputs CW oriented polygons (Paul Ramsey)
  - #4070, use standard interruption error code on GEOS interruptions
   (Paul Ramsey)
  - #3980, delay freeing input until processing complete (lucasvr)
  - #4090, PG 11 support (Paul Ramsey, Raúl Marín)
  - #4077, Serialization failure for particular empty geometry cases (Paul
Ramsey)
  - #3997, fix bug in lwgeom_median and avoid division by zero (Raúl Marín)
  - #4093, Inconsistent results from qsort callback (yugr)
  - #4081, Geography DWithin() issues for certain cases (Paul Ramsey)
  - #4105, Parallel build of tarball (Bas Couwenberg)

https://trac.osgeo.org/postgis/query?status=closed&resolution=fixed&milestone=PostGIS+2.4.5&col=id&col=summary&col=milestone&col=status&col=type&col=priority&col=component&order=priority

As usual, source downloads are available via http://postgis.net/source/

Your faithful,
PostGIS Development Crew
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] On UpdateGeometrySRID() performance

2018-11-01 Thread Paul Ramsey

Also, if you created an index during your import process, that'll also make
your update slower.
I usually skip bulk updates in favour of writing a fresh table with the
stuff I want..

CREATE TABLE mynewtable AS SELECT ST_SetSRID(geom, 4326) AS geom, ... FROM
myoldtable

That generally writes as fast as is possible though still only single
threaded.

P


On Thu, Nov 1, 2018 at 9:51 AM Darafei "Komяpa" Praliaskouski 
wrote:

> Hi,
>
> Please feed in your WKT polygons in EWKT format:
>
> SRID=4326;POLYGON(...)
>
> Since every update in Postgres is essentially Delete+Insert, time of
> rewriting each and every row being equal to initial Insert time is expected
> thing.
>
> You can also update SRID in two columns in one go:
>
> update tablename set geom1 = ST_SetSRID(geom1, 4326), geom2 =
> ST_SetSRID(geom2, 4326);
>
> Out of curiosity, where did you learn about UpdateGeometrySRID before
> learning about ST_SetSRID?
>
> ср, 31 окт. 2018 г. в 21:57, jerry73204 :
>
>> Hi all,
>>
>> I'm stuck in the low performance of UpdateGeometrySRID().
>>
>> I get started with a 50GB polygon dataset in CSV in EPSG:4326
>> coordinates. Since I find no way to `\copy` the csv while preserving the
>> SRID, the data is imported with null SRID and then `SELECT
>> UpdateGeometrySRID('table', 'column', 4326)`.
>>
>> The `UpdateGeometrySRID()` takes as long time as that of `\copy`, which
>> turns out to be approx two hours. The dataset has two geometry columns
>> and thus I have to take triple time to finish this data.
>>
>> I profiled the postgresql daemon. The avg disk writing speed is 30MB/s,
>> while occasionally peaks to 100MB/s. The SSD, F2FS formatted disk is
>> capable of up to 150MB/s. The daemon does not utilize the 4-core
>> i5-7600k CPU. It seems to be a single process task with avg CPU load
>> 20%, while other workers are idle. I wonder if there's a room for
>> improving the performance. Also, I'm looking for if it's possible to
>> preserve SRID with `\copy`.
>>
>> Jerry Lin
>>
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> --
> Darafei Praliaskouski
> Support me: http://patreon.com/komzpa
> ___
> 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] raster2pgsql binary in apt distros

2018-11-15 Thread Paul Ramsey

>
> Something to think about for our 3.0 release.
>

Might need to be ticketed and assigned to packagers? It's not a big ask,
but it's easy to forget if they just copy'n'ship the old specs.

P


>
>
>
> > -Original Message-
> > From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On
> Behalf
> > Of Nils Nolde
> > Sent: Thursday, November 15, 2018 5:55 AM
> > To: postgis-users@lists.osgeo.org
> > Subject: [postgis-users] raster2pgsql binary in apt distros
> >
> > Hi,
> >
> > I had trouble accessing the raster2pgsql binary with different apt
> postgresql-x-
> > postgis-x distros in the past. It�s not callable as executable and I
> can�t find it
> > in the usual bin/ directories. I can only access it when installing
> �apt-get
> > install postgis� which leaves me with a postgresql v10 distro. Not a
> > dealbreaker, but a bit annoying. So my questions are:
> >
> > - how can I access raster2pgsql in a postgres specific postgis version
> from apt,
> > e.g. postgresql-9.6-postgis-2.4?
> >
> > - is there a way to compile the tool independent of a postgis
> installation, as
> > to not have a full postgres installation for just using raster2pgsql?
> Context:
> > sometimes I only need the tool in a docker container to update an
> external
> > database.
> >
> > Or does it need compilation against specific postgis versions? I guess
> GDAL is a
> > dependency, but the produced .sql should be rather independent of postgis
> > versions right?
> >
> > Of course I could write my own sql statements for raster import, but
> it�s sooo
> > convenient:)
> >
> > Many thanks
> > Nils
> > ___
> > 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] GEOS 3.7.1

2018-11-29 Thread Paul Ramsey

The GEOS development team is happy to release GEOS 3.7.1.

Source code can be downloaded from:
https://download.osgeo.org/geos/geos-3.7.1.tar.bz2

Changes in 3.7.1
2018-11-29

- Bug fixes / improvements
  - Fix crash in GEOSCoordSeq_isCCW with empty coordseq
(#927, Sergey Fedoseev)
  - Fix crash in GEOSInterpolate with empty LineString
(#926, Sergey Fedoseev)
  - Fix crash in GEOSUnaryUnion with empty LineString
(#928, Sergey Fedoseev)
  - Fix memory leak in SIRtree::insert (#919, Dan Baston)
  - Reduce required autoconf to 2.63
(#56, John Harvey)
  - Fix incorrect return values on error from GEOSLength
GEOSisValidDetail (#941, Dan Baston)

Thanks,
GEOS Development Team
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Improvement suggestion

2018-12-02 Thread Paul Ramsey

Try the solution outlined here:

https://gis.stackexchange.com/questions/31310/acquiring-arcgis-like-speed-in-postgis


On Sun, Dec 2, 2018 at 10:44 AM Paul van der Linden <
paul.doskabou...@gmail.com> wrote:

> As I am working with large polygons, I'm always struggling with
> performance, and trying to find ways to improve them.
> F.e. I have lots of queries like:
> SELECT ST_Intersection(table1.geom,table2.geom)
> FROM table1
> JOIN table2 on ST_Intersects(table1.geom,table2.geom)
>
> In case of large polygons this is sometimes a bottleneck, and I have the
> following suggestion:
> Create a function which returns the relation between 2 polygons (within,
> intersects or disjunct) so that I can do the following:
>
> SELECT
>   CASE
>  WHEN ST_Relate(table1.geom,table2.geom)=intersects THEN
> ST_Intersection(table1.geom,table2.geom)
>  ELSE table1.geom
>   END
> FROM table1
> JOIN table2 on ST_Relate(table1.geom,table2.geom) IN (intersects,within)
>
> or (because ST_Relate is calculated twice in previous query):
>
> SELECT
>   CASE
>  WHEN relate=intersects THEN ST_Intersection(t1geom,t2geom)
>  ELSE t1geom
>   END
> FROM (
>   SELECT ST_Relate(table1.geom,table2.geom) as relate,table1.geom AS
> t1geom,table2.geom AS t2geom FROM table1
>   JOIN table2 on table1.geom && table2.geom
> ) AS allpolies
> WHERE relate IN (intersects,within)
>
> ___
> 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] Improvement suggestion

2018-12-05 Thread Paul Ramsey

On Tue, Dec 4, 2018 at 2:27 AM Darafei "Komяpa" Praliaskouski 
wrote:

> If you are managing large geometries, splitting them off into "geometry
> table" with non-uniquie ID and subdivided parts sometimes helps.
> You want to build a tree on top of your geometry internals to make it all
> fast, one way to do that is to make sure the internal parts of geometry are
> available for indexing in GiST, via ST_Subdivide.
>
> Other beautiful way would be to hide this all behind the scenes in PostGIS
> itself and making ST_Intersection and ST_Intersects optimize the cases you
> mentioned internally, caching some kind of tree internally, probably in the
> geometry itself.
>

The efficient handling of ultra-large geometries, even in a caching case,
is going to involve some core changes to how PostGIS deals with PostgreSQL.
First, we need to make checking the relevance of the cache cheaper. Right
now, checking the cache involves comparing the entire contents of the
current geometry (gserialized form) with the entire contents of the cached
geometry (gserialized form), using a memcmp. The memcmp itself isn't so
expensive, but fully reading in the current geometry (gserialized) *is*
expensive, as the whole thing has to be de-toasted. This gets into the
decompression issues for toasted values, which we can address both by
patching PgSQL [1] and by changing up our own serialization to use
uncompressed storage and compressing the things we want to compress
ourselves (leaving an uncompressed header, for example). Once we are able
to read back only a part of a large geometry, it becomes possible to use a
hashcode in the header to test whether the current cache is still valid,
and that aspect of ultra-large geometry reading gets better.

One aspect that doesn't get better is the selectivity of the bbox of the
ultralarge geometry. This is something that subdividing neatly fixes, at
the same time as it dodges the toasting problem. If you aren't subdividing
then your ultralarge geometry will probably have an extremely
over-determined bounds, and so you will be testing more inputs against the
full geometry than you really want to. Even with a nice, efficient, cached
tree to do the testing with, the penalty of all those unnecessary tests
builds up.

The way around that is to start looking at using inverted indexes and
multi-key coverages of polygons, which looks a lot like the way people with
key/value stores do spatial indexing. There are some implementation issues
there, particularly with geometry, that doesn't have a nice, implicit
coordinate bounds for any given collection of features (as opposed to
geography, which always lives inside (-180,-90,180,90). There's also some
limitations with respect to the current PgSQL implementation which we might
want to address, most notably the use of 32 bit keys in the GIN index. If
we take a bit away for indicating key containment vs overlaps, we're left
with only 31 bits, which in geography space is a about metre resolution (if
memory serves). Not survey grade, and maybe not suitable for all purposes.

Note that many of these issues can be worked around very easily (amazingly
easily, in my opinion) by modelling with a subdivided query table, given
orders of magnitude better performance without any infrastructural changes
to PostGIS *or* PgSQL. The power of homogeneous inputs is hard to
overstate, and the difficulty of dealing efficiently with the full
heterogeneous range of spatial data inputs is hard to understate.

P

>
> On Mon, Dec 3, 2018 at 11:43 PM Paul van der Linden <
> paul.doskabou...@gmail.com> wrote:
>
>> No, didn't do that.
>> Don't think it's going to improve readability of the query to be honest
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
> --
> Darafei Praliaskouski
> Support me: http://patreon.com/komzpa
> ___
> 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] Improvement suggestion

2018-12-05 Thread Paul Ramsey

Forgot to include my reference [1]
https://commitfest.postgresql.org/21/1868/

On Wed, Dec 5, 2018 at 11:19 AM Paul Ramsey 
wrote:

>
>
> On Tue, Dec 4, 2018 at 2:27 AM Darafei "Komяpa" Praliaskouski <
> m...@komzpa.net> wrote:
>
>> If you are managing large geometries, splitting them off into "geometry
>> table" with non-uniquie ID and subdivided parts sometimes helps.
>> You want to build a tree on top of your geometry internals to make it all
>> fast, one way to do that is to make sure the internal parts of geometry are
>> available for indexing in GiST, via ST_Subdivide.
>>
>> Other beautiful way would be to hide this all behind the scenes in
>> PostGIS itself and making ST_Intersection and ST_Intersects optimize the
>> cases you mentioned internally, caching some kind of tree internally,
>> probably in the geometry itself.
>>
>
> The efficient handling of ultra-large geometries, even in a caching case,
> is going to involve some core changes to how PostGIS deals with PostgreSQL.
> First, we need to make checking the relevance of the cache cheaper. Right
> now, checking the cache involves comparing the entire contents of the
> current geometry (gserialized form) with the entire contents of the cached
> geometry (gserialized form), using a memcmp. The memcmp itself isn't so
> expensive, but fully reading in the current geometry (gserialized) *is*
> expensive, as the whole thing has to be de-toasted. This gets into the
> decompression issues for toasted values, which we can address both by
> patching PgSQL [1] and by changing up our own serialization to use
> uncompressed storage and compressing the things we want to compress
> ourselves (leaving an uncompressed header, for example). Once we are able
> to read back only a part of a large geometry, it becomes possible to use a
> hashcode in the header to test whether the current cache is still valid,
> and that aspect of ultra-large geometry reading gets better.
>
> One aspect that doesn't get better is the selectivity of the bbox of the
> ultralarge geometry. This is something that subdividing neatly fixes, at
> the same time as it dodges the toasting problem. If you aren't subdividing
> then your ultralarge geometry will probably have an extremely
> over-determined bounds, and so you will be testing more inputs against the
> full geometry than you really want to. Even with a nice, efficient, cached
> tree to do the testing with, the penalty of all those unnecessary tests
> builds up.
>
> The way around that is to start looking at using inverted indexes and
> multi-key coverages of polygons, which looks a lot like the way people with
> key/value stores do spatial indexing. There are some implementation issues
> there, particularly with geometry, that doesn't have a nice, implicit
> coordinate bounds for any given collection of features (as opposed to
> geography, which always lives inside (-180,-90,180,90). There's also some
> limitations with respect to the current PgSQL implementation which we might
> want to address, most notably the use of 32 bit keys in the GIN index. If
> we take a bit away for indicating key containment vs overlaps, we're left
> with only 31 bits, which in geography space is a about metre resolution (if
> memory serves). Not survey grade, and maybe not suitable for all purposes.
>
> Note that many of these issues can be worked around very easily (amazingly
> easily, in my opinion) by modelling with a subdivided query table, given
> orders of magnitude better performance without any infrastructural changes
> to PostGIS *or* PgSQL. The power of homogeneous inputs is hard to
> overstate, and the difficulty of dealing efficiently with the full
> heterogeneous range of spatial data inputs is hard to understate.
>
> P
>
>
>
>>
>> On Mon, Dec 3, 2018 at 11:43 PM Paul van der Linden <
>> paul.doskabou...@gmail.com> wrote:
>>
>>> No, didn't do that.
>>> Don't think it's going to improve readability of the query to be honest
>>> ___
>>> postgis-users mailing list
>>> postgis-users@lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>>
>>
>> --
>> Darafei Praliaskouski
>> Support me: http://patreon.com/komzpa
>> ___
>> 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] Compiling Postgis from source - gdal error

2018-12-11 Thread Paul Ramsey

Well, first “is it plugged in” question… before running ./configure if you run 
“which gdal-config” what do you see?

P

> On Dec 10, 2018, at 8:57 PM, Bruce Rindahl  wrote:
> 
> I have successfully compiled 2.5.0 from source with pg11.0, geos 2.3.2.  The 
> ./configure message  is:
> checking for gdal-config... /usr/local/bin/gdal-config
> checking GDAL version... 2.3.2
> checking for OGR enabled... yes
> checking gdal.h usability... yes
> checking gdal.h presence... yes
> checking for gdal.h... yes
> checking ogr_api.h usability... yes
> checking ogr_api.h presence... yes
> checking for ogr_api.h... yes
> checking cpl_conv.h usability... yes
> checking cpl_conv.h presence... yes
> checking for cpl_conv.h... yes
> checking for library containing GDALAllRegister... none required
> checking for library containing OGRRegisterAll... none required
> checking for library containing GDALFPolygonize... none required
> 
> However, with postgis-2.5.1, postgis-2.5.2-dev, and postgis-3.0-dev I get:
> checking for gdal-config... /usr/local/bin/gdal-config
> checking GDAL version... 2.3.2
> checking for OGR enabled... yes
> checking gdal.h usability... yes
> checking gdal.h presence... yes
> checking for gdal.h... yes
> checking ogr_api.h usability... yes
> checking ogr_api.h presence... yes
> checking for ogr_api.h... yes
> checking cpl_conv.h usability... yes
> checking cpl_conv.h presence... yes
> checking for cpl_conv.h... yes
> checking for library containing GDALAllRegister... no
> configure: error: could not find GDAL
> 
> Same installation of GDAL.  Any ideas?
> Thanks
> 
> ___
> 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] Compiling Postgis from source - gdal error

2018-12-11 Thread Paul Ramsey

Reading through the configure routine, I see nothing that should be causing
those tests to fail now if they worked in the past... they are the same
code as in 2.5.0

On Tue, Dec 11, 2018 at 12:32 PM Bruce Rindahl 
wrote:

> Response from 'which gdal-config'
>
> pi@pi3ch:~/postgis-2.5.0 $ which gdal-config
> /usr/local/bin/gdal-config
> pi@pi3ch:~/postgis-2.5.0 $ gdal-config --version
> 2.3.2
>
> pi@pi3B0:~/postgis-2.5.1 $ which gdal-config
> /usr/local/bin/gdal-config
> pi@pi3B0:~/postgis-2.5.1 $ gdal-config --version
> 2.3.2
>
> pi@pi3B0:~/postgis-3.0.0dev $ gdal-config --version
> 2.3.2
> pi@pi3B0:~/postgis-3.0.0dev $ which gdal-config
> /usr/local/bin/gdal-config
> ___
> 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] very strange bug generating incorrect ST_Distance calculations

2019-01-10 Thread Paul Ramsey

Comparing the results of _ST_DistanceTree and _ST_DistanceUnCached is
a good way to tease out whether there are differences between the
implementations without having to fight the caching machinery along
the way.

P.

On Thu, Jan 10, 2019 at 7:00 AM Raúl Marín Rodríguez
 wrote:
>
> Hi,
>
> In my case I get dist_geography between gid 1 and gid 3 0 in both queries.
>
> This looks like a possible issue around the geos cache, something similar
> to https://trac.osgeo.org/postgis/ticket/4269. Can you run them with
> EXPLAIN ANALYZE?
>
> --
> Raúl Marín Rodríguez
> carto.com
> ___
> 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] very strange bug generating incorrect ST_Distance calculations

2019-01-10 Thread Paul Ramsey

You’ve found a bug, it’s in PostGIS (geodetic code is in postgis native), and 
the extra detail that you can get it to manifest with MULTIPOLYGON but the same 
rings in POLYGON don’t manifest is an excellent extra detail to help in fixing 
it. 

> On Jan 10, 2019, at 8:06 AM, David M. Kaplan  wrote:
> 
> 
> On 10/01/2019 12:26, David M. Kaplan wrote:
>> Comparing the results of _ST_DistanceTree and _ST_DistanceUnCached is
>> a good way to tease out whether there are differences between the
>> implementations without having to fight the caching machinery along
>> the way.
>> 
>> P.
> 
> Thanks for the responses. I tried with _ST_DistanceTree and 
> _ST_DistanceUnCached
> and as predicted, tree gives 0 and uncached gives the correct answer. What is 
> the appropriate work around to avoid this issue? Who should I report this 
> problem to? The maintainers of libgeos?
> 
> Also, how should I interpret that this error only occurs when one of the 
> geometries is a multipolygon?
> 
> In response to Raúl, I found that both queries give 0 for postgis 2.5.0 
> r16836, but only the multiline query gives zero for POSTGIS="2.4.3 r16312"...
> 
> Cheers,
> David
> 
>> 
>> On Thu, Jan 10, 2019 at 7:00 AM Raúl Marín Rodríguez
>> > > wrote:
>> >
>> > Hi,
>> >
>> > In my case I get dist_geography between gid 1 and gid 3 0 in both queries.
>> >
>> > This looks like a possible issue around the geos cache, something similar
>> > to https://trac.osgeo.org/postgis/ticket/4269. 
>> >  Can you run them with
>> > EXPLAIN ANALYZE?
>> >
>> > --
>> > Raúl Marín Rodríguez
>> > carto.com
>> > ___
>> > postgis-users mailing list
>> > postgis-users at lists.osgeo.org 
>> > 
>> > https://lists.osgeo.org/mailman/listinfo/postgis-users 
>> > 
> 
>> Hi, 
>> 
>> I have found what seems to be an extremely bizarre bug. I am not sure if it 
>> is a postgis issue or postgresql issue, but it leads 
>> ST_Distance(geography(Polygon,4326),geography(MultiPolygon,4326)) to return 
>> 0 (zero) when the true answer is not zero under certain peculiar situations. 
>> 
>> The table data for a set of polygons that generate the error can be 
>> downloaded here: 
>> 
>> http://www.davidmkaplan.fr/bad_geography_dist_calcs.csv 
>>  
>> 
>> Once you have that data, the code to generate the error is: 
>> 
>> CREATE TEMP TABLE tt (gid int PRIMARY KEY, geom geometry); 
>> 
>> \copy tt FROM 'bad_geography_dist_calcs.csv' WITH (FORMAT 'csv',HEADER TRUE) 
>> 
>> -- Incorrect dist_geography for t1.gid=1 and t2.gid=3 
>> SELECT t1.gid AS gid1, t2.gid AS gid2, 
>>ST_Distance(t1.geom,t2.geom) AS dist_lonlat, 
>>ST_Distance(ST_Transform(t1.geom,26918), 
>>ST_Transform(t2.geom,26918)) AS dist_utm, 
>>ST_Distance(t1.geom::geography,t2.geom::geography) AS dist_geography, 
>> ST_Distance((ST_Dump(t1.geom)).geom::geography,(ST_Dump(t2.geom)).geom::geography)
>>  AS dist_geography_dump 
>> FROM tt t1 JOIN tt t2 ON t1.gid> WHERE t1.gid=1 
>> ; 
>> 
>> -- No error just by specifying t2.gid=3 in the WHERE clause 
>> SELECT t1.gid AS gid1, t2.gid AS gid2, 
>>ST_Distance(t1.geom,t2.geom) AS dist_lonlat, 
>>ST_Distance(ST_Transform(t1.geom,26918), 
>>ST_Transform(t2.geom,26918)) AS dist_utm, 
>>ST_Distance(t1.geom::geography,t2.geom::geography) AS dist_geography, 
>> ST_Distance((ST_Dump(t1.geom)).geom::geography,(ST_Dump(t2.geom)).geom::geography)
>>  AS dist_geography_dump 
>> FROM tt t1 JOIN tt t2 ON t1.gid> WHERE t1.gid=1 AND t2.gid=3 
>> ; 
>> 
>> Hopefully, if I am not crazy, you will have a 0 value of dist_geography in 
>> the first query for t2.gid=3, but not in the second. 
>> 
>> Does anyone have any idea what could be causing this? To be honest, I am not 
>> really certain that I know where to start do diagnose the problem... 
>> 
>> The specifics of my installation are: 
>> 
>> POSTGIS="2.4.3 r16312" PGSQL="100" GEOS="3.7.0-CAPI-1.11.0 673b9939" 
>> PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.3.2, released 2018/09/21" 
>> LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" RASTER 
>> 
>> psql 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)) 
>> 
>> Ubuntu 18.04.1 LTS 
>> 
>> I have also tested this with postgis 2.5.0 r16836 and there the incorrect 0 
>> is in both queries!?!?!? 
>> 
>> Thanks, 
>> David 
>> 
>> 
> 
> -- 
> **
> David M. Kaplan
> Charge de Recherche 1
> 
> Institut de Recherche pour le Developpement (IRD)
> UMR MARBEC (IRD/Ifremer/CNRS/UMII)
> av. Jean Monnet
> CS 30171
> 34203 Sete cedex
> France
> 
> Email: david.kap...@ird.fr 
> Phone: +33 (0)4 99 57 32 25
> Fax: +33 (0)4 99 57 32 95
> 
> http://www.umr-marbec.

Re: [postgis-users] Multicurve

2019-01-11 Thread Paul Ramsey

Where are these “shapefiles with curves” you are seeing? the original spec did 
not include them… any documentation you can point to?

https://www.esri.com/library/whitepapers/pdfs/shapefile.pdf 


P.

> On Jan 11, 2019, at 4:13 PM, Clifford Snow  wrote:
> 
> Lately some of the data from local governments contain road data with 
> multicurve geometry. Attempting to load with shp2pgsql fails. 
> 
> I've been using ogr2ogr with the -nlt option to specify MULTILINESTRING. 
> 
> Is there any better work arounds? According to PostGIS, it does support 
> multicurve geometries. 
> 
> Thanks in advance,
> Clifford
> 
> -- 
> @osm_seattle
> osm_seattle.snowandsnow.us 
> OpenStreetMap: Maps with a human touch
> ___
> 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] Geography/geometry

2019-01-21 Thread Paul Ramsey

If it’s just Ireland, transform to a good Irish projection after casting to 
geometry. For maximum awsomeness, transform to gnomonic, in which great circles 
are represented as straight lines… then even all the introduced points will be 
at the correct geographic locations. Works for smaller areas, like Ireland.

P

> On Jan 20, 2019, at 9:23 PM, Simon Greener  wrote:
> 
> Folks,
> 
> I'm in a situation where I need to ST_Union or ST_Collect some osm_county 
> Polygon (not MultiPolygon) data for Ireland.
> 
> Now, because ST_Union or ST_Collect do not support geography, I cast to 
> geometry before calling.
> 
> select min(a.osm_id) as osm_id, 
>a.name,
>count(*) as parts, 
>ST_Union(a.geog4326::geometry)::geography as geog4326 -- or ST_Collect
> from data.osm_county as a
> group by a.name;
> 
> Whence I get this:
> 
> ERROR: lwgeom_area_spher(oid) returned area < 0.0
> 
> Investigating I get results like this:
> 
> select distinct st_isvalidreason(a.geog4326::geometry) from data.osm_county 
> as a;
> 
> "Hole lies outside shell[-10.2589459 53.9746452]"
> etc
> 
> I guess this is expected because geodetic lines in the source geography are 
> being treated as straight in the cast'd geometry.
> 
> If I use ST_Transform to project a 4326 poly to a 3857 and then call the 
> ST_Union aggregate, or identify a single geography that has the invalidity 
> and execute a self-union, I get the following in both situations.
> 
> ERROR: GEOSUnaryUnion: TopologyException: Input geom 0 is invalid: Hole lies 
> outside shell at or near point -1148162.9982628345 7095296.1166736316 at 
> -1148162.9982628345 7095296.1166736316
> 
> I can't for the life of me work out how to complete the aggregated ST_Union 
> on the 4326 geography data.
> 
> Anyone point out what I am doing wrong or give me a pointer to what I can do 
> to achieve the aggregated union?
>  
> Regards
> Simon
> 
> Spatial Advice & Solutions Architecture
> Database Spatial Stored Procedure Designer
> Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE FME
> Awarded "2011 Oracle Spatial Excellence Award for Education and Research"
> A: 39 Cliff View Drive, Allens Rivulet, 7150, Tas, Aust
> W: www.spdba.com.au 
> E: si...@spdba.com.au 
> V: +61 362 396 397
> M: +61 418 396 391
> GITC Supplier: T1005
> Skype: sggreener
> Long: 147.20515 (147° 12' 18" E)
> Lat: -43.01530 (43° 00' 55" S)
> GeoHash: r22em9r98wg
> NAC:W80CK 7SWP3
> ___
> 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] Geography/geometry

2019-01-21 Thread Paul Ramsey

> On Jan 21, 2019, at 1:00 PM, Simon Greener  wrote:
> 
> When a geography is casted to geometry for use in the aggregate form of 
> ST_Union (or other commands such as ST_Within) is the processing done 
> assuming the data is projected even when geodetic?

All processing of geometry make cartesian assumptions. Processing of geography 
using geography native functions (no casting required) makes spherical 
assumptions. When you case from geography::geometry you are telling the 
database “please process this data using cartesian assumptions”. Do we support 
spherical processing for all (or even many) kinds of operations? No we do not. 
That’s hard. Could we in the future? Sure, it might happen.

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

Re: [postgis-users] Geography/geometry

2019-01-21 Thread Paul Ramsey

Not really sure that’s the answer… I’d much rather see work on geodetic edges 
in GEOS

P

> On Jan 21, 2019, at 1:20 PM, Darafei Komяpa Praliaskouski  
> wrote:
> 
> If someone is willing, there is an old Geography Overlays patch that needs 
> lots of love: https://github.com/postgis/postgis/pull/191 
> <https://github.com/postgis/postgis/pull/191>
> 
> On Tue, Jan 22, 2019 at 12:13 AM Paul Ramsey  <mailto:pram...@cleverelephant.ca>> wrote:
> 
> 
> > On Jan 21, 2019, at 1:00 PM, Simon Greener  > <mailto:si...@spatialdbadvisor.com>> wrote:
> > 
> > When a geography is casted to geometry for use in the aggregate form of 
> > ST_Union (or other commands such as ST_Within) is the processing done 
> > assuming the data is projected even when geodetic?
> 
> All processing of geometry make cartesian assumptions. Processing of 
> geography using geography native functions (no casting required) makes 
> spherical assumptions. When you case from geography::geometry you are telling 
> the database “please process this data using cartesian assumptions”. Do we 
> support spherical processing for all (or even many) kinds of operations? No 
> we do not. That’s hard. Could we in the future? Sure, it might happen.
> 
> ATB,
> P
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
> https://lists.osgeo.org/mailman/listinfo/postgis-users 
> <https://lists.osgeo.org/mailman/listinfo/postgis-users>
> 
> -- 
> Darafei Praliaskouski
> Support me: http://patreon.com/komzpa 
> <http://patreon.com/komzpa>___
> 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] Geography/geometry

2019-01-21 Thread Paul Ramsey



> On Jan 21, 2019, at 1:28 PM, Simon Greener  wrote:
> 
> I can't find a suitable gnomic srid for PostGIS.
> 

You’re going to want one centred on Ireland, not the pole, and I think maybe 
it’s wanting a geodetic basis, try this:

 +proj=gnom +lat_0=53.35 +lon_0=-6.26 +x_0=0 +y_0=0 +ellps=WGS84 +units=m 
+no_defs

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

Re: [postgis-users] Geography/geometry

2019-01-21 Thread Paul Ramsey



> On Jan 21, 2019, at 1:46 PM, Simon Greener  wrote:
> 
> Thanks Paul.
> 
> However, I get the same number of distinct st_isvalidreason results.

Something is awry here, because I get only 'Valid Geometry’ as a validity check 
result… I loaded your shape file, I have 42 irish counties, I didn’t even try 
to use geography, just used geometry and they are all valid to start with.

I can even push them through a couple casts and they remain valid (as I would 
hope they would, since the coordinates shouldn’t change) 

select distinct st_isvalidreason(geom::geography::geometry) from county;

P

> 
> Is there anything I can do with ogr2ogr when loading to help?
> 
> Simon
> 
> On Tue, 22 Jan 2019 08:34:02 +1100, Paul Ramsey  
> wrote:
> 
>> +proj=gnom +lat_0=53.35 +lon_0=-6.26 +x_0=0 +y_0=0 +ellps=WGS84 +units=m 
>> +no_defs
> 
> 
> -- 
> Regards
> Simon
> 
> Spatial Advice & Solutions Architecture
> Database Spatial Stored Procedure Designer
> Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE FME
> Awarded "2011 Oracle Spatial Excellence Award for Education and Research"
> A: 39 Cliff View Drive, Allens Rivulet, 7150, Tas, Aust
> W: www.spdba.com.au
> E: si...@spdba.com.au
> V: +61 362 396 397
> M: +61 418 396 391
> GITC Supplier: T1005
> Skype: sggreener
> Long: 147.20515 (147° 12' 18" E)
> Lat: -43.01530 (43° 00' 55" S)
> GeoHash: r22em9r98wg
> NAC:W80CK 7SWP3

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

Re: [postgis-users] Geography/geometry

2019-01-21 Thread Paul Ramsey

Just ’select geom’ will return the extended hexwkb (which we should have JTS 
support as a matter of convenience). If you need pure standard hex wkb, then

select encode(st_asbinary(geom), ‘hex’) 

will do that

P

> On Jan 21, 2019, at 2:39 PM, Martin Davis  wrote:
> 
> Not much help, I'm afraid.   I need the geometry in WKBHex.  Not sure offhand 
> how to get that out of PostGIS.
> 
> Just post the WKT and let's see what that looks like.
> 
> On Mon, Jan 21, 2019 at 1:52 PM Simon Greener  > wrote:
> Try here for an example:
> 
> https://www.dropbox.com/s/n234bc6i5hj7y4o/Ireland.sql?dl=0 
> ___
> 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 upgrade with Postgresql 9.6 to 11 upgrade

2019-01-25 Thread Paul Ramsey

You have two options, or maybe less, depending on where you get your PostgreSQL 
and PostGIS from. If you get them from some packager or other you might not 
have the freedom to mix-and-match versions.

OPTION: Simple And Lots of Steps

- Install Pg11 and PostGIS 2.4.4 for Pg11. 
- Run your PgSQL pg_upgrade process
- Install PostGIS 2.5 for Pg11
- Run the PostGIS ‘ALTER EXTENSION postgis UPDATE’ process
- Celegrate

OPTION: More harder

- Install Pg11 and PostGIS 2.5 for Pg11
- Try and run your pg_upgrade
- If you’re lucky, it “just works”
- If you’re not lucky, you might have to find the Pg11 install of 
postgis-2.5.so and make a symlink to it from postgis-2.4.so
- If you’re not lucky, you might have to track down and remove old versions of 
geos that are interfering with newer versions

Note that in both options, you do have to install a version of PostGIS that has 
been built against your new database version. The PostGIS 2.4 you already have 
installed for Pg9.6 is only serving your 9.6 databases, it does nothing for 
your new Pg11 databases.

ATB,
P




> On Jan 25, 2019, at 8:26 AM, Bo Guo  wrote:
> 
> Hi all,
> 
> We plan to upgrade PostgreSQL 9.6 (PostGIS "2.4.4 r16526") to PostgreSQL 11 
> in the next couple of weeks.
> 
> I am just wondering if there is anything I should be concerned about on the 
> side of Postgis, such as will postgis version upgrade automatically?  If not, 
> should I upgrade Postgis after the PostgreSQL update?, etc.
> 
> Thanks!
> 
> Bo
> 
> 
> ___
> 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 upgrade with Postgresql 9.6 to 11 upgrade

2019-01-25 Thread Paul Ramsey

Just run 

  SELECT postgis_full_version()

You should see the right version and libraries referenced.

P

> On Jan 25, 2019, at 8:46 AM, Bo Guo  wrote:
> 
> OK.  Is there a way to validate the success of a Postgis upgrade?
> 
> On 1/25/19 9:33 AM, Paul Ramsey wrote:
>> You have two options, or maybe less, depending on where you get your 
>> PostgreSQL and PostGIS from. If you get them from some packager or other you 
>> might not have the freedom to mix-and-match versions.
>> 
>> OPTION: Simple And Lots of Steps
>> 
>> - Install Pg11 and PostGIS 2.4.4 for Pg11. 
>> - Run your PgSQL pg_upgrade process
>> - Install PostGIS 2.5 for Pg11
>> - Run the PostGIS ‘ALTER EXTENSION postgis UPDATE’ process
>> - Celegrate
>> 
>> OPTION: More harder
>> 
>> - Install Pg11 and PostGIS 2.5 for Pg11
>> - Try and run your pg_upgrade
>> - If you’re lucky, it “just works”
>> - If you’re not lucky, you might have to find the Pg11 install of 
>> postgis-2.5.so and make a symlink to it from postgis-2.4.so
>> - If you’re not lucky, you might have to track down and remove old versions 
>> of geos that are interfering with newer versions
>> 
>> Note that in both options, you do have to install a version of PostGIS that 
>> has been built against your new database version. The PostGIS 2.4 you 
>> already have installed for Pg9.6 is only serving your 9.6 databases, it does 
>> nothing for your new Pg11 databases.
>> 
>> ATB,
>> P
>> 
>> 
>> 
>> 
>>> On Jan 25, 2019, at 8:26 AM, Bo Guo  
>>> <mailto:bo@gisticinc.com> wrote:
>>> 
>>> Hi all,
>>> 
>>> We plan to upgrade PostgreSQL 9.6 (PostGIS "2.4.4 r16526") to PostgreSQL 11 
>>> in the next couple of weeks.
>>> 
>>> I am just wondering if there is anything I should be concerned about on the 
>>> side of Postgis, such as will postgis version upgrade automatically?  If 
>>> not, should I upgrade Postgis after the PostgreSQL update?, etc.
>>> 
>>> Thanks!
>>> 
>>> Bo
>>> 
>>> 
>>> ___
>>> postgis-users mailing list
>>> postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users 
>>> <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 
>> <https://lists.osgeo.org/mailman/listinfo/postgis-users>-- 
> Bo Guo, PhD, PE 
> Gistic Research, Inc.
> 2033 E Warner Rd. Ste 105 
> Tempe, AZ 85284 
> www.gisticinc.com  
> Tel: 480-656-9962
> ___
> 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] Request for ideas to enhance the geometry viewer - GSoC

2019-01-29 Thread Paul Ramsey

Yeah, every time I’ve thought about renderer-in-the-db I’ve come back to this. 
There’s nothing especially privileged about the db that makes a lot of sense 
for bringing rendering in-process. And boy it’s a rat hole… once you go down, 
you’re never coming back out.

P

> On Jan 29, 2019, at 5:36 PM, Paolo Cavallini  wrote:
> 
> Are you sure you want to reimplement what a GIS does already? Doing proper, 
> powerful styling it's a really big task.
> Sorry to jump in like this, I'm a strong advocate of open source tools also 
> because you can reuse what is available, avoiding duplication of efforts.
> Cheers.
> 
> Il 30 gennaio 2019 02:13:50 CET, Martin Davis  > ha scritto:
> Here's an idea which might be a bit out there...  
> 
> Allow styling/labelling to be driven by columns in the displayed query.  The 
> styling columns would have well-known names which are unlikely to conflict 
> with data columns.  A nice pattern to follow is the SVG style names.  So e.g. 
> styleStroke = line color, styleFill - fill color, styleStrokeWidth = line 
> width, etc etc etc.  styleLabel would be the string to label with.
> 
> This allows styling to be data-driven and easily captured and shared.  
> 
> 
> On Mon, Jan 28, 2019 at 8:16 PM Victoria Rautenbach 
> mailto:victoria.rautenb...@gmail.com>> wrote:
> Dear PostGIS devs and users
> 
> Firstly, we (Frikan, Xuri and I) would like to thank the PostGIS
> community again for the support you provided us in 2018 during Google
> Summer of Code (GSoC). We truly appreciate your time and effort.
> 
> The call for OSGeo GSoC ideas is now out [1]. Frikan and I would like
> to suggest a project to address some of the issues left in the
> geometry viewer (available in pgAdmin4), but also include some new
> functionality.
> 
> Are there any ideas or suggestions from you, the PostGIS community,
> for extra functionalities that would improve the usefulness of the
> geometry viewer?
> 
> Thank you in advance.
> 
> Regards
> Victoria
> 
> 
> [1] https://lists.osgeo.org/pipermail/soc/2019-January/004200.html 
> 
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org 
> https://lists.osgeo.org/mailman/listinfo/postgis-users 
> 
> -- 
> Sorry for being short___
> 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.jar for 2.4

2019-02-01 Thread Paul Ramsey

Well, the java work is all contained here 
https://github.com/postgis/postgis-java

Nothing has changed structurally between 2.3 and 2.4 so I see no reason why the 
2.3 jar couldn’t be used just fine w/ 2.4 or 2.5

P

> On Feb 1, 2019, at 8:54 AM, Ailleen Pace  wrote:
> 
> Hi all,
> 
> Where can I find the postgis.jar file for PostGIS 2.4 with the following 
> classes:
> 
> import org.postgis.Geometry;
> import org.postgis.LineString;
> import org.postgis.LinearRing;
> import org.postgis.PGgeometry;
> import org.postgis.Point;
> import org.postgis.Polygon;
> 
> Does 2.3 work for PostgreSQL 11.1?
> 
> Best,
> Ailleen Pace
> ___
> 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 2.0 Shapefile and DBF Loader

2019-03-22 Thread Paul Ramsey

I wonder if we could do some thing native in pgadmin4 with shapely 

> On Mar 22, 2019, at 11:15 AM, Carson Gray  wrote:
> 
> I am very new to this and need to upload shapefiles to pgAdmin 4 and my 
> instructions say to use the Shapefile Loader. 
> 
>> On Fri, Mar 22, 2019 at 2:12 PM Regina Obe  wrote:
>> Carson,
>> 
>>  
>> 
>> Do you compile your own PostGIS or you get from a distribution.  We still 
>> have shp2pgsql-gui in PostGIS code base, and I still ship it as part of the 
>> Windows EDB builds.
>> 
>>  
>> 
>> Unfortunately pgAdmin4 plugin architecture changed from pgAdmin3 days, so 
>> shp2gpsql-gui cannot work as a plugin under pgAdmin4.
>> 
>>  
>> 
>> I took it out of our upcoming PostGIS In Action book we are doing because of 
>> that and hoping we'll have a replacement for it soon that piggy backs on 
>> shp2pgsql commandline.
>> 
>>  
>> 
>>  
>> 
>>  
>> 
>> From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf 
>> Of Carson Gray
>> Sent: Friday, March 22, 2019 1:50 PM
>> To: postgis-users@lists.osgeo.org
>> Subject: [postgis-users] PostGIS 2.0 Shapefile and DBF Loader
>> 
>>  
>> 
>> Hello,
>> 
>>  
>> 
>> I have a Macbook Pro and am using PGAdmin 4 and PostGIS. I cannot seem to 
>> find the PostGIS 2.0 Shapefile and DBF Loader. Can anyone help with this? Is 
>> there something else I need to download? Thanks.
>> 
>> ___
>> 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] pg_dump on upgraded server

2019-04-03 Thread Paul Ramsey

Put a symlink to the postgis-2.3.so file, named as postgis-2.1.so

P

> On Apr 3, 2019, at 9:08 AM, Paolo Cavallini  wrote:
> 
> Hi all,
> I'm on a difficult situation: a debian sever with a pg9.4/pgis2.1 db has
> been upgraded to stretch, therefore to pg9.6/pgis2.3. Now pg_dump will
> complain of the missing pgis2.1 lib, even if I dump only a non public
> (thus without pgis functions) schema.
> How could I fix this?
> Thanks a lot.
> -- 
> Paolo Cavallini - www.faunalia.eu
> QGIS.ORG Chair:
> http://planet.qgis.org/planet/user/28/tag/qgis%20board/
> ___
> 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] pg_dump on upgraded server

2019-04-03 Thread Paul Ramsey

Use a pg_upgrade process instead of a pg_dump. You should be able to that with 
just the old data area and the new server software.

> On Apr 3, 2019, at 9:21 AM, Paolo Cavallini  wrote:
> 
> I mean: server versions don't match (9.4 vs 9.6)
> 
> On 03/04/19 18:20, Paolo Cavallini wrote:
>> done already, but it complains that the versions don't match
>> thanks
>> 
>> On 03/04/19 18:10, Paul Ramsey wrote:
>>> Put a symlink to the postgis-2.3.so file, named as postgis-2.1.so
>>> 
>>> P
>>> 
>>>> On Apr 3, 2019, at 9:08 AM, Paolo Cavallini  wrote:
>>>> 
>>>> Hi all,
>>>> I'm on a difficult situation: a debian sever with a pg9.4/pgis2.1 db has
>>>> been upgraded to stretch, therefore to pg9.6/pgis2.3. Now pg_dump will
>>>> complain of the missing pgis2.1 lib, even if I dump only a non public
>>>> (thus without pgis functions) schema.
>>>> How could I fix this?
>>>> Thanks a lot.
>>>> -- 
>>>> Paolo Cavallini - www.faunalia.eu
>>>> QGIS.ORG Chair:
>>>> http://planet.qgis.org/planet/user/28/tag/qgis%20board/
>>>> ___
>>>> 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
>>> 
>> 
> 
> -- 
> Paolo Cavallini - www.faunalia.eu
> QGIS.ORG Chair:
> http://planet.qgis.org/planet/user/28/tag/qgis%20board/
> ___
> 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] pg_dump on upgraded server

2019-04-03 Thread Paul Ramsey

Well, this speaks to not having micro version numbers in the extension 
versioning, for sure.
Also, potentially for having a global source version, an idea that we’d have to 
add to the PgSQL extension system, so a script like postgis—ANY—2.3.sql could 
take a best-effort run at upgrading any prior installed version.
It can perhaps be fixed by copying postgis—2.3.0--2.3.1.sql (or, really, any of 
the extension upgrade scripts) to postgis—2.1.4–2.3.1.sql
P.

> On Apr 3, 2019, at 10:34 AM, Paolo Cavallini  wrote:
> 
> 
> 
> On 03/04/19 19:04, Sandro Santilli wrote:
>> Can you show the ALTER EXTENSION command you're issuing and the
>> error you're getting ? Also please show the output of
>> 
>>  SELECT postgis_full_version();
> 
> I tried both
> ALTER EXTENSION postgis UPDATE;
> with something like
> the 2.1.4 version is already installed
> and
> ALTER EXTENSION postgis
> UPDATE TO "2.3.1"
> with
> ERRORE:  l'estensione "postgis" non ha un percorso di aggiornamento
> dalla versione "2.1.4" alla versione "2.3.1"
> 
> unfortunately the VPN just went down, and I don't have currently access
> to the machine.
> Thanks a lot.
> -- 
> Paolo Cavallini - www.faunalia.eu
> QGIS.ORG Chair:
> http://planet.qgis.org/planet/user/28/tag/qgis%20board/
> ___
> 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] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11

2019-04-11 Thread Paul Ramsey

something like

select encode(st_asbinary(st_collect(geom)), 'hex') from mytable where
id < 1

just a nice hex-encoded wkb

p

On Thu, Apr 11, 2019 at 12:11 PM Martin Davis  wrote:
>
> Yes, that's exactly the test needed.  So it looks like ST_Union(agg) on the 
> 11 instance is much slower than on 9.5:
>
> 11: 25335.136 ms
> 9.5: 4409.919 ms
>
> The question is why...  We'll have a look and see if there's any regression 
> in GEOS (which is what is performing the union).
>
> Are you able to share the dataset from the above test?  (As a file of WKT or 
> WKB)
>
> On Thu, Apr 11, 2019 at 11:55 AM Lars Aksel Opsahl  
> wrote:
>>
>> Hi
>>
>> Do mean something like this ?
>>
>> Here is the result from postgres 11 -
>>
>> EXPLAIN ANALYZE
>>
>> select (ST_dump(st_union(geo))).geom as geo
>>
>> from sde_markslag.markslag_myrikilden_temp
>>
>> where gid  < 1
>>
>> ;
>>
>> QUERY 
>> PLAN
>>
>> ---
>>
>>  Result  (cost=62634.26..62899.53 rows=1000 width=32) (actual 
>> time=25249.956..25331.325 rows=9205 loops=1)
>>
>>->  ProjectSet  (cost=62634.26..62639.53 rows=1000 width=32) (actual 
>> time=25249.953..25330.483 rows=9205 loops=1)
>>
>>  ->  Aggregate  (cost=62634.26..62634.28 rows=1 width=32) (actual 
>> time=25244.718..25244.719 rows=1 loops=1)
>>
>>->  Seq Scan on markslag_myrikilden_temp  
>> (cost=0.00..62609.51 rows=9900 width=1593) (actual time=0.008..173.033 
>> rows= loops=1)
>>
>>  Filter: (gid < 1)
>>
>>  Rows Removed by Filter: 557242
>>
>>  Planning Time: 0.064 ms
>>
>>  Execution Time: 25335.136 ms
>>
>> (8 rows)
>>
>>
>>
>> Here is the same postgres 9.5 -
>>
>> EXPLAIN ANALYZE
>>
>> [more] - > select (ST_dump(st_union(geo))).geom as geo
>>
>> [more] - > from sde_markslag.markslag_myrikilden_temp
>>
>> [more] - > where gid  < 1
>>
>> [more] - > ;
>>
>>QUERY PLAN
>>
>> 
>>
>>  Aggregate  (cost=170462.27..170467.28 rows=1000 width=1604) (actual 
>> time=4311.911..4409.250 rows=9205 loops=1)
>>
>>->  Seq Scan on markslag_myrikilden_temp  (cost=0.00..170437.51 rows=9903 
>> width=1604) (actual time=0.054..629.045 rows= loops=1)
>>
>>  Filter: (gid < 1)
>>
>>  Rows Removed by Filter: 557242
>>
>>  Planning time: 0.045 ms
>>
>>  Execution time: 4409.919 ms
>>
>> (6 rows)
>>
>>
>>
>> Thanks
>>
>> Lars
>>
>> 
>> From: postgis-users  on behalf of 
>> Martin Davis 
>> Sent: Thursday, April 11, 2019 8:07 PM
>> To: PostGIS Users Discussion
>> Subject: Re: [postgis-users] diffrent execution plan on Postgres 9.5 and 
>> Postgres 11 for ST_union and performance problem Postgres 11
>>
>>
>>
>> On Thu, Apr 11, 2019 at 3:27 AM Lars Aksel Opsahl  
>> wrote:
>>
>> Can it be problem related ST_Union and aggregate ?
>>
>>
>> Can you test the performance of aggregate ST_Union on it's own by dropping 
>> the GROUP BY and instead using a WHERE clause to union a small-ish subset of 
>> the geometries?  (But large enough to give a measurable query time.)
>> ___
>> 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 + pgdump

2019-05-16 Thread Paul Ramsey

You haven’t really provided any clues to your underlying problem. What platform 
you’re on, how your postgis is installed (packages? source?), what the errors 
you see actually ARE. We don’t publish broken software and then hide the top 
secret directions for correct use. So when you see things broken, consider it 
an exceptional condition and gather all the clues you can to share and aid in 
the process of getting help, otherwise… you won’t get help.

P

> On May 16, 2019, at 8:09 AM, Zwettler Markus (OIZ) 
>  wrote:
> 
> Hi,
>  
>  
> We did a default PostGIS installation within a PostgreSQL 9.6 database:
>  
> ===
> create extension if not exists postgis;
> create extension if not exists postgis_topology;
> create extension if not exists ogr_fdw;
> create extension if not exists pgrouting;
> ===
>  
> This installed PostGIS within the public schema of the database.
> (SET SCHEMA is not supported since V2.3 anymore)
>  
> Our customer application was also installed within the public schema.
>  
> When we pg_dump + pg_restore the database we got a lot of errors.
>  
> We tried to pg_restore the dump into a database without postgis extension => 
> postgis errors
> We tried to pg_restore the dump into a database with postgis extension => 
> postgis errors
> We tried to pg_restore only the public schema into a database with postgis 
> extension => postgis errors
>  
> QUESTION:
> How does the PostGIS extension have to be installed so that pg_dump + 
> pg_restore DO NOT error out?
>  
>  
> Thanks,
> Markus
>  
> ___
> 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 + pgdump

2019-05-17 Thread Paul Ramsey

ttps://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
> 
> When replying, please edit your Subject line so it is more specific than "Re: 
> Contents of postgis-users digest..."
> 
> 
> Today's Topics:
> 
>   1. PostGIS + pgdump (Zwettler Markus (OIZ))
>   2. Re: PostGIS + pgdump (Paul Ramsey)
>   3. Re: PostGIS + pgdump (James Keener)
>   4. Re: PostGIS + pgdump (Sandro Santilli)
> 
> 
> --
> 
> Message: 1
> Date: Thu, 16 May 2019 13:09:54 +
> From: "Zwettler Markus (OIZ)" 
> To: "postgis-users@lists.osgeo.org" 
> Subject: [postgis-users] PostGIS + pgdump
> Message-ID: <4a928fcb137b4be3a60ec896b552f...@zuerich.ch>
> Content-Type: text/plain; charset="utf-8"
> 
> Hi,
> 
> 
> We did a default PostGIS installation within a PostgreSQL 9.6 database:
> 
> ===
> create extension if not exists postgis;
> create extension if not exists postgis_topology; create extension if not 
> exists ogr_fdw; create extension if not exists pgrouting; ===
> 
> This installed PostGIS within the public schema of the database.
> (SET SCHEMA is not supported since V2.3 anymore)
> 
> Our customer application was also installed within the public schema.
> 
> When we pg_dump + pg_restore the database we got a lot of errors.
> 
> We tried to pg_restore the dump into a database without postgis extension => 
> postgis errors We tried to pg_restore the dump into a database with postgis 
> extension => postgis errors We tried to pg_restore only the public schema 
> into a database with postgis extension => postgis errors
> 
> QUESTION:
> How does the PostGIS extension have to be installed so that pg_dump + 
> pg_restore DO NOT error out?
> 
> 
> Thanks,
> Markus
> 
> -- next part --
> An HTML attachment was scrubbed...
> URL: 
> <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190516/e49ecaff/attachment-0001.html>
> 
> --
> 
> Message: 2
> Date: Thu, 16 May 2019 08:12:46 -0500
> From: Paul Ramsey 
> To: PostGIS Users Discussion 
> Subject: Re: [postgis-users] PostGIS + pgdump
> Message-ID: <94e1e72b-94bb-46cb-9baa-ebe278c41...@cleverelephant.ca>
> Content-Type: text/plain; charset="utf-8"
> 
> You haven’t really provided any clues to your underlying problem. What 
> platform you’re on, how your postgis is installed (packages? source?), what 
> the errors you see actually ARE. We don’t publish broken software and then 
> hide the top secret directions for correct use. So when you see things 
> broken, consider it an exceptional condition and gather all the clues you can 
> to share and aid in the process of getting help, otherwise… you won’t get 
> help.
> 
> P
> 
>> On May 16, 2019, at 8:09 AM, Zwettler Markus (OIZ) 
>>  wrote:
>> 
>> Hi,
>> 
>> 
>> We did a default PostGIS installation within a PostgreSQL 9.6 database:
>> 
>> ===
>> create extension if not exists postgis; create extension if not exists 
>> postgis_topology; create extension if not exists ogr_fdw; create 
>> extension if not exists pgrouting; ===
>> 
>> This installed PostGIS within the public schema of the database.
>> (SET SCHEMA is not supported since V2.3 anymore)
>> 
>> Our customer application was also installed within the public schema.
>> 
>> When we pg_dump + pg_restore the database we got a lot of errors.
>> 
>> We tried to pg_restore the dump into a database without postgis 
>> extension => postgis errors We tried to pg_restore the dump into a 
>> database with postgis extension => postgis errors We tried to 
>> pg_restore only the public schema into a database with postgis 
>> extension => postgis errors
>> 
>> QUESTION:
>> How does the PostGIS extension have to be installed so that pg_dump + 
>> pg_restore DO NOT error out?
>> 
>> 
>> Thanks,
>> Markus
>> 
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
>> https://lists.osgeo.org/mailman/listinfo/postgis-users 
>> <https://lists.osgeo.org/mailman/listinfo/postgis-users>
> -- next part --
> An HTML attachment was scrubbed...
> URL: 
> <http://lists.osgeo

Re: [postgis-users] Tolerance/SnapTo in Postgis Topology for meter and degrees.

2019-06-07 Thread Paul Ramsey

Surely there is an “all of Norway” planar projection you can use to handle the 
integrative whole-country work cases? Even if there isn’t, you can always 
invent one and add it to the spatial_ref_sys table. 

P

> On Jun 7, 2019, at 3:02 AM, Lars Aksel Opsahl  wrote:
> 
> Hi
> 
> We have one issue/problem and that is how to handle Tolerance/SnapTo in a 
> good way when working in degrees.
> 
> We work on maps that covers all of Norway so we use degrees 
> https://epsg.io/4258  . We also use Postgis Topology, 
> which means that we just have add new lines both for surface and line layers 
> and let Postgis Topology take care generating the surfaces so this samples I 
> just focus on lines.
> 
> A description and some code pieces to illustrate the problem:
> 
> A: Layers in meter 
> 
> First we show that this works well when working in meter. I create a Postgis 
> Topology layer with layer_precision 10 meter using https://epsg.io/25833 
> . Then we add 4 horizontal lines and 4 vertical lines 
> using a precision with 2 meter. Postgis Topology handles this nicely and we 
> end with total of 4 lines and not 8 lines as we added, because every second 
> line snaps to the line added before because the distance between the lines 
> are less 2 meter which use as precision we used when adding new lines.
> 
> The sample code 
> :https://github.com/NibioOpenSource/pgtopo_update_sql/blob/develop/src/test/sql/snapto/snapto_code_example_meter.sql
>  
> 
> 
> A image of the result 
> :https://github.com/NibioOpenSource/pgtopo_update_sql/blob/develop/src/test/sql/snapto/snapto_code_example_meter.png
>  
> 
> 
> The green lines are the lines we added, the red lines are the lines that are 
> stored in the edge table.
> 
> Layers in degrees :
> 
> But when we are using degrees things starts be more difficult. The reason why 
> we are using degrees is to get a accurate transformations to local UTM zones 
> which are different depending on where in Norway you are.
> 
> So the problem is how to use tolerances so we get a behavior equal to the 
> test using meter.
> 
> We can we define the layer in Postgis Topology with quite big value because 
> this is just max value as it seems. So we can adjust the tolerance parameter 
> as we add lines but the problem is that we need to adjust this parameter 
> depending on where we are and what orientation the line has. For vertical 
> lines we need a bigger tolerance than for horizontal lines in Norway. This 
> makes it quite complicated to handle adding new lines.
> 
> The sample code 
> :https://github.com/NibioOpenSource/pgtopo_update_sql/blob/develop/src/test/sql/snapto/snapto_code_example_degrees.sql
>  
> 
> 
> A image of the result 
> :https://github.com/NibioOpenSource/pgtopo_update_sql/blob/develop/src/test/sql/snapto/snapto_code_example_degrees.png
>  
> 
> 
> 
> The green lines are the lines we added, the red lines are the lines that are 
> stored in the edge table.
> 
> It real life it's impossible to do it this because water, farm land are not 
> built up by horizontal and vertical lines only. Since we use Postgis Topology 
> we could split a line in the different line parts and based on the angle 
> select the best tolerance value but that is complicated and means extra 
> computing. Another problem is that edges then will be broken up into 
> unnecessary small pieces.
> 
> Any ideas on how to handle this in a better way ?
> 
> Can “PostGIS Geography Type” help us here ?
> 
> Thanks Lars
> 
> ___
> 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] Tolerance/SnapTo in Postgis Topology for meter and degrees.

2019-06-11 Thread Paul Ramsey

Not ideal, a whole-of-Norway projection would have even more error 
minimization, but not a bad stopgap, certainly better than using UTM 
projections way outside their areas of validity. Check a few measurements, and 
do things like comparing an ST_Distance(A, B) with ST_Distance(A::geography, 
B::geography) to see how much deviation from truth the projection is adding.

P.

> On Jun 11, 2019, at 4:34 AM, Lars Aksel Opsahl  wrote:
> 
> Hi
> 
> I had a discussion with Knut Bjørkelo a co worker of me today. 
> 
> He was wondering if we could use https://epsg.io/3035 , 
> then all the area values should be correct for all valid UTM Zones in Noway.
> 
> When we need to show a map we transform in to the correct to UTM zone to get 
> a visual correct map also.
> 
> If I test a single point the transformation seems be accurate up to 4. 
> decimal using 3035 even for a extreme point.
> 
> select 
> ST_AStexT(ST_Transform(ST_Transform(ST_Transform(ST_Transform(ST_setSrid(ST_MakePoint(1108142.0,7788000.0),25835),3035),25832),3035),25835));
> st_astext 
> --
>  POINT(1108142.5503 7787999.99979103)
> (1 row)
> 
> Lars
> 
> 
> From: postgis-users  on behalf of Lars 
> Aksel Opsahl 
> Sent: Monday, June 10, 2019 10:50 PM
> To: PostGIS Users Discussion
> Cc: Sandro Santilli
> Subject: Re: [postgis-users] Tolerance/SnapTo in Postgis Topology for meter 
> and degrees.
>  
> Hi
> 
> You have this C-code
> 
> 4870 _lwt_minTolerance( LWGEOM *g )
> 4871 {
> 4872 const GBOX* gbox;
> 4873 double max;
> 4874 double ret;
> 4875
> 4876 gbox = lwgeom_get_bbox(g);
> 4877 if ( ! gbox ) return 0; /* empty */
> 4878 max = FP_ABS(gbox->xmin);
> 4879 if ( max < FP_ABS(gbox->xmax) ) max = FP_ABS(gbox->xmax);
> 4880 if ( max < FP_ABS(gbox->ymin) ) max = FP_ABS(gbox->ymin);
> 4881 if ( max < FP_ABS(gbox->ymax) ) max = FP_ABS(gbox->ymax);
> 4882
> 4883 ret = 3.6 * pow(10, - ( 15 - log10(max?max:1.0) ) );
> 4884
> 4885 return ret;
> 4886 }
> 
> In postgis branch svn-2.5 we have this SQL code.
> 
> CREATE OR REPLACE FUNCTION topology._st_mintolerance(ageom Geometry)
> RETURNS float8
> AS $$
> SELECT 3.6 * power(10, - ( 15 - log(coalesce(
> nullif(
> greatest(abs(ST_xmin($1)), abs(ST_ymin($1)),
> abs(ST_xmax($1)), abs(ST_ymax($1))),
> 0),
> 1)) ));
> $$ LANGUAGE 'sql' IMMUTABLE STRICT;
> 
> I am not that into C-code but the SQL code looks like the C-code above as you 
> suggested in your mail.
> 
> So I did a small test 
> https://github.com/NibioOpenSource/pgtopo_update_sql/blob/develop/src/test/sql/snapto/snapto_code_example_degrees_st_min_tolerance.sql
>  
> 
>  using topology._st_mintolerance and yes I was able to get snap to every 
> second line both vertically and horizontally to work and both and north and 
> south in Norway. I just got this to work by playing around with 
> topology._st_mintolerance together different factor values.
> 
> But I have no idea about how I can use this min tolerance code to make a 
> generic code that behaves like 10 meter tolerance in a planar projection. 
> 
> And I am also little unsure about what projection that is correct to use for 
> datasets that covers all of Norway now.
> 
> Before we did not really have any choice because the projection was quite bad 
> if moved outside the bonds of the projection limits. We pretty much had to 
> use degrees to get the a OK quality if we needed to store the data in one 
> single dataset.
> 
> So whats the "correct projection" now to use for original dataset that covers 
> all of Norway and which are updated regularly ?
> 
> Thanks a lot.
> 
> Lars
> 
> 
> From: s...@kbt.io  on behalf of Sandro Santilli 
> Sent: Saturday, June 8, 2019 2:06 PM
> To: PostGIS Users Discussion
> Cc: Lars Aksel Opsahl
> Subject: Re: [postgis-users] Tolerance/SnapTo in Postgis Topology for meter 
> and degrees.
>  
> On Fri, Jun 07, 2019 at 10:02:23AM +, Lars Aksel Opsahl wrote:
> 
> > So the problem is how to use tolerances so we get a behavior equal to
> > the test using meter.
> > 
> > 
> > We can we define the layer in Postgis Topology with quite big value
> > because this is just max value as it seems. So we can adjust the tolerance
> > parameter as we add lines but the problem is that we need to adjust this
> > parameter depending on where we are and what orientation the line has. For
> > vertical lines we need a bigger tolerance than for horizontal lines in
> > Norway. This makes it quite complicated to handle adding new lines.
> 
> I'm not sure if it'd help but PostGIS Topology has an internal
> function (not to be relied upon, but could be copied to your own
> function) to determine "min tolerance" based on absolute coordinate
> values. That function is meant to deal with non-uniform floating-point
> resolution. What you're after is a function to

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

2019-07-03 Thread Paul Ramsey

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 “as much precision as we can 
take” system still has wonderful corner cases since we don’t actually have 
infinite precision at our command: the results of ST_Intersection(A, B) does 
not necessarily ST_Intersects() A or B.
The new overlay work in GEOS requires explicit precision, so we are going to 
start inching in that direction over time. Until then, enforcing precision on 
insert/update with triggers is not the end of the world if you want command of 
your precision model.
ATB,
P

> On Jul 3, 2019, at 5:47 AM, Darafei Komяpa Praliaskouski  
> wrote:
> 
> Hi,
> 
> Can you describe how the operations like Intersection should behave in such 
> type? E.g. if you have a type with 0 decimals (say), and have a line going 
> from (0,0) to (1,1) intersect with a line going from (1, 0) to (0, 1) - what 
> is the intersection point that is going to be reported? Will that 
> ST_Intersection in turn ST_Intersects=true with original point?
> 
> There are a number of such requests across the years but nobody seems to 
> propose a working algebra for such type, and without it it's not going to get 
> implemented.
> 
> On Wed, Jul 3, 2019 at 2:43 PM Giunta Igor  > wrote:
> How can a precision be assigned to a geometry, e.g. a point/line/polygon to 3 
> decimals, in order that any input/output will be given in that precision 
> (without usage of rounding functions like snap2grid)
> How can the problem be tackeld? Shall we request to include new types? Or 
> shall we rather let develop a new feature?
> 
> I guess we are not the first users facing this problem.
> 
> 
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org 
> https://lists.osgeo.org/mailman/listinfo/postgis-users 
> 
> 
> -- 
> Darafei Praliaskouski
> Support me: http://patreon.com/komzpa 
> ___
> 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] Inside / Outside of geography polygons

2019-08-12 Thread Paul Ramsey

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.

https://alastaira.wordpress.com/2012/01/27/ring-orientation-bigger-than-a-hemisphere-polygons-and-the-reorientobject-method-in-sql-server-2012/

The second part of the blog post is instructive, in that it shows that “fixing” 
the problem will just migrate a new problem out to users with small, but 
mis-oriented polygons. What they think of as local objects (parcels, cities, 
whatever) will, to their surprise, suddenly become nearly-global.

There’s some interesting stuff in the SQL Server documentation, in particular 
the idea of using circular envelopes for geographic objects, which has a 
certain appeal, but would necessitate a pretty major re-write of geography.  

There’s actually two “hemisphere problems” to deal with in any geodetic 
database: 

- what is the correct interpretation of an edge from A to B, since there are 
two paths between A and B over the great circle that joins them
- what is the correct interpretation of a ring

The PostGIS geodetic attempts to use “take the smaller thing”, but imperfectly, 
as you have demonstrated. The question is whether to continue to patch that up, 
or attempt to reimplement with something more explicit, like “ring orientation 
determines enclosure” and accept all the other knock-on issues that arise as 
people try and load mis-oriented rings into the system.

It’s a very interesting and challenging topic, thanks for investigating, I 
particularly like your point-field method of visualizing the issues with the 
algorithm, which are associated with the point-in-polygon routine. 
Point-in-polygon is one of those algorithms that is challenging on the sphere, 
and more so when what is “in” the polygon is not necessarily clear.

ATB,

P

> On Aug 9, 2019, at 9:53 AM, Christian Pschierer  
> wrote:
> 
> Hi Darafei,
> 
> yes, the MakeBox example was not a good one. Even though the basic question 
> -- which rule determines inside and outside of a large geography polygon -- 
> remains the same. 
> 
> But the problem is a different one: 
> SELECT ST_Distance(ST_GeomFromText('POLYGON((-36.5625 40.9798980696201, -22.5 
> -7.71099165543322,6.328125 -44.0875850282452,
> 130.078125 -49.3823727870096,170.546875 -47.9899216674142,170.546875 
> 69.162557908105,
> 172.96875 75.320025232208,68.203125 77.9156689863258,4.21875 71.3007929163745,
> -36.5625 40.9798980696201))')::geography  , ST_Point(0, 0)) / 1000
> return 0 in PostGis 2.5.2. (as expected) 
> 
> However: ST_Intersect or ST_DWithin on the same geometries return false. 
> Distance 0 and Intersects=false does not make sense. 
> The culprit is the bounding-box check in the latter two function ( 
> OPERATOR(public.&&) ). 
> 
> Test Case: 
> Create a sample dataset with one point per 1x1 degree: 
> CREATE MATERIALIZED VIEW public.tmp_points_1x1 AS ( 
> SELECT row_number() over() AS eid, ST_Translate(point, j, i) AS geom 
> FROM 
> generate_series(-89, 89) AS i, 
> generate_series(-180, 179) AS j, 
> (SELECT ('POINT(0 0)')::geometry AS point) AS b )
> 
> Now select from this dataset: 
> SELECT eid,ST_SetSRID(geom, 4326) FROM tmp_points_1x1 
> WHERE ST_Distance(geom::geography, ST_GeomFromText('POLYGON((-36.5625 
> 40.9798980696201, -22.5 -7.71099165543322,6.328125 -44.0875850282452, 
> 130.078125 -49.3823727870096,170.546875 -47.9899216674142,170.546875 
> 69.162557908105, 
> 172.96875 75.320025232208,68.203125 77.9156689863258,4.21875 
> 71.3007929163745, 
> -36.5625 40.9798980696201))')::geography ) <= 0.0
> 
> returns the expected result: 
>  
> 
> ST_Intersects leaves a hole around the point 0, 0! 
> SELECT eid,ST_SetSRID(geom::geography, 4326) FROM tmp_points_1x1 
> WHERE ST_Intersects(geom::geography, ST_GeomFromText('POLYGON((-36.5625 
> 40.9798980696201, -22.5 -7.71099165543322,6.328125 -44.0875850282452, 
> 130.078125 -49.3823727870096,170.546875 -47.9899216674142,170.546875 
> 69.162557908105, 
> 172.96875 75.320025232208,68.203125 77.9156689863258,4.21875 
> 71.3007929163745, 
> -36.5625 40.9798980696201))')::geography )
>  
> 
> && leaves 2 holes and select a lot of additional points outside of the 
> expected bounding box. 
> SELECT eid,ST_SetSRID(geom, 4326) FROM tmp_points_1x1 
> WHERE geom::geography OPERATOR(public.&&) ST_GeomFromText('POLYGON((-36.5625 
> 40.9798980696201, -22.5 -7.71099165543322,6.328125 -44.0875850282452, 
> 130.078125 -49.3823727870096,170.546875 -47.9899216674142,170.546875 
> 69.162557908105, 
> 172.96875 75.320025232208,68.203125 77.9156689863258,4.21875 
> 71.3007929163745, 
> -36.5625 40.9798980696201))')::geography
>  
> 
> Greetings 
> Christian 
> 
>> On 09 August 2019 at 14:56 "Darafei \"Komяpa\" Praliaskousk

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

2019-08-12 Thread Paul Ramsey

So, I have a patch for this, against 3.0, it’s a little big, but can probably 
also apply against 2.5 and 2.4… it’s not great, it’s just another layer of 
plaster on top of a shakey foundation. An error in the computation of the XYZ 
bounding volume is fixed, which exposes more cases where an external point 
cannot be generated, so a hack to plaster over that issue is also added. At the 
end of it, these two test cases now return the right answers, though I have not 
today yet run your clever point field tests to ensure that things look more 
correct over the whole point field.

  WITH data AS (
SELECT 
  'SRID=4326;POLYGON((-36.5625 40.9798980696201, -22.5 
-7.71099165543322,6.328125 -44.0875850282452,130.078125 
-49.3823727870096,170.546875 -47.9899216674142,170.546875 
69.162557908105,172.96875 75.320025232208,68.203125 77.9156689863258,4.21875 
71.3007929163745,-36.5625 40.9798980696201))'::geography AS ply,
  'SRID=4326;POINT(0 0)'::geography AS pt
  )
  SELECT 
   ST_Distance(ply,pt) AS distance,
   ST_DWithin(ply,pt,0) AS dwithin_0,
   ST_DWithin(ply,pt,1) AS dwithin_1,
   _ST_DistanceTree(ply,pt) AS distance_tree,
   _ST_DistanceUnCached(ply,pt) AS distance_uncached
  FROM data;

WITH data AS (
  SELECT 
'SRID=4326;POLYGON((-40.0 52.0, -67.0 -29.0, 102.0 -6.0, -40.0 
52.0))'::geography AS ply,
'SRID=4326;POINT(4 11)'::geography AS pt
)
SELECT 
  ST_Distance(ply,pt) AS distance,
  ST_DWithin(ply,pt,0) AS dwithin_0,
  ST_DWithin(ply,pt,1) AS dwithin_1,
  _ST_DistanceTree(ply,pt) AS distance_tree,
  _ST_DistanceUnCached(ply,pt) AS distance_uncached
FROM data;

One thing that leaks into this “fix” is an assumption of CCW exterior ring 
orientation for polygons that fail the usual external point computation phase 
(generally, large ones). So 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 hemisphere well. This is not unlike 
> the SQL Server 2008 limitation, except we don’t toss errors at you.
> 
> https://alastaira.wordpress.com/2012/01/27/ring-orientation-bigger-than-a-hemisphere-polygons-and-the-reorientobject-method-in-sql-server-2012/
>  
> <https://alastaira.wordpress.com/2012/01/27/ring-orientation-bigger-than-a-hemisphere-polygons-and-the-reorientobject-method-in-sql-server-2012/>
> 
> The second part of the blog post is instructive, in that it shows that 
> “fixing” the problem will just migrate a new problem out to users with small, 
> but mis-oriented polygons. What they think of as local objects (parcels, 
> cities, whatever) will, to their surprise, suddenly become nearly-global.
> 
> There’s some interesting stuff in the SQL Server documentation, in particular 
> the idea of using circular envelopes for geographic objects, which has a 
> certain appeal, but would necessitate a pretty major re-write of geography.  
> 
> There’s actually two “hemisphere problems” to deal with in any geodetic 
> database: 
> 
> - what is the correct interpretation of an edge from A to B, since there are 
> two paths between A and B over the great circle that joins them
> - what is the correct interpretation of a ring
> 
> The PostGIS geodetic attempts to use “take the smaller thing”, but 
> imperfectly, as you have demonstrated. The question is whether to continue to 
> patch that up, or attempt to reimplement with something more explicit, like 
> “ring orientation determines enclosure” and accept all the other knock-on 
> issues that arise as people try and load mis-oriented rings into the system.
> 
> It’s a very interesting and challenging topic, thanks for investigating, I 
> particularly like your point-field method of visualizing the issues with the 
> algorithm, which are associated with the point-in-polygon routine. 
> Point-in-polygon is one of those algorithms that is challenging on the 
> sphere, and more so when what is “in” the polygon is not necessarily clear.
> 
> ATB,
> 
> P
> 
>> On Aug 9, 2019, at 9:53 AM, Christian Pschierer > <mailto:christian.pschie...@gmx.net>> wrote:
>> 
>> Hi Darafei,
>> 
>> yes, the MakeBox example was not a good one. Even though the basic question 
>> -- which rule determines inside and outside of a large geography polygon -- 
>> remains the same. 
>> 
>> But the problem is a different one: 
>> SELECT ST_Distance(ST_GeomFromText('POLYGON((-36.5625 40.9798980696201, 
>> -22.5 -7.71099165543322,6.328125 -44.0875850282452,
>> 130.078125 -49.3823727870096,170.546875 -47.9899216674142,170.546875 
>> 69.162557908105,
>> 172.96875 75.3200252322

Re: [postgis-users] Interior rings

2019-08-13 Thread Paul Ramsey

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 5, 5 4, 4 
4)))'::geometry AS g
  ),
rings AS (
  SELECT (ST_DumpRings((ST_Dump(g)).geom)).* FROM poly
  )
SELECT * FROM rings
WHERE path != '{0}';


> On Aug 13, 2019, at 7:39 AM,   wrote:
> 
> Hi, I would like to create a table with all the interior rings (islands) from 
> a table with multipolygons lakes.
> I’ve only come this far:
> CREATE TABLE "islands" AS SELECT  (ST_InteriorRingn(the_geom, 2)).geometry AS 
> the_geom FROM public."lakes";
> But this is only collecting the first island in each lake.
> Thankful for any suggestions.
> /Paul 
> ___
> 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 PostgreSQL/PostGIS Upgrade

2019-08-13 Thread Paul Ramsey

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 point (it’s been 
removed during the update) when the system catalog tries to make sense of 
postgis objects and references to old library, you get this error.
A hack to work around the issue is to find the location of postgis-2.5.so (the 
new library copy from the upgrade) and then create a symlink from the 
postgis-2.3.so to that file, so that the system will end up finding a library 
to load. The new and old libraries have mostly the same symbols, so this trick 
works fine for the purposes of upgrade.
You may run into yet further problems, but this is a quick way to try and move 
forward. Once you have the database up and running, do not forget to run ALTER 
'EXTENSION postgis UDPATE’ on all your postgis databases. This re-installs all 
the SQL functions, pointing to the new library file. Once that’s done you can 
remove the symlink.
ATB,
P

PS, here’s a link to a blog post about working through this same issue, but on 
a Centos system. So it’s not command-perfect, but provides general explanations 
of problems that an admin might use to navigate the Ubuntu specific issues. 
https://info.crunchydata.com/blog/upgrading-postgis-on-centos-7

> On Aug 13, 2019, at 7:56 AM, Birgit Laggner  wrote:
> 
> Dear list,
> 
> on our test server, we currently try to test how to upgrade our 
> PostgreSQL/PostGIS database. Sadly, we are stuck somewhere and would be very 
> glad if anybody could help.
> 
> Our test server is running on an Ubuntu 16.04 system in a virtual machine. We 
> are coming from PostgreSQL 9.5.17 with PostGIS 2.3.3 and want to upgrade to 
> PostgreSQL 11.2 and PostGIS 2.5.
> 
> Here the outputs of the version queries:
> SELECT version(); 
> "PostgreSQL 9.5.17 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
> 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit"
> 
> SELECT postgis_full_version();
> "POSTGIS="2.3.3 r15473" GEOS="3.5.1-CAPI-1.9.1 r4246" SFCGAL="1.2.2" 
> PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" 
> LIBXML="2.9.3" LIBJSON="0.11.99" TOPOLOGY RASTER"
> 
> Our admin tried to upgrade PostGIS first and swears he did as told in the 
> PostGIS manual. But apparently, the upgrade was not fully successful, since 
> PostgreSQL still seems to look for PostGIS 2.3.
> 
> His error message while trying to upgrade PostgreSQL is:
> postgres@gis ERROR: could not access file "$libdir/postgis-2.3": No such file 
> or directory.
> 
> Has anybody any idea what we are doing wrong and what we should be doing 
> instead?
> 
> With hopeful regards,
> Birgit
> 
> 
> ___
> 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] Upgrading from 1.5

2019-08-13 Thread Paul Ramsey

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: http://postgis.net/docs/postgis_installation.html#hard_upgrade 

If your database isn’t too large it won’t be too onerous. And you can do it in 
“one step”, in that the process is: dump, filter dump file, install brand new 
software, load filtered dump file. No need to traverse intermediate versions or 
anything fiddly like that.

ATB,

P

> On Aug 13, 2019, at 8:39 AM, Dara Unglaube  wrote:
> 
> Good morning.
> We are running a WAY out of date postgres (9.1) and postgis (1.5). We are 
> trying to figure out how to upgrade to a current version (hoping to get to 
> PosgreSQL 11 with postGIS 2.5).  Is there a way to directly upgrade to 
> postgres11/postgis2.5 without intermediate steps (upgrades)?  
> Thank you in advance for your time!
> Dara
> ___
> 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 PostgreSQL/PostGIS Upgrade

2019-08-13 Thread Paul Ramsey

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,
> 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 point (it’s 
> been removed during the update) when the system catalog tries to make sense 
> of postgis objects and references to old library, you get this error.
> A hack to work around the issue is to find the location of postgis-2.5.so 
> (the new library copy from the upgrade) and then create a symlink from the 
> postgis-2.3.so to that file, so that the system will end up finding a library 
> to load. The new and old libraries have mostly the same symbols, so this 
> trick works fine for the purposes of upgrade.
> You may run into yet further problems, but this is a quick way to try and 
> move forward. Once you have the database up and running, do not forget to run 
> ALTER 'EXTENSION postgis UDPATE’ on all your postgis databases. This 
> re-installs all the SQL functions, pointing to the new library file. Once 
> that’s done you can remove the symlink.
> ATB,
> P
> 
> PS, here’s a link to a blog post about working through this same issue, but 
> on a Centos system. So it’s not command-perfect, but provides general 
> explanations of problems that an admin might use to navigate the Ubuntu 
> specific issues. 
> https://info.crunchydata.com/blog/upgrading-postgis-on-centos-7
> 
>> On Aug 13, 2019, at 7:56 AM, Birgit Laggner  
>> wrote:
>> 
>> Dear list,
>> 
>> on our test server, we currently try to test how to upgrade our 
>> PostgreSQL/PostGIS database. Sadly, we are stuck somewhere and would be very 
>> glad if anybody could help.
>> 
>> Our test server is running on an Ubuntu 16.04 system in a virtual machine. 
>> We are coming from PostgreSQL 9.5.17 with PostGIS 2.3.3 and want to upgrade 
>> to PostgreSQL 11.2 and PostGIS 2.5.
>> 
>> Here the outputs of the version queries:
>> SELECT version(); 
>> "PostgreSQL 9.5.17 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
>> 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit"
>> 
>> SELECT postgis_full_version();
>> "POSTGIS="2.3.3 r15473" GEOS="3.5.1-CAPI-1.9.1 r4246" SFCGAL="1.2.2" 
>> PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" 
>> LIBXML="2.9.3" LIBJSON="0.11.99" TOPOLOGY RASTER"
>> 
>> Our admin tried to upgrade PostGIS first and swears he did as told in the 
>> PostGIS manual. But apparently, the upgrade was not fully successful, since 
>> PostgreSQL still seems to look for PostGIS 2.3.
>> 
>> His error message while trying to upgrade PostgreSQL is:
>> postgres@gis ERROR: could not access file "$libdir/postgis-2.3": No such 
>> file or directory.
>> 
>> Has anybody any idea what we are doing wrong and what we should be doing 
>> instead?
>> 
>> With hopeful regards,
>> Birgit
>> 
>> 
>> ___
>> 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] Inside / Outside of geography polygons

2019-08-13 Thread Paul Ramsey

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 this, against 3.0, it’s a little big, but can probably 
> also apply against 2.5 and 2.4… it’s not great, it’s just another layer of 
> plaster on top of a shakey foundation. An error in the computation of the XYZ 
> bounding volume is fixed, which exposes more cases where an external point 
> cannot be generated, so a hack to plaster over that issue is also added. At 
> the end of it, these two test cases now return the right answers, though I 
> have not today yet run your clever point field tests to ensure that things 
> look more correct over the whole point field.
> 
>   WITH data AS (
> SELECT 
>   'SRID=4326;POLYGON((-36.5625 40.9798980696201, -22.5 
> -7.71099165543322,6.328125 -44.0875850282452,130.078125 
> -49.3823727870096,170.546875 -47.9899216674142,170.546875 
> 69.162557908105,172.96875 75.320025232208,68.203125 77.9156689863258,4.21875 
> 71.3007929163745,-36.5625 40.9798980696201))'::geography AS ply,
>   'SRID=4326;POINT(0 0)'::geography AS pt
>   )
>   SELECT 
>ST_Distance(ply,pt) AS distance,
>ST_DWithin(ply,pt,0) AS dwithin_0,
>ST_DWithin(ply,pt,1) AS dwithin_1,
>_ST_DistanceTree(ply,pt) AS distance_tree,
>_ST_DistanceUnCached(ply,pt) AS distance_uncached
>   FROM data;
> 
> WITH data AS (
>   SELECT 
> 'SRID=4326;POLYGON((-40.0 52.0, -67.0 -29.0, 102.0 -6.0, -40.0 
> 52.0))'::geography AS ply,
> 'SRID=4326;POINT(4 11)'::geography AS pt
> )
> SELECT 
>   ST_Distance(ply,pt) AS distance,
>   ST_DWithin(ply,pt,0) AS dwithin_0,
>   ST_DWithin(ply,pt,1) AS dwithin_1,
>   _ST_DistanceTree(ply,pt) AS distance_tree,
>   _ST_DistanceUnCached(ply,pt) AS distance_uncached
> FROM data;
> 
> One thing that leaks into this “fix” is an assumption of CCW exterior ring 
> orientation for polygons that fail the usual external point computation phase 
> (generally, large ones). So 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 > <mailto:pram...@cleverelephant.ca>> wrote:
>> 
>> 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.
>> 
>> https://alastaira.wordpress.com/2012/01/27/ring-orientation-bigger-than-a-hemisphere-polygons-and-the-reorientobject-method-in-sql-server-2012/
>>  
>> <https://alastaira.wordpress.com/2012/01/27/ring-orientation-bigger-than-a-hemisphere-polygons-and-the-reorientobject-method-in-sql-server-2012/>
>> 
>> The second part of the blog post is instructive, in that it shows that 
>> “fixing” the problem will just migrate a new problem out to users with 
>> small, but mis-oriented polygons. What they think of as local objects 
>> (parcels, cities, whatever) will, to their surprise, suddenly become 
>> nearly-global.
>> 
>> There’s some interesting stuff in the SQL Server documentation, in 
>> particular the idea of using circular envelopes for geographic objects, 
>> which has a certain appeal, but would necessitate a pretty major re-write of 
>> geography.  
>> 
>> There’s actually two “hemisphere problems” to deal with in any geodetic 
>> database: 
>> 
>> - what is the correct interpretation of an edge from A to B, since there are 
>> two paths between A and B over the great circle that joins them
>> - what is the correct interpretation of a ring
>> 
>> The PostGIS geodetic attempts to use “take the smaller thing”, but 
>> imperfectly, as you have demonstrated. The question is whether to continue 
>> to patch that up, or attempt to reimplement with something more explicit, 
>> like “ring orientation determines enclosure” and accept all the other 
>> knock-on issues that arise as people try and load mis-oriented rings into 
>> the system.
>> 
>> It’s a very interesting and challenging topic, thanks for investigating, I 
>> particularly like your point-field method of visualizing the issues with the 
>> algorithm, which are associated with the point-in-polygon routine. 
>> Point-in-polygon is one of those algorithms that is challenging on the 
>> sphere, and more so when what is “in” the polygon is not necessarily c

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

2019-08-13 Thread Paul Ramsey

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 going 
to be problematic” does hold, the reported case was in fact addressable and 
fixable, if not in general then at least for most common cases.

In general:

- try to break your large things into equivalent sets of smaller things
- remember that no edge will span more than 180 degrees, as we do interpret an 
edge as the shortest distance between a coordinate pair

ATB,

P

> On Aug 13, 2019, at 9:55 AM, Szymon Haas  wrote:
> 
> Dear PostGIS users,
> 
> We are using PostGIS as an engine to retrieve data in GeoJSON format.
> As the data is worldwide and requests concern large geographic areas we did a 
> choice of using "geography" data type.
> 
> The main functionality of our system is retrieving data in some "places" 
> (points, lines, areas), including buffers. 
> Thus, after several tries, we decided to use ST_DWithin function.
> 
> Recently, during the tests, we found an issue with that function.
> Briefly - for very large areas (from the observations BBOX wider than 180 
> degrees EW) results of the ST_DWithin are missing data in a circular area 
> around the 0,0 point (the prime meridian and equator cross-section).
> 
> To reproduce the issue one can create a table/view with a grid of points 
> (lines or polygons would have the same effect) around the globe:
> 
> CREATE MATERIALIZED VIEW tmp_points_1x1 AS (
> SELECT row_number() over() AS eid, ST_Translate(point, j, i)::geography AS 
> geog
> FROM 
>   generate_series(-89, 89) AS i,
>   generate_series(-180, 179) AS j,
>   (SELECT ('POINT(0 0)')::geometry AS point) AS b )
> 
> adding these indexes will speedup test queries:
> CREATE INDEX tmp_points_1x1_geog
> ON tmp_points_1x1 USING gist(geog)
> TABLESPACE pg_default;
> 
> CREATE INDEX tmp_points_1x1_eid
> ON tmp_points_1x1 USING btree(eid)
> TABLESPACE pg_default;
> 
> The below test query returns in Json format data for requested geojson area 
> (here it's 190 degrees wide):
> SELECT row_to_json(fc)
> FROM (
> SELECT 'FeatureCollection' AS type, array_to_json(array_agg(f)) AS 
> features
> FROM (
> SELECT 'Feature' AS type, 
> ST_AsGeoJSON(lg.geog)::json AS geometry, 
> row_to_json(lp) AS properties
> FROM tmp_points_1x1 AS lg 
> INNER JOIN (SELECT eid 
> FROM tmp_points_1x1 
> WHERE st_dwithin(geog, 
> ST_GEOMFROMGEOJSON('{"type":"Polygon","coordinates":[[[-20,-50],[75,-50],[170,-50],[170,50],[75,50],[-20,50],[-20,-50]]]}'
>  ),0)
> ) AS lp 
> ON lg.eid = lp.eid  
> ) AS f 
> ) AS fc;
> 
> On the visualization of the output you can see the missing data:
>  above180wide.png 
> 
> If the request area is narrowed to <180 degrees wide, e.g. 170 degrees: 
> {"type":"Polygon","coordinates":[[[-20,-50],[20,-50],[20,50],[-20,50],[-20,-50]]]}
> the problem of missing data doesn't appear any more:
> 
>  below180wide.png 
> Is
>  it a known issue?
> Are there chances for that to be resolved?
> Or maybe it's not a bug (e.g. request geometries' BBOXes should not exceed 
> 180 degrees?)
> 
> Thank you for any explanation,
> Eliasz Haas
> ___
> 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] [Longest distance between two from list of points?]

2019-08-14 Thread Paul Ramsey

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 harder for sure, I almost 
think you would need a recursive CTE for that, but with only 10K points, it 
might not perform too too badly. Basically pick a random point, and then find 
the furthest point from that one (ORDER BY ST_Distance() DESC LIMIT 1), then 
use that point as the seed for the next iteration, and repeat until you end up 
with the same pairing in subsequent iterations.

Maybe someone on the list will feel like writing out an example for the 
intellectual glory… :)

P.

> On Aug 14, 2019, at 7:15 AM, Sergiusz Pawlowicz  
> wrote:
> 
> I have about ten thousands of points stored in a postgis database.
> 
> I need to find the longest distance between two of the furthest of
> them from each other, something like bounding box, but let's say
> "bounding line".
> 
> Any suggestions?
> 
> I browse the archives but there was no clearly opensource answer, the
> only one was suggesting some FME-like commercial products.
> 
> Thanks,
> Serge
> ___
> 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] [Longest distance between two from list of points?]

2019-08-14 Thread Paul Ramsey

I dunno, 1 * 1 = a really big number… and the join condition on NOT 
doesn’t get an index assist…
P

> On Aug 14, 2019, at 8:58 AM, Regina Obe  wrote:
> 
> I would do a cross join
> 
> SELECT a.id As a_id, b.id aS b_id
> -- pick a number you know is not the furthest this is just to save some 
> processing, you can skip this and just do a CROSS JOIN but will lose some 
> speed
> FROM your_points AS a INNER JOIN your_points AS b ON NOT 
> ST_DWithin(a,b,some_biggish_number)   
> ORDER BY ST_Distance(a,b) DESC LIMIT 1
> 
> 
> 
> -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?]
> 
> 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 harder for sure, I almost 
> think you would need a recursive CTE for that, but with only 10K points, it 
> might not perform too too badly. Basically pick a random point, and then find 
> the furthest point from that one (ORDER BY ST_Distance() DESC LIMIT 1), then 
> use that point as the seed for the next iteration, and repeat until you end 
> up with the same pairing in subsequent iterations.
> 
> Maybe someone on the list will feel like writing out an example for the 
> intellectual glory  :)
> 
> P.
> 
>> On Aug 14, 2019, at 7:15 AM, Sergiusz Pawlowicz  
>> wrote:
>> 
>> I have about ten thousands of points stored in a postgis database.
>> 
>> I need to find the longest distance between two of the furthest of 
>> them from each other, something like bounding box, but let's say 
>> "bounding line".
>> 
>> Any suggestions?
>> 
>> I browse the archives but there was no clearly opensource answer, the 
>> only one was suggesting some FME-like commercial products.
>> 
>> Thanks,
>> Serge
>> ___
>> 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] [Longest distance between two from list of points?]

2019-08-14 Thread Paul Ramsey

It’s pretty easy to synthesize a case where the extrema aren’t (both) on the 
bounding box.
I like the idea of using the convex hull to reduce the number of input points 
to a cross join.
As pointed out on IRC, we have the code already in place in 
ST_MinimumBoundingRadius we just aren’t returning all the information necessary 
to derive the extrema.
P

> On Aug 14, 2019, at 9:29 AM, Regina Obe  wrote:
> 
> Why wouldn't it get an index assistance? ST_DWithin I would think would be 
> separated apart
> 
> So if you fill in something like ST_DWithin(a,b, 
> a_distance_you_know_is_legit_for_distance_but biggish)
> 
> Anyrate as was mentioned on IRC, I think the furthest points since you are 
> dealing with just points should lie on the extent
> 
> So would be just
> 
> SELECT ST_LongestLine(ST_Extent(geom))
> FROM your_table;
> 
> I would 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, 2019 12:00 PM
> To: PostGIS Users Discussion 
> Subject: Re: [postgis-users] [Longest distance between two from list of 
> points?]
> 
> I dunno, 1 * 1 = a really big number  and the join condition on NOT 
> doesn t get an index assist  P
> 
>> On Aug 14, 2019, at 8:58 AM, Regina Obe  wrote:
>> 
>> I would do a cross join
>> 
>> SELECT a.id As a_id, b.id aS b_id
>> -- pick a number you know is not the furthest this is just to save some 
>> processing, you can skip this and just do a CROSS JOIN but will lose some 
>> speed
>> FROM your_points AS a INNER JOIN your_points AS b ON NOT 
>> ST_DWithin(a,b,some_biggish_number)   
>> ORDER BY ST_Distance(a,b) DESC LIMIT 1
>> 
>> 
>> 
>> -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?]
>> 
>> 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 harder for sure, I 
>> almost think you would need a recursive CTE for that, but with only 10K 
>> points, it might not perform too too badly. Basically pick a random point, 
>> and then find the furthest point from that one (ORDER BY ST_Distance() DESC 
>> LIMIT 1), then use that point as the seed for the next iteration, and repeat 
>> until you end up with the same pairing in subsequent iterations.
>> 
>> Maybe someone on the list will feel like writing out an example for 
>> the intellectual glory  :)
>> 
>> P.
>> 
>>> On Aug 14, 2019, at 7:15 AM, Sergiusz Pawlowicz  
>>> wrote:
>>> 
>>> I have about ten thousands of points stored in a postgis database.
>>> 
>>> I need to find the longest distance between two of the furthest of 
>>> them from each other, something like bounding box, but let's say 
>>> "bounding line".
>>> 
>>> Any suggestions?
>>> 
>>> I browse the archives but there was no clearly opensource answer, the 
>>> only one was suggesting some FME-like commercial products.
>>> 
>>> Thanks,
>>> Serge
>>> ___
>>> 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
> 
> ___
> 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] Is postgres still free?

2019-08-14 Thread Paul Ramsey

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 


P. 

> On Aug 14, 2019, at 1:51 PM, Bistrais, Bob  wrote:
> 
> I just got a new PC issued and I wanted to install the latest 
> postgres/postgis.  When I went to EnterpriseDB’s website, it appeared that 
> one can download Postgres, but it only has a limited free trial period.  Is 
> that correct?  If so, is there a truly free version of Postgres still 
> available?
> ___
> 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] Inside / Outside of geography polygons

2019-08-15 Thread Paul Ramsey

Well, the changes put in only mitigate the 1/2 world case. As you get towards 
larger things, like the 3/4 world or all-of-the-world-except-this-bit, things 
will fall apart again, likely.

Yes, it’s a balance, and I expect that even this small change will cause a few 
folks to have some surprise issues.

P

> On Aug 14, 2019, at 7:48 AM, Christian Pschierer 
>  wrote:
> 
> Thanks Paul for the quick reply and the patch!
> 
> Regarding reimplementation with something more explicit, like “ring 
> orientation determines enclosure”. 
> I would prefer such a solution. From a users perspective, some polygons are 
> larger than a hemisphere. E.g. great circle mapper: "show me a polygon with 
> 15000km radius around London" (http://www.gcmap.com/mapui?R=15000km@LHR 
> <http://www.gcmap.com/mapui?R=15000km@LHR> ). This is different from "5000km 
> around a point somewhere in the south Pacific". As a power user I want to be 
> able to force one or the other interpretation.
> 
> To mitigate the knock-on effects, a convenience function might be helpful to 
> automatically reverse the polygon if the area is >>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...@cleverelephant.ca>> wrote:
>> 
>> 
>> So, I have a patch for this, against 3.0, it’s a little big, but can 
>> probably also apply against 2.5 and 2.4… it’s not great, it’s just another 
>> layer of plaster on top of a shakey foundation. An error in the computation 
>> of the XYZ bounding volume is fixed, which exposes more cases where an 
>> external point cannot be generated, so a hack to plaster over that issue is 
>> also added. At the end of it, these two test cases now return the right 
>> answers, though I have not today yet run your clever point field tests to 
>> ensure that things look more correct over the whole point field.
>> 
>>  WITH data AS (
>>SELECT 
>>  'SRID=4326;POLYGON((-36.5625 40.9798980696201, -22.5 
>> -7.71099165543322,6.328125 -44.0875850282452,130.078125 
>> -49.3823727870096,170.546875 -47.9899216674142,170.546875 
>> 69.162557908105,172.96875 75.320025232208,68.203125 77.9156689863258,4.21875 
>> 71.3007929163745,-36.5625 40.9798980696201))'::geography AS ply,
>>  'SRID=4326;POINT(0 0)'::geography AS pt
>>  )
>>  SELECT 
>>   ST_Distance(ply,pt) AS distance,
>>   ST_DWithin(ply,pt,0) AS dwithin_0,
>>   ST_DWithin(ply,pt,1) AS dwithin_1,
>>   _ST_DistanceTree(ply,pt) AS distance_tree,
>>   _ST_DistanceUnCached(ply,pt) AS distance_uncached
>>  FROM data;
>> 
>> WITH data AS (
>>  SELECT 
>>'SRID=4326;POLYGON((-40.0 52.0, -67.0 -29.0, 102.0 -6.0, -40.0 
>> 52.0))'::geography AS ply,
>>'SRID=4326;POINT(4 11)'::geography AS pt
>> )
>> SELECT 
>>  ST_Distance(ply,pt) AS distance,
>>  ST_DWithin(ply,pt,0) AS dwithin_0,
>>  ST_DWithin(ply,pt,1) AS dwithin_1,
>>  _ST_DistanceTree(ply,pt) AS distance_tree,
>>  _ST_DistanceUnCached(ply,pt) AS distance_uncached
>> FROM data;
>> 
>> One thing that leaks into this “fix” is an assumption of CCW exterior ring 
>> orientation for polygons that fail the usual external point computation 
>> phase (generally, large ones). So 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 hemisphere well. This is 
>>> not unlike the SQL Server 2008 limitation, except we don’t toss errors at 
>>> you.
>>> 
>>> https://alastaira.wordpress.com/2012/01/27/ring-orientation-bigger-than-a-hemisphere-polygons-and-the-reorientobject-method-in-sql-server-2012/
>>>  
>>> <https://alastaira.wordpress.com/2012/01/27/ring-orientation-bigger-than-a-hemisphere-polygons-and-the-reorientobject-method-in-sql-server-2012/>
>>>  
>>> <https://alastaira.wordpress.com/2012/01/27/ring-orientation-bigger-than-a-hemisphere-polygons-and-the-reorientobject-method-in-sql-server-2012/
>>>  
>>> <https://alastaira.wordpress.com/2012/01/27/ring-orientation-bigger-than-a-hemisphere-polygons-and-the-reorientobject-method-in-sql-server-2012/>>
>

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

2019-08-21 Thread Paul Ramsey

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 contents of those 
templates, so alterations to them will percolate into any new databases 
created, so they should generally be left alone (unless you have a reason to 
want something included in all new databases). If you drop template1 you will 
find it hard to create new databases (unless you specify a different template) 
but otherwise things will keep running.

Anyways, I imagine the mystery of the Esri connection to ‘postgres’ is just 
that someone at Esri thought the same as you, seeing that most builds of 
postgres do ship with a postgres database already created, so that the postgres 
database user can easily have something to connect to that is not one of the 
templates. But it’s not special, it’s just a database.

P.

> On Aug 21, 2019, at 9:33 AM, Andy Anderson  wrote:
> 
> The postgres database is set up by default by the postgres server and holds 
> configuration information: databases, users, etc. Don’t touch. Set up another 
> database for Arc to use.
> 
> — Andy
> 
>> On Aug 21, 2019, at 12:23 PM, Basques, Bob (CI-StPaul) 
>> mailto:bob.basq...@ci.stpaul.mn.us>> wrote:
>> 
>> That’s what I’m talking about.  when I first tried to connect to Posters 
>> (and the “postgres” named database, I kept getting an error from ArcGIS 
>> saying the the “postgres” name for a database was a bad thing, and it 
>> wouldn’t connect.  After much searching, I discover that the name, 
>> “postgres” for a database wasn’t allowed by the ArcGIS client to connect to. 
>>  
>> 
>> As long as you don’t try connecting to the default “postgres” database, all 
>> seem to work fine.
>> 
>> bobb
>> 
>> 
>> 
>>> On Aug 21, 2019, at 10:59 AM, Shaozhong SHI >> > wrote:
>>> 
>>> Think Before You Click: This email originated outside our organization.
>>> 
>>> 
>>> Hi, Bobb,
>>> 
>>> I am confused with this.
>>> 
>>> The idea is to use ArcGIS desktops to read data from PostGIS/PostgreSQL.
>>> 
>>> How best to configure and make them happy to talk to each other?
>>> 
>>> Regards,
>>> 
>>> Shao
>>> 
>>> On Wed, 21 Aug 2019 at 15:01, Basques, Bob (CI-StPaul) 
>>> mailto:bob.basq...@ci.stpaul.mn.us>> wrote:
>>> Shao,
>>> 
>>> I had to dig fairly deeply into the ESRI site to find this particular 
>>> explanation for why the service couldn’t/wouldn’t get connected.  Just to 
>>> be clear, we do use logins for accessing the database, we just have chosen 
>>> to use the “postgres” (named) database.
>>> 
>>> So, I just went and tried to find the refence I found previously, and can’t 
>>> find it (real quick).  Anyway,as long as you don’t try to connect to the 
>>> “postgres” database, things do seem to work ok.
>>> 
>>> bobb
>>> 
>>> 
>>> 
>>> 
>>> 
 On Aug 21, 2019, at 2:18 AM, Shaozhong SHI >>> > wrote:

 Think Before You Click: This email originated outside our organization.

 Hi, Bob,
 Can you point me to excellent technical documentation on what you 
 mentioned, so that I can double-check.
 My colleagues have made it working, but there are some issues.  For 
 instance, not all ArcGIS desktops can access and retrieve data.

 Looking forward to have more insight into this matter.

 Regards,

 Shao

 On Tue, 20 Aug 2019 at 17:08, Basques, Bob (CI-StPaul) 
 mailto:bob.basq...@ci.stpaul.mn.us>> wrote:
 All,

 One biggie, is that the ESRI side does not like connecting to the 
 “postgres” named database, claiming it’s a System database (whatever that 
 means).  This is not readily available as a limitation either when looking 
 for reasons why it won’t connect.  We’ve been using the “postgres” 
 database name for years (a decade now??) with no ill effects from other 
 applications/ connections.  I sort of understand this from the commercial 
 side having worked with other databases like Oracle and MSSQL, but still, 
 there should be a way to side step this with a setting somewhere.

 I looked high and low for a way to alias the “postgres” database name 
 inside of Postgres, but didn’t find anything very easy to implement or 
 maintain very easily.  I did think about setting up another dataabase and 
 using Views to the postgres database, but that seemed like a bad idea for 
 some reason, along with being somewhat hard to maintain, but upon 
 reflection I keep coming back to the idea.

 All non “postgres” named connections seem to work fine so far.  Haven’t 
 implemented anything yet that is really heavy duty as far as loading 
 though, so who knows.

 Anyway, just my 2cents.

 bobb

Re: [postgis-users] Upgrade issues

2019-09-03 Thread Paul Ramsey



> 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.  I’m guessing it’s checking to make 
> sure 4283 is a valid entry in spatial_ref_sys.
>  
> I’m not sure why it doesn’t need to check for geometry or maybe we just never 
> bother since geometry if you can’t transform it is not a huge deal and things 
> like 0 srid aren’t in the spatial_ref_sys anyway.
>  
> Paul – any thoughts on how to fix this one?

None. Is it easily replicable? Does any non-standard geography srid trigger it?

P

>  
> Thanks,
> Regina
>  
>  
> From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf 
> Of James Sewell
> Sent: Monday, September 2, 2019 8:32 PM
> To: PostGIS Users Discussion 
> Subject: Re: [postgis-users] Upgrade issues
>  
> Sadly that's not the case - the issue is with the actual Geometry column.
>  
> ERROR:  relation "public.spatial_ref_sys" does not exist
> LINE 21: location_pt public.geography(Point,4283),
>  
> I'm curious how this ever worked?
>  
> James
>  
> On Tue, 3 Sep 2019 at 09:35, Regina Obe  wrote:
>> I recall Raúl  mentioning he triggered this.  I think he had something like 
>> a table constraint on ST_Buffer(geog.. ) or some other function that 
>> internally relies on spatial_ref_sys.
>>  
>> The issue is that since spatial_ref_sys is a table, pg_upgrade doesn’t 
>> populate before it populates other tables. pg_upgrade first makes the 
>> structure of all the tables and pg_upgrade doesn’t know it needs to populate 
>> the data in spatial_ref_sys before the other tables as it does a create 
>> extensions in non-standard way when migrating the data to maintain same 
>> exact functions/data etc as it had before.
>>  
>> I thought we fixed this issue like in 2.5.3, but I I can’t find the bug fix 
>> notice in the notices, so maybe not.  I also can’t remember what ticket it 
>> was but I think it is ticketed.
>>  
>> What you could do is drop the offending constraint before you pg_upgrade, 
>> and then create it after the upgrade is done.
>>  
>>  
>>  
>> From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf 
>> Of James Sewell
>> Sent: Sunday, September 1, 2019 10:59 PM
>> To: postgis-users@lists.osgeo.org
>> Subject: [postgis-users] Upgrade issues
>>  
>> Hi all,
>>  
>> I'm upgrading from 9.6 -> 11. Both versions have PostGIS 2.5.1.
>>  
>> The upgrade starts then I see this in the logs:
>>  
>> pg_restore: [archiver (db)] could not execute query: ERROR:  relation 
>> "public.spatial_ref_sys" does not exist
>> LINE 39: "location_pt" "public"."geography"(Point,4283),
>>  
>> Above this I can see:
>>  
>> pg_restore: creating EXTENSION "postgis"
>>  
>> This is created fine - it just doesn't make the PostGIS tables in any schema?
>>  
>> Has anyone seen anything like this before?
>>  
>> Cheers,
>> 
>> James Sewell,
>>  
>>  
>> The contents of this email are confidential and may be subject to legal or 
>> professional privilege and copyright. No representation is made that this 
>> email is free of viruses or other defects. If you have received this 
>> communication in error, you may not copy or distribute any part of it or 
>> otherwise disclose its contents to anyone. Please advise the sender of your 
>> incorrect receipt of this correspondence.
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>  
> The contents of this email are confidential and may be subject to legal or 
> professional privilege and copyright. No representation is made that this 
> email is free of viruses or other defects. If you have received this 
> communication in error, you may not copy or distribute any part of it or 
> otherwise disclose its contents to anyone. Please advise the sender of your 
> incorrect receipt of this correspondence.
> ___
> 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-devel] Upgrade issues

2019-09-03 Thread Paul Ramsey

> 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 with regular PostGIS upgrade – ALTER 
> EXTENSION postgis UPDATE;
> 3)It’s because pg_upgrade tries to replicate exactly what you had by 
> first creating an empty PostGIS extension and then loading in all the 
> extension parts from the database.  In theory it should recognize that 
> spatial_ref_sys is a part of PostGIS, so I’m very surprised you run into a 
> situation where spatial_ref_sys table doesn’t exist.  The spatial_ref_sys 
> table being empty is a bit more understandable.
> 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).
>  
>  
> From: postgis-devel [mailto:postgis-devel-boun...@lists.osgeo.org] On Behalf 
> Of James Sewell
> Sent: Tuesday, September 3, 2019 10:26 PM
> To: PostGIS Users Discussion 
> Cc: PostGIS Development Discussion 
> Subject: Re: [postgis-devel] [postgis-users] Upgrade issues
>  
>>> > Paul – any thoughts on how to fix this one?
>>> 
>>> None. Is it easily replicable? Does any non-standard geography srid trigger 
>>> it?
>  
> Making some progress now.
>  
> It looks like pg_upgrade from any (tested 9.6, 10, -> 11) version of Postgres 
> will fail if any version of PostGIS (tested 2.5.1, 3.0) is installed and a 
> table  with a Geography, non 4326 SRID column exists.
>  
> The failure can take one of two forms:
> a) When the table is created spatial_ref_sys doesn't exist
> b) When the table is created spatial_ref_sys exists but is empty (data isn't 
> added till after the schema import using pg_upgrade)
>  
>  
>  I can't answer is how this has not been hit before???

Yeah, hard to believe, this is very old code… I guess people just don’t use 
non-WGS84 geography very often, or if they do they don’t think to set up their 
type constraints to enforce it. Or they’ve never got around to upgrading.

Anyways, the offending code is very old, and here it is:

https://github.com/postgis/postgis/blob/svn-trunk/postgis/gserialized_typmod.c#L296

When reading in the string “geography(point, 4267)” a typmod string, in the 
case of geography, the system checks that the provided SRID number is in fact a 
geodetic coordinate system and WHoops, if this is running in the context of 
pg_upgrade, that means it needs to read spatial_ref_sys, even though it’s still 
at the “setting up the schemas” stage of the process. 

Since this problem has been around forever, that kind fo points to the fact 
that maybe enforcing the geodetic nature of geography typmods isn’t the most 
important piece of data integrity checking we have to do, so the simplest fix 
would be to excise this check in all patch releases. Then the fix would be to 
upgrade to the latest patch releases before upgrading.

P.

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

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

2019-09-03 Thread Paul Ramsey

> 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 didn’t look at srids at all, or rather, 
just ignored them and used wgs84 no matter what srid you provided. But there 
weren’t ever secret hardcoded srids (except for wgs84, which was hardcoded)

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

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

2019-09-03 Thread Paul Ramsey

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  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 didn’t look at srids at all, or rather, 
> just ignored them and used wgs84 no matter what srid you provided. But there 
> weren’t ever secret hardcoded srids (except for wgs84, which was hardcoded)
> 
> Unrelated - but what on earth is this SRID list in the CREATE EXT command 
> doing?
> 
>  
> https://dpaste.de/2uf8 
> 
>  
> 
> 
> The contents of this email are confidential and may be subject to legal or 
> professional privilege and copyright. No representation is made that this 
> email is free of viruses or other defects. If you have received this 
> communication in error, you may not copy or distribute any part of it or 
> otherwise disclose its contents to anyone. Please advise the sender of your 
> incorrect receipt of this 
> correspondence.___
> 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] Are there psql command for detecting geometry types stored in PostGIS?

2019-09-20 Thread Paul Ramsey

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 c.relname = 'countries' and a.attnum > 0;



> On Sep 20, 2019, at 8:22 AM, Shaozhong SHI  wrote:
> 
> Hi, All,
> 
> Are there psql command for detecting geometry types stored in PostGIS?
> 
> For instance, data is stored in the geometry column, can we use any commands 
> to detect what types of geometry it is (e.g., collection of points, or point 
> only and etc.)?
> 
> Regards,
> 
> Shao
> ___
> 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] Are there psql command for detecting geometry types stored in PostGIS?

2019-09-20 Thread Paul Ramsey

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 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 c.relname = 'countries' and a.attnum > 0;
> 
> 
> 
>> On Sep 20, 2019, at 8:22 AM, Shaozhong SHI  wrote:
>> 
>> Hi, All,
>> 
>> Are there psql command for detecting geometry types stored in PostGIS?
>> 
>> For instance, data is stored in the geometry column, can we use any commands 
>> to detect what types of geometry it is (e.g., collection of points, or point 
>> only and etc.)?
>> 
>> Regards,
>> 
>> Shao
>> ___
>> 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] Why is && operator causing Parallel to kick in ?

2019-09-25 Thread Paul Ramsey

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 you’ve found.

P.

> On Sep 25, 2019, at 7:14 AM, Lars Aksel Opsahl  wrote:
> 
> Hi
> 
> In the  first sql below we just use "ST_Intersects(r1.geo,r2.geo)", in the 
> second SQL we add  "r1.geo && r2.geo" 
> 
> The second sql executes 3 times faster probably because of parallel seems to 
> kick in. (if we adjust parallell settings we can get the second SQL to run 6. 
> times faster)
> 
> Why is this happening when I just add "r1.geo && r2.geo" ?
> 
> I have ran ANALYZE on the table  before running the tests and there are 
> indexes on geo and gid.
> Indexes:
> "markslag_myrikilden_temp_gid_idx" UNIQUE, btree (gid)
> "geoidx__markslag_myrikilden_temp___gist" gist (geo)
> 
> We run on
> PostgreSQL 11.5 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 
> 20150623 (Red Hat 4.8.5-36), 64-bit
> POSTGIS="2.5.3 r17699" [EXTENSION] PGSQL="110" GEOS="3.8.0dev-CAPI-1.12.0 " 
> SFCGAL="1.3.7" PROJ="Rel. 6.2.0, September 1st, 2019" GDAL="GDAL 
> 3.1.0dev-7a9a0f4-dirty, released 2019/99/99" LIBXML="2.9.1" TOPOLOGY RASTER
> 1. SQL :
> EXPLAIN ANALYZE
> select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext, 
> ST_Collect(geo) as geo 
> from (
> select r1.gid, r1.atil, r1.myr, 
> r1.myrtype,r1.myromdanning,r1.myrtypetext,r1.myromdanningtext,r1.geo, true 
> doUnion
> from sde_markslag.markslag_myrikilden_temp r1,  
> sde_markslag.markslag_myrikilden_temp r2
> where ST_Intersects(r1.geo,r2.geo) and r1.gid != r2.gid
> ) as r
> group by atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext;
> ---
>  HashAggregate  (cost=10519178.25..10519180.65 rows=192 width=46) 
> (actualtime=185774.300..187207.628 rows=163 loops=1)
>Group Key: r1.atil, r1.myr, r1.myrtype, r1.myromdanning, r1.myrtypetext, 
> r1.myromdanningtext
>->  Nested Loop  (cost=0.29..9169289.37 rows=77136507 width=1612) (actual 
> time=0.680..183087.904 rows=721668 loops=1)
>  ->  Seq Scan on markslag_myrikilden_temp r1  (cost=0.00..59170.91 
> rows=567241 width=1616) (actual time=0.021..640.233 rows=567241 loops=1)
>  ->  Index Scan using geoidx__markslag_myrikilden_temp___gist 
> onmarkslag_myrikilden_temp r2  (cost=0.29..15.87 rows=19 width=1602) (actual 
> time=0.257..0.317 rows=1 loops=567241)
>Index Cond: (r1.geo && geo)
>Filter: ((r1.gid <> gid) AND _st_intersects(r1.geo, geo))
>Rows Removed by Filter: 2
>  Planning Time: 4.800 ms
>  Execution Time: 187214.730 ms
> (10 rows)
> Time: 187231.267 ms (03:07.231)
> 
> 
> 
> 2. SQL :
> EXPLAIN ANALYZE
> select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext, 
> ST_Collect(geo) as geo 
> from (
> select r1.gid, r1.atil, r1.myr, 
> r1.myrtype,r1.myromdanning,r1.myrtypetext,r1.myromdanningtext,r1.geo, true 
> doUnion
> from sde_markslag.markslag_myrikilden_temp r1,  
> sde_markslag.markslag_myrikilden_temp r2
> where r1.geo && r2.geo and ST_Intersects(r1.geo,r2.geo) and r1.gid != r2.gid
> ) as r
> group by atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext;
> ---
>  HashAggregate  (cost=243692.31..243694.71 rows=192 width=46) 
> (actualtime=62439.948..63863.421 rows=163 loops=1)
>Group Key: r1.atil, r1.myr, r1.myrtype, r1.myromdanning, r1.myrtypetext, 
> r1.myromdanningtext
>->  Gather  (cost=1000.28..242721.48 rows=55476 width=1612) (actual 
> time=1.519..59526.211rows=721668 loops=1)
>  Workers Planned: 2
>  Workers Launched: 2
>  ->  Nested Loop  (cost=0.29..236173.88 rows=23115 width=1612) 
> (actual time=0.583..59945.673 rows=240556 loops=3)
>->  Parallel Seq Scan on markslag_myrikilden_temp r1  
> (cost=0.00..55862.00 rows=236350 width=1616) (actual time=0.013..260.552 
> rows=189080 loops=3)
>->  Index Scan using geoidx__markslag_myrikilden_temp___gist 
> onmarkslag_myrikilden_temp r2  (cost=0.29..0.75 rows=1 width=1602) (actual 
> time=0.252..0.311 rows=1 loops=567241)
>  Index Cond: ((r1.geo && geo) AND (r1.geo && geo))
>  Filter: ((r1.gid <> gid) AND _st_intersects(r1.geo, geo))
>  Rows Removed by Filter: 2
>  Planning Time: 7.791 ms
>  Execution Time: 63871.265 ms
> (13 rows)
> Time: 63886.211 ms (01:03.886)
> 
> 
> Lars
> 
> ___
> postgis-users mailing list

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

2019-10-27 Thread Paul Ramsey

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 config in 
postgresql.conf.

P

> On Oct 27, 2019, at 10:25 AM, Jorge Gustavo Rocha  wrote:
> 
> Hi,
> 
> When I connect to a database, I can not query the postgis GUC variables
> postgis.enable_outdb_rasters or postgis.gdal_enabled_drivers.
> 
> $ psql service=geotuga
> psql (11.4 (Ubuntu 11.4-1.pgdg18.10+1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
> 256, compression: off)
> 
> localhost:5432 geobox@geotuga=# show postgis.gdal_enabled_drivers;
> ERROR:  42704: unrecognized configuration parameter
> "postgis.gdal_enabled_drivers"
> LOCATION:  GetConfigOptionByName, guc.c:8342
> localhost:5432 geobox@geotuga=# show postgis.enable_outdb_rasters;
> ERROR:  42704: unrecognized configuration parameter
> "postgis.enable_outdb_rasters"
> LOCATION:  GetConfigOptionByName, guc.c:8342
> localhost:5432 geobox@geotuga=#
> 
> But if I run ST_GDALDrivers(), afterwards the variables can be queried.
> 
> $ psql service=geotuga
> psql (11.4 (Ubuntu 11.4-1.pgdg18.10+1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
> 256, compression: off)
> 
> localhost:5432 geobox@geotuga=# SELECT short_name FROM ST_GDALDrivers()
> LIMIT 1;
> short_name
> 
> VRT
> 
> localhost:5432 geobox@geotuga=# show postgis.enable_outdb_rasters;
> postgis.enable_outdb_rasters
> --
> on
> 
> localhost:5432 geobox@geotuga=# show postgis.gdal_enabled_drivers;
> postgis.gdal_enabled_drivers
> --
> ENABLE_ALL
> 
> My question is: aren't these variables already assigned? Why only after
> ST_GDALDrivers() call are these variables defined?
> 
> Best regards
> 
> Jorge Gustavo
> -- 
> Jorge Gustavo Rocha
> Departamento de Informática
> Universidade do Minho
> 4710-057 Braga
> Gabinete 3.29 (Piso 3)
> Tel: +351 253604480
> Fax: +351 253604471
> Móvel: +351 910333888
> skype: nabocudnosor
> ___
> 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] Status of PostGIS 3 and PostgreSQL Yum Repos?

2019-11-04 Thread Paul Ramsey

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 the
> same data through ST_Transform() in excellent time.  Here's a
> comparison of the explain analyze on both hosts
> 
> RHEL7
> 
> POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120"
> GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.2.0" LIBXML="2.9.1" LIBJSON="0.11"
> LIBPROTOBUF="1.0.2" WAGYU="0.4.3 (Internal)"
> 
> explain analyze select st_transform(geom, 2163) from stations;
> QUERY
> PLAN
> -
> Gather  (cost=1000.00..113124.15 rows=69980 width=32) (actual
> time=3535.920..4624.442 rows=70025 loops=1)
>   Workers Planned: 1
>   Workers Launched: 1
>   ->  Parallel Seq Scan on stations  (cost=0.00..105126.15 rows=41165
> width=32) (actual time=4061.506..4104.824 rows=35012 loops=2)
> Planning Time: 0.057 ms
> Execution Time: 4627.474 ms
> 
> 
> RHEL8
> 
> POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120"
> GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.2.0" LIBXML="2.9.7" LIBJSON="0.13.1"
> LIBPROTOBUF="1.3.0" WAGYU="0.4.3 (Internal)"
> 
> explain analyze select st_transform(geom, 2163) from stations;
>   QUERY PLAN
> -
> Gather  (cost=1000.00..112529.32 rows=69618 width=32) (actual
> time=46.818..90.248 rows=69618 loops=1)
>   Workers Planned: 1
>   Workers Launched: 1
>   ->  Parallel Seq Scan on stations  (cost=0.00..104567.52 rows=40952
> width=32) (actual time=49.211..72.335 rows=34809 loops=2)
> Planning Time: 0.036 ms
> Execution Time: 91.871 ms
> 
> 
> I don't have an exact copy of the data between the two hosts, but it is close.
> 
> thanks
> daryl
> 
>> On Wed, Oct 30, 2019 at 8:58 AM  wrote:
>> 
>> The are waiting for GDAL 3.0.2 to be out to package them together.
>> Source: https://twitter.com/DevrimGunduz/status/1189216750895882243
>> 
>> In any case, ST_Transform is slower because PROJ6 is slower, but it
>> shouldn't be that slow.
>> 
>>> On Wed, Oct 30, 2019 at 2:54 PM Daryl Herzmann  wrote:
>>> 
>>> Greetings,
>>> 
>>> Does anybody know what's going on with the PostgreSQL Yum repos
>>> supporting PostGIS 3?  The repos continue to be stuck with 3.0 alpha4
>>> 
>>> https://yum.postgresql.org/12/redhat/rhel-7-x86_64/
>>> 
>>> I am having issues with performance with that alpha4 release and am
>>> hoping they magically go away with the GA release of version 3 :)
>>> 
>>> https://redmine.postgresql.org/issues/4826
>>> 
>>> Is there some public interface to see the build status results of these 
>>> RPMs?
>>> 
>>> thanks
>>> daryl
>>> ___
>>> postgis-users mailing list
>>> postgis-users@lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>> 
>> 
>> 
>> --
>> Raúl Marín Rodríguez
>> carto.com
>> ___
>> 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] Status of PostGIS 3 and PostgreSQL Yum Repos?

2019-11-05 Thread Paul Ramsey

ibboost_atomic.so.1.66.0 => /usr/lib64/libboost_atomic.so.1.66.0
> > (0x7ff536527000)
> > libCGAL.so.13 => /usr/lib64/libCGAL.so.13 (0x7ff536308000)
> > libmpfr.so.4 => /usr/lib64/libmpfr.so.4 (0x7ff5360a4000)
> > libgmp.so.10 => /usr/lib64/libgmp.so.10 (0x7ff535e0e000)
> > librt.so.1 => /usr/lib64/librt.so.1 (0x7ff535c05000)
> >
> > So RHEL8 has CGAL-4.14-1.rhel8.x86_64, whereas RHEL7 has
> > CGAL-4.7-1.rhel7.1.x86_64
> >
> > This RHEL7 host was performing fine with previous PostgreSQL + Postgis
> > releases.  Wonder what I could have fouled up
> >
> > daryl
> >
> > On Mon, Nov 4, 2019 at 9:18 PM Paul Ramsey  
> > wrote:
> > >
> > > 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 the
> > > > same data through ST_Transform() in excellent time.  Here's a
> > > > comparison of the explain analyze on both hosts
> > > >
> > > > RHEL7
> > > >
> > > > POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120"
> > > > GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.2.0" LIBXML="2.9.1" LIBJSON="0.11"
> > > > LIBPROTOBUF="1.0.2" WAGYU="0.4.3 (Internal)"
> > > >
> > > > explain analyze select st_transform(geom, 2163) from stations;
> > > > QUERY
> > > > PLAN
> > > > -
> > > > Gather  (cost=1000.00..113124.15 rows=69980 width=32) (actual
> > > > time=3535.920..4624.442 rows=70025 loops=1)
> > > >   Workers Planned: 1
> > > >   Workers Launched: 1
> > > >   ->  Parallel Seq Scan on stations  (cost=0.00..105126.15 rows=41165
> > > > width=32) (actual time=4061.506..4104.824 rows=35012 loops=2)
> > > > Planning Time: 0.057 ms
> > > > Execution Time: 4627.474 ms
> > > >
> > > >
> > > > RHEL8
> > > >
> > > > POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120"
> > > > GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.2.0" LIBXML="2.9.7" LIBJSON="0.13.1"
> > > > LIBPROTOBUF="1.3.0" WAGYU="0.4.3 (Internal)"
> > > >
> > > > explain analyze select st_transform(geom, 2163) from stations;
> > > >   QUERY PLAN
> > > > -
> > > > Gather  (cost=1000.00..112529.32 rows=69618 width=32) (actual
> > > > time=46.818..90.248 rows=69618 loops=1)
> > > >   Workers Planned: 1
> > > >   Workers Launched: 1
> > > >   ->  Parallel Seq Scan on stations  (cost=0.00..104567.52 rows=40952
> > > > width=32) (actual time=49.211..72.335 rows=34809 loops=2)
> > > > Planning Time: 0.036 ms
> > > > Execution Time: 91.871 ms
> > > >
> > > >
> > > > I don't have an exact copy of the data between the two hosts, but it is 
> > > > close.
> > > >
> > > > thanks
> > > > daryl
> > > >
> > > >> On Wed, Oct 30, 2019 at 8:58 AM  wrote:
> > > >>
> > > >> The are waiting for GDAL 3.0.2 to be out to package them together.
> > > >> Source: https://twitter.com/DevrimGunduz/status/1189216750895882243
> > > >>
> > > >> In any case, ST_Transform is slower because PROJ6 is slower, but it
> > > >> shouldn't be that slow.
> > > >>
> > > >>> On Wed, Oct 30, 2019 at 2:54 PM Daryl Herzmann  
> > > >>> wrote:
> > > >>>
> > > >>> Greetings,
> > > >>>
> > > >>> Does anybody know what's going on with the PostgreSQL Yum repos
> > > >>> supporting PostGIS 3?  The repos continue to be stuck with 3.0 alpha4
> > > >>>
> > > >>> https://yum.postgresql.org/12/redhat/rhel-7-x86_64/
> > > >>>
> > > >>> I am having issues with performance with that alpha4 release and am
> > > >>> hoping they magically go away with the GA release of version 3 :)
> > > >>>
> > > >>> https://redmine.postgresql.org/issues/4826
> > > >>>
> > > >>> Is there some public interface to see the build status results of 
> > > >>> these RPMs?
> > > >>>
> > > >>> thanks
> > > >>> daryl
> > > >>> ___
> > > >>> postgis-users mailing list
> > > >>> postgis-users@lists.osgeo.org
> > > >>> https://lists.osgeo.org/mailman/listinfo/postgis-users
> > > >>
> > > >>
> > > >>
> > > >> --
> > > >> Raúl Marín Rodríguez
> > > >> carto.com
> > > >> ___
> > > >> 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
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

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

2019-11-06 Thread Paul Ramsey

Are the contents of the proj-6.2 packages the same on both systems? Like we’re 
looking mostly for build difference here, since the code versions are all the 
same…

P

> On Nov 6, 2019, at 1:31 PM, Daryl Herzmann  wrote:
> 
> Hi Paul,
> 
> Thanks again for your response.  I ran valgrind like so:
> 
> echo "explain analyze select st_transform(geom, 2163) from stations;"
> | valgrind --tool=callgrind -v --dump-every-bb=1
> /usr/pgsql-12/bin/postgres --single -D 12/data asos
> 
> which resulted in the following log:
> 
> https://mesonet.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
> 
> On Tue, Nov 5, 2019 at 9:45 PM Paul Ramsey  wrote:
>> 
>> Do you have access to cachegrind or some other sampling profiler in
>> your environment? Maybe you can get a feel for where the cycles are
>> different? Also, does your proj install include a pgk-config? You
>> could check and see if the build flags differ between systems.
>> 
>> On Tue, Nov 5, 2019 at 7:32 PM Daryl Herzmann  wrote:
>>> 
>>> Greetings,
>>> 
>>> I have created a reproducer in development and can run whatever
>>> command necessary to help debug this problem.  I can't resolve what I
>>> should try though :)  Any suggestions?
>>> 
>>> daryl
>>> 
>>> On Mon, Nov 4, 2019 at 9:34 PM Daryl Herzmann  wrote:
>>>> 
>>>> Good evening Paul,
>>>> 
>>>> Thanks for the reply.  Some more details:
>>>> 
>>>> RHEL7
>>>> 
>>>> # ldd /usr/pgsql-12/lib/postgis-3.so
>>>> linux-vdso.so.1 =>  (0x7ffc7ef0b000)
>>>> libstdc++.so.6 => /lib64/libstdc++.so.6 (0x7fbdc45b5000)
>>>> libgeos_c.so.1 => /usr/geos38/lib64/libgeos_c.so.1 (0x7fbdc4378000)
>>>> libproj.so.15 => /usr/proj62/lib/libproj.so.15 (0x7fbdc3eef000)
>>>> libjson-c.so.2 => /lib64/libjson-c.so.2 (0x7fbdc3ce4000)
>>>> libprotobuf-c.so.1 => /lib64/libprotobuf-c.so.1 (0x7fbdc3adb000)
>>>> libxml2.so.2 => /lib64/libxml2.so.2 (0x7fbdc3771000)
>>>> libm.so.6 => /lib64/libm.so.6 (0x7fbdc346f000)
>>>> libSFCGAL.so.1 => /lib64/libSFCGAL.so.1 (0x7fbdc29ac000)
>>>> libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x7fbdc2796000)
>>>> libc.so.6 => /lib64/libc.so.6 (0x7fbdc23c8000)
>>>> /lib64/ld-linux-x86-64.so.2 (0x7fbdc4beb000)
>>>> libgeos-3.8.0.so => /usr/geos38/lib64/libgeos-3.8.0.so (0x7fbdc200)
>>>> libsqlite3.so.0 => /lib64/libsqlite3.so.0 (0x7fbdc1d4b000)
>>>> libpthread.so.0 => /lib64/libpthread.so.0 (0x7fbdc1b2f000)
>>>> libdl.so.2 => /lib64/libdl.so.2 (0x7fbdc192b000)
>>>> libz.so.1 => /lib64/libz.so.1 (0x7fbdc1715000)
>>>> liblzma.so.5 => /lib64/liblzma.so.5 (0x7fbdc14ef000)
>>>> libCGAL.so.11 => /usr/lib64/libCGAL.so.11 (0x7fbdc12c7000)
>>>> libCGAL_Core.so.11 => /usr/lib64/libCGAL_Core.so.11 (0x7fbdc108e000)
>>>> libmpfr.so.4 => /usr/lib64/libmpfr.so.4 (0x7fbdc0e33000)
>>>> libgmp.so.10 => /usr/lib64/libgmp.so.10 (0x7fbdc0bbb000)
>>>> libboost_date_time-mt.so.1.53.0 =>
>>>> /usr/lib64/libboost_date_time-mt.so.1.53.0 (0x7fbdc09aa000)
>>>> libboost_thread-mt.so.1.53.0 =>
>>>> /usr/lib64/libboost_thread-mt.so.1.53.0 (0x7fbdc0793000)
>>>> libboost_system-mt.so.1.53.0 =>
>>>> /usr/lib64/libboost_system-mt.so.1.53.0 (0x7fbdc058f000)
>>>> libboost_serialization-mt.so.1.53.0 =>
>>>> /usr/lib64/libboost_serialization-mt.so.1.53.0 (0x7fbdc0323000)
>>>> librt.so.1 => /usr/lib64/librt.so.1 (0x7fbdc011b000)
>>>> 
>>>> RHEL8
>>>> 
>>>> # ldd /usr/pgsql-12/lib/postgis-3.so
>>>> linux-vdso.so.1 (0x7ffcb9f66000)
>>>> libstdc++.so.6 => /lib64/libstdc++.so.6 (0x7ff53a841000)
>>>> libgeos_c.so.1 => /usr/geos38/lib64/libgeos_c.so.1 (0x7ff53a602000)
>>>> libproj.so.15 => /usr/proj62/lib/libproj.so.15 (0x7ff53a175000)
>>>> libjson-c.so.4 => /lib64/libjson-c.so.4 (0x7ff539f65000)
>>>> libprotobuf-c.so.1 => /lib64/libprotobuf-c.so.1 (0x7ff539d5c000)
>>>> libxml2.so.2 => /lib64/libxml2.so.2 (0x

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

2019-11-06 Thread Paul Ramsey

92566 page faults vs 2221...

On Wed, Nov 6, 2019 at 6:46 PM Daryl Herzmann  wrote:
>
> And yikes, this appears to be the source of the troubles here.
>
> On RHEL7:
>
> echo "0 0" | time /usr/proj62/bin/cs2cs +proj=longlat +datum=WGS84 +to
> +init=epsg:2163
> 9473741.42 1181205.06 0.00
> 3.04user 0.11system 0:03.15elapsed 99%CPU (0avgtext+0avgdata 
> 14744maxresident)k
> 0inputs+0outputs (0major+92566minor)pagefaults 0swaps
>
> On RHEL8:
>
> echo "0 0" | time /usr/proj62/bin/cs2cs +proj=longlat +datum=WGS84 +to
> +init=epsg:2163
> 9473741.42 1181205.06 0.00
> 0.06user 0.00system 0:00.07elapsed 97%CPU (0avgtext+0avgdata 
> 13228maxresident)k
> 0inputs+0outputs (0major+2221minor)pagefaults 0swaps
>
> So that's 3.04 seconds vs 0.06 seconds.  Will do some more checking
> and engage the proj folks perhaps.
>
> daryl
>
> On Wed, Nov 6, 2019 at 8:23 PM Daryl Herzmann  wrote:
> >
> > Greetings,
> >
> > Thanks for the continued support. I am using the RPM provided by
> > https://yum.postgresql.org/.
> >
> > On RHEL7:
> >
> > #  ldd /usr/proj62/lib/libproj.so
> > linux-vdso.so.1 =>  (0x7ffcf7deb000)
> > libsqlite3.so.0 => /lib64/libsqlite3.so.0 (0x7ff7a3f82000)
> > libpthread.so.0 => /lib64/libpthread.so.0 (0x7ff7a3d66000)
> > libstdc++.so.6 => /lib64/libstdc++.so.6 (0x7ff7a3a5f000)
> > libm.so.6 => /lib64/libm.so.6 (0x7ff7a375d000)
> > libc.so.6 => /lib64/libc.so.6 (0x7ff7a338f000)
> > libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x7ff7a3179000)
> > libdl.so.2 => /lib64/libdl.so.2 (0x7ff7a2f75000)
> > /lib64/ld-linux-x86-64.so.2 (0x7ff7a46c3000)
> >
> >
> > On RHEL8:
> >
> > # ldd /usr/proj62/lib/libproj.so
> > linux-vdso.so.1 (0x7ffcea502000)
> > libsqlite3.so.0 => /lib64/libsqlite3.so.0 (0x7fd3334b)
> > libpthread.so.0 => /lib64/libpthread.so.0 (0x7fd33329)
> > libstdc++.so.6 => /lib64/libstdc++.so.6 (0x7fd332efb000)
> > libm.so.6 => /lib64/libm.so.6 (0x7fd332b79000)
> > libc.so.6 => /lib64/libc.so.6 (0x7fd3327b5000)
> > libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x7fd33259d000)
> > 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 same spec file
> >
> > https://git.postgresql.org/gitweb/?p=pgrpms.git;a=blob;f=rpm/redhat/master/proj62/master/proj62.spec
> >
> > I have sqlite-libs-3.26.0-3.el8.x86_64 on RHEL8 and
> > sqlite-3.7.17-8.el7.x86_64 on RHEL7.
> >
> > daryl
> >
> > On Wed, Nov 6, 2019 at 3:35 PM Paul Ramsey  
> > wrote:
> > >
> > > Are the contents of the proj-6.2 packages the same on both systems? Like 
> > > we’re looking mostly for build difference here, since the code versions 
> > > are all the same…
> > >
> > > P
> > >
> > > > On Nov 6, 2019, at 1:31 PM, Daryl Herzmann  wrote:
> > > >
> > > > Hi Paul,
> > > >
> > > > Thanks again for your response.  I ran valgrind like so:
> > > >
> > > > echo "explain analyze select st_transform(geom, 2163) from stations;"
> > > > | valgrind --tool=callgrind -v --dump-every-bb=1
> > > > /usr/pgsql-12/bin/postgres --single -D 12/data asos
> > > >
> > > > which resulted in the following log:
> > > >
> > > > https://mesonet.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
> > > >
> > > > On Tue, Nov 5, 2019 at 9:45 PM Paul Ramsey  
> > > > wrote:
> > > >>
> > > >> Do you have access to cachegrind or some other sampling profiler in
> > > >> your environment? Maybe you can get a feel for where the cycles are
> > > >> different? Also, does your proj install include a pgk-config? You
> > > >> could check and see if the build flags differ between systems.
> > > >>
> > > >> On Tue, Nov 5, 2019 at 7:32 PM Daryl Herzmann  
> > > >> wrote:
>

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

2019-11-19 Thread Paul Ramsey

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 huge bbox so spatial index 
> does not help when trying to render them in mapserver.
> 
> I plan was to chop these into shorter segments with a command like:
> 
> bathymetry=# explain select depth,
> feet,
> st_linesubstring(geom, 0.5*n/length,
> case when 0.5*(n+1) from (
> select a.depth,
> -round(a.depth/0.3048) as feet,
> st_linemerge(a.geom) as geom,
> st_length(a.geom) as length
> from c100 a
> where st_length(a.geom)>0
> ) as t
> cross join generate_series(0,1) as n
> where n*0.5/length < 1;
> QUERY PLAN
> ---
>  Nested Loop  (cost=0.00..549466002.55 rows=1345242667 width=1723)
>Join Filter: (n.n)::numeric * 0.5))::double precision / 
> st_length(a.geom)) < '1'::double precision)
>->  Seq Scan on c100 a  (cost=0.00..1279615.77 rows=4035728 width=1719)
>  Filter: (st_length(geom) > '0'::double precision)
>->  Function Scan on generate_series n  (cost=0.00..10.00 rows=1000 
> width=4)
> (5 rows)
> 
> but running a restricted test query on 10,000 lines took 1.5 hours and with 
> about 12M lines in the source table I estimate about 75 days to compute :(
> 
> The 0.5 in the query is for 1/2 degree (~35 mile) max length. I can adjust 
> that to say 1 deg but I suspect that will only nominally impact the run time 
> (running a test to see).
> 
> So is there a faster way to do this?
> 
> Would smoothing the lines first help or would the cost of smooth and then 
> dividing be about the same?
> 
> -Steve W
> ___
> 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] Performance boost with ST_ValueCount() - wondering why

2020-01-09 Thread Paul Ramsey

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, so my guess is parallelism in PostgreSQL is 
the “culprit”.

P

> 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 upgrade from
> 
> Postgres 9.6 and PostGIS 2.3
> 
> to 
> 
> Postgres 12.1 and PostGIS 3.0
> 
> One of our queries has gone from about 80 seconds to 30 seconds on the new 
> releases, which is great, but I'm just trying to figure out why. I realize 
> there are a host of different reasons why performance changes may result 
> across different versions and servers, but just wondering if it's related to 
> a specific performance enhancement in Postgres or PostGIS. Can't seem to find 
> anything in the release notes that would explain it.
> 
> I've narrowed down the improvement to the part of the larger query that 
> issues an ST_ValueCount() against a large raster and then sums the results. I 
> can replicate the improvement with this simpler query subset:
> 
> SELECT pvc.value, SUM(pvc.count) AS sum 
> FROM 
> (SELECT (ST_ValueCount(cv.rast, 1)).* 
> FROM calveg_whrtype_20m AS cv) AS pvc 
> GROUP BY pvc.value
> 
> 9.6 plan
> 12.0 plan
> 
> Anything jump out as the reason for the improved plan in terms of changes to 
> Postgres or PostGIS? Something to do with the HashAggregate it seems, but not 
> sure why. And yes, this may have nothing to do with PostGIS per se, so feel 
> free to point me over to the Postgres Performance list, if so. Just thought 
> I'd start here since the raster function is involved. 
> 
> Thanks much!
> Shira
> 
> 
> 
>  
> ___
> 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] Performance boost with ST_ValueCount() - wondering why

2020-01-09 Thread Paul Ramsey

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 upgrade from
> 
> Postgres 9.6 and PostGIS 2.3
> 
> to 
> 
> Postgres 12.1 and PostGIS 3.0
> 
> One of our queries has gone from about 80 seconds to 30 seconds on the new 
> releases, which is great, but I'm just trying to figure out why. I realize 
> there are a host of different reasons why performance changes may result 
> across different versions and servers, but just wondering if it's related to 
> a specific performance enhancement in Postgres or PostGIS. Can't seem to find 
> anything in the release notes that would explain it.
> 
> I've narrowed down the improvement to the part of the larger query that 
> issues an ST_ValueCount() against a large raster and then sums the results. I 
> can replicate the improvement with this simpler query subset:
> 
> SELECT pvc.value, SUM(pvc.count) AS sum 
> FROM 
> (SELECT (ST_ValueCount(cv.rast, 1)).* 
> FROM calveg_whrtype_20m AS cv) AS pvc 
> GROUP BY pvc.value
> 
> 9.6 plan
> 12.0 plan
> 
> Anything jump out as the reason for the improved plan in terms of changes to 
> Postgres or PostGIS? Something to do with the HashAggregate it seems, but not 
> sure why. And yes, this may have nothing to do with PostGIS per se, so feel 
> free to point me over to the Postgres Performance list, if so. Just thought 
> I'd start here since the raster function is involved. 
> 
> Thanks much!
> Shira
> 
> 
> 
>  
> ___
> 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] FOSS4G 2020 Early Paper Deadline is Feb 4!

2020-01-28 Thread Paul Ramsey

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 Acceptance!

  https://2020.foss4g.org/speakers/

Why submit early? We hope early acceptance will help speakers begin 
planning their conference experience earlier, with the certainty 
that you'll be an accepted speaker. Early acceptance also helps 
us build out the program and communicate the value of FOSS4G 
to new attendees.

Help yourself, help us, submit your talk early!
Thanks for your help, 
We're looking forward to seeing you all in August,
Paul and the FOSS4G 2020 program committee
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

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

2020-01-28 Thread Paul Ramsey

Further to this:

Do not fall prey to the notion that somehow what you know or have
learned about PostGIS is "not interesting" or "not advanced" enough.
The FOSS4G audience is full of people at every stage of the learning
journey of open source geospatial, and there is an audience 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 AM Paul Ramsey  wrote:
>
> 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 Acceptance!
>
>   https://2020.foss4g.org/speakers/
>
> Why submit early? We hope early acceptance will help speakers begin
> planning their conference experience earlier, with the certainty
> that you'll be an accepted speaker. Early acceptance also helps
> us build out the program and communicate the value of FOSS4G
> to new attendees.
>
> Help yourself, help us, submit your talk early!
> Thanks for your help,
> We're looking forward to seeing you all in August,
> Paul and the FOSS4G 2020 program committee
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

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

2020-02-02 Thread Paul Ramsey

> 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 your data or some mangled version that 
exhibits the same performance gradient change from 2.5 to 3? We’ll need 
something to profile to figure out where the extra time is going…

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

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

2020-02-04 Thread Paul Ramsey

> 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 testing SQL and just test the contents of the 
bb_edge CTE. That seems to be sufficient, and it’s the only spatial part.
Second, for testing 3.0, drop the use of the &&& operator and see what happens 
with just the ST_DWithin3D function call, which should be throwing an index op 
in there implicitly.

SELECT Count(*) FROM (
 SELECT te.id, te.parent_id
FROM treenode_edge te
WHERE ST_3DDWithin(te.edge, ST_MakePolygon(ST_MakeLine(ARRAY[
ST_MakePoint(471548.0,  290140.0,160420.0),
ST_MakePoint(542460.0, 290140.0,160420.0),
ST_MakePoint(542460.0, 330140.0, 160420.0),
ST_MakePoint(471548.0,  330140.0, 160420.0),
ST_MakePoint(471548.0,  290140.0,160420.0)]::geometry[])),
20.0)
) a;

We are definitely seeing different plans in that CTE. 

Testing for 12/3: 

12/3 is picking an index scan on the spatial index, which is turning out to be 
slower. 
You can adjust the COST of the ST_3DDWithin down: default cost is 1. 
Where does the plan change as you adjust it down? 
ALTER FUNCTION ST_3DDWithin COST 9000;

A big change between 2.5 and 3.0 was costing on spatial functions, so seeing 
different plans is not surprising, especially since the mechanisms for building 
the plan have changed so much (SQL wrapper functions vs Pg12 support 
functions). It’s possible that with the cost of the function set so high, and 
actually being seen by the planner now, we’re getting an index scan that, in 
this case, is less efficient than the alternative.

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

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

2020-02-19 Thread Paul Ramsey

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 parallel is actually an antipattern, (there’s no 
performance benefit in the collect itself, so the only potential win is in 
allowing other things under the collect to go parallel)
- one of the other functions in your query has gotten a lot hotter
The external sort is the same size in both cases, so that’s not the issue.
You can probably confirm if parallelism is the problem by just turning it off 
in pg12 and re-running, see if things get better. set max_workers_per_gather to 
1 or one of the other parallel config options.
Thanks for gathering data,
P

> On Feb 19, 2020, at 5:37 AM, Stefan Duling  wrote:
> 
> Hi everyone,
> 
> at the moment I am trying out a new setup for our geo database server. 
> Primarily I upgraded from Postgres and Postgis to newer versions. Beside of 
> the memory both databases run on the same machine setups and contain 
> identical data. Differences are:
> 
> OLD SETUP
> 
> 16GB RAM
> Postgres 10.12
> Postgis 2.4
> shared_buffers = 4GB
> work_mem = 128MB
> maintenance_work_mem = 1536MB  
> 
> NEW SETUP
> 
> 32GB RAM
> Postgres 12.1
> Postgis 3.0
> shared_buffers = 6GB
> work_mem = 256MB
> maintenance_work_mem = 3GB
> 
> 
> Sadly I observe several queries that take multiple times to execute with the 
> new setup. While the most expensive query took ~10h on the old setup, nearly 
> 3 days are needed on the new setup. Currently I am analysing a more 
> lightweight query, that execution time doubled with the new setup.
> 
> Can someone help me with the EXPLAIN ANALYZE results? Is Postgres 12 / 
> Postgis 3 slower in general? Is the use of parallel workers ineffective in my 
> use case?
> 
> Thanks in advance!
> 
> Stefan Duling
> 
> —
> 
> OLD SETUP
> 
> EXPLAIN ANALYZE CREATE TABLE osm_admin_lines_split AS
> SELECT osm_id, name, admin_level, name_en, name_fr, area, iso, is_in, 
> ST_SubDivide(ST_Collect(ST_ExteriorRing(the_geom)),5000) AS geometry
> FROM (SELECT osm_id, name, admin_level, name_en, name_fr, area, iso, is_in, 
> (ST_Dump(geometry)).geom AS the_geom FROM osm_admin_areas) AS foo
> GROUP BY osm_id, name, admin_level, name_en, name_fr, area, iso, is_in;
>   
>QUERY PLAN
> ---
> ProjectSet  (cost=274906731.77..3540274986.77 rows=61668900 width=65) 
> (actual time=97024.487..141100.410 rows=739733 loops=1)
>   ->  GroupAggregate  (cost=274906731.77..299574291.77 rows=616689000 
> width=65) (actual time=97024.476..118509.455 rows=611704 loops=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  (cost=274906731.77..276448454.27 rows=616689000 width=65) 
> (actual time=97024.431..108902.529 rows=708178 loops=1)
>   Sort 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 Method: external merge  Disk: 4087720kB
>   ->  Result  (cost=0.00..163888355.59 rows=616689000 width=65) 
> (actual time=42.029..84168.985 rows=708178 loops=1)
> ->  ProjectSet  (cost=0.00..3549215.58 rows=616689000 
> width=65) (actual time=42.019..83866.290 rows=708178 loops=1)
>   ->  Seq Scan on osm_admin_areas  
> (cost=0.00..308514.89 rows=616689 width=6931) (actual time=16.288..9536.779 
> rows=611707 loops=1)
> Planning time: 0.364 ms
> Execution time: 244169.613 ms
> (11 rows)
> 
> —
> 
> NEW SETUP
> 
> EXPLAIN ANALYZE CREATE TABLE public.osm_admin_lines_split AS
> SELECT osm_id, name, admin_level, name_en, name_fr, area, iso, is_in, 
> ST_SubDivide(ST_Collect(ST_ExteriorRing(the_geom)),5000) AS geometry
> FROM (SELECT osm_id, name, admin_level, name_en, name_fr, area, iso, is_in, 
> (ST_Dump(geometry)).geom AS the_geom FROM osm_admin_areas) AS foo
> GROUP BY osm_id, name, admin_level, name_en, name_fr, area, iso, is_in;
>   
>QUERY PLAN
> ---
> ProjectSet  (c

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

2020-02-25 Thread Paul Ramsey

d: external merge  Disk: 4087808kB
>>>->  Gather  (cost=1000.00..40638832.88 rows=609088 width=65) 
>>> (actual time=1240.826..35018.389 rows=708178 loops=1)
>>>  Workers Planned: 4
>>>  Workers Launched: 4
>>>  ->  Result  (cost=0.00..40576924.08 rows=152272000 
>>> width=65) (actual time=988.020..29198.984 rows=141636 loops=5)
>>>->  ProjectSet  (cost=0.00..986204.08 
>>> rows=152272000 width=65) (actual time=988.012..29085.994 rows=141636 
>>> loops=5)
>>>  ->  Parallel Seq Scan on osm_admin_areas  
>>> (cost=0.00..186014.72 rows=152272 width=6545) (actual time=0.369..5959.617 
>>> rows=122341 loops=5)
>>>  Planning Time: 14.213 ms
>>>  JIT:
>>>Functions: 110
>>>Options: Inlining true, Optimization true, Expressions true, Deforming 
>>> true
>>>Timing: Generation 39.248 ms, Inlining 649.432 ms, Optimization 2369.206 
>>> ms, Emission 1896.395 ms, Total 4954.282 ms
>>>  Execution Time: 620994.272 ms
>>> (18 rows)
>>>
>>> —
>>>
>>> NEW SETUP WITH POSTGIS 2.5 AND max_parallel_workers_per_gather = 1
>>>
>>>
>>> EXPLAIN ANALYZE CREATE TABLE public.osm_admin_lines_split AS
>>> SELECT osm_id, name, admin_level, name_en, name_fr, area, iso, is_in, 
>>> ST_SubDivide(ST_Collect(ST_ExteriorRing(the_geom)),5000) AS geometry
>>> FROM (SELECT osm_id, name, admin_level, name_en, name_fr, area, iso, is_in, 
>>> (ST_Dump(geometry)).geom AS the_geom FROM osm_admin_areas) AS foo
>>> GROUP BY osm_id, name, admin_level, name_en, name_fr, area, iso, is_in;
>>> 
>>>   QUERY PLAN
>>> ---
>>>  ProjectSet  (cost=95352014.80..98577135.76 rows=609088000 width=65) 
>>> (actual time=51995.515..493885.155 rows=750429 loops=1)
>>>->  GroupAggregate  (cost=95352014.80..95376378.32 rows=609088 width=65) 
>>> (actual time=51995.438..66093.453 rows=611704 loops=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  (cost=95352014.80..95353537.52 rows=609088 width=65) 
>>> (actual time=51995.267..55931.535 rows=708178 loops=1)
>>>Sort 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 Method: external merge  Disk: 4087808kB
>>>->  Gather  (cost=1000.00..95287401.86 rows=609088 width=65) 
>>> (actual time=969.364..38077.753 rows=708178 loops=1)
>>>  Workers Planned: 1
>>>  Workers Launched: 1
>>>  ->  Result  (cost=0.00..95225493.06 rows=358287000 
>>> width=65) (actual time=755.966..34508.959 rows=354089 loops=2)
>>>->  ProjectSet  (cost=0.00..2070873.06 
>>> rows=358287000 width=65) (actual time=755.958..34344.231 rows=354089 
>>> loops=2)
>>>  ->  Parallel Seq Scan on osm_admin_areas  
>>> (cost=0.00..188074.87 rows=358287 width=6545) (actual time=4.870..4186.864 
>>> rows=305854 loops=2)
>>>  Planning Time: 0.310 ms
>>>  JIT:
>>>Functions: 53
>>>Options: Inlining true, Optimization true, Expressions true, Deforming 
>>> true
>>>Timing: Generation 10.729 ms, Inlining 235.111 ms, Optimization 789.374 
>>> ms, Emission 455.139 ms, Total 1490.353 ms
>>>  Execution Time: 602556.200 ms
>>> (18 rows)
>>>
>>>
>>>
>>> > Am 19.02.2020 um 12:24 schrieb Paul Ramsey :
>>> >
>>> > 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

Re: [postgis-users] AWS RDS performance

2020-02-25 Thread Paul Ramsey

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 low CPU utilization. I tried 11.6 and 
> 10.11 and RDS size db.m5.2xlarge (8 vCPU, 32 GB).
> 
> Any suggestions? Performance is not what I'd expect from a box with those 
> specs. 
> 
> Get BlueMail for Android
> On Feb 25, 2020, at 10:34 AM, Alexander Gataric  wrote:
> What parameter file settings are you using? I got low CPU utilization on 11.6 
> with postgis 2.4. 
> 
> Get BlueMail for Android
> On Feb 25, 2020, at 3:31 AM, Stefan Duling < stefan.dul...@mapz.com> wrote:
> Sorry there is a major typor in my previous mail with the postgres version. 
> Each time i wrote 10.4 it has to be 10.12. Corrected version:
> 
> 
> 
> Hello everyone,
> 
> I just want to report the outcomes of my latest performance research.
> 
> Postgres 12.1 and Postgres 12.2 are both restrictively slow in performing our 
> geometry processing queries - no matter what postgis version (3.0.0, 2.5) I 
> use and how the raw data came into the db. Setting jit on or off and/or allow 
> parallel workers and ANALYZE before the queries hasn’t a significant effect 
> on my test query. 
> 
> I installed Postgres 10.12 with postgis 2.4 on the same machine on a separate 
> cluster and it runs the queries in fraction of time. See explain analyze 
> output below.
> 
> I haven’t tested the newly released postgis 3.0.1 since there is no easy 
> installation so far (as long as I see).
> As long as I don’t see a clear way to break it down further we will stay with 
> postgres 10.12.
> 
> Regards,
> 
> Stefan
> 
> —
> NEW SETUP WITH POSTGRES 10.12, POSTGIS 2.4
> 
> 
> EXPLAIN ANALYZE CREATE TABLE osm_admin_lines_split AS
> SELECT osm_id, name, admin_level, name_en, name_fr, area, iso, is_in, 
> ST_SubDivide(ST_Collect(ST_ExteriorRing(the_geom)),5000) AS geometry
> FROM (SELECT osm_id, name, admin_level, name_en, name_fr, area, iso, is_in, 
> (ST_Dump(geometry)).geom AS the_geom FROM osm_admin_areas) AS foo
> GROUP BY osm_id, name, admin_level, name_en, name_fr, area, iso, is_in;
>   
> QUERY PLAN
> 
>
> 
> 
>  ProjectSet  (cost=272668581.44..3513224466.44 rows=61200300 width=65) 
> (actual time=56997.276..91803.763 rows=739733 loops=1)
>->  GroupAggregate  (cost=272668581.44..297148701.44 rows=612003000 
> width=65) (actual time=56997.263..69736.892 rows=611704 loops=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  (cost=272668581.44..274198588.94 rows=612003000 width=65) 
> (actual time=56997.204..61484.384 rows=708178 loops=1)
>Sort 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 Method: external merge  Disk: 4087800kB
>->  Result  (cost=0.00..162527467.79 rows=612003000 width=65) 
> (actual time=6.103..44923.073 rows=708178 loops=1)
>  ->  ProjectSet  (cost=0.00..3406687.79 rows=612003000 
> width=65) (actual time=6.095..44633.240 rows=708178 loops=1)
>->  Seq Scan on osm_admin_areas  
> (cost=0.00..190612.03 rows=612003 width=6983) (actual time=0.103..4156.204 
> rows=611707 loops=1)
>  Planning time: 4.031 ms
>  Execution time: 193917.392 ms
> (11 rows)
> 
> 
>
> 
> 
> 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] Inconsistent results on ST_Intersects and ST_Distance

2020-02-26 Thread Paul Ramsey

> 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 shows, that there is a 
> distance of 6.611191059663252E-13.
> Should ST_Distance not also return this result?
> 

They are different code lines, and have different tolerances. At some point 
“very small” becomes “zero”, and unfortunately these two lines have different 
places where that happens. If we patched up this case you’d just find a new one 
at E-20 :) the boundary between real math and discrete math exists and is 
manifest in the code.

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

[postgis-users] PostGIS 2.5.4 is Released

2020-02-28 Thread Paul Ramsey

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 systems (Paul Ramsey)
  - #4475, Avoid reading into empty ptarray (Paul Ramsey)
  - #4492, Fix ST_Simplify ignoring the value of the 3rd parameter (Raúl Marín)
  - #4494, Fix ST_Simplify output having an outdated bbox (Raúl Marín)
  - #4493, Fix ST_RemoveRepeatedPoints output having an outdated bbox (Raúl 
Marín)
  - #4495, Fix ST_SnapToGrid output having an outdated bbox (Raúl Marín)
  - #4496, Make ST_Simplify(TRIANGLE) collapse if requested (Raúl Marín)
  - #4506, Remove tolerance in point-in-ring tests (Paul Ramsey)
  - #4338, Fix Census block level data (tabblock table) loading (Regina Obe)
  - #4519, Fix getSRIDbySRS crash (Raúl Marín)
  - #4517, Documentation error ST_EndPoint (Samuel Spurling)
  - #4530, Avoid bogus AddRasterConstraint in upgrade tests (Sandro Santilli)
  - #4534, Fix leak in lwcurvepoly_from_wkb_state (Raúl Marín)
  - #4536, Fix leak in lwcollection_from_wkb_state (Raúl Marín)
  - #4537, Fix leak in WKT collection parser (Raúl Marín)
  - #4547, Fix AddRasterConstraints handling of empty tables (Sandro Santilli)
  - #4549, Fix schema qualification of internal types (Raúl Marín)
  - #4546, Fix PLPGSQL functions missing the schema qualification (Raúl Marín)
  - #4588, Fix update when st_union(geometry) doesn't exist (Raúl Marín)
  - #4599, ST_AddPoint: Accept -1 as a valid position (Raúl Marín)
  - #4605, Fix postgis_upgrade.pl with PostgreSQL 12 (Matti Linnanvuori)
  - #4621, Prevent stack overflow when parsing WKB (Raúl Marín)
  - #4626, Support pkg-config for libxml2 (Bas Couwenberg)
  - #4646, Fix gserialized_cmp incorrect comparison (dkvash)


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

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

2020-02-28 Thread Paul Ramsey

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 ops, I get this result:

-[ RECORD 1 ]---+---
st_3dintersects | f
st_intersects   | f
st_distance | 2.9103830456733704e-10
st_3ddistance   | 2.9103830456733704e-10

So they are close enough to being intersecting that slightly different 
comparisons in a different order get different answers. Discrete math is fun.

P


> On Feb 28, 2020, at 4:45 AM, Alexandre Silva 
>  wrote:
> 
> Hello, i'm having a problem that i find strange with intersecting geometries. 
> I have two geometries, they intersect in 3d but not in 2d. How is this 
> possible?
> Here's a sample:
> 
> with x as (
> select 
> st_geomfromewkb('\x0102a0b30e0200947bdfddb6b3e6c0563ada3e08920441957bdfdd36b1e6c060dcd03e08920441')
>  as broken_geom,
> st_geomfromewkb('\x010220b30e0e00b34ffa12d0b2e6c099d9d63e0892044135ff664cd8b2e6c0045b0799079204419a71eb3de1b2e6c01a83fdfe069204415b5ca842eab2e6c0930530690692044140271ba8f3b2e6c02d065ee405920441de0afec7fcb2e6c0fca16e8e0592044185ae750a06b3e6c073a9671c0592044169af95520fb3e6c0134c1ed40492044173cde2c218b3e6c0a892c48604920441f8f7d21f22b3e6c0db66bf6b04920441d13e0c902bb3e6c0f1384f4004920441e8ae92dc34b3e6c0388f5e3d0492044120cb297f3eb3e6c02ff6c54204920441cd9519fe47b3e6c0437fde4904920441')
>  as blade
>  )
>  select st_3dintersects(broken_geom, blade), st_intersects(broken_geom, 
> blade) from x;
> NOTICE:  One or both of the geometries is missing z-value. The unknown 
> z-value will be regarded as "any value"
>  st_3dintersects | st_intersects 
> -+---
>  t   | f
> (1 row)
> 
> any ideas?
> 
> Best regards,
> Alexandre Silva
> 
> 
> 
> 
> 
> ___
> 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] st_union

2020-03-02 Thread Paul Ramsey

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 takes such a long time to execute, is there a smarter way to do it?
>  
> Kind regards,
> Paul
> ___
> 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] How does PostGIS / PostgreSQL distribute parallel work to cores for GIS type data?

2020-03-02 Thread Paul Ramsey

> 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 does say “I have N records and C cores, so every core 
gets N/C records”. 
It says “I still have records, here Core 1, have 10K”. “I still have records, 
here Core 2, have 10K”, and so on. The chunks are generally smaller than N/C, 
so the net effect over a large table is that all the cores stay busy most of 
the time. 
In theory, carefully optimizing by handing out records based on vertices is a 
thing, in practice, it’s not a big deal.
  One nuance I’m not 100% sure of is if the master hands out records to workers 
in matches of num_records, or batches of num_pages. If the latter, then the 
scheme would be very much like you propose anyways, since large records would 
take up more space on a page, and data volume would determine distribution, not 
record volume. 

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

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

2020-03-02 Thread Paul Ramsey

So, this gets stranger the more I poke, but not in any helpful way.
I installed 3.0.2dev and 2.5.4dev in my Pg12 instance and ran the same
query using your test file.

https://github.com/tomka/tmp/blob/master/postgres/catmaid-postgis-test.pgsql
pg_restore -O -f- catmaid-postgis-test.pgsql | psql postgis254d

And using this test query:

explain analyze SELECT Count(*) FROM (

   SELECT te.id, te.parent_id

  FROM
treenode_edge te

   WHERE ST_3DDWithin(te.edge,
ST_MakePolygon(ST_MakeLine(ARRAY[
 ST_MakePoint(471548.0,
290140.0,160420.0),

ST_MakePoint(542460.0, 290140.0,160420.0),

 ST_MakePoint(542460.0, 330140.0, 160420.0),

 ST_MakePoint(471548.0,
330140.0, 160420.0),

ST_MakePoint(471548.0,  290140.0,160420.0)]::geometry[])),

  20.0)
) a;

And I get a 8ms execution from 12/3 and a 128ms execution from 12/2.5!

--- pg12/postgis3.0

 Aggregate  (cost=584.97..584.98 rows=1 width=8) (actual
time=7.155..7.155 rows=1 loops=1)
   ->  Index Scan using treenode_edge_gix on treenode_edge te
(cost=0.53..584.88 rows=33 width=0) (actual time=0.706..7.057 rows=143
loops=1)
 Index Cond: (edge &&&
st_expand('01038001000500F0C71C4170B5114120950341F88D204170B5114120950341F88D20417026144120950341F0C71C417026144120950341F0C71C4170B5114120950341'::geometry,
'20'::double precision))
 Filter: st_3ddwithin(edge,
'01038001000500F0C71C4170B5114120950341F88D204170B5114120950341F88D20417026144120950341F0C71C417026144120950341F0C71C4170B5114120950341'::geometry,
'20'::double precision)
 Planning Time: 2.843 ms
 Execution Time: 7.278 ms

-- pg12/postgis2.5

 Aggregate  (cost=14477.72..14477.73 rows=1 width=8) (actual
time=148.645..148.645 rows=1 loops=1)
   ->  Bitmap Heap Scan on treenode_edge te  (cost=1142.85..14472.35
rows=2151 width=0) (actual time=16.940..148.583 rows=143 loops=1)
 Recheck Cond: (edge &&
'01038001000500A0C71C4120B5114180940341A0C71C41C026144180940341208E2041C0261441C0950341208E204120B51141C0950341A0C71C4120B5114180940341'::geometry)
 Filter:
(('01038001000500F0C71C4170B5114120950341F88D204170B5114120950341F88D20417026144120950341F0C71C417026144120950341F0C71C4170B5114120950341'::geometry
&& st_expand(edge, '20'::double precision)) AND _st_3ddwithin(edge,
'01038001000500F0C71C4170B5114120950341F88D204170B5114120950341F88D20417026144120950341F0C71C417026144120950341F0C71C4170B5114120950341'::geometry,
'20'::double precision))
 Rows Removed by Filter: 32400
 Heap Blocks: exact=3507
 ->  Bitmap Index Scan on treenode_edge_2d_gist
(cost=0.00..1142.32 rows=32271 width=0) (actual time=15.024..15.024
rows=32543 loops=1)
   Index Cond: (edge &&
'01038001000500A0C71C4120B5114180940341A0C71C41C026144180940341208E2041C0261441C0950341208E204120B51141C0950341A0C71C4120B5114180940341'::geometry)
 Planning Time: 0.833 ms
 Execution Time: 148.855 ms

Also, the 12/2.5 query wants to parallelize (???) while the 12/3 one does not.

Anyways, it's super funky and not so helpful.

The trouble with your test set is that it's too small (relative to the
query?) so it's not processing many rows.

If you could run a sampling profiler against your two queries that
would pick up the places where the execution paths differ for the
different versions, it's all I'm trying to do, but I haven't been able
to even replicate the behavious so far, so I am at an impasse at this
moment.

P


On Tue, Feb 4, 2020 at 1:01 PM Tom Kazimiers  wrote:
>
> >> 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.
> >Ticket for reference https://trac.osgeo.org/postgis/ticket/4635#comm

Re: [postgis-users] st_union

2020-03-02 Thread Paul Ramsey

On Mon, Mar 2, 2020 at 1:16 PM Alexandre Neto  wrote:
>
> Stating the obvious,
>
> Do you have a spatial index on your geom column?

That will make no difference, the query is reading the whole contents
of the column, a spatial index will do nothing.

P


>
> Alexandre Neto
>
> 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 would like to do this:
>> > CREATE TABLE public."Areas_union" AS select  
>> > (st_dump(st_union("the_geom"))).geom from "Areas";
>> >
>> > But it takes such a long time to execute, is there a smarter way to do it?
>> >
>> > Kind regards,
>> > Paul
>> > ___
>> > 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] ST_Distance provides unexpected results when using Geography type

2020-03-05 Thread Paul Ramsey

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, 
-167.51953125 63.35212928507874, -167.51953125 
8.841651120809145))'::geography,50))

Past that into GeoJSON.io  and see what it looks like. All 
will become clear.

P



> On Mar 5, 2020, at 2:41 PM, Wayne Rowcliffe  > wrote:
> 
> Hi,
> 
> I've attached a file containing sample queries that reproduce what I am 
> seeing.
> 
> Basically, we have an application that shows data on the globe. We are 
> filtering the results to those within the current window viewport. What I am 
> seeing is that depending on where the map is, I'll either get results or I 
> won't, even when there should be data visible.
> 
> While investigating, I switched to calling ST_Distance and noticed that when 
> using geometry the results were always accurate. However, when using 
> geography (like we do in our application), we will sometimes get the correct 
> result, and other times it will say a point within the bounding geometry is 
> actually hundreds of thousands of meters away.
> 
> This is my version string using the query on the postgis webpage:
> PostgreSQL 10.9 (Ubuntu 10.9-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled 
> by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit POSTGIS="2.5.2 
> r17328" [EXTENSION] PGSQL="100" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 
> 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" 
> LIBXML="2.9.3" LIBJSON="0.11.99" LIBPROTOBUF="1.2.1" TOPOLOGY RASTER
> 
> The closest bug I could find was this: 
> https://trac.osgeo.org/postgis/ticket/4480 
>  
> It seems potentially different though since the bounding area of my query is 
> less than 180 degrees.
> 
> Wayne Rowcliffe
> Software Engineer, Priority5 Holdings
> E-mail: wa...@priority5.com
> 
> 
> ___
> 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] gdal version for Postgis 2.4

2020-03-09 Thread Paul Ramsey

> 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 the new gdal and put an old one back in. Run ldconfig to refresh the 
linker. Report the mistake to the pgdg yum redmine bug tracker.

P

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

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

2020-03-12 Thread Paul Ramsey

(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 your const PJ *obj, and then
using proj_identify() to see if proj can look up your projection with
a good match. Maybe it can!
(c) try a text lookup, it will probably fail, since as you note
there's a million ways text representations can differ while still
being the same.

P.

On Thu, Mar 12, 2020 at 8:20 AM Etienne B. Racine  wrote:
>
> Dear list,
>
> I'm currently updating the simple feature package for R to use the latest 
> proj6 to interact with postgis. One of the things we perform behind the scene 
> is synching the projections between R and postgis.
>
> The current version of the package provides interoperability in two ways: 
> read and write data to postgis, and allow the user to write R code that is 
> executed, as SQL, on the remote database, just like a normal data.frame (R 
> tables).
>
> Our current implementation relies on epsg code and proj4string matching to 
> synch database srid with the local crs, but with PROJ6, we need to add proj 
> wkt matching.
>
> Two questions:
>
> This is how I plan to adapt the projection synching, any suggestion for 
> improvement or pointer to other implementations would be much appreciated:
> 1. Match local epsg code and srid, create a crs in R using the `srtext` and 
> check that the two projections are equivalent using `IsSame` from 
> OGRSpatialReference.
> 2. No matching code, so check `spatial_ref_sys` table to see if a proj WKT 
> would match any `srtext` (slow and not very robust, I have a follow up 
> question)
> 3. No matching code and no WKT match, so try to match proj4string.
> 4. Clearly no matching projection, so insert new srid in `spatial_ref_sys` 
> and set `srid = max(srid) + 1`, `auth_name = 'sf'` (the simple feature 
> package in R is called sf).
> 5. If it fails (e.g. lack of permission), then error and ask the user to 
> change the projection, or use srid = 0.
>
> Is string matching `=` the best way to match a `srtext` and `proj4string` in  
> `spatial_ref_sys`?
> ``` sql
> select * from spatial_ref_sys where srtext = {wkt};
> ```
> But `=` obviously rejects matches if parameters are ordered differently (and 
> I suspect we could have issues with encoding). One solution is to read 
> `spatial_ref_sys` to R, and match there, but it is very very slow since it 
> requires to parse all the wkt to create a projection, and then perform a 
> comparison with OGRSpatialReference->IsSame on every projection.
>
> Thanks for your help!
> Etienne
> ___
> 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

1 2 3 4 5 >

1 - 100 of 417 matches

Mail list logo