Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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: enter image description here

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

3 Answers 3

up vote 3 down vote accepted

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):

enter image description here

enter image description here

enter image description here

enter image description here

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

enter image description here

enter image description here

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).

enter image description here

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:

enter image description here

But, the newly created table has the 4326 SRID:

enter image description here

Therefore, we need to change it to our own:

enter image description here

(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.)

enter image description here

share|improve this answer
    
The problem still remains. Gives back all the records. Any other suggestion? –  Catlover Mar 1 at 23:13
    
Sorin: the data is not in SRID 26918. Check the binary blobs in the updated screenshot. –  BradHards Mar 2 at 9:47
    
ST_DWithin cannot mix SRID. So your solution works if the_geom is in 31700, but not if its in 4326. –  BradHards Mar 2 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? –  Catlover Mar 2 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.... –  Catlover Mar 2 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.

share|improve this answer

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.

share|improve this answer
    
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?) –  Catlover Mar 2 at 9:51
    
SELECT * FROM Buildings WHERE ST_DWithin(geom, ST_Buffer(ST_GeomFromText('POINT(0 0)', 4326), 2000, 'quad_segs=8'),0); –  Catlover Mar 2 at 9:51
1  
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. –  BradHards Mar 2 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? –  Catlover Mar 2 at 9:53
    
Don't rush. Just read the documentation. –  BradHards Mar 2 at 9:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.