1

assuming I use the following query to match the locations to a poi within 10km distance:

SELECT
poi.poi_id,
h.loc_id,
(ST_Distance(poi.geom,h.geom)*111.111) as distanceinkm
FROM iut_poi_transl AS poi, hrs_loc as h
WHERE poi.poigr_id != 30 AND ST_DWithin(poi.geom,h.geom,0.09)

Data is SRID 4326.

Does it use the geometry or the geography type?

If uses geometry how do I have to change the query to get use geography type?

2 Answers 2

4

As per documentation of function it can use both: http://postgis.net/docs/ST_DWithin.html

boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);

boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters);

boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters, boolean use_spheroid);

Watch out for this:

For geography units are in meters and measurement is defaulted to use_spheroid=true (measure around WGS 84 spheroid), for faster check, use_spheroid=false to measure along sphere.

But for 10km buffer radius it shouldn't be a big error.

2

as said before it uses the type it gets. so what you have to do is to feed it with geography. If your data is stored as geometry you can cast to geography on the fly

poi.geom::geography

SELECT
poi.poi_id,h.loc_id,
(ST_Distance(poi.geom::geography,h.geom::geography)/1000) as distanceinkm
FROM iut_poi_transl AS poi, hrs_loc as h
WHERE poi.poigr_id != 30 AND ST_DWithin(poi.geom::geography,h.geom::geography,10000)
2
  • which makes the query very slow, right?
    – hoge6b01
    Jun 20, 2013 at 13:24
  • It is not casting to geography that makes it slow. But doing calculations on the sphere instead of just euclidean is far more expensive. But I think PostGIS 2.1 will be a lot faster here. Paul has put in some smart tree structures for making things happen faster. Look at postgis.net at the release notes for postgis 2.1 beta 2 (I think it was released today) Jun 20, 2013 at 14:17

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.