Problem with result of query in PostGIS

Question

I run the following query and irrelevant of the point coordinates and radius I always recieve all the records of the table I'm querying. Can anyone point out what the problem could be with this query?

SELECT * FROM Buildings WHERE ST_DWithin(geom, ST_Buffer(ST_GeomFromText('POINT(0 0)', 26918), 2000, 'quad_segs=8'),0);

Just to be clear, my aim is to select all buildings that fall into a selected circular buffer. Here the (0,0) are the coordinates of the center of circle and 2000 is the radius. No matter what value I put for these, the query returns all the records of table.

Please help!

The following image shows the results of 12 records:

Answer 1

To select all buildings that fall into a selected circular buffer with a 2000 radius you need this command:

SELECT * FROM Buildings WHERE ST_DWithin(geom, (SELECT ST_GeomFromText('POINT(0 0)', 26918)), 2000);

See this, for more examples.

---

EDIT1:

I've gave you already the right answer!

To prove you I'll show the tests I've done before answering to you, on my test data (my SRID is 31700):

If you want to see the selected points in QGIS then you need to follow the images bellow:

So, I think that your center point coordinates are not [0, 0] and if you'll follow carefully my demonstration you'll obtain the proper results!

---

EDIT2:

My tests showed me that it is possible a user to keep its data in a PostGIS table with the 4326 SRID, and to obtain a proper result, like above, using its own SRID (e.g. 26918).

In this case one my use this command:

SELECT * FROM Buildings WHERE ST_DWithin(ST_Transform(geom, 26918), (SELECT ST_GeomFromText('POINT(0 0)', 26918)), 2000);

Yet, in this case, I've noticed that the selected points are still in the 4326 SRID. Therefore, if you create a table based on this query, eventually, you need to update the table SRID to your own.

Here's what I've done (using my test data) prior to see the selected point in QGIS:

But, the newly created table has the 4326 SRID:

Therefore, we need to change it to our own:

(I've verified my solution, considering the buildings are points, polygons and multipolygons, and didn't noticed any other problems than those already presented here.)

Answer 2

This is a total shot in the dark. Check the coordinate system for your building table, if it's not in UTM 18N it could be causing the query to return everything.

Answer 3

Based on the output you've shown (which would have been a lot easier to read if you'd converted it to EWKT first), your data is referenced to EPSG 4326 (that is what the E610 part of the geometry blob is - 4326 in hexadecimal). EPSG 4326 is WGS-84 long/lat.

The documentation for ST_DWithin says:

For Geometries: The distance is specified in units defined by the spatial reference system of the geometries. For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID.

SRID 4326 is not the same as the SRID you are trying to use - 26918.

So you need to transform everything in to a common SRID. dblanchett had it right!

The transform function is ST_Transform. There are a couple of options here - you can transform the whole table to 26918 (creating a new table), or you can do the transform as part of the query.

Here is an example of the latter:

SELECT * FROM Buildings WHERE ST_DWithin(ST_Transform(geom, 26918), ST_Buffer(ST_GeomFromText('POINT(0 0)', 26918), 2000, 'quad_segs=8'),0)

(Obviously you'll need to fix the centre point to something that is reasonable for your data).

Hint: ST_MakePoint is usually better than calling ST_GeomFromText on a static string point set.