Creating closest line along the point''s azimuth using PostgreSQL Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?Drawing line between points at specific distance in PostGIS?How to efficiently find the closest point over the dateline?How to find the nearest point by using PostGIS function?PostGIS nearest point with LATERAL JOIN in PostgreSQL 9.3+Creating a table and inserting selected streets using plpgsql functionsCreating a table that stores Distances and other columnSaving select query results (year wise) from PostgreSQL/PostGIS to text filesWhat is the information behind this geometry?How to give start and end vertex ids dynamically in pgr_dijkstra?Point to Polygon nearest distance DS_distance is not using geography index & knn <-> or <#> does not give result in orderLine to point conversion with start point and end point detection?
On SQL Server, is it possible to restrict certain users from using certain functions, operators or statements?
Can non-competes be enforced after expiration?
Can a new player join a group only when a new campaign starts?
How to show element name in portuguese using elements package?
Why do we bend a book to keep it straight?
Around usage results
When a candle burns, why does the top of wick glow if bottom of flame is hottest?
How to react to hostile behavior from a senior developer?
Why aren't air breathing engines used as small first stages
How can I make names more distinctive without making them longer?
Quick way to create a symlink?
Where are Serre’s lectures at Collège de France to be found?
Is it cost-effective to upgrade an old-ish Giant Escape R3 commuter bike with entry-level branded parts (wheels, drivetrain)?
8 Prisoners wearing hats
How to answer "Have you ever been terminated?"
Why wasn't DOSKEY integrated with command.com?
Fundamental Solution of the Pell Equation
What causes the direction of lightning flashes?
How to find 'n' nodes where all distances between them are greater than 'k'?
Withdrew £2800, but only £2000 shows as withdrawn on online banking; what are my obligations?
Is "Reachable Object" really an NP-complete problem?
How come Sam didn't become Lord of Horn Hill?
Why are both D and D# fitting into my E minor key?
What is this building called? (It was built in 2002)
Creating closest line along the point''s azimuth using PostgreSQL
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)
Announcing the arrival of Valued Associate #679: Cesar Manara
Unicorn Meta Zoo #1: Why another podcast?Drawing line between points at specific distance in PostGIS?How to efficiently find the closest point over the dateline?How to find the nearest point by using PostGIS function?PostGIS nearest point with LATERAL JOIN in PostgreSQL 9.3+Creating a table and inserting selected streets using plpgsql functionsCreating a table that stores Distances and other columnSaving select query results (year wise) from PostgreSQL/PostGIS to text filesWhat is the information behind this geometry?How to give start and end vertex ids dynamically in pgr_dijkstra?Point to Polygon nearest distance DS_distance is not using geography index & knn <-> or <#> does not give result in orderLine to point conversion with start point and end point detection?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
1
I have a point table which contains start point and end point:
When I am writing this query in PostgreSQL to create line it gives me this output.
select distinct on (a.id) a.id,
min(st_astext(st_makeline(a.geometry, b.geometry))),
min(st_distance(a.geometry, b.geometry)) as dist
into results
from fpts1 as a,
fpts1 as b
where not a.geometry = b.geometry
and not a.link_id = b.link_id
and ST_DWithin(a.geometry, b.geometry,0.0002)
group BY a.id,
ST_Distance(a.geometry, b.geometry);
How I can avoid line creation like this and get output like below image.
postgis postgresql
edited Apr 10 at 11:24
JGH
13.6k21239
asked Apr 10 at 9:02
poonam patelpoonam patel
283
add a comment |
1
I have a point table which contains start point and end point:
When I am writing this query in PostgreSQL to create line it gives me this output.
select distinct on (a.id) a.id,
min(st_astext(st_makeline(a.geometry, b.geometry))),
min(st_distance(a.geometry, b.geometry)) as dist
into results
from fpts1 as a,
fpts1 as b
where not a.geometry = b.geometry
and not a.link_id = b.link_id
and ST_DWithin(a.geometry, b.geometry,0.0002)
group BY a.id,
ST_Distance(a.geometry, b.geometry);
How I can avoid line creation like this and get output like below image.
postgis postgresql
edited Apr 10 at 11:24
JGH
13.6k21239
asked Apr 10 at 9:02
poonam patelpoonam patel
283
gis.stackexchange.com/questions/188058/…
– Cyril
Apr 10 at 10:36
yes, that is a great couple of answers. Basically, you just need to add ST_Azimuth logic into either of those approaches, which might be easier with the RECURSIVE graph approach.
– John Powell
Apr 10 at 11:30
you have a set of questions that seem to adress the same issue; although there are a bunch of good ideas in the linked post, I fear your specific problem is near impossible to solve to a satisfying extent (and without the need for extensive manual edits) without any topology between linestrings. if this represents a road network, or parcels, I suggest to rather look at other sources (e.g. OSM) to acquire usable data.
– ThingumaBob
Apr 10 at 13:29
@ cyril thank you for help .its almost similar to what i ma looking for but i have huge data and joining two table is very costly operation for me as it takes alot time.
– poonam patel
Apr 12 at 4:31
@ John Powell .yes, i try to use azimuth angle but i think its not useful as i have perpendicular lines too.
– poonam patel
Apr 12 at 4:33
add a comment |
1
1
1
I have a point table which contains start point and end point:
When I am writing this query in PostgreSQL to create line it gives me this output.
select distinct on (a.id) a.id,
min(st_astext(st_makeline(a.geometry, b.geometry))),
min(st_distance(a.geometry, b.geometry)) as dist
into results
from fpts1 as a,
fpts1 as b
where not a.geometry = b.geometry
and not a.link_id = b.link_id
and ST_DWithin(a.geometry, b.geometry,0.0002)
group BY a.id,
ST_Distance(a.geometry, b.geometry);
How I can avoid line creation like this and get output like below image.
postgis postgresql
edited Apr 10 at 11:24
JGH
13.6k21239
asked Apr 10 at 9:02
poonam patelpoonam patel
283
I have a point table which contains start point and end point:
When I am writing this query in PostgreSQL to create line it gives me this output.
select distinct on (a.id) a.id,
min(st_astext(st_makeline(a.geometry, b.geometry))),
min(st_distance(a.geometry, b.geometry)) as dist
into results
from fpts1 as a,
fpts1 as b
where not a.geometry = b.geometry
and not a.link_id = b.link_id
and ST_DWithin(a.geometry, b.geometry,0.0002)
group BY a.id,
ST_Distance(a.geometry, b.geometry);
How I can avoid line creation like this and get output like below image.
postgis postgresql
postgis postgresql
edited Apr 10 at 11:24
JGH
13.6k21239
asked Apr 10 at 9:02
poonam patelpoonam patel
283
edited Apr 10 at 11:24
JGH
13.6k21239
asked Apr 10 at 9:02
poonam patelpoonam patel
283
edited Apr 10 at 11:24
JGH
13.6k21239
edited Apr 10 at 11:24
JGH
13.6k21239
edited Apr 10 at 11:24
JGH
13.6k21239
13.6k21239
asked Apr 10 at 9:02
poonam patelpoonam patel
283
asked Apr 10 at 9:02
poonam patelpoonam patel
283
asked Apr 10 at 9:02
poonam patelpoonam patel
283
283
gis.stackexchange.com/questions/188058/…
– Cyril
Apr 10 at 10:36
yes, that is a great couple of answers. Basically, you just need to add ST_Azimuth logic into either of those approaches, which might be easier with the RECURSIVE graph approach.
– John Powell
Apr 10 at 11:30
you have a set of questions that seem to adress the same issue; although there are a bunch of good ideas in the linked post, I fear your specific problem is near impossible to solve to a satisfying extent (and without the need for extensive manual edits) without any topology between linestrings. if this represents a road network, or parcels, I suggest to rather look at other sources (e.g. OSM) to acquire usable data.
– ThingumaBob
Apr 10 at 13:29
@ cyril thank you for help .its almost similar to what i ma looking for but i have huge data and joining two table is very costly operation for me as it takes alot time.
– poonam patel
Apr 12 at 4:31
@ John Powell .yes, i try to use azimuth angle but i think its not useful as i have perpendicular lines too.
– poonam patel
Apr 12 at 4:33
add a comment |
gis.stackexchange.com/questions/188058/…
– Cyril
Apr 10 at 10:36
yes, that is a great couple of answers. Basically, you just need to add ST_Azimuth logic into either of those approaches, which might be easier with the RECURSIVE graph approach.
– John Powell
Apr 10 at 11:30
you have a set of questions that seem to adress the same issue; although there are a bunch of good ideas in the linked post, I fear your specific problem is near impossible to solve to a satisfying extent (and without the need for extensive manual edits) without any topology between linestrings. if this represents a road network, or parcels, I suggest to rather look at other sources (e.g. OSM) to acquire usable data.
– ThingumaBob
Apr 10 at 13:29
@ cyril thank you for help .its almost similar to what i ma looking for but i have huge data and joining two table is very costly operation for me as it takes alot time.
– poonam patel
Apr 12 at 4:31
@ John Powell .yes, i try to use azimuth angle but i think its not useful as i have perpendicular lines too.
– poonam patel
Apr 12 at 4:33
gis.stackexchange.com/questions/188058/…
– Cyril
Apr 10 at 10:36
gis.stackexchange.com/questions/188058/…
– Cyril
Apr 10 at 10:36
yes, that is a great couple of answers. Basically, you just need to add ST_Azimuth logic into either of those approaches, which might be easier with the RECURSIVE graph approach.
– John Powell
Apr 10 at 11:30
yes, that is a great couple of answers. Basically, you just need to add ST_Azimuth logic into either of those approaches, which might be easier with the RECURSIVE graph approach.
– John Powell
Apr 10 at 11:30
you have a set of questions that seem to adress the same issue; although there are a bunch of good ideas in the linked post, I fear your specific problem is near impossible to solve to a satisfying extent (and without the need for extensive manual edits) without any topology between linestrings. if this represents a road network, or parcels, I suggest to rather look at other sources (e.g. OSM) to acquire usable data.
– ThingumaBob
Apr 10 at 13:29
you have a set of questions that seem to adress the same issue; although there are a bunch of good ideas in the linked post, I fear your specific problem is near impossible to solve to a satisfying extent (and without the need for extensive manual edits) without any topology between linestrings. if this represents a road network, or parcels, I suggest to rather look at other sources (e.g. OSM) to acquire usable data.
– ThingumaBob
Apr 10 at 13:29
@ cyril thank you for help .its almost similar to what i ma looking for but i have huge data and joining two table is very costly operation for me as it takes alot time.
– poonam patel
Apr 12 at 4:31
@ cyril thank you for help .its almost similar to what i ma looking for but i have huge data and joining two table is very costly operation for me as it takes alot time.
– poonam patel
Apr 12 at 4:31
@ John Powell .yes, i try to use azimuth angle but i think its not useful as i have perpendicular lines too.
– poonam patel
Apr 12 at 4:33
@ John Powell .yes, i try to use azimuth angle but i think its not useful as i have perpendicular lines too.
– poonam patel
Apr 12 at 4:33
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
);
);
draft saved
draft discarded
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%2fgis.stackexchange.com%2fquestions%2f318339%2fcreating-closest-line-along-the-points-azimuth-using-postgresql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Geographic Information Systems Stack Exchange!
- 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.
draft saved
draft discarded
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%2fgis.stackexchange.com%2fquestions%2f318339%2fcreating-closest-line-along-the-points-azimuth-using-postgresql%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
gis.stackexchange.com/questions/188058/…
– Cyril
Apr 10 at 10:36
yes, that is a great couple of answers. Basically, you just need to add ST_Azimuth logic into either of those approaches, which might be easier with the RECURSIVE graph approach.
– John Powell
Apr 10 at 11:30
you have a set of questions that seem to adress the same issue; although there are a bunch of good ideas in the linked post, I fear your specific problem is near impossible to solve to a satisfying extent (and without the need for extensive manual edits) without any topology between linestrings. if this represents a road network, or parcels, I suggest to rather look at other sources (e.g. OSM) to acquire usable data.
– ThingumaBob
Apr 10 at 13:29
@ cyril thank you for help .its almost similar to what i ma looking for but i have huge data and joining two table is very costly operation for me as it takes alot time.
– poonam patel
Apr 12 at 4:31
@ John Powell .yes, i try to use azimuth angle but i think its not useful as i have perpendicular lines too.
– poonam patel
Apr 12 at 4:33