ST_Distance values in kilometers

Question

I need to make a query to measure the distance from a given point to all points in a table and return the five closest to that point. The problem is I can't convert the distance to kilometers.

Here is the query:

SELECT gid,name , ST_Distance(ST_Transform(g1.geom,2163),ST_Transform(ST_GeomFromText('POINT(37.791458999999996 26.698004299999997)',2100),2163)) as km
    FROM samos As g1   
    WHERE ST_DWithin(ST_Transform(g1.geom,2163),ST_Transform(ST_GeomFromText('POINT(37.791458999999996 26.698004299999997)',2100),2163), 50)   
    ORDER BY ST_Distance(ST_Transform(g1.geom,2163),ST_Transform(ST_GeomFromText('POINT(37.791458999999996 26.698004299999997)',2100),2163)) 
    LIMIT 5;

The coords is from Greece the table is in 2100 Greece WGS geom(Point,2100) Version is 9.3 of postgresql the out put of cords in table samos coordinates

Answer 1

There are a number of issues with your SQL statement, so let's take them in order:

1. The ST_GeomFromText geometry constructor accepts coordinates in X,Y order, but you've specified them in Latitude,Longitude order, which is Y,X (37.8E 26.7N is in Saudi Arabia)

2. The ST_GeomFromText constructor requires SRID of the provided coordinates, which, for WGS1984 decimal degrees, is 4326

3. You've specified your decimal degrees coordinates to fifteen decimal places. Using a rough conversion factor of 111,120 meters per degree (based on 60 nautical miles), you're asserting precision to 1.11e-10 meters (which is 1.11 angstroms, less than the distance of the carbon-carbon covalent bond). Even if you lop off nine of those digits, you'll still be within a hand-span of the specified location.

4. When specifying a projection with ST_Transform you must be careful to use an appropriate SRID for the geographic area being evaluated. You've chosen a conic projection over the United States instead of Greece, which will, at best, produce random results. Instead, use the appropriate SRID, specified elsewhere in the query (ST_Transform(...,2100))

5. The ST_Distance function returns values in the projection in which the operation was performed, which for SRID 2100 is meters. Converting to kilometers only requires division by 1000 (ST_Distance(...) / 1000 as Km)

6. The ST_DWithin function also operates in the projection units, so instead of limiting to 50km, you've limited to 50 meters (ST_DWithin(...,...,50000))

So, putting it all together, you get:

SELECT  gid,
        name, 
        ST_Distance(
            g1.geom,
            ST_Transform(
                ST_GeomFromText(
                    'POINT(26.6980043 37.791459)',
                    4326),
                2100)) / 1000.0 as km
FROM    samos g1   
WHERE   ST_DWithin(
            g1.geom,
            ST_Transform(
                ST_GeomFromText(
                    'POINT(26.6980043 37.791459)',
                    4326),
                2100),
            50000)   
ORDER BY ST_Distance(
            g1.geom,
            ST_Transform(
                ST_GeomFromText(
                    'POINT(26.6980043 37.791459)',
                    4326),
                2100))
LIMIT   5;

(I didn't build a sample dataset to test this, so it needs validation).

[NOTE: Edited to remove unneeded ST_Transform from g1.geom (2100) to 2100]

Once you get this working, there are plenty of SQL optimizations left, but that can be a different question (in a different SE group).

Answer 2

You're using SRID 2163, which is in meters. To convert meters to kilometers, divide by 1000:

...ST_Distance(ST_Transform(g1.geom,2163),ST_Transform(ST_GeomFromText('POINT(37.791458999999996 26.698004299999997)',2100),2163))/1000 ...

Answer 3

I could imagine your coordinates are epsg 4326-coordinates( 37.791458999999996 26.698004299999997 = Greece, near karlovasi /samos?).if so you should declare them as 4326 (and if all your data has 4326-coordinates, you could use st_distance_spheroid:

 SELECT ST_Distance_Spheroid( geom1, geom2),
 'SPHEROID["WGS 84",6378137,298.257223563]')