search

CrunchyData / pg_tileserv

A very thin PostGIS-only tile server in Go. Takes in HTTP tile requests, executes SQL, returns MVT tiles.
Apache License 2.0
858 stars 159 forks source link

Clipping in ST_AsMVTGeom crashes to 0-dimension geometry #39

Closed mikko-kolehmainen closed 4 years ago

mikko-kolehmainen commented 4 years ago

Hi, Sometimes pg_tileserv returns error like ERROR: lwcollection_construct: mixed dimension geometries: 2/0 SQL state: XX000

I found the crashed query with pg_stat_activity: SELECT ST_AsMVT(mvtgeom, 'reitti.tieosoiteverkko', 4096, 'geometry') FROM ( SELECT ST_AsMVTGeom( ST_Transform(t."geometry", 3857), bounds.geom_clip, 4096, 256 ) AS "geometry" , "id" FROM "reitti"."tieosoiteverkko" t, ( SELECT ST_MakeEnvelope(3.0525891615967937e+06, 8.531595349078214e+06, 3.130860678560812e+06, 8.609866866042234e+06, 3857) AS geom_clip, ST_MakeEnvelope(3.0476971917865425e+06, 8.526703379267963e+06, 3.135752648371063e+06, 8.614758835852485e+06, 3857) AS geom_query ) bounds WHERE ST_Intersects(t."geometry", ST_Transform(bounds.geom_query, 3067)) LIMIT 10000 ) mvtgeom

It seems that the clipping in ST_AsMVTGeom creates 'GeometryCollection Empty'. I found them by querying SELECT ST_AsText(ST_Transform(t."geometry", 3857)) AS "original_geometry", ST_AsText( ST_Intersection(ST_Transform(t."geometry", 3857),bounds.geom_clip)) AS "geometry" FROM "reitti"."tieosoiteverkko" t, ( SELECT ST_MakeEnvelope(3.0525891615967937e+06, 8.531595349078214e+06, 3.130860678560812e+06, 8.609866866042234e+06, 3857) AS geom_clip, ST_MakeEnvelope(3.0476971917865425e+06, 8.526703379267963e+06, 3.135752648371063e+06, 8.614758835852485e+06, 3857) AS geom_query ) bounds WHERE ST_Intersects(t."geometry", ST_Transform(bounds.geom_query, 3067)) AND ST_Dimension(ST_Intersection(ST_Transform(t."geometry", 3857),bounds.geom_clip)) != 0 LIMIT 10000 ST_Dimension returns 0 for those empty collections.

The query is generated here in code: https://github.com/CrunchyData/pg_tileserv/blob/220bc68e42dda153db08c9b4f6890a7c11c0a6d5/layer_table.go#L386-L404

I found a way to fix this, by extending WHERE-clause with
AND ST_Dimension(ST_Intersection(ST_Transform(t."geometry", 3857),bounds.geom_clip)) != 0 As a downside, it makes the query slow, as clipping is performed twice. Probably there is some better way to fix it, but I don't know if the fix should be done in PostGIS to ST_AsMVTGeom-function

pramsey commented 4 years ago

Hm, not something I want to solve at the tileserv level. What PostGIS version are you running?

smnorris commented 4 years ago

I'm running into this issue too, both with pg_tileserv and with a slightly dated version of dirt (https://github.com/tobinbradley/dirt-simple-postgis-http-api/tree/ebb83132a9b89a4521645f76bda501ab22d33af0)

pg_tileserv is hitting my local postgis 3:

postgis=# select postgis_full_version();
                                                                                                                                                             postgis_full_version
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.0.1 rec2a9aa" [EXTENSION] PGSQL="120" GEOS="3.8.1-CAPI-1.13.3" SFCGAL="1.3.7" PROJ="7.0.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.4.3 (Internal)" (core procs from "3.0.0 r17983" need upgrade) TOPOLOGY (topology procs from "3.0.0 r17983" need upgrade) (sfcgal procs from "3.0.0 r17983" need upgrade)
(1 row)

dirt is querying postgis 2.5.1:

                                                                                                    postgis_full_version
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="2.5.1 r17027" [EXTENSION] PGSQL="100" GEOS="3.7.0-CAPI-1.11.0 673b9939" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.4.0, released 2018/12/14" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" RASTER
(1 row)

I haven't dug into the problematic geometries yet....

pramsey commented 4 years ago

Having example geometries would of course be excellent... particularly since 3 is also displaying it. The clipping code in 3 is a complete re-work so it might be fiddly to track down where to fix this without some data to exercise it. I looked over the code this morning, and there's numerous places that might be where the problem is happening, and I don't want to patch them all in a whack'a'mole operation.

smnorris commented 4 years ago

Problematic features are everywhere in whse_basemapping.fwa_stream_networks_sp. They have Z values in my 2.5.1db, both Z and M in my v3 db. I'll pull a subset but you can see it here: https://hillcrestgeo.ca/projects/mvt_test/

Hmm, the source streams tile fine, must be something I've done to them.

smnorris commented 4 years ago

Here is a dump https://hillcrestgeo.ca/outgoing/public/test_streams_m_mvt.sql

I think all that I've done to the geoms is add M values: https://github.com/smnorris/fwapg/blob/master/sql/create_fwa_stream_networks_sp.sql#L65

Sample error: http://localhost:7800/temp.test_streams_m_mvt/13/1283/2832.pbf

SQL error on temp.test_streams_m_mvt/13/1283/2832.pbf
ERROR: lwcollection_construct: mixed dimension geometries: 2/0 (SQLSTATE XX000)

edit - pretty sure it isn't the measures, the problem is also present in the geoms extracted from BCGW via FME. Features requested via WFS are just fine.

smnorris commented 4 years ago

From my source file at https://geobc.s3-us-west-2.amazonaws.com/FWA.zip, I can reproduce the issue with a much small number of features:

ogr2ogr \
  -t_srs EPSG:3005 \
  -f PostgreSQL "PG:host=localhost port=5432 user=postgres dbname=postgis password=None" \
  -lco OVERWRITE=YES \
  -lco SCHEMA=temp \
  -lco GEOMETRY_NAME=geom \
  -nln test_streams_mvt \
  -where "WATERSHED_GROUP_CODE='VICT' and GNIS_NAME='Craigflower Creek'" \
  FWA.gpkg \
  FWA_STREAM_NETWORKS_SP

If I dump this test table to file and load back to postgis, the issue is gone.

pramsey commented 4 years ago

Wait, what? You load w/ ogr2ogr and there's a problem, but dump and reload the table and it's gone?

smnorris commented 4 years ago

Yep, pretty sure - I've triple checked. My first try was thru geojson so there was a reprojection that might have affected things but this seems pretty straightforward: https://gist.github.com/5dd3721bd6e006d2b64476023207cc0e

Handy workaround for now anyway 🤣

hoippatruong commented 4 years ago

Hi! For the same case as @mikko-kolehmainen , we're using postgis version: POSTGIS="2.4.4" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.1.4, released 2017/06/23" LIBXML="2.9.3" LIBJSON="0.12.99" LIBPROTOBUF="1.3.0" (core procs from "2.4.4" need upgrade) RASTER (raster procs from "2.4.4" need upgrade)

A couple examples of geometries below:

MULTILINESTRING Z ((245085.386021327 6712924.80482271 11.5439765,245093.414021321 6712931.03582276 11.5329765,245108.75802131 6712943.05282284 11.5139765,245128.118021295 6712958.20082294 11.5569765,245145.520021282 6712973.63882303 11.5829765,245160.846021271 6712988.61282311 11.6259765,245161.707021271 6712989.52182312 11.6379765,245177.362021259 6713006.0258232 11.7869765,245192.883021249 6713023.05582328 12.0199765,245208.830021237 6713040.66882336 12.2899765,245217.821021231 6713051.4858234 12.4439765,245226.153021225 6713061.93082345 12.6929765,245234.040021219 6713071.75882349 12.9089765,245250.744021207 6713093.20782357 13.3899765,245264.910021198 6713113.38882364 13.9259765,245271.170021193 6713122.33182367 14.1499765,245285.598021183 6713142.35182375 14.8189765,245299.857021173 6713163.90782382 15.3599765,245318.604021161 6713191.31982391 16.3229765,245333.58302115 6713212.70782399 17.0219764999999,245357.700021133 6713245.63782411 18.7789765,245376.77902112 6713279.4178242 20.5299765),(249003.888018581 6715623.30384254 24.7449765,249010.167018576 6715621.67084258 24.6159765,249020.106018569 6715624.75684262 24.4879764999999,249051.788018547 6715635.94784278 24.0309765,249071.627018534 6715643.18684288 23.8619765,249087.414018524 6715649.45884296 23.8069765,249105.604018511 6715656.41184304 23.6809765,249118.228018503 6715661.7348431 23.4089765,249135.623018491 6715668.56784319 23.0969765,249158.202018475 6715677.3678433 22.8869764999999,249190.399018454 6715690.56084346 22.9289765,249225.208018431 6715705.27884363 23.0669765,249245.136018418 6715713.93084372 23.0759765,249253.554018412 6715718.24484376 23.0969765,249258.715018408 6715720.74684379 23.1019765,249260.434018408 6715722.6228438 23.1209765,249262.780018406 6715726.22084381 23.1669765))

LINESTRING Z (361588.068000003 6761291.43099872 123.3319765,361596.911000004 6761294.71399872 123.1139765,361603.032000003 6761296.32699873 122.9229765,361607.071000004 6761298.02299873 122.8039765,361614.355000004 6761302.84399873 122.6379765,361621.293000003 6761307.59799873 122.4469765,361638.970000003 6761319.72299873 121.9349765,361659.577000003 6761333.30799873 121.2859765,361667.111000003 6761338.26199873 120.9999765,361688.412000003 6761351.75999873 120.1919765,361721.775000004 6761373.10399874 118.7229765,361751.160000003 6761392.72499874 117.2769765,361784.193000003 6761414.66399874 115.4779765,361816.038000003 6761436.01199874 113.6589765,361855.860000003 6761462.41699875 111.9669765,361890.411000003 6761485.32799875 110.4589765,361905.850000002 6761495.56499875 109.9079765,361935.416000003 6761515.16999876 109.0639765,362002.648000002 6761559.68399876 107.9499765,362014.591000002 6761567.31599876 107.7769765,362044.606000002 6761587.04699877 107.4639765,362093.659000002 6761619.80499877 107.1249765,362143.301000002 6761652.91699877 106.7059765,362202.949000002 6761691.43899878 106.1029765,362264.124000001 6761729.78799878 105.7289765,362324.282000001 6761764.51499879 105.4769765,362356.457000001 6761782.96699879 105.4049765,362411.469 6761811.96199879 105.3589765,362419.054 6761815.89999879 105.3369765,362472.380000001 6761843.7709988 105.2119765,362525.103000001 6761870.5599988 105.2399765,362609.307 6761913.84999881 105.6819765,362693.811 6761957.45699881 106.7289765,362703.812 6761962.61799882 106.8529765,362830.961 6762027.86299883 107.0929765,362933.879999999 6762080.79699884 105.6749765,362949.104 6762088.62699884 105.4649765,362978.101999999 6762103.54199884 104.7729765,362990.514 6762109.92599884 104.4559765,363019.037999999 6762124.62299884 103.7169765,363071.600999999 6762151.49999885 102.1479765,363089.164999999 6762160.65599885 101.6369765,363089.716999999 6762160.94399885 101.6159765,363139.838999999 6762187.07399885 100.1079765,363183.004999999 6762209.09799885 98.8009765,363199.694999999 6762217.56599885 98.3079765,363215.552999999 6762225.61699886 97.8659765,363254.318999999 6762245.29899886 96.7859764999999,363305.868999998 6762271.18999886 95.3749765,363369.932999998 6762303.99799887 93.4919765,363386.390999998 6762312.34199887 93.0749764999999,363408.265999998 6762323.91699887 92.4189765,363435.606999998 6762337.93099887 91.6049765,363462.885999998 6762351.99299888 90.6679765,363487.705999998 6762364.80499888 89.9589764999999,363527.116999997 6762385.45299888 88.7879765,363560.492999997 6762402.62599888 87.7609765,363602.469999997 6762423.87099888 86.4819765,363631.281999997 6762438.23199889 85.6939765,363653.127999997 6762448.78799889 85.2339765,363673.264999997 6762458.61599889 84.8759765,363677.746999998 6762461.13399889 84.8269765,363683.370999998 6762464.71299889 84.7979765,363686.641999997 6762469.07999889 84.9399764999999)

pramsey commented 4 years ago

@smnorris I'm not seeing the SQL error, on PostGIS 2.5, 3.0 or 3.1. I followed your GIST to download the WSA, extract the one river, and then used both the browse UI and your specific tile request...

pramsey commented 4 years ago

@hoippatruong do you have a SQL query I can run against those linestrings to generate the error condition? (Something you copy out of the --debug log, for example?) Once I can replicate the condition in PostGIS I can patch things up to fix it.

pramsey commented 4 years ago

(Just as a general note: the newer the version of PostGIS you run, the faster and more reliable tile generation will be.)

smnorris commented 4 years ago

Here's my log, requesting from the table with the single river:

2020-05-15 09:21:37.083 PDT [47351] ERROR:  lwcollection_construct: mixed dimension geometries: 2/0
2020-05-15 09:21:37.083 PDT [47351] STATEMENT:
        SELECT ST_AsMVT(mvtgeom, 'temp.test_streams_mvt_1', 4096, 'geom', 'id') FROM (
            SELECT ST_AsMVTGeom(
                ST_Transform(t."geom", 3857),
                bounds.geom_clip,
                4096,
                256
              ) AS "geom"

              , "gnis_name", "stream_magnitude", "blue_line_key_50k", "watershed_code_50k", "id", "length_metre", "gnis_id", "watershed_key", "fwa_watershed_code", "waterbody_key", "watershed_key_50k", "watershed_group_code_50k", "linear_feature_id", "edge_type", "blue_line_key", "feature_code", "left_right_tributary", "stream_order", "gradient", "watershed_group_id", "local_watershed_code", "watershed_group_code", "downstream_route_measure", "feature_source", "id"

            FROM "temp"."test_streams_mvt_1" t, (
                SELECT ST_MakeEnvelope(-1.3746435166806066e+07, 6.173665900537102e+06, -1.3736651227185564e+07, 6.183449840157604e+06, 3857)  AS geom_clip,
                        ST_MakeEnvelope(-1.3747046663032347e+07, 6.17305440431082e+06, -1.3736039730959283e+07, 6.184061336383886e+06, 3857) AS geom_query
                ) bounds
            WHERE ST_Intersects(t."geom",
                                ST_Transform(bounds.geom_query, 3005))
            LIMIT 10000
        ) mvtgeom

2020-05-15 09:21:38.937 PDT [47374] ERROR:  lwcollection_construct: mixed dimension geometries: 2/0
2020-05-15 09:21:38.937 PDT [47374] STATEMENT:
        SELECT ST_AsMVT(mvtgeom, 'temp.test_streams_mvt_1', 4096, 'geom', 'id') FROM (
            SELECT ST_AsMVTGeom(
                ST_Transform(t."geom", 3857),
                bounds.geom_clip,
                4096,
                256
              ) AS "geom"

              , "downstream_route_measure", "feature_source", "id", "length_metre", "gnis_id", "gnis_name", "stream_magnitude", "blue_line_key_50k", "watershed_code_50k", "linear_feature_id", "edge_type", "blue_line_key", "watershed_key", "fwa_watershed_code", "waterbody_key", "watershed_key_50k", "watershed_group_code_50k", "feature_code", "watershed_group_id", "local_watershed_code", "watershed_group_code", "left_right_tributary", "stream_order", "gradient", "id"

            FROM "temp"."test_streams_mvt_1" t, (
                SELECT ST_MakeEnvelope(-1.3746435166806066e+07, 6.185895825062729e+06, -1.374398918190094e+07, 6.188341809967855e+06, 3857)  AS geom_clip,
                        ST_MakeEnvelope(-1.3746588040862637e+07, 6.185742951006159e+06, -1.374383630784437e+07, 6.188494684024425e+06, 3857) AS geom_query
                ) bounds
            WHERE ST_Intersects(t."geom",
                                ST_Transform(bounds.geom_query, 3005))
            LIMIT 10000
        ) mvtgeom
smnorris commented 4 years ago

If I get the WKT of an offending geom and query that... no error. 🤨

with weird_geom as (
select 1 as id, st_geomfromtext('LINESTRING ZM (1190233.196 385730.295 1 0,1190223.886 385736.916 2.25 0,1190205.334 385748.104 2.48300000000745 0,1190183.76 385759.182 2.6929999999702 0,1190161.038 385774.173 3 0,1190158.92 385777.101 3 0,1190142.418 385787.415 3 0,1190124.769 385801.64 3 0,1190106.149 385814.882 3 0,1190080.496 385831.823 3 0,1190061.926 385842.062 3 0,1190044.47 385852.341 3 0,1190026.889 385864.514 3 0,1190003.906 385885.505 3 0,1189998.436 385896.377 3 0,1189995.739 385911.145 3 0,1189992.422 385920.988 3 0,1189992.336 385922.092 3 0,1189988.809 385934.933 3 0,1189987.469 385942.791 3 0,1189989.515 385964.848 3 0,1189988.488 385989.798 3 0,1189985.932 386003.622 3 0,1189983.291 386018.551 3 0,1189978.809 386031.356 3 0,1189967.949 386048.833 3 0,1189956.951 386070.417 3 0,1189953.702 386078.207 3 0,1189950.867 386097.082 3 0,1189949.06 386117.101 3 0,1189945.533 386129.942 3 0,1189941.153 386136.742 3 0,1189930.837 386146.331 3 0,1189917.493 386152.648 3 0,1189900.07 386158.975 3 0,1189883.761 386165.343 3 0,1189877.609 386167.967 3 0,1189869.133 386179.679 3 0,1189868.872 386185.68 3 0,1189871.632 386191.789 3 0,1189877.626 386195.011 3 0,1189898.206 386204.929 3 0,1189914.721 386218.495 3 0,1189916.368 386224.564 3 0,1189918.747 386241.731 3 0,1189920.985 386259.84 3 0,1189929.404 386274.062 3 0,1189942.379 386276.588 3 0,1189953.74 386269.093 3 0,1189967.414 386254.722 3 0,1189974.395 386254.026 3 0,1189988.393 386254.533 3 0,1190001.492 386255.166 3 0,1190008.298 386259.367 3 0,1190013.944 386269.693 3 0,1190014.774 386271.621 3 0,1190017.204 386285.784 3.03200000000652 0,1190018.312 386308.755 3.17599999997765 0,1190017.458 386328.809 3.43999999994412 0,1190016.566 386349.652 3.73100000002887 0,1190012.186 386356.452 3.77800000004936 0,1190004.891 386366.151 3.94999999995343 0,1189986.338 386377.34 4.35499999998137 0,1189967.681 386388.683 4.82999999995809 0,1189960.386 386398.382 5.03300000005402 0,1189954.734 386410.987 5.27199999999721 0,1189952.057 386429.868 5.48199999995995 0,1189948.108 386448.703 5.64399999997113 0,1189948.056 386451.706 5.64399999997113 0,1189945.345 386468.688 5.76899999997113 0,1189945.362 386469.637 6 0,1189942.612 386487.408 6 0,1189939.242 386494.403 6 0,1189921.329 386514.629 6 0,1189899.508 386532.659 6 0,1189879.028 386542.829 6.26599999994505 0,1189871.922 386545.418 6.28200000000652 0,1189851.687 386548.639 6.42200000002049 0,1189837.547 386549.076 6.52899999998044 0,1189823.567 386549.519 6.66200000001118 0,1189804.582 386548.673 6.83299999998417 0,1189789.54 386546.073 7 0,1189774.742 386542.374 7 0,1189752.838 386535.414 7 0,1189731.979 386530.547 7 0,1189714.284 386524.846 7 0,1189696.43 386519.139 7 0,1189691.55 386515.958 7 0,1189675.05 386500.178 7 0,1189652.061 386474.359 7 0,1189643.308 386470.88 7 0,1189635.196 386470.586 7 0,1189628.975 386475.264 7 0,1189620.516 386487.926 7 0,1189609.085 386497.476 7 0,1189601.631 386507.17 7 0,1189582.157 386514.373 7 0,1189561.053 386516.457 7 0,1189536.024 386515.393 7 0,1189523.953 386515.906 7 0,1189502.814 386516.091 7 0,1189486.573 386520.406 7 0,1189469.394 386525.637 7 0,1189451.171 386528.774 7 0,1189432.861 386533.015 7 0,1189413.933 386532.331 7 0,1189395.761 386532.465 7 0,1189381.832 386529.906 7 0,1189375.043 386526.656 7 0,1189363.285 386518.166 7 0,1189355.714 386506.822 7 0,1189352.953 386500.713 7 0,1189350.716 386482.603 7 0,1189350.841 386480.71 7 0,1189343.463 386465.419 7 0,1189330.836 386455.791 7 0,1189315.952 386453.197 7 0,1189301.882 386451.582 7 0,1189291.099 386447.239 7 0,1189286.22 386444.058 7 0,1189277.694 386432.68 7 0,1189275.888 386426.605 7 0,1189276.462 386411.602 7 0,1189278.275 386394.746 7 0,1189280.969 386376.814 7 0,1189282.623 386359.952 7 0,1189279.431 386341.808 7 0,1189278.721 386334.824 7 0,1189274.819 386309.696 7 0,1189269.866 386305.405 7 0,1189253.318 386295.794 7.0570000000298 0,1189236.572 386286.965 7.07499999995343 0,1189219.865 386277.348 7.17599999997765 0,1189202.358 386264.539 7.39500000001863 0,1189179.106 386244.722 7.85900000005495 0,1189159.429 386237.845 8.30299999995623 0,1189134.382 386235.834 8.87199999997392 0,1189122.311 386236.347 9.09900000004563 0,1189104.087 386239.486 9.37699999997858 0,1189089.914 386243.877 9.65700000000652 0,1189074.837 386245.231 9.81099999998696 0,1189052.727 386244.434 10 0,1189029.693 386242.338 10 0,1189007.742 386241.547 10 0,1188985.735 386240.595 10 0,1188969.544 386241.909 10 0,1188949.082 386253.033 10 0,1188939.578 386263.603 10 0,1188927.762 386281.047 10 0,1188926.722 386282.117 10 0,1188922.57 386286.87 10 0,1188916.071 386296.599 10 0,1188910.878 386302.421 10 0,1188899.602 386308.815 10 0,1188875.39 386311.897 10 0,1188850.361 386310.837 10 0,1188825.315 386308.828 10 0,1188800.285 386307.768 10 0,1188775.256 386306.709 10 0,1188753.021 386307.806 10 0,1188740.03 386310.185 10 0,1188738.917 386310.145 10 0,1188732.821 386312.93 10 0,1188711.069 386328.911 10 0,1188706.762 386336.822 10 0,1188704.501 386340.694 10 0,1188704.95 386353.678 10 0,1188712.209 386374.024 10 0,1188724.205 386398.494 10 0,1188727.693 386412.536 10 0,1188729.981 386427.643 10 0,1188733.452 386440.736 10 0,1188736.161 386449.848 10 0,1188744.283 386468.169 10 0,1188745.741 386481.348 10 0,1188744.56 386489.212 10 0,1188732.743 386506.658 10 0,1188714.172 386516.901 10 0,1188693.902 386524.079 10 0,1188672.343 386536.113 10 0,1188656.742 386549.469 10 0,1188644.005 386562.927 10 0,1188639.784 386569.734 10 0,1188636.685 386594.61 10 0,1188639.132 386609.722 10 0,1188643.419 386626.956 10 0,1188644.958 386635.867 10 0)', 3005) as geom)

SELECT ST_AsMVT(mvtgeom, 'temp.weird_geom', 4096, 'geom', 'id') FROM (
            SELECT ST_AsMVTGeom(
                ST_Transform(t."geom", 3857),
                bounds.geom_clip,
                4096,
                256
              ) AS "geom"

            FROM weird_geom t, (
                SELECT ST_MakeEnvelope(-1.3746435166806066e+07, 6.185895825062729e+06, -1.374398918190094e+07, 6.188341809967855e+06, 3857)  AS geom_clip,
                        ST_MakeEnvelope(-1.3746588040862637e+07, 6.185742951006159e+06, -1.374383630784437e+07, 6.188494684024425e+06, 3857) AS geom_query
                ) bounds
            WHERE ST_Intersects(t."geom",
                                ST_Transform(bounds.geom_query, 3005))
            LIMIT 10000
        ) mvtgeom
pramsey commented 4 years ago

And I get no error at all in either case. For something that is now presenting as a very quirky precision issue a lot of people are running into it though.

Update: Loaded the whole VICT watershed group and panned around for a while and saw no errors. I'm on Pg12, 3.0.2dev, MacOS.

Update 2: Shipped the data to a Centos7 server (12.2/3.0.1), and the error occurs quite frequently. Interesting and frightening.

pramsey commented 4 years ago

OK... OS, platform? (Of the database, not the tile server.)

pramsey commented 4 years ago

I've pulled this to a PostGIS ticket here https://trac.osgeo.org/postgis/ticket/4690

smnorris commented 4 years ago

I'm on MacOS (10.15.4) PG12.2 (via homebrew) for what it's worth. It also reproduces on Ubuntu 16.04 with postgres 10.12 / postgis 2.5.1

I tried a bigger set of data - several watershed groups - for the round trip through gpkg and everything looked just fine where there were multiple problems before.

Liking the server though!

hoippatruong commented 4 years ago

Here's an example linestring of the issue mentioned:

LINESTRING Z (3085377.40494936 8531012.22844338 17.6058970270678,3085382.21835126 8530999.71911969 17.4918970260769,3085389.9713089 8530975.21549923 17.2008970277384,3085394.80909505 8530958.60120541 17.1198970275,3085402.34840215 8530948.83195261 17.0388970263302,3085402.00510049 8530929.60016953 16.9778970284387,3085403.51202684 8530925.2449505 16.9748970288783,3085435.05139344 8530857.12090406 16.6538970265538,3085439.2168388 8530851.17988027 16.5818970277905,3085474.61932462 8530813.41964005 15.7138970289379,3085493.48475439 8530795.76512666 15.2048970293254,3085500.03911177 8530789.98116882 15.0668970262632,3085564.82487512 8530733.31383071 13.7588970288634,3085626.08024273 8530671.95970954 11.9008970297873,3085637.9895905 8530662.79192758 11.6828970294446,3085691.85281852 8530621.75056347 10.1728970305994,3085733.60583846 8530589.74681159 9.14589702896774,3085752.95049826 8530576.82839617 8.7108970284462,3085833.76189663 8530540.19585947 6.61889702826738,3085927.94528264 8530495.44073724 5.16489703021944,3085965.85640076 8530470.6373369 4.70889703091234,3085995.78613069 8530434.63173925 4.12989703193307,3086002.20581845 8530422.18671557 3.99689702969044,3086009.47951504 8530396.8349419 3.68589703179896,3086003.787919 8530356.33138162 2.64989703241736,3086001.96117823 8530341.47218308 2.36189703177661,3086002.60817143 8530330.43585985 1.9818970290944,3086006.41300625 8530315.38862757 1.62389703188092,3086015.46332697 8530300.89014096 1.48489703144878,3086024.12655029 8530290.37211635 1.41389703098685,3086041.56721544 8530279.75957902 1.28689703252167,3086051.6125682 8530276.71678517 1.21989703271538,3086065.25097538 8530275.26642593 1.21289703063667,3086080.55217411 8530277.05153478 1.10289703309536,3086093.60687101 8530281.4680409 1.38389703072608,3086105.0538553 8530287.85369185 1.42889703158289,3086126.82080013 8530305.47839689 1.48089703172445,3086181.77379467 8530352.11544984 2.84589703287929,3086214.89051617 8530376.28340091 4.51589703280479,3086258.21122834 8530390.00538051 6.97189703304321,3086276.13881248 8530392.64226235 7.97789703030139,3086325.0012755 8530400.94273253 8.74989703111351,3086396.22324715 8530436.4817229 7.81489702966064,3086416.55664199 8530445.40611348 7.59489702992141,3086452.17368678 8530452.07973846 7.36789703182876,3086482.21438039 8530446.07817233 7.38389702793211,3086508.4866994 8530432.55635306 7.345897029154,3086545.36680883 8530400.87331321 7.25889703072608,3086572.17197697 8530377.1199572 6.8908970290795,3086629.94417502 8530338.59188636 5.13589703198522,3086674.07552693 8530318.62254476 3.76789703126997,3086718.42501096 8530298.55899878 2.45589703228325,3086762.92430796 8530285.81513026 1.82789703179151,3086800.96632667 8530279.97996589 1.66989703197032,3086837.53745207 8530275.61589822 1.64789703208953,3086866.15076075 8530271.24852275 1.64289703126997,3086893.28787293 8530256.50322668 1.56489703245461,3086917.99423078 8530233.79796961 1.64189703110605,3086956.68705836 8530192.52148778 2.09789703134447,3086994.89070214 8530154.8013069 2.29889703076333,3087029.83906223 8530115.7682614 2.26789703220129,3087068.96363441 8530077.80992357 2.18989703338593,3087098.21948747 8530054.37427783 2.09689703304321,3087154.97864965 8530011.63274046 3.01789703313261,3087213.03023779 8529987.24687331 3.38089703395963,3087255.46121955 8529964.32310238 4.11489703319967,3087301.84301586 8529913.65529161 4.44789703376591,3087307.31418106 8529907.48570761 4.57089703530073,3087347.2362706 8529862.02188657 5.47689703479409,3087375.45579574 8529819.03509144 5.66489703580737,3087389.76173144 8529784.9921938 5.22489703632891,3087410.06987455 8529701.34643972 3.98689703643322,3087444.4051862 8529651.45521318 3.05089703574777,3087474.77566016 8529627.67729701 2.53589703794569,3087483.40542236 8529609.68739293 2.50989703647792)

I run this query on the table which included above mentioned linestring:

SELECT ST_AsMVT(mvtgeom, 'reitti.tieosoiteverkko', 4096, 'geometry') FROM ( SELECT ST_AsMVTGeom( ST_Transform(t."geometry", 3857), bounds.geom_clip, 4096, 256 ) AS "geometry" , "id" FROM "reitti"."tieosoiteverkko" t, ( SELECT ST_MakeEnvelope(3.0525891615967937e+06, 8.531595349078214e+06, 3.130860678560812e+06, 8.609866866042234e+06, 3857) AS geom_clip, ST_MakeEnvelope(3.0476971917865425e+06, 8.526703379267963e+06, 3.135752648371063e+06, 8.614758835852485e+06, 3857) AS geom_query ) bounds WHERE ST_Intersects(t."geometry", ST_Transform(bounds.geom_query, 3067)) LIMIT 10000 ) mvtgeom

pramsey commented 4 years ago

Workaround in 8e271f8 should suffice until fixes are in GEOS.