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?

1

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)
  • which makes the query very slow, right? – hoge6b01 Jun 20 '13 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) – Nicklas Avén Jun 20 '13 at 14:17
2

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.

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.