5

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 (like a great circle line with a buffer around it) 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.

| improve this question | |
  • 1
    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. – EM0 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. – EM0 Dec 21 '11 at 4:43
  • Try removing ::geography. That might help – user17612 Apr 27 '13 at 0:00
1

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.

| 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? – EM0 Dec 13 '11 at 11:03
  • try iquilezles.org/www/articles/ellipses/ellipses.htm – Ian Turton♦ Dec 13 '11 at 11:31
  • 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) ? – EM0 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. – EM0 Dec 15 '11 at 12:13
1

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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