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.

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!

share|improve this question
    
Can we get performance differences between suggested answers ? It would be cool to know real diffrence –  simplexio Apr 18 '13 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. –  Xavier Holt Apr 19 '13 at 15:08

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
1  
This looks promising! Let me implement that and get back to you... –  Xavier Holt Apr 16 '13 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. –  René Wolferink Apr 16 '13 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! –  Xavier Holt Apr 16 '13 at 14:13
1  
Since google uses metres as units, you should indeed be alright buffering the point in that projection and then transforming the disc into the projection the database is using. Happy to help! :) –  René Wolferink Apr 16 '13 at 14:20
1  
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. –  Paul Ramsey Apr 16 '13 at 17:04

My answer is that i dont know howto 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 allways measured in used 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.

you 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 
LIMIT 10;

Code Translates everything to 3067 (tm35fin) and returns everything from a table which distance to b.geom in b table with id 1. Thisassuem s that you have correct srid in every geom. If not you need to set it with SetSRID (st_?). If you want answer in degree decimal then use 4326 ( im not 100% sure about, but i recall it uses degree units)

share|improve this answer
    
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? –  Xavier Holt Apr 16 '13 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. –  simplexio Apr 16 '13 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? –  Xavier Holt Apr 16 '13 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 '13 at 13:58

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.