PostGIS

The national loader script generated by the geocoder contains a syntax error when you include ZCTA data. See attached patch.

After loading the Tiger Geocoder in a staging database I moved it to my main development database on the same server via pg_dump/pg_restore. The restore generated constraint errors trying to load table zcta_all: pg_restore: [archiver (db)] Error ... [More] from TOC entry 5776; 0 1025631 TABLE DATA zcta5_all dbadmin pg_restore: [archiver (db)] COPY failed for table "zcta5_all": ERROR: new row for relation "zcta5_all" violates check constraint "enforce_geotype_the_geom" DETAIL: Failing row contains (1, 39, 43451, B5, G6350, S, 63411475, 157689, +41.3183010, -083.6174935, N, 0103000020AD100000010000001600000014950D6B2AEB54C0C80A7E1B62AA44...). CONTEXT: COPY zcta5_all, line 1: "1 39 43451 B5 G6350 S 63411475 157689 +41.3183010 -083.6174935 N 0103000020AD10000001000000160000001..." Seems as though the tiger.zcta5 table has a constraint enforcing type of objects: CONSTRAINT enforce_geotype_the_geom CHECK ((geometrytype(the_geom) = 'MULTIPOLYGON'::text) OR (the_geom IS NULL)), Most of the records from the 2017 load are POLYGON type, with a handful GEOMETRYCOLLECTION. This constraint seems to be created on the tiger.zcta5 table by the CREATE EXTENSION for tiger_geocoder Dropping the constraint allowed the restore to proceed normally. Discovered on Ubuntu 16.04 with PostGIS installed via package POSTGIS="2.4.4 r16526" 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" RASTER [Less]

​https://github.com/postgis/postgis/pull/240 This is to accept Even Rouault's patch provided. Note these functions will be used later for clients such as QGIS for aiding in viewing out-db rasters in cases where both client and server have access to the raw files to verify the files being referenced are the same.

It appears that ST_DWithin is not properly calculating distance for geography points. Or at the very least, it is not calculating distance the same as ST_Distance does. (When forced to use a sphere, both ST_DWithin and ST_Distance match.) I've ... [More] included a query below to demonstrate the problem. It includes several different calculations to illustrate what is going on, but I will summarize the key points here: The points are Point(1 2) and Point(1 1). According to a 3rd party calculator (​http://www.flymicro.com/records/distcalc.cfm) the distance between these two points is 110575 meters and this matches with the calculation produced by ST_Distance. ST_DWithin appears to be calculating a value of around 111155.5 meters, which does not match the value calculated by ST_Distance with either a spheroid or sphere. However, the function _ST_DWithinUncached appears to be calculating the correct value of 110575. Query: select ST_asEWKT(a), ST_asEWKT(b), pg_typeof(a), pg_typeof(b), ST_Distance(a, b, true) as dist_true, ST_Distance(a, b, false) as dist_false, ST_DWithin(a, b, 111155.5, true) as within_111155_5_true, ST_DWithin(a, b, 111155.6, true) as within_111155_6_true, ST_DWithin(a, b, 111195.079, false) as within_111195_079_false, ST_DWithin(a, b, 111195.08, false) as within_111195_08_false, _ST_DWithinUncached(a, b, 110576, true) as within_uncached_110576, _ST_DWithinUncached(a, b, 110575, true) as within_uncached_110575, _ST_DWithinUncached(a, b, 111195.08, false) as within_uncached_111195_08_false, _ST_DWithinUncached(a, b, 111195.079, false) as within_uncached_111195_079_false from ( select ST_GeogFromText('SRID=4326;POINT(1.0 2.0)') as a, ST_GeogFromText('SRID=4326;POINT(1.0 1.0)') as b ) as points Results: st_asewkt | st_asewkt | pg_typeof | pg_typeof | dist_true | dist_false | within_111155_5_true | within_111155_6_true | within_111195_079_false | within_111195_08_false | within_uncached_110576 | within_uncached_110575 | within_uncached_111195_08_false | within_uncached_111195_079_false ----------------------+----------------------+-----------+-----------+-----------------+-----------------+----------------------+----------------------+-------------------------+------------------------+------------------------+------------------------+---------------------------------+---------------------------------- SRID=4326;POINT(1 2) | SRID=4326;POINT(1 1) | geography | geography | 110575.06481434 | 111195.07973463 | f | t | f | t | t | f | t | f [Less]

​MVT wants its rings to be CW, but we output CCW rings, because we apply the orientation forcing after the coordinates have been flipped into image coordinates.

The gdal test suite fails with GDAL 2.3.0-rc1: Suite: gdal Test: test_gdal_configured ...ERROR 4: Unable to open EPSG support file gcs.csv. Try setting the GDAL_DATA environment variable to point to the directory containing EPSG csv files. ... [More] FAILED 1. cu_gdal.c:28 - rt_util_gdal_configured() Test: test_gdal_drivers ...passed Test: test_gdal_rasterize ...passed Test: test_gdal_polygonize ...passed Test: test_raster_to_gdal ...Warning 1: Cannot find pcs.csv Warning 1: Cannot find datum.csv or gdal_datum.csv Warning 1: Cannot find ellipsoid.csv Warning 1: Cannot find prime_meridian.csv Warning 6: PNG driver doesn't support data type Float64. Only eight bit (Byte) and sixteen bit (UInt16) bands supported. Defaulting to Byte passed Test: test_gdal_to_raster ...passed Test: test_gdal_warp ...passed The full buildlog is attached. [Less]

Actually I am not sure it is ST_RemoveRepeatedPoints that is the problem, but something has happened since PostGIS 2.4. I have problems vatching what is actually happening. I am running r16554 This query returns as expected a "MULTIPOLYGON ... [More] EMPTY": # select st_snaptogrid('multipolygon(((1 1, 1 2, 2 2,1 1)))'::geometry, 10); st_snaptogrid -------------------- 010600000000000000 (1 row) And if I feed ST_RemoveRepeadedPoints with Multipolygon Empty I get, as expected a Multipolygon Empty back: # select st_removerepeatedpoints('010600000000000000'::geometry); st_removerepeatedpoints ------------------------- 010600000000000000 (1 row) But if I feed ST_RemoveRepeatedPoints with the result from query 1 directly it reads the geometry type from the right place: # select st_removerepeatedpoints(st_snaptogrid('multipolygon(((1 1, 1 2, 2 2,1 1)))'::geometry, 10)); ERROR: Unknown geometry type: 1983230296 - Invalid type In Postgis 2.4.4 r16526 I get the expected Multipolygon empty back instead. Also if I change to polygon instead of multipolygon it works as expected also in latest trunk version: # select st_removerepeatedpoints(st_snaptogrid('polygon((1 1, 1 2, 2 2,1 1))'::geometry, 10)); st_removerepeatedpoints ------------------------- 010300000000000000 (1 row) [Less]

See ​https://git.osgeo.org/gitea/postgis/postgis/pulls/24. Aims to simplify code and increase performance. It is faster according to some limited manual tests. Wanted to do some more scientific and automatic performance tests but got lost in how to proceed with that.

After upgrading PostGIS from version 2.4.1 to 2.4.3 (and in the process also updating the geos dependency from 3.5.0 to 3.6.2) a query on a dataset of 5310482 rows with a geometry of type Polygon which used to take around 3.5 hours to complete, now ... [More] doesn't complete. The query is below: select st_multi((st_dump(geom)).geom)::geometry(multipolygon,25832) as geom from ( select st_union(b.geom) as geom from temp_jonyp.st_union_test b ) foo ; I've enclosed a sample dataset of 31489 rows. Using the sample dataset, the query on the machine with PostGIS 2.4.3 takes 1 minute and 20 seconds, and on a Windows machine with PosGIS 2.3.2 (geos 3.5.0) it takes about 20 seconds This ticket refers to a discussion on the PostGIS Users Mailing List, header 'Performance problems with ST_Union on postgres 9.6, Postgis 2.4.3 running on top of Red Hat 7.4' Version information: 'PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit POSTGIS="2.4.3 r16312" PGSQL="96" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" SFCGAL="1.2.2" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" RASTER' [Less]

The following command fails: SELECT ST_OffsetCurve(ST_GeomFromText('LINESTRING(362194.505 5649993.044,362197.451 5649994.125,362194.624 5650001.876,362189.684 5650000.114,362192.542 5649992.324,362194.505 5649993.044)', 25832), -0.045 ... [More] , 'join=mitre'); with the error: FEHLER: GEOSOffsetCurve: TopologyException?: assigned depths do not match at 362194.48950385087 5649993.0862477142 SQL Status:XX000 I am using: 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" RASTER I also tested it with: 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 Note that the first and last coordinates of the linestring are the same. If the first or last coordinate or both are changed the error does not occur. Also if another distance than -0.045 is used the error does not occur. This error might be somewhat related to #2496, the fix however is not applicable as the bug does appear with newer versiosn of GEOS. [Less]