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.

Using PostGIS 2.0.0 I need to filter the results of a function, which returns many POINTs, to find the ones that lie within an elliptical area defined by two points and a distance (all parameters passed into the query). A filter using ST_Distance does the trick, but I wonder if there's any way to speed it up?

Simplified query:

SELECT candidate_point
FROM my_function(some, params)
WHERE
    ST_Distance(candidate_point, 'SRID=4326;POINT(1 2)'::geography) +
    ST_Distance(candidate_point, 'SRID=4326;POINT(3 4)'::geography) <= 60000

I tried using ST_DWithin as an additional filter, but that slows it down tenfold! Because candidate_point comes from a function, not a table, there is no index to use. Perhaps there is still something that can make it faster?

share|improve this question
    
I don't follow your response, Reality: at bottom, this question appears to have nothing to do with any polygon. As before, it asks to select points relative to two foci of an ellipse. It appears both questions address possible workarounds for a selection criterion you have yet to articulate. Why don't you start over and ask the question you really need to ask: precisely what is the selection criterion you want to apply? Let your respondents use their creativity and experience to suggest solutions rather than focusing on making your workarounds succeed. –  whuber Dec 20 '11 at 22:39
    
This question does have a polygon - the one created by ST_Buffer. That shape is not an ellipse, as before (the requirements have changed). The context is that I need to find matching rows for features that lie on the path between two points. The full criteria for what path should be used are very complex and most of the work is done outside of the DB (by someone else). My task is to do a preliminary filter to eliminate features that are definitely too far away to be relevant. I was first asked to find features within an ellipse, but it's now narrowed down a "corridor", ie. an expanded line. –  Reality Exists Dec 21 '11 at 1:03
    
So this really is the same question, as @underdark has suspected: you want to select points that are "close" to the geodesic between two given points. The buffer approach here and the ellipse approach in the previous question are just two possible ways to solve the same problem. –  whuber Dec 21 '11 at 3:11
    
OK, never mind. –  Reality Exists Dec 21 '11 at 4:43
add comment

3 Answers

I haven't tried this out but could something like this help:

SELECT candidate_point
FROM my_function(some, params)
WHERE
    ST_Distance(candidate_point, 'SRID=4326;POINT(1 2)'::geography) <= 60000 &&
    ST_Distance(candidate_point, 'SRID=4326;POINT(3 4)'::geography) <= 60000 &&
    ST_Distance(candidate_point, 'SRID=4326;POINT(1 2)'::geography) +
    ST_Distance(candidate_point, 'SRID=4326;POINT(3 4)'::geography) <= 60000

Alternatively if you could calculate the bounding box of the elipse before hand and use that to filter the points before you do the ST_Distance calls I would expect a speed up.

share|improve this answer
    
I tried the extra ST_Distance checks and they do seem to speed it up, though only by about 10%. Could you suggest how to calculate the bounding box for the ellipse? –  Reality Exists Dec 13 '11 at 11:03
    
2  
The following is the simplest formula I can work out. Let the total distance be r and the foci be at (x0,y0) and (x1,y1) with y1>=y0. The vector between them is (x,y)=(x1-x0,y1-y0). Compute eta=(Sqrt(r^2-x^2)-y)/2. The y-limits of the ellipse's bounding box are y0-eta and y1+eta. To find the x-limits, interchange all coordinates and repeat these calculations. –  whuber Dec 13 '11 at 21:52
    
Just to clarify when you say "interchange all coordinates" that's not for the vector (x, y), just eta := (sqrt(r^2 - y^2) - x) / 2 and x-limits are (x0 - eta, x1 + eta) ? –  Reality Exists Dec 15 '11 at 12:01
    
Hmm, never mind - I just tried it with a hardcoded bounding box (smaller than the real one would be) and it made the query MUCH slower, even though it returned fewer rows. –  Reality Exists Dec 15 '11 at 12:13
add comment

If your data is in an equidistant area projection, you could do this:

SELECT gid,the_geog FROM mytable WHERE ST_DWithIn(the_geog,ST_MakeLine(point1,point2),corridor_width_m);

ST_DWithIn also works with geography types, but even if you cast to geometry it still will be slow because you don't have geometry indexes.

If you want speed, you have to use geometry types.

share|improve this answer
    
Thanks. I tried ST_DWithin, but yes, that was also too slow on geography. +1 anyway. –  Reality Exists Dec 21 '11 at 0:59
add comment

Try removing ::geography. That might help

share|improve this answer
2  
Welcome to SE-GIS and thankyou for following up on an old Question! We always have quite a backlog to get through and it is great to see not just the newest being reviewed. With this one perhaps you can expand on your answer to help him/her understand why this part of the code would/might not be necessary. –  PolyGeo Apr 27 '13 at 0:45
add comment

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.