# PostGIS: Convert meters to arbitrary spatial reference units?

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?

SELECT * FROM table
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
• @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

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

SELECT * FROM table
WHERE geom && proj_disc
AND st_within(geom, proj_disc)
ORDER BY st_distance(geography(geom), geography(proj_point)) DESC
LIMIT 10;

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.

-update-

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

SELECT * FROM table
WHERE ST_DWithin(geom::geography, proj_point, distance)
ORDER BY ST_Distance(geom::geography, proj_point) DESC
LIMIT 10

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

• This looks promising! Let me implement that and get back to you... Apr 16, 2013 at 13:54
• 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
• 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
• 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
• 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.