[postgis-users] Query for nearest linestrings to a point, for latitude/longitude

[Ovidiu Gheorghies]

Hello all,

I'm writing to ask if at this moment there is a better approach to what I am trying to achieve in PostGIS.

The purpose of the query is to determine all the linestrings within x
km from a point, given that the linestrings and the point are stored as
GPS positions (latitude/longitude, SRID=4326).

(see attached image)

My proposed algorithm is:

    * step 1: select all the linestrings within, say, 1 degree of latitude/longitude from the given point P, located at POINT(44 15):

 SELECT myline, myname
   FROM mylines
     WHERE myline && Expand('POINT(44 15)', 1)

The purpose is to quickly select relevant linestrings, using an index. For example, in the figure, lines L1, L2 and L3 will be selected, while L4 and other far-away linestrings will not be selected.

    * step 2: for each of the obtained lines (L1, L2, and L3 in the figure) determine the closest points/projections (P1, P2 and P3) on the corresponding linestrings using (in pseudo-code):

Pi_factor := line_locate_point(Li, P)
Pi := line_interpolate_point(Li, Pi_factor)

    * step 3: for each of the determined points (P1, P2, P3), compute the distance to P:

Di := distance_sphere(P, Pi)

    * step 4: knowing the correct distances on Earth between the points, select the desired linestrings

I'd love to hear that this can be done much easier in PostGIS, but if not I will contribute a working plpgsql function based on your feedback.

Best regards,
Ovidiu

-------------- next part --------------
A non-text attachment was scrubbed...
Name: PostGIS-GPS-select-lines-closest-to-point-smaller.png
Type: image/png
Size: 53749 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070817/386bff42/attachment.png>