Distance between 2 POINTs in Postgis in srid 4326 in metres

Question

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.

Perhaps of some help: [ST_DWithin takes parameter as degree , not meters , why?](http://stackoverflow.com/questions/8444753/st-dwithin-takes-parameter-as-degree-not-meters-why) — radek, Dec 11 '11 at 16:38
obligatory question: What PostGIS version? What PostgreSQL version? — filiprem, Dec 11 '11 at 17:31

score 33 · Answer 1 · edited Nov 07 '13 at 12:38

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  |

score 9 · Accepted Answer · answered Dec 12 '11 at 12:29

9

Since writing this, I have discovered the "geographic" as opposed to the "geometry" type in PostGIS, which might do exactly what I want.

answered Dec 12 '11 at 12:29

Amandasaurus

58,203
71
188
248

1

Geography should work. you didn't mention the data type in the question. The problem with Geom is it's ultimately a 2D object. Geography is a 4D object that includes an altitude and curvature of the earth parameter that can be traslated into meters...without these two parameters, you really can't estimate 'meters' as different lat/lons mean different distances pending where you are on the planet. Might seem trivial when you are writing, but please include a table create statement so we know what column data types you are referring to – Twelfth Dec 15 '11 at 23:11

score 2 · Answer 3 · answered Dec 26 '18 at 17:48

2

Old question but you just have to cast like this: ST_Distance(p.geom::geography, u.geom::geography) where p and u are alias for two tables

answered Dec 26 '18 at 17:48

jr from Yaoundé

646
6
9

4

Not recommended. As you are converting the elements from the tables you need to perform a sequential scan over the complete tables and can't use any indices. So the performance will suffer a lot. – Jakube Jan 21 '22 at 13:18

Distance between 2 POINTs in Postgis in srid 4326 in metres

3 Answers3

Linked