GEOCODE Street corner --- brain stopped
-
Hi all, my brain seems to have stopped functioning. Does anyone have a really clever way of getting this right without lots and lots of lines of code.
Here it is, I have a Geocode database, each property has a plot number and as you would expect they are all along a street.
Each plot has an x and y coordinate, how in the world would do I simply get the coordinates of a street intersection?
essentially I can query all the coordinates of each street and I will have four columns to compare against to get the 'best match. I have considered adding the X and Y coordinates and then setting them within a range, then comparing the ranges with one another with a <= and >= then reducing the range until the accuracy result is 1. Then unbundling the sum of the coordinates to reveal the actual coordinates… see my problem?
This is what I mean;
Below is the results of two streets, note that not each street has the same amount of records to it, however there is a point where the two streets will cross one another, and the coordinates are not 100% the same, the values are close, how does one determine the best match between the two?
Street1
Street 2
x
y
x
y
27.9256
-26.1695
27.9238
-26.1642
27.9262
-26.1692
27.9238
-26.1631
27.9266
-26.1693
27.924
-26.1632
27.9268
-26.1686
27.9241
-26.1645
27.927
-26.1684
27.9243
-26.1634
27.9272
-26.1671
27.9243
-26.1647
27.9272
-26.1671
27.9245
-26.1649
27.9272
-26.1689
27.9245
-26.1636
27.9276
-26.1688
27.9246
-26.1638
27.9278
-26.1687
27.9248
-26.1638
27.928
-26.1685
27.9248
-26.1651
27.9282
-26.1683
27.925
-26.1652
27.9286
-26.168
27.9252
-26.1654
27.9289
-26.1679
27.9253
-26.1642
27.9291
-26.1677
27.9254
-26.1655
27.9291
-26.1666
27.9255
-26.1643
27.9293
-26.1656
27.9256
-26.1657
27.9296
-26.1674
27.9258
-26.1658
27.9297
-26.1653
27.9258
-26.1645
27.9298
-26.1672
27.926
-26.1647
27.9301
-26.1671
27.9262
-26.1659
27.9302
-26.165
27.9263
-26.1649
27.9305
-26.1668
27.9265
-26.1651
27.9307
-26.1666
27.9267
-26.1652
27.931
-26.1665
27.927
-26.1654
27.9311
-26.165
27.9274
-26.1671
27.9311
-26.165
27.9291
-26.1677
27.9314
-26.1662
27.9293
-26.1679
27.9315
-26.166
27.9295
-26.1681
27.9317
-26.165
27.9297
-26.1683
27.9318
-26.1659
27.9299
-26.1684
27.9322
-26.165
27.93
-26.1676
27.9322
-26.1656
27.93
-26.1685
27.9324
-26.1654
27.9302
-26.1678
27.9327
-26.1653
27.9303
-26.1686
27.9328
-26.1645
27.9304
-26.1679
27.9306
-26.1687
27.9306
-26.1681
27.9308
-26.1682
27.9309
-26.169
27.9311
-26.1683
27.9311
-26.1683
27.9315
-26.1688
27.9315
-26.1688
27.9317
-26.1689
27.9318
-26.1699
27.932
-26.1699
27.9322
-26.1694
27.9322
-26.17
27.9325
-26.1701
27.9327
-26.1696
27.933
-26.1697
27.9334
-26.1698
-
Assuming street1 and street2 are tables, this method does a cross join and then orders by the sum of the absolute differences in x and y coordinates, selecting the lowest. It also assumes the existence of a unique ID field in both tables.
select top 1 s1.id, s1.x x1, s1.y y1, s2.id, s2.x x2, s2.y y2, abs(s1.x-s2.x) + abs(s1.y-s2.y) diff
from street1 s1, street2 s2
order by abs(s1.x-s2.x) + abs(s1.y-s2.y)
Is that what you are after?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
It is very easy to build!
Suppose you have all coordinates in a table name 'Streets'. This table has a field named 'StreetName', a field named 'x' and a field named 'y'.
Example:
StreetName | x | y
High Street|27.9256|-26.1692
Low Street |28.4140|-25.2387
Then just call my function with
SELECT * FROM dbo.fnIntersections('High Street', 'Low Street')
to get all intersection points between High Street and Low Street!
CREATE FUNCTION dbo.fnIntersections
(
@StreetName1 VARCHAR(50),
@StreetName2 VARCHAR(50)
)
RETURNS @Points TABLE
(
x SMALLMONEY,
y SMALLMONEY
)
AS
BEGIN
DECLARE @XY TABLE
(
i INT IDENTITY(0, 1),
x1 SMALLMONEY,
y1 SMALLMONEY,
x2 SMALLMONEY,
y2 SMALLMONEY
 
INSERT INTO @XY
(
x2,
y2
 
SELECT x,
y
FROM Streets
WHERE StreetName = @StreetName1
UPDATE @XY
SET x1 = z.x2,
y1 = z.y2
FROM @XY t,
(
SELECT i,
x2,
y2
FROM @XY
 
zWHERE t.i = z.i + 1
DECLARE @UV TABLE
(
i INT IDENTITY(0, 1),
u1 SMALLMONEY,
v1 SMALLMONEY,
u2 SMALLMONEY,
v2 SMALLMONEY
 
INSERT INTO @UV
(
u2,
v2
 
SELECT x,
y
FROM Streets
WHERE StreetName = @StreetName2
UPDATE @UV
SET u1 = z.u2,
v1 = z.v2
FROM @UV t,
(
SELECT i,
u2,
v2
FROM @UV
 
zWHERE t.i = z.i + 1
DECLARE @Lines TABLE
(
x1 SMALLMONEY,
y1 SMALLMONEY,
x2 SMALLMONEY,
y2 SMALLMONEY,
u1 SMALLMONEY,
v1 SMALLMONEY,
u2 SMALLMONEY,
v2 SMALLMONEY,
b1 NUMERIC(38, 10),
b2 NUMERIC(38, 10),
a1 NUMERIC(38, 10),
a2 NUMERIC(38, 10),
x NUMERIC(38, 10),
y NUMERIC(38, 10)
 
INSERT INTO @Lines
(
x1,
y1,
x2,
y2,
u1,
v1,
u2,
v2
 
SELECT xy.x1,
xy.y1,
xy.x2,
xy.y2,
uv.u1,
uv.v1,
uv.u2,
uv.v2
FROM (
SELECT x1,
y1,
x2,
y2
FROM @XY
WHERE i > 0
 
xyCROSS JOIN (
SELECT u1,
v1,
u2,
v2
FROM @UV
WHERE i > 0
 
uvUPDATE @Lines
SET b1 = CASE WHEN x1 <> x2 THEN (y2 - y1) / (x2 - x1) END,
b2 = CASE WHEN u1 <> u2 THEN (v2 - v1) / (u2 - u1) END
DELETE @Lines
WHERE b1 IS NULL OR b2 IS NULL
UPDATE @Lines
SET a1 = y1 - (b1 * x1),
a2 = v1 - (b2 * u1)
UPDATE @Lines
SET x = CASE WHEN b1 <> b2 THEN - (a1 - a2) / (b1 - b2) END
DELETE @Lines
WHERE x IS NULL
UPDATE @Lines
SET y = CASE WHEN b1 IS NULL OR x IS NULL THEN NULL ELSE a1 + b1 * x END
INSERT INTO @Points
(
x,
y
 
SELECT DISTINCT CONVERT(SMALLMONEY, x) x,
CONVERT(SMALLMONEY, y) y
FROM @Lines
WHERE (x1 - x) * (x - x2) >= 0.0
AND (u1 - x) * (x - u2) >= 0.0
AND (y1 - y) * (y - y2) >= 0.0
AND (v1 - y) * (y - v2) >= 0.0
RETURN
END
N 56°04'39.16"
E 12°55'05.25" -
The result of your supplied geodata is:
(27.9272; -26.1670)
(27.9290; -26.1677)
(27.9291; -26.1677)
(27.9292; -26.1678)
(27.9293; -26.1678)
(27.9300; -26.1673)
There are six intersections within the supplied geodata.
N 56°04'39.16"
E 12°55'05.25" -
Heureka! I've found it!
Here is a "one query"-solution...
SELECT DISTINCT CONVERT(SMALLMONEY, x1 + ((x4 - x3) * (y1 - y3) - (y4 - y3) * (x1 - x3)) * (x2 - x1)) x,
CONVERT(SMALLMONEY, y1 + ((x4 - x3) * (y1 - y3) - (y4 - y3) * (x1 - x3)) * (y2 - y1)) y
FROM (
SELECT S1.x x1,
S1.y y1,
S2.x x2,
S2.y y2
FROM Streets S1,
(
SELECT x,
y
FROM Streets
WHERE StreetName = 'Low Street'
 
S2WHERE StreetName = 'Low Street'
AND S1.x <> S2.x AND S1.y <> S2.y
 
T1,(
SELECT S1.x x3,
S1.y y3,
S2.x x4,
S2.y y4
FROM Streets S1,
(
SELECT x,
y
FROM Streets
WHERE StreetName = 'High Street'
 
S2WHERE StreetName = 'High Street'
AND S1.x <> S2.x AND S1.y <> S2.y
 
T2WHERE (y4 - y3) * (x2 - x1) - (x4 - x3) * (y2 - y1) <> 0.0
AND (x4 - x3) * (y1 - y3) - (y4 - y3) * (x1 - x3) / (y4 - y3) * (x2 - x1) - (x4 - x3) * (y2 - y1) BETWEEN 0.0 AND 1.0
AND (x2 - x1) * (y1 - y3) - (y2 - y1) * (x1 - x1) / (y4 - y3) * (x2 - x1) - (x4 - x3) * (y2 - y1) BETWEEN 0.0 AND 1.0
N 56°04'39.16"
E 12°55'05.25"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply