[postgis-users] ST_Intersects(BOOM!)

[Regina Obe]

Aaron,

This works fine for me.  

I'm also assuming you are using geometry and not geography so the code snippet you pointed at shouldn't be relevant.

In case you are though I tested both and they work fine for me.

What does your 

SELECT postgis_full_version(); 

Return?  Mine returns:
POSTGIS="2.4.0dev r15600" GEOS="3.7.0dev-CAPI-1.11.0 8fe2ce6" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.2.1, released 2017/06/23" LIBXML="2.7.8" LIBJSON="0.12" LIBPROTOBUF="1.2.1" RASTER

But the 2.4.0 part shouldn't matter, if you are running a lower Proj, that might matter.

Here is how I tested -- just to verify we are testing the same thing

I got to test out my new http extension compile - https://github.com/pramsey/pgsql-http 
 :)

CREATE EXTENSION http;

CREATE TABLE whosonfirst(id serial, geom geometry);

--SELECT http_set_curlopt('CURLOPT_CAINFO', 'C:/ssl/certs/ca-bundle.crt');
INSERT INTO  whosonfirst(geom)
SELECT ST_GeomFromGeoJSON(h.content::jsonb->>'geometry')
FROM http_get('https://whosonfirst.mapzen.com/data/136/253/057/136253057.geojson') AS h;

-- this works

SELECT id  FROM whosonfirst WHERE ST_Intersects(geom::geography, ST_GeomFromGeoJSON('{"type": "Point",
"coordinates": [145.748209, 15.193315]}')::geography);

Returns 1 as expected.

This also works:

SELECT id  FROM whosonfirst WHERE ST_Intersects(geom::geometry, ST_GeomFromGeoJSON('{"type": "Point",
"coordinates": [145.748209, 15.193315]}')::geometry);

Returns 1


SELECT ST_NPoints(geom) from whosonfirst;
Returns:
36822

Hope that helps,
Regina


-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Aaron Cope
Sent: Monday, August 28, 2017 6:24 PM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] ST_Intersects(BOOM!)

Hi,

Apologies if this has been answered elsewhere already. If it has I've not been able to find it.

We are using PGIS to do point-in-poly lookups and having trouble involving certain very large records.

SELECT version();
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
(1 row)

Specifically:

https://whosonfirst.mapzen.com/spelunker/id/136253057/
https://whosonfirst.mapzen.com/data/136/253/057/136253057.geojson

Whose MBR is:

-179.143503384, -14.532891534, 179.780935092, 71.412502346

This is where it starts to get confusing for me. PGIS indicates that both the geometry itself and the point we are trying to intersect with are valid:

SELECT ST_IsValid(geom::geometry) FROM whosonfirst WHERE id=136253057;  st_isvalid
------------
 t
(1 row)

SELECT ST_IsValid(ST_GeomFromGeoJSON('{"type": "Point", "coordinates":
[145.748209, 15.193315]}')::geometry);
 st_isvalid
------------
 t
(1 row)

However, when try to intersect against the former I trigger the "BOOM!
Could not generate outside point" error:

SELECT id, parent_id, placetype_id, meta FROM whosonfirst WHERE ST_Intersects(geom, ST_GeomFromGeoJSON('{"type": "Point",
"coordinates": [145.748209, 15.193315]}'));
ERROR:  BOOM! Could not generate outside point!

https://github.com/postgis/postgis/blob/cc3437595e88eac7f20b0f3b780a3816cdc912c9/liblwgeom/lwgeodetic.c#L1536-L1537

We've also been able to reproduce the problem with this record which has a similarly large MBR:

https://whosonfirst.mapzen.com/spelunker/id/136253037/
https://whosonfirst.mapzen.com/data/136/253/037/136253037.geojson

Am I missing something obvious or is there something else at work here?


Thanks,
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users