1

I have a PostGIS table having a column "geom" having a type geometry(MultiPolygon) and the projection epsg:4326 (degree decimal), I want to query all objects within 1000m and I use this query:

SELECT ST_AsGeoJSON(geom) FROM table 
WHERE ST_DWithin(geom, 'POINT(long lat)', 1000.0);

but I always ended up querying all objects.

  • Do you want to know the objects that are in the 1000m radius of your point? – Stefan Dec 9 '13 at 14:40
  • no, thats why i am querying it. :( – noobprog Dec 9 '13 at 14:42
  • but i know that my table having 200000 rows is scattered more than 500km – noobprog Dec 9 '13 at 14:49
4

Cast your geometries to GEOGRAPHY type:

SELECT ST_AsGeoJSON(geom) FROM table WHERE ST_DWithin(geom::geography, ST_GeographyFromText('POINT(long lat)'), 1000.0);

otherwise, PostGIS will treat the value "1000" as 1000 decimal degrees, which effectively means "return all objects".

|improve this answer|||||
0

For a multipolygon column, this works for me:

SELECT
    ST_AsGeoJSON(geom) 
FROM table 
   WHERE ST_Intersects(ST_Buffer(geom, 1000), 'SRID=4326;POINT(long lat)'::geometry);

EDIT

Michal is right with the buffer. I tried it without using the geography type and this seems to work as well.

SELECT
    ST_AsGeoJSON(geom)
FROM table 
    WHERE ST_DWithin(geom, 'SRID=4326;POINT(long lat)'::geometry, 1000);
|improve this answer|||||
  • but my point is also in degree decimal :( – noobprog Dec 9 '13 at 14:43
  • I changed my query to a more common one. For lat and long insert your data in degree decimal. I tried it with data in my database and they were in gauss krueger coordinates (german stuff). – Stefan Dec 9 '13 at 14:45
  • 2
    -1. ST_Buffer adds too much overhead, this function should be used only if you intend to visualize the search radius. – Michal Mackiewicz Dec 9 '13 at 15:47
  • You're right. Having a lot of data this is a slow workaround. Whatever, I will leave my first answer here. Is it really necessary to cast to the geography type? I think the query I've additionally added to my post does the job as well. – Stefan Dec 9 '13 at 18:40

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.