How to Find the Nearest Node from a Given Coordinate

Question

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.

Thanks.

Answer 1

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