5

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

1
  • Please edit the question to include the versions of software (PostgreSQL & PostGIS), the coordinate reference SRID of the "samos" table, and confirm the location by specifying the country.
    – Vince
    Feb 6, 2015 at 20:30

3 Answers 3

16

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).

5
  • i change the value from 50000 to 5000000 and find me same place in distance give me 4250.2451 50000 is 5 meters ? and the value i give(5000000) is 500 meters ? the result wich give me is 4.250 kilometers ? tis is i dont understand it the all change everything else i understand
    – vagelis
    Feb 7, 2015 at 15:39
  • You need to provide the coordinates in your table (as an edit to the question). If they're not right, then even 50,000km (more than twice the circumference of the Earth) wouldn't matter.
    – Vince
    Feb 7, 2015 at 16:02
  • The SELECT geom FROM samos LIMIT 5 give Point,2100 @Vince
    – vagelis
    Feb 7, 2015 at 16:12
  • Not used to PostGIS. How about SELECT ST_AsText(geom) ... Place the output in the question!!
    – Vince
    Feb 7, 2015 at 17:18
  • The query is perfect i have make wrong insert with coords you have wryte , thank you very much thank you
    – vagelis
    Feb 7, 2015 at 18:02
3

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 ...

4
  • The point in the query is in Saudi Arabia (26.7N 37.8E) or Greece (37.8N, 26.7N), which isn't going to work with a US projection (neither would 26.7N 37.8W or 26.7S 37.8E, or any of the other permutations), so it's probably too early to be doing meter/kilometer conversion.
    – Vince
    Feb 6, 2015 at 20:36
  • To expand a little, 2163 is for a conic projection centered over the US.
    – mkennedy
    Feb 6, 2015 at 20:53
  • The coords is from Greece the table is in 2100 Greece WGS and the point given in 4326 and make a point in 2100 WGS with 'ST_GeomFromText('POINT(37.791458999999996 26.698004299999997)',2100)' .
    – vagelis
    Feb 6, 2015 at 21:16
  • Fly with @Vince -- he's got a mich better answer. Mine's too short-sighted.
    – andytilia
    Feb 7, 2015 at 1:35
2

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]')

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.