I have already developed a table manually. It contains the coordinates and the distance of all the nodes, there is no geom field or any field related to postgis. But I want to find out the nearest nodes to a given coordinates. Is this possible, or is there any other way to find out?

Kindly help me so that I can proceed further.



i assume that coordinates are in WGS84 and are in longitude and latitude columns

ALTER TABLE aaa ADD COLUM the_geom geometry UPDATE table aaa SET the_geom = ST_GeomFromText('POINT(' || longitude || ' ' || latitude || ' ' || altitude || ')', 4326)

(gist index is optional for geometry colum but recommended)

Where altitude is optional or can be also M-value

After that you do

SELECT * FROM aaa ORDER BY ST_Distance(ST_GeomFromText('POINT(1 2)', 4326), the_geom) ASC LIMIT 1

WHERE clause to limit search like: ST_Distance(ST_GeomFromText('POINT(1 2)', 4326), the_geom) < 1

NOTE: PostGIS documentation says about ST_distance returns the 2-dimensional minimum cartesian distance between two geometries in projected units, in this case decimal degrees (that WGS84, srid 4326)

You can also use geography type if all your data is in WGS84 and you do stuff at global scale. if data is in another system check spatial_ref_sys table for supported srid's. You can use ST_Transform() to change between coordiante systems.

PostGIS 2.0 Manual

| improve this answer | |
  • Hi Dear,Thanks for the reply i have gone through the steps..inserted the geom column after that i m facing problem.If i have a coordinate which is not present in the table and i have to find the nearest node to it from the tables data(that coordinate should be in the table) – Soumyajeet Sarangi Feb 8 '13 at 9:17
  • 1
    Replace 'POINT(1 2)' with searched coordinate note decimal uses . and " " (space) between coordinates – simplexio Feb 8 '13 at 13:21
  • Hi Dear,Thanks for ur valuable reply..Now i could able to find it. – Soumyajeet Sarangi Feb 11 '13 at 4:49
  • ST_Distance(ST_GeomFromText('POINT(1 2)', 4326), the_geom) ASC LIMIT 1 , see ST_GeomFromText( insert your point here ) – simplexio Feb 11 '13 at 7:47
  • Thnks boss.Now i could able to find out the proper result. – Soumyajeet Sarangi Feb 13 '13 at 5:03

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.