Find two polygons closest to each other postgis
I have a table of buildings where they are all polygons and it looks something like this:
Building_name | Building_ID | Geom
A | 1 | *polygon object
B | 2 | *polygon object
C | 3 | *polygon object
How do I find which 2 buildings are closest to each other using straight-line distance?
Output should be for example: A and B are the buildings closest to each other.
Is this possible for polygons?
Thanks in advance!
postgresql postgis
asked Nov 23 '18 at 5:55
AmorosoAmoroso
1611212
add a comment |
I have a table of buildings where they are all polygons and it looks something like this:
Building_name | Building_ID | Geom
A | 1 | *polygon object
B | 2 | *polygon object
C | 3 | *polygon object
How do I find which 2 buildings are closest to each other using straight-line distance?
Output should be for example: A and B are the buildings closest to each other.
Is this possible for polygons?
Thanks in advance!
postgresql postgis
asked Nov 23 '18 at 5:55
AmorosoAmoroso
1611212
Calculate the minimum value for a column with the ST_ShortestLine between any two polygons.
– lusitanica
Nov 23 '18 at 10:10
add a comment |
I have a table of buildings where they are all polygons and it looks something like this:
Building_name | Building_ID | Geom
A | 1 | *polygon object
B | 2 | *polygon object
C | 3 | *polygon object
How do I find which 2 buildings are closest to each other using straight-line distance?
Output should be for example: A and B are the buildings closest to each other.
Is this possible for polygons?
Thanks in advance!
postgresql postgis
asked Nov 23 '18 at 5:55
AmorosoAmoroso
1611212
I have a table of buildings where they are all polygons and it looks something like this:
Building_name | Building_ID | Geom
A | 1 | *polygon object
B | 2 | *polygon object
C | 3 | *polygon object
How do I find which 2 buildings are closest to each other using straight-line distance?
Output should be for example: A and B are the buildings closest to each other.
Is this possible for polygons?
Thanks in advance!
postgresql postgis
postgresql postgis
asked Nov 23 '18 at 5:55
AmorosoAmoroso
1611212
asked Nov 23 '18 at 5:55
AmorosoAmoroso
1611212
asked Nov 23 '18 at 5:55
AmorosoAmoroso
1611212
asked Nov 23 '18 at 5:55
AmorosoAmoroso
1611212
asked Nov 23 '18 at 5:55
AmorosoAmoroso
1611212
1611212
Calculate the minimum value for a column with the ST_ShortestLine between any two polygons.
– lusitanica
Nov 23 '18 at 10:10
add a comment |
Calculate the minimum value for a column with the ST_ShortestLine between any two polygons.
– lusitanica
Nov 23 '18 at 10:10
Calculate the minimum value for a column with the ST_ShortestLine between any two polygons.
– lusitanica
Nov 23 '18 at 10:10
Calculate the minimum value for a column with the ST_ShortestLine between any two polygons.
– lusitanica
Nov 23 '18 at 10:10
add a comment |
2 Answers
2
active
oldest
votes
For sample geometry collection
Following SQL would do slightly faster, compared to st_distance, on large number of geometries, since it makes use of closest neighbor index. Besides, you might consider 'true' as your third st_distance parameter in order to get spherical distance, if you decide, for whatever reason, to use one.
select x1.name, x2.name
from (values (1,'A',st_geomfromtext('Polygon ((325 708, 348 768, 424 758, 481 653, 421 589, 340 628, 325 708))')),
(2,'B',st_geomfromtext('Polygon ((643 891, 692 808, 769 825, 793 885, 786 923, 730 939, 705 902, 643 891))')),
(3,'C',st_geomfromtext('Polygon ((692 620, 669 553, 767 523, 882 548, 893 622, 834 683, 692 620))')),
(4,'D',st_geomfromtext('Polygon ((519 703, 549 676, 586 707, 585 751, 555 778, 526 745, 519 703))'))) x1(id,name,geom)
join (values (1,'A',st_geomfromtext('Polygon ((325 708, 348 768, 424 758, 481 653, 421 589, 340 628, 325 708))')),
(2,'B',st_geomfromtext('Polygon ((643 891, 692 808, 769 825, 793 885, 786 923, 730 939, 705 902, 643 891))')),
(3,'C',st_geomfromtext('Polygon ((692 620, 669 553, 767 523, 882 548, 893 622, 834 683, 692 620))')),
(4,'D',st_geomfromtext('Polygon ((519 703, 549 676, 586 707, 585 751, 555 778, 526 745, 519 703))'))) x2(id,name,geom)
on x1.id<>x2.id
order by x1.geom<->x2.geom
limit 1
answered Nov 26 '18 at 10:00
Yevgen GorbunkovYevgen Gorbunkov
2,62721433
add a comment |
SELECT
a."Building_name" "Building name A",
b."Building_name" "Building name B",
ST_Distance(a."Geom", b."Geom") distance
FROM
polygons a, polygons b
WHERE
a."Building_ID" <> b."Building_ID"
-- optionally add a ST_DWithin condition to improve performance:
-- AND ST_DWithin(a."Geom", b."Geom", 1000)
ORDER BY
distance
LIMIT 1;
ST_Distance calculates the shortest line distance (in SRID units), while ST_ShortestLine returns the actual geometry of the shortest line.
answered Nov 24 '18 at 19:28
thibautgthibautg
1,0391511
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53441303%2ffind-two-polygons-closest-to-each-other-postgis%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
For sample geometry collection
Following SQL would do slightly faster, compared to st_distance, on large number of geometries, since it makes use of closest neighbor index. Besides, you might consider 'true' as your third st_distance parameter in order to get spherical distance, if you decide, for whatever reason, to use one.
select x1.name, x2.name
from (values (1,'A',st_geomfromtext('Polygon ((325 708, 348 768, 424 758, 481 653, 421 589, 340 628, 325 708))')),
(2,'B',st_geomfromtext('Polygon ((643 891, 692 808, 769 825, 793 885, 786 923, 730 939, 705 902, 643 891))')),
(3,'C',st_geomfromtext('Polygon ((692 620, 669 553, 767 523, 882 548, 893 622, 834 683, 692 620))')),
(4,'D',st_geomfromtext('Polygon ((519 703, 549 676, 586 707, 585 751, 555 778, 526 745, 519 703))'))) x1(id,name,geom)
join (values (1,'A',st_geomfromtext('Polygon ((325 708, 348 768, 424 758, 481 653, 421 589, 340 628, 325 708))')),
(2,'B',st_geomfromtext('Polygon ((643 891, 692 808, 769 825, 793 885, 786 923, 730 939, 705 902, 643 891))')),
(3,'C',st_geomfromtext('Polygon ((692 620, 669 553, 767 523, 882 548, 893 622, 834 683, 692 620))')),
(4,'D',st_geomfromtext('Polygon ((519 703, 549 676, 586 707, 585 751, 555 778, 526 745, 519 703))'))) x2(id,name,geom)
on x1.id<>x2.id
order by x1.geom<->x2.geom
limit 1
answered Nov 26 '18 at 10:00
Yevgen GorbunkovYevgen Gorbunkov
2,62721433
add a comment |
For sample geometry collection
Following SQL would do slightly faster, compared to st_distance, on large number of geometries, since it makes use of closest neighbor index. Besides, you might consider 'true' as your third st_distance parameter in order to get spherical distance, if you decide, for whatever reason, to use one.
select x1.name, x2.name
from (values (1,'A',st_geomfromtext('Polygon ((325 708, 348 768, 424 758, 481 653, 421 589, 340 628, 325 708))')),
(2,'B',st_geomfromtext('Polygon ((643 891, 692 808, 769 825, 793 885, 786 923, 730 939, 705 902, 643 891))')),
(3,'C',st_geomfromtext('Polygon ((692 620, 669 553, 767 523, 882 548, 893 622, 834 683, 692 620))')),
(4,'D',st_geomfromtext('Polygon ((519 703, 549 676, 586 707, 585 751, 555 778, 526 745, 519 703))'))) x1(id,name,geom)
join (values (1,'A',st_geomfromtext('Polygon ((325 708, 348 768, 424 758, 481 653, 421 589, 340 628, 325 708))')),
(2,'B',st_geomfromtext('Polygon ((643 891, 692 808, 769 825, 793 885, 786 923, 730 939, 705 902, 643 891))')),
(3,'C',st_geomfromtext('Polygon ((692 620, 669 553, 767 523, 882 548, 893 622, 834 683, 692 620))')),
(4,'D',st_geomfromtext('Polygon ((519 703, 549 676, 586 707, 585 751, 555 778, 526 745, 519 703))'))) x2(id,name,geom)
on x1.id<>x2.id
order by x1.geom<->x2.geom
limit 1
answered Nov 26 '18 at 10:00
Yevgen GorbunkovYevgen Gorbunkov
2,62721433
add a comment |
For sample geometry collection
Following SQL would do slightly faster, compared to st_distance, on large number of geometries, since it makes use of closest neighbor index. Besides, you might consider 'true' as your third st_distance parameter in order to get spherical distance, if you decide, for whatever reason, to use one.
select x1.name, x2.name
from (values (1,'A',st_geomfromtext('Polygon ((325 708, 348 768, 424 758, 481 653, 421 589, 340 628, 325 708))')),
(2,'B',st_geomfromtext('Polygon ((643 891, 692 808, 769 825, 793 885, 786 923, 730 939, 705 902, 643 891))')),
(3,'C',st_geomfromtext('Polygon ((692 620, 669 553, 767 523, 882 548, 893 622, 834 683, 692 620))')),
(4,'D',st_geomfromtext('Polygon ((519 703, 549 676, 586 707, 585 751, 555 778, 526 745, 519 703))'))) x1(id,name,geom)
join (values (1,'A',st_geomfromtext('Polygon ((325 708, 348 768, 424 758, 481 653, 421 589, 340 628, 325 708))')),
(2,'B',st_geomfromtext('Polygon ((643 891, 692 808, 769 825, 793 885, 786 923, 730 939, 705 902, 643 891))')),
(3,'C',st_geomfromtext('Polygon ((692 620, 669 553, 767 523, 882 548, 893 622, 834 683, 692 620))')),
(4,'D',st_geomfromtext('Polygon ((519 703, 549 676, 586 707, 585 751, 555 778, 526 745, 519 703))'))) x2(id,name,geom)
on x1.id<>x2.id
order by x1.geom<->x2.geom
limit 1
answered Nov 26 '18 at 10:00
Yevgen GorbunkovYevgen Gorbunkov
2,62721433
For sample geometry collection
Following SQL would do slightly faster, compared to st_distance, on large number of geometries, since it makes use of closest neighbor index. Besides, you might consider 'true' as your third st_distance parameter in order to get spherical distance, if you decide, for whatever reason, to use one.
select x1.name, x2.name
from (values (1,'A',st_geomfromtext('Polygon ((325 708, 348 768, 424 758, 481 653, 421 589, 340 628, 325 708))')),
(2,'B',st_geomfromtext('Polygon ((643 891, 692 808, 769 825, 793 885, 786 923, 730 939, 705 902, 643 891))')),
(3,'C',st_geomfromtext('Polygon ((692 620, 669 553, 767 523, 882 548, 893 622, 834 683, 692 620))')),
(4,'D',st_geomfromtext('Polygon ((519 703, 549 676, 586 707, 585 751, 555 778, 526 745, 519 703))'))) x1(id,name,geom)
join (values (1,'A',st_geomfromtext('Polygon ((325 708, 348 768, 424 758, 481 653, 421 589, 340 628, 325 708))')),
(2,'B',st_geomfromtext('Polygon ((643 891, 692 808, 769 825, 793 885, 786 923, 730 939, 705 902, 643 891))')),
(3,'C',st_geomfromtext('Polygon ((692 620, 669 553, 767 523, 882 548, 893 622, 834 683, 692 620))')),
(4,'D',st_geomfromtext('Polygon ((519 703, 549 676, 586 707, 585 751, 555 778, 526 745, 519 703))'))) x2(id,name,geom)
on x1.id<>x2.id
order by x1.geom<->x2.geom
limit 1
answered Nov 26 '18 at 10:00
Yevgen GorbunkovYevgen Gorbunkov
2,62721433
edited Nov 26 '18 at 10:16
answered Nov 26 '18 at 10:00
Yevgen GorbunkovYevgen Gorbunkov
2,62721433
answered Nov 26 '18 at 10:00
Yevgen GorbunkovYevgen Gorbunkov
2,62721433
answered Nov 26 '18 at 10:00
Yevgen GorbunkovYevgen Gorbunkov
2,62721433
2,62721433
add a comment |
add a comment |
SELECT
a."Building_name" "Building name A",
b."Building_name" "Building name B",
ST_Distance(a."Geom", b."Geom") distance
FROM
polygons a, polygons b
WHERE
a."Building_ID" <> b."Building_ID"
-- optionally add a ST_DWithin condition to improve performance:
-- AND ST_DWithin(a."Geom", b."Geom", 1000)
ORDER BY
distance
LIMIT 1;
ST_Distance calculates the shortest line distance (in SRID units), while ST_ShortestLine returns the actual geometry of the shortest line.
answered Nov 24 '18 at 19:28
thibautgthibautg
1,0391511
add a comment |
SELECT
a."Building_name" "Building name A",
b."Building_name" "Building name B",
ST_Distance(a."Geom", b."Geom") distance
FROM
polygons a, polygons b
WHERE
a."Building_ID" <> b."Building_ID"
-- optionally add a ST_DWithin condition to improve performance:
-- AND ST_DWithin(a."Geom", b."Geom", 1000)
ORDER BY
distance
LIMIT 1;
ST_Distance calculates the shortest line distance (in SRID units), while ST_ShortestLine returns the actual geometry of the shortest line.
answered Nov 24 '18 at 19:28
thibautgthibautg
1,0391511
add a comment |
SELECT
a."Building_name" "Building name A",
b."Building_name" "Building name B",
ST_Distance(a."Geom", b."Geom") distance
FROM
polygons a, polygons b
WHERE
a."Building_ID" <> b."Building_ID"
-- optionally add a ST_DWithin condition to improve performance:
-- AND ST_DWithin(a."Geom", b."Geom", 1000)
ORDER BY
distance
LIMIT 1;
ST_Distance calculates the shortest line distance (in SRID units), while ST_ShortestLine returns the actual geometry of the shortest line.
answered Nov 24 '18 at 19:28
thibautgthibautg
1,0391511
SELECT
a."Building_name" "Building name A",
b."Building_name" "Building name B",
ST_Distance(a."Geom", b."Geom") distance
FROM
polygons a, polygons b
WHERE
a."Building_ID" <> b."Building_ID"
-- optionally add a ST_DWithin condition to improve performance:
-- AND ST_DWithin(a."Geom", b."Geom", 1000)
ORDER BY
distance
LIMIT 1;
ST_Distance calculates the shortest line distance (in SRID units), while ST_ShortestLine returns the actual geometry of the shortest line.
answered Nov 24 '18 at 19:28
thibautgthibautg
1,0391511
answered Nov 24 '18 at 19:28
thibautgthibautg
1,0391511
answered Nov 24 '18 at 19:28
thibautgthibautg
1,0391511
answered Nov 24 '18 at 19:28
thibautgthibautg
1,0391511
1,0391511
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53441303%2ffind-two-polygons-closest-to-each-other-postgis%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Calculate the minimum value for a column with the ST_ShortestLine between any two polygons.
– lusitanica
Nov 23 '18 at 10:10