ST_Covers: Geography Implementation is not working, Geometry Implementation taking too long

Question

I have a query: (This query uses geographical implementation of ST_Covers function)

SELECT ST_Covers(ST_GeoGraphyFromText('MULTIPOLYGON(((179 -89,179 89,-179 89,-179 -89,179 -89)))'),ST_GeographyFromText('POINT(20 30)'));

When I run this query it should return true but it returns false. I don't know whats wrong with PostGIS (or with this query). When I change the geographical implementation with geometrical one, and rearrange the query to be like below:

SELECT ST_Covers(ST_ASTEXT(ST_GeoGraphyFromText('MULTIPOLYGON(((179 -89,179 89,-179 89,-179 -89,179 -89)))')),text('POINT(20 30)'));

This query works as it should, returning true:

I can use this query to be content but the problem is when database is large it takes too much time. Can someone tell me

1. how to make query 1 to work right (as intended, returning true)? or

2. how to make query 2 work fast with large tables?

(please do not suggest that i should remove ST_GeoGraphyFromText('MULTIPOLYGON(((179 -89,179 89,-179 89,-179 -89,179 -89) because it only represents geographical data that will be replaced by data from the column of table )

Other values which fail with query 1, are (5 5) (10 10) (-10 -10) (and many more)

Answer 1

This return true as it should

SELECT ST_Covers(ST_GeoGraphyFromText('MULTIPOLYGON(((179 1, 179 89,1 89, 1 1 ,179 1)))'),ST_GeoGraphyFromText('POINT(20 30)'));

So my best quess is that st_covers has bug or feature that it does not work when polygon fills all quartes of globe.

EDIT:

Played with this (PostGIS 2.0)

select 
  ST_Covers(
    ST_CollectionHomogenize(
      ST_Multi( -- this multi is probably useless here. it needed collectionhomogenize to get multiplogyn 
         ST_Split( -- Has to be geometry 
         ST_Split( -- Has to be geometry 
         ST_GeomFromText('MULTIPOLYGON(((179 -89,179 89,-179 89,-179 -89,179 -89)))')
    ,ST_GeomFromText('LINESTRING(0 -89, 0 89)'))
       , ST_GeomFromText('LINESTRING(-179 0, 179 0)')))
    )::geography
  , ST_GeographyFromText('POINT(20 30)')
  )

So it splits polygon to its quarters and casts geometry to geography and then compares point to polygon. It has its own problems It Uses Geometry to split because split doesn't support grography, but if you split it from meridian and equator it shouldn't be problem because lines are "straight" ( i may be wrong , but i'm fairly sure about it )

This solution is nice because you can convert all your polygons to multipolygons, or if you really want you can do it run time.

Answer 2

1. Any two points in GEOGRAPHY will be joined by the shortest great circle arc that connects them. Think carefully about what that means. Any two points on the sphere are connected by two great circle arcs, a short one and a long one (unless the points are antipodal, in which case both arcs are the same length). The polygon you intend covers most of the world. The polygon you got covers a little strip of the Pacific Ocean.

2. You are intending to create a polygon that covers the whole world, and then ask really large tables "what things does this cover"? This seems a little pointless (a polygon that covers the whole world will cover all things, all the time). It's also not something that can be optimized: if your query polygon covers the whole space of the table, then every record in the table is going to have to be examined against the polygon and tested. Whole table scans are expensive, full stop.

Now, practically, I doubt your use case involves a whole world polygon, but maybe it involves something really large, like the ocean. A classic question might be "how many people live within 10 miles of the sea?"

If your ocean is represented as a single massive polygon, then every population record will have to be tested to see if it's within 10 miles, and each of those tests will have to evaluate against the whole ocean polygon.

If, on the other hand, your ocean is represented by a collection of small ocean polygons (if, for example, you pre-process it by cutting it into a grid shape) then each little ocean polygon can find just those population records that are near to it for testing, and everything gets much more efficient.

Answer 3

I don't know why your first query does not work. I see that you are using Postgis 1.5 - I only checked this on Postgis 2.0. One alternative approach that works, is to cast your geographies to geometries. This assumes that using a geometric projection for the globe would be sufficiently accurate for your purposes.

SELECT ST_Covers(
    ST_Transform(
        ST_GeoGraphyFromText('MULTIPOLYGON(((179 -89,179 89,-179 89,-179 -89,179 -89)))')::geometry, 4326), 
    ST_Transform(
        ST_GeographyFromText('POINT(20 30)')::geometry, 4326)
);

You could also use ST_Intersects:

SELECT ST_Intersects(
    ST_Transform(
        ST_GeoGraphyFromText('MULTIPOLYGON(((179 -89,179 89,-179 89,-179 -89,179 -89)))')::geometry, 4326), 
    ST_Transform(
        ST_GeographyFromText('POINT(20 30)')::geometry, 4326)
);

(Also, it does not make a big difference, but you don't need to use multipolygon here; polygon would be sufficient).