2

Problem

I'm developing an app, that needs to calculate distances between my current position and different places in earth. I use PostGIS.

As my first solution I've stored all the positions as Geography, and I calculate distances easily. Everything is correct and working. However, I need to do this queries lots of times, so I want to speed up this calculation.

Premises

I have two premises:

  • I store places around the world
  • I only need to check distances between my current location and places that are near (100km distance max).
  • some other variables
    • We may have about 100-1000 places that match the distance
    • places do not move

Proposed solution

My idea is to use geometry instead of Geography.

  • I was thinking on using UTM.
  • For each place I'll store its position stored in its UTM zone projection
    • Each place that is 100km near the border of the zone, I also calculate its position in the neighbor zone and store it (This may put some error in the distance, but I don't think will be much).
  • When I want to look for distances I'll only check for the points that have the same zone, and calculate the distance.

As an example, I'll have a table like

| id    | place_id  | point     | utm_zone  |
|----   |---------- |-------    |---------- |
| 1     | 1         | 1,1       | 24M       |
| 2     | 2         | 0,0       | 25M       |
| 3     | 2         | -1,-1     | 24M       |

Question

  • Is this idea correct or I'm missing something?
  • Is there any other better way to do this?
Improve this question
6
  • 1
    You've left out a number of variables, primarily the number of points involved, the time and error associated with the much more complex storage model, the frequency of doing distance computations, and the frequency the the locations move. Please edit the question to include this information, and to ask exactly one question. The result is likely to still be opinion-based.
    – Vince
    Commented Nov 14, 2016 at 12:13
  • 1
    I've added info to the question. I know that its not an easy yes/no question, I just wanted to listen of the solutions from people with the same problem, and if as you say, if the complexity generated will really increase the results.
    – Mateu
    Commented Nov 14, 2016 at 12:34
  • 1
    You can use ST_DWithin to limit search distances and this works with the geography datatype and uses a spatial index. Converting to UTM would seem to add extra complication.
    – John Powell
    Commented Nov 14, 2016 at 12:49
  • 1
    Once you go to your UTM architecture, any query that has to span zones will also need to deal w/ deduping your in-two-zones points. Unless there's a serious reason you cannot use ST_DWithin(geog, geog, radius) you should use that. If you need nearest-neighbor lists on geography, look at postgis 2.3
    – Paul Ramsey
    Commented Nov 14, 2016 at 17:08
  • If places do not move, there is no reason to make the problem harder. You'll have to bit the bullet to pay the neighbor distance calculation cost, but only once, to populate node-distance table with an index on fromNode,ToNode. From that point on results are instantaneous.
    – Vince
    Commented Nov 15, 2016 at 0:07

1 Answer 1

Reset to default
0

As John Barca has said, ST_Dwithin is what you need because it filters out anything outside the distance you specify and it makes use of the index which is a huge performance gain. It accepts geography or geometry as input.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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