ERROR: BOOM! Could not generate outside point!

[jczaplewski]

I keep seeing this error when using the geography data type in an ST_Intersects() in a WHERE clause. For example, my query currently looks like this:

SELECT gid, state FROM geologic_units WHERE ST_Intersects(the_geom::geography, (SELECT ST_Buffer('LINESTRING(-93 44,-104 39)'::geography, 35000))

If I use geometry instead of geography, it works perfectly:

SELECT gid, state FROM .geologic_units WHERE ST_Intersects(the_geom, (SELECT ST_Buffer('SRID=4326;LINESTRING(-93 44,-104 39)', 1))) is true

Using PostGIS 2.1.0 with GEOS 3.3.8-CAPI-1.7.8 and GDAL 1.10.0. Any help would be greatly appreciated. Thanks!

[pramsey]

Without the data I can't really debug this. Can you cut it down to a known, static pair of geographies? Bisect your table until you find the particular geography that is causing the problem?

[jczaplewski]

Sorry about that! After some additional testing I've realized I can only repeat this issue on the full dataset, which is 750MB (national coverage of state-level geologic maps). If I take a subset of it and run the same query I don't encounter this error.

What would be the best way for me to give you a copy of this dataset? I could zip it and put it on a server somewhere, if that works for you.

[pramsey]

There's probably one particular row that's causing the error. By "bisect" I mean do something like this. If you had a table of, say 100 entries, you'd find the offending entry by going

    WITH subset AS ( select * from mytable limit 50 offset 0 ) SELECT * FROM table WHERE ST_Intersects()
    WITH subset AS ( select * from mytable limit 50 offset 50 ) SELECT * FROM table WHERE ST_Intersects()

Then you'd know if the problem was in the first or last half of the data. So if the problem was in the last half you could go

    WITH subset AS ( select * from mytable limit 25 offset 50 ) SELECT * FROM table WHERE ST_Intersects()
    WITH subset AS ( select * from mytable limit 25 offset 75 ) SELECT * FROM table WHERE ST_Intersects()

And then you'd know if it was first or last part of *that* half, and so on until you're down to only one bad record.

[jczaplewski]

I'm going to have to dig a little more. I did LIMIT 10000 OFFSET 0 and got the error. I then did LIMIT 5000 OFFSET 0 and received no error, followed by LIMIT 5000 OFFSET 5000 and also received no error.

Another time I did LIMIT 10000 OFFSET 0 and received no error. I then limited to 20,000, 30,000 and 40,000 (there are ~34,000 rows) and didn't get any errors. I then removed the LIMIT and OFFSET and received an error. I then modified by query to include LIMIT 10000 OFFSET 0 again and received the error.

Basically, I can't consistently reproduce the issue, but it seems to be tied to the query size…? I'll hopefully be able to get a consistent reproduction of this issue on Monday.

[pramsey]

try forcing the limiting query to order by gid, something to get a known forced order in place, in case you've got an index scan or something re-ordering your output on you.

[robe]

jczaplewski,

Would it be possible for you to upgrade to a newer 2.1 (like 2.1.3 or 2.1.4?). There have been a lot of geography bugs fixed since 2.1.0 came out, that you probably aren't safe using geography with 2.1.0 anyway. Might save you trying to bisect this too if the issue is already fixed.

[jczaplewski]

I upgraded to 2.1.3 and narrowed it down to some problematic geometries. Basically, I LIMIT 10 with an offset, and if I do

ST_Intersects(geom, (SELECT buffer FROM linestring))

I get the BOOM! error. However, if I wrap the geometry in an ST_MakeValid() I do not:

ST_Intersects(ST_MakeValid(geom), (SELECT buffer FROM linestring))

Would you like me to pass along the problematic geometries for debugging, or should we consider this resolved? Thanks for your help!

[pramsey]

By wrapping in makevalid you might be inadvertently pushing the whole calculation over into geometry space. Make sure that you're in geography space with a ::geography cast on the results of make valid. If it still works then I consider the matter closed.

[jczaplewski]

Casted the results of the ST_MakeValid() to geography and it worked. I think we can consider this closed and chalk it up to some invalid geometries.