Getting points within a range from a postgres database?

Question

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?

Answer 1

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.