I'm writing a query that needs to be able to work with data of any SRID. The basic problem is very simple - I get two pieces of information:

  1. A point (SRID = 900913)
  2. A distance (unit = meters)

I [would like to] transform these into the native SRID, and then run a query to find features within the given distance of the point:

proj_point = ST_Transform(point, (SELECT ST_SRID(geom) FROM table LIMIT 1));
proj_dist  = Something...  But what?

    WHERE ST_Distance(geom, proj_point) < proj_dist
    ORDER BY geom <-> proj_point
    LIMIT 10;

My problem is the conversion of the units. I can look up the SRID of the native data and transform the point no problem. But how do I do the same with the distance?

I've seen answers to related questions, but they have all involved reprojecting the queried data. Holy overkill! This should be doable with a single lookup and multiplication...

Pointers, anyone? Thanks very much in advance!

  • Can we get performance differences between suggested answers ? It would be cool to know real diffrence Apr 18, 2013 at 12:14
  • 1
    @simplexio - I don't have hard numbers right now, and it seems much discussion has happened while I wasn't paying attention... But I'm curious, too - I'll update the question once I've run some tests. Apr 19, 2013 at 15:08

2 Answers 2


Rather than calling st_distance on all your objects, which most likely alread prevents your index from being used, why not make your point into a "disc" and then find all enclosed features.

Geometries are usually very bad at handling measures like meters. However, geographies are good with them. So what we can do is transform your point to a geography, buffer it a few metres and then make it a geometry again. Then use that polygon to do a quick indexed search and finally do an accurate search to omit false positives.

As you already successfully transformed your point to the correct srid, I will use that.

proj_disc = geometry(st_buffer(geography(proj_point), distance))

WHERE geom && proj_disc
  AND st_within(geom, proj_disc)
ORDER BY st_distance(geography(geom), geography(proj_point)) DESC

Note that I'm ordering by the distance after translating the points to geographies, to get a distance in meters as not to get skewed results because of the used projection.

Also, I'm using st_within. This will work fine for points, though if you're using polygons or lines, you might want to check for intersection or something else, since this only shows results that are fully within the distance of the point.

The first part of the where clause is what will make it all be quick, since that will trigger the spatial index. The slower operations (st_within and st_distance) are then only done for the ones that are likely to match.


According to Paul Ramsey there is a simpler way of achieving the desired result, which should still make use of a spatial index:

WHERE ST_DWithin(geom::geography, proj_point, distance)
ORDER BY ST_Distance(geom::geography, proj_point) DESC

(You may have to cast your point to a geography as well, haven't tried.)

  • 1
    This looks promising! Let me implement that and get back to you... Apr 16, 2013 at 13:54
  • 1
    And one last side-note: you would ideally have wanted to use st_dwithin and be done with it, but that can't be done with your distance if the units of measurement of your srid aren't meters. This is why I changed them into geographies to allow the use of meters. Apr 16, 2013 at 14:09
  • 2
    Just tried it, and the && is most definitely still needed. I also tweaked your answer to not use geographies, but to buffer in SRID 900913, which the point is already in, and which uses meters. Anyhow, your method works beautifully. Thank you very much! Apr 16, 2013 at 14:13
  • 2
    This is bad on many levels: you can perform an exact distance test in meters in geography, so you don't need the buffer step; the buffer in geography actually flips everything back to an automatically chosen geometry SRID, so it's not doing what you think; buffering is really expensive and distance tests are not, so it's a poor performer. Just take the geometry into geography and perform the distance test there. Apr 16, 2013 at 17:04
  • 3
    If you use ST_DWithin(geom::geography, point, distance) you'll get an index-accelerated distance test in native geographic coordinates, no projections required, performance guaranteed. Apr 17, 2013 at 19:03

My answer is that I dont know how to transform distance but this one works.

SELECT ST_DISTANCE(ST_TRANSFORM(a.geom , 4326), ST_TRANSFORM(b.geom, 4326))     
FROM tablea AS a. tableb AS b 
WHERE a.id =1 AND b.id = 2 

It just converts geoms to wanted SRID and measures distance, which is always measured in using SRID. So if you use SRID 3067, then you use meters; if you use 4236 then you use degree (I think it uses degrees) as unit.

Your code changes to :

SELECT a.* FROM tablea as a , tableb as b
WHERE ST_Distance(ST_TransForm(a.geom, 3067), ST_Transform(b.geom, 3067) < 10 
AND b.id = 1 -- you compare all data from table a to this 
ORDER BY ST_Distance(ST_TransForm(a.geom, 3067), ST_Transform(b.geom, 3067) ASC 

Code Translates everything to 3067 (tm35fin) and returns everything from a table with distance to b.geom in b table with id 1. This assumes that you have the correct SRID in every geom. If not you need to set it with SetSRID (st_?). If you want the answer in degree decimal then use 4326 (I'm not 100% sure about that, but I recall it uses degree units).

  • I'm sure this works - and I've seen other answers using this method - but I'm specifically trying to avoid transforming my native data. Why perform hundreds of transformations (and probably prohibit use of my spatial index) when I could theoretically use a single multiplication? Apr 16, 2013 at 13:29
  • then you change that b.poin to match your table A srid? That ST_transform is "useless" if data is allready in 3067. Problem with this is that if measure very long distances or you handle data on area which is not defiend for used srid, you can cause measurement errors. Apr 16, 2013 at 13:40
  • I'm not sure I understand you... What are tables A and B? I only have a single table (of unknown, but determinable, SRID) and a point from outside the DB (of known SRID). Can you clarify? Apr 16, 2013 at 13:47
  • Converting degress to meters is not a 'simple multiplication'. Choose a srid that fits you and your problem and stick to it. problem solved.
    – nickves
    Apr 16, 2013 at 13:58

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.