Distance between 2 POINTs in Postgis in srid 4326 in metres
This is probably a simple question, but I'm not very good at PostGIS and don't fully grok all of this.
Basically I have a table (nodes) with a POINT column (point). I have created an index on this column
create index nodes__points on nodes using gist (point)
The column was created with
select addgeometrycolumn('nodes', 'points', 'POINT', 4326, 2)
I am using srid 4326 because I'm adding data that's in the form (latitude, longitude). (i.e. the co-ordinate system where the position of Dublin, Ireland is lat=53.353 lon=-6.264 (which I've added with GeomFromText('POINT(-6.264 53.535)'))).
For each point, I want to find all points that are roughly within a 1km box centred on that point (so selcet a.id, count(*) from nodes as a, nodes as b where SOME_DISTANCE_FUNCTION_HERE(a.point, b.point, 1000) group by a.id;. It doesn't have to be exact, just a rough hueristic figure. a 1km bbox is fine, a 1km circle is fine. It doesn't have to be exactly 1km, just that order of magnitude.
The ST_Distance/ST_DWithin/etc. all use the units of the SRID, which for 4326/WGS64 is degrees (so 1 = 1 degree of lattitude/longitude). But I want to use metres.
I tried ST_distance_sphere and st_dwithin which can use metres, but if I do that, the explain shows that the index isn't being used.
How can I get roughly what I want, and use the geographical index?
UPDATE: This is on PostgreSQL 9.1 and PostGIS 2.0 svn build.
Answer
You could use ST_Transform to use meters, also remeber that not all functions are available with geography types but if you really need speed use ST_DWithin, is the fastest way. Here's an aproximation of conversions between degrees and meters:
| places | degrees | distance |
| ------ | ---------- | -------- |
| 0 | 1.0 | 111 km |
| 1 | 0.1 | 11.1 km |
| 2 | 0.01 | 1.11 km |
| 3 | 0.001 | 111 m |
| 4 | 0.0001 | 11.1 m |
| 5 | 0.00001 | 1.11 m |
| 6 | 0.000001 | 0.111 m |
| 7 | 0.0000001 | 1.11 cm |
| 8 | 0.00000001 | 1.11 mm |