# Problem with result of query in PostGIS

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.

The following image shows the results of 12 records: • Can you show some representative data (e.g. `SELECT AsEWKT(geom) FROM Buildings LIMIT 5`) Mar 1, 2014 at 23:55
• @BradHards I've updated the question with an image of the output. Any clue what the problem could be? Mar 2, 2014 at 9:35
• by the way, I tried AsEWKT(geom), it seems the function is unknown to PostGIS Mar 2, 2014 at 10:00

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.) • The problem still remains. Gives back all the records. Any other suggestion? Mar 1, 2014 at 23:13
• Sorin: the data is not in SRID 26918. Check the binary blobs in the updated screenshot. Mar 2, 2014 at 9:47
• ST_DWithin cannot mix SRID. So your solution works if the_geom is in 31700, but not if its in 4326. Mar 2, 2014 at 10:09
• @SorinCălinică, Thank you for step by step instruction. I understand all, I tried the same, but still have all records of my table as return! :( I am sure I pass a valid point for the center of circle. Do you have any other suggestion how to fix this? Mar 2, 2014 at 11:04
• @Sorina, Thank you for the update. I am still confused. I get my point (center of circle) from the user click in Openlayers map. I click on Germany for example and it gives me this point: POINT(1116858.6062789 7088526.4511342), do you know in which SRID is this point? I guess I have to know this in order to transform it using ST_Transform function.... Mar 2, 2014 at 12:42

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.

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.

• Right, the table SRID is 4326, and therefor I changed the value SRID in query to 4326. Still have the same problem. the new query is like below: (any idea where I'm doing wrong?) Mar 2, 2014 at 9:51
• SELECT * FROM Buildings WHERE ST_DWithin(geom, ST_Buffer(ST_GeomFromText('POINT(0 0)', 4326), 2000, 'quad_segs=8'),0); Mar 2, 2014 at 9:51
• Read the documentation again. "The distance is specified in units defined by the spatial reference system of the geometries." 4326 is in degrees. So you just asked for 2000 degrees, which isn't what you want. Do it in UTM, as I showed. Mar 2, 2014 at 9:52
• If I give a wrong center point which does not fall into the dataset, shouldn't it give me a 0(zero) result as query instead of all data in table? Mar 2, 2014 at 9:53
• Don't rush. Just read the documentation. Mar 2, 2014 at 9:53