I have a table that has sets of lat/long coordinates--that is, a
lat/long for point #1 and a lat/long for point #2.  These define
streets.  I also have a table of points with lat/long coordinates that
are roughly six feet apart.  I would like to determine the lat/long
coordinates where a line defined by the first table intersects (or
nearly coincides with) a point in the second table.  Is there a
formula that I could use in a stored procedure to determine this?

---

Request for Question Clarification by elmarto-ga on 18 Apr 2005 15:53 PDT

Hi glscott,
Do the points in the second table also form a line? Could it be that
there are several points of the second table that pass through the
line?

Regards,
elmarto

---

Clarification of Question by glscott-ga on 19 Apr 2005 06:38 PDT

Yes, the points in the second table do form a line.  Basically what
I'm interested in here is the lat/long coordinates of the
intersections between these lines.  There are thousands of streets,
each with a hundred or more line segments (intervals between lat/long
points).  I am currently dealing only with one rail line -- this is
the table with coordinates for points that are six feet apart -- but
later I want to use this same technique with many other rail lines.  I
want the coordinates of the streets as they intersect the rail line.

---

Clarification of Question by glscott-ga on 19 Apr 2005 06:45 PDT

With regard to the comments posted by racecar-ga (18 Apr 2005 22:06
PDT), I do not think I need to take the curvature of the earth into
consideration.  The first of coordinates (streets) are about 50-75
feet apart; the second set of coordinates are six feet apart.

Latitude and Longtitude is based on the degrees/minutes/seconds
system. It is the world divided either way into 360 degrees, each
degree into 60 minutes and each minute into 60 seconds, like a clock.
This is a simplistic answer as I do not know your level of knowledge.
A degree equates with 60 'sea miles' so a sea mile equates with a
minute. A second equates with 1/60th of a mile. [ This gives rise to
the saying 'a mile a minute']. A good conversion table will show the
difference between a sea mile, a land mile and a kilometre. The sea
mile is largest. Find the distance between your two streets in land
miles and convert to sea miles, which becomes minutes and seconds of
Lat/Long. Add or subtract from the nearest parallel of degrees to get
the required lat/long coordinates. Hope this helps.




'

You have pairs of points which define lines.  You want to know which
of a set of other points is closest to each of these lines.  Is this
correct?

If so, it would be helpful to know how far apart the points are.  If
they're all within a few miles (up to say a couple hundred) of each
other, you can ignore the curvature of the earth, and the problem is
simpler.  If the points are more than several hundred miles apart,
spherical geometry is required.

Comment on a previous comment: it is true that a degree of latitude is
60 nautical miles, but it is not true in general that a degree of
longitude is 60 miles.  And there are not 360 degrees of latitude,
only 180.

For the scale you're talking about, as long as you're not right near
one of the poles (where there are no streets anyway) you can use
lat/lon as a rectangular coordinate system.  Let x be lon and y be
lat.  Then if two points defining a street are (lon1, lat1) and (lon2,
lat2), the slope of the line along which the street runs is

m = (lat2 - lat1)/(lon2 - lon1).

An equation for the line is then

y - lat1 = m(x - lon1).

The same formulas work when applied to any two of your second set of
points (the ones that define the rail line).  Once you have the
equations for the lines defining the street and rail line, you have a
simple set of 2 linear equations and 2 unknowns, and you can easily
solve for the point of intersection.