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.

ok so this is query i'm making which returns no result:

SELECT 
    building.gid
FROM 
    building
WHERE
    ST_Dwithin(building.geom, ST_GeomFromText('POINT(long lat)',4326), 400)

If I run this in QGIS, I get a syntax error, but if I run this on pgAdmin, i get no error, but simply 0 rows....

my long and lat are in degrees.

what could possibly go wrong?

share|improve this question
1  
What is the spatial reference of the building btw? Is it 4326, too? –  R.K. Dec 3 '12 at 6:57
    
SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 4326)); this returns 4326, is this what you mean? SRID? –  Kourosh Dec 3 '12 at 7:05
    
try select ST_SRID(building.geom) from building limit 1 –  iant Dec 3 '12 at 7:24
    
i get 0 in return. –  Kourosh Dec 3 '12 at 7:49

1 Answer 1

up vote 3 down vote accepted

I'm not sure the QGIS portion of the problem, but I can see a couple of problems right away.

First, if you are using geometry types (as opposed to geography types) then the unit of measurement for the distance is the unit of the coordinate system (i.e. degrees). Remember, geometry types work on planar geometries, not spheroids. In short, you're asking for points within 400 degrees, not 400 feet or meters. Obviously measuring distance in degrees makes no sense.

Second, in your comment you mention that you get an SRID of 0, which means that the SRID is not defined for that geometry. That's a problem – you need to know what the SRID is (you can't guess - you have to know). You may need to reload the data, specifying the SRID, or you may need to update the geometry column to specify the SRID. (If you need help with that, please open another question.)

I'm willing to bet that your building geometry is not stored in Lat/Long. To check, try SELECT ST_AsText(geom) FROM buildings LIMIT 1 – if it's showing values that aren't reasonable Lat/Long values, then you'll really need to fix that before going any further.

OK, so, let's assume that you've figured out what the SRID is, and that is really is Lat/Long (WGS84), and that you've fixed your the geometry definition in your table, then you have two options for calculating ST_DWithin():

  1. you can convert the point to a geography and do the calculation on a spheroid (in which case, you use meters as the measure of distance; or
  2. you can convert to a projected coordinate system and do the calculation on the plane.

The first would look like this:

SELECT 
    building.gid
FROM 
    building
WHERE
    ST_Dwithin(building.geom::geography, ST_GeogFromText('POINT(long lat)'), 400)

The second like this:

SELECT 
  building.gid
FROM 
  building
WHERE
  ST_Dwithin(ST_Transform(building.geom, XXXX),
             ST_Transform(ST_GeomFromText('POINT(long lat)',4326),XXXX),
             400)

You have to choose an appropriate SRID for XXXX, and make sure the units it's using are in the units you're expecting (i.e. meters or ft).

Generally speaking, the former is going to be slower than the latter. If you'll be doing a lot of these calculations, you probably want to convert the geometry column in your buildings table to be stored using your chosen XXXX SRID, which will reduce the query time considerably if your table is large.

share|improve this answer
    
Thank you Darrell, very useful, ok now I know the Correct SRID which is 4258, but I don't know why ST_Transform does not work here, using this line :SELECT ST_AsText(ST_Transform(st_setsrid(ST_makepoint(28.859093333,41.0254498000),4326)‌​,4258)) I still get the same degree coordinations....it's as if it's not converted at all! –  Kourosh Dec 4 '12 at 18:34
1  
It is converted – you're using a different Lat/Long (i.e. geographic) reference datum - 4258 is the European Terrestrial Reference System 1999, while 4326 is the World Geodetic System 1984. You're still in Lat/Long. I would update the geometry column so the correct SRID is set on the geometry, then I would use of the two methods I mentioned before. I would start by trying the appropriate UTM zone for your transformation before doing the comparison – that's probably zone 30 or 31 if your data is in Western Europe. –  Darrell Fuhriman Dec 4 '12 at 18:52

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.