[postgis-users] ST_Intersects(BOOM!)

[Aaron Cope]

Hi Regina,

Just a quick note to say thanks for this. I've been swamped with other
things so I haven't been able to revisit this but I will shortly.



On Mon, Aug 28, 2017 at 8:38 PM, Regina Obe <lr at pcorp.us> wrote:
> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users