ST_Distance geography throws an error with empty geographies

[robe]

I suppose this is technically not a defect, but is not consistent with the way ST_Distance behaves with geometry. ST_Distance returns 0 in this case . Also its not desirable for queries to fail with empty geometries/geoegraphies

But this: SELECT ST_Distance(ST_GeographyFromText('SRID=4326;POLYGON EMPTY'), ST_GeographyFromText('SRID=4326;POINT(1 2)'));

returns: ERROR: Error in geography_distance_sphere calculation. CONTEXT: SQL function "st_distance" statement 1

[pramsey]

Oh, the semantics of EMPTY, I love you so. So, distance(something, empty) == 0 in general? What about distance(empty, empty)? Same? Shouldn't distance(something, empty) return NULL? Or (heh, IEEE) NaN?

[robe]

Yap that's why I said its technically not a bug just a difference in behavior and philosophy.

I have no idea what theoretically the distance is between empty space and a nonempty geometry or empty and empty. I would say its 0 because empty space exists everywhere

However NULL with anything should be NULL — because NULL is just missing value (missing geometr/geography and empty space are hmm different )

[pramsey]

We now return 0.0 for empty distance calcs, just like geography :/ at r4598

[mcayland]

Hmmm I think I like Paul's original idea of returning NULL here, mainly because 0.0 is also a valid distance. Otherwise if a user unknowingly passes in an empty polygon to the distance calculation, then they are returned a seemingly meaningful answer for a meaningless calculation

ATB,

Mark.

[robe]

Well its inconsistent with what we are currently doing is all. Right now regular distance returns 0. either way it shouldn't be throwing an error.

[mcayland]

Oh yeah - I'd say that throwing an error is definitely excessive here since the input is perfectly valid - it's just that the result has no answer.

ATB,

Mark.

[robe]

Okay I'm fine with NULL. Don't care to argue the philosophy of where or where not an empty geometry is located in the world. However all I'm saying is geography distance and geometry distance should behave consistently. So if you are going to change it to NULL — we should probably do the same for geometry distance. or we can say not to bother since geography is new we can break old habits. I feel very unstrongly about the choice of NULL and 0 (and lukewarm about maintaining consistency) — so I leave it up to the rest of you to decide.

FWIW: SQL Server 2008 returns NULL when I do this.

SELECT geometry::STGeomFromText('POLYGON EMPTY',4326).STDistance(geometry::STGeomFromText('POLYGON EMPTY',4326))

Wonder how other databases react to this question.

[robe]

Wait a minute — I see Paul already put in a patch (and he put in a patch to keep 0). though I think he meant geometry when he said geography. I would just assume keep it that way. I hate to change old behavior on something we can't even agree on.

[mcayland]

Well the argument for NULL is fairly easy, given that the result is unknown (i.e. can't be calculated) in this case, and the fact that SQL Server 2008 returns the same answer is also a good indication that someone else independently has followed the same logic. I'd be inclined to say change both geometry and geography to return NULL in trunk but don't backpatch.

ATB,

Mark.

[pramsey]

Sold, to the man with the winning smile. I find the 0.0 result more confusing than the null result, so I'll make both results null. Mind you there's all kinds of other interesting questions in dealing with empties that I'm sure we'll get to Here's one: for st_dwithin(empty, geometry), should the result be NULL or FALSE?

[mcayland]

That's a tricky one… Off the top of my head, I see this:

ST_DWithin(null, null) → null ST_DWithin(null, geometry) → null ST_DWithin(geometry, null) → null

i.e. if any input parameter is null, the output is null

ST_DWithin(empty, geometry) → false ST_DWithin(geometry, empty) → false

i.e. if any input parameter is a geometry (but empty) then the predicate can never be true (because an empty geometry exists but has no area or perimeter)

It would be nice to come up with a definitive reference for this, whatever we choose, and put it up somewhere on the wiki.

ATB,

Mark.

[robe]

This is not fixed in my book.

I still get

ERROR:  Error in gbox_from_gserialized calculation.
CONTEXT:  SQL function "st_distance" statement 1

When I try to do

SELECT ST_Distance(ST_GeographyFromText('SRID=4326;POLYGON EMPTY'), ST_GeographyFromText('SRID=4326;POINT(1 2)')); 

[pramsey]

Thanks for re-testing, I see what I'm going to do today, carefully combing the index and function semantics of EMPTY. What is the bounding box of EMPTY? How do you index EMPTY? What a great day!

[pramsey]

Apply handling for EMPTY geometries to all geography functions per the DevWikiEmptyGeometry page. r4778