Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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)

share|improve this question

3 Answers 3

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.

share|improve this answer
    
Yeah that part I know, So what should be the solution then if I wish to cover the whole globe? –  khobab Jan 18 '13 at 14:50
    
The globe doesn't have "corners". This is the core of your problem. –  Paul Ramsey Feb 20 '13 at 17:33
    
Oh, and while this approach does fix the "longer than 180" problem with the arcs, the answer is still not exactly what you expect, since those northern arcs from (-179 89) to (0, 89) don't run along latitude 89, they curve up and over the pole. However, you're probably OK with that. Still not sure why you're asking "select * from mytable where <in the world>" instead of "select * from mytable". –  Paul Ramsey Feb 20 '13 at 17:37
  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.

share|improve this answer

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.