1

I'm using this query in CartoDB to find the number of locations within a certain range of a set point:

SELECT COUNT(*) 
FROM location_fusion_table 
where ST_DWithin(location_fusion_table.the_geom, 
ST_GeomFromText('POINT(39.704017 -104.959488)', 4326), 500)

If I use the number 500 or higher, this returns a count that equals every location in my table (i.e., it counts every row). 200 returns about 2/3 of all of my locations in the table (it should be about 100 returned instead of 20,000).

Clearly, these numbers are wrong, but I'm not sure what the issue is.

  • 1
    If you're using GEOMETRY (not GEOGRAPHY), the third parameter is a distance in the coordinate reference of the table (which apparently is degrees). 500 linear degrees goes a long way, even from a location at 105 degrees south (coordinates are in X,Y order, which is lon,lat) – Vince Mar 25 '15 at 15:10
  • @Vince Thanks. That makes sense, I'd like to search by meters, not radians. What do I need to change with this? – jonmrich Mar 25 '15 at 15:16
  • You need to reload your tables in X,Y order, either as decimal degrees with a geography type, or in meters in an equal area projection as geometry. Editing the question to give more details about the PostgreSQL/PostGIS release (and removing cartodb from the tags) and how the table has been constructed would allow for more details. – Vince Mar 25 '15 at 15:22
  • PostgreSQL/PostGIS are completely new to me. I've only encountered it in working with CartoDB, which is where I'm using these queries, so that's why I included it in the tags. I'm not sure what other details to provide. Seems like I need to replace this part, but not sure with what: ST_GeomFromText('POINT(39.704017 -104.959488)', 4326) – jonmrich Mar 25 '15 at 15:24
1

The third argument of ST_DWithin depends on the type of the first ones: geometry or geography:

http://postgis.net/docs/ST_DWithin.html

boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);

boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters);

boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters, boolean use_spheroid);

As you're using geometries, your third parameter is not stated in meters, to the distance "500" in terms of your projection may take the whole world. As you're using 4326, your "500" parameter refers to Cartesian degrees (thanks Vince).

If you're interested in applying the distance in meters you need to cast the parameters to geography.

SELECT *
FROM location_fusion_table 
where ST_DWithin(location_fusion_table.the_geom::geography, 
ST_GeomFromText('POINT(39.704017 -104.959488)', 4326)::geography, 50000)
  • I want to do this in meters, but not sure how to cast the parameters correctly. – jonmrich Mar 25 '15 at 15:17
  • 1
    It's not radians (in which case 500 would wrap the globe 80 times), but Cartesian degrees which still covers the globe. – Vince Mar 25 '15 at 15:17
  • How do I change this query to use meters instead of degrees or radians? – jonmrich Mar 25 '15 at 15:19
  • A geography cast is likely to fail with -104.96 latitude. – Vince Mar 25 '15 at 15:37
0

Finally got it working with this:

select count(*) from location_fusion_table where
st_distance(the_geom,st_GeomFromText('POINT(-104.959488 39.704017)', 4326), true) < 1000

with 1000 being the distance in meters.

  • This query cannot use a spatial index (since it forces a full table scan). Performance is therefore dependent on the number of features in the table. – Vince Mar 25 '15 at 16:54
  • @Vince Good feedback. So knowing this is the query I need, how would you change it to use a spatial index? Like I said, I'm completely new to PostGIS, so I'm not sure where to start. – jonmrich Mar 25 '15 at 21:41
  • That's what ST_DWithin is for. A little due diligence goes a long way. – Vince Mar 25 '15 at 23:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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