Title changed, Content structured

Source Link

edited Jun 17, 2020 at 7:27

34k
4
73
148

Update points geometry in PostGIS databasewhile snapping them to nearest line

I have two tables in the PostGIS database - points and lines. And I want to update points geometry to snap them to line if they lay within a certain distance.

By now I managed to create a new table with snapped points which has only geometries. This works:

CREATE TABLE new_table AS
SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id;

CREATE TABLE new_table AS
SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id;

But what I really need is to update the existing table. So I try:

UPDATE points
SET geom =  
(SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id);

UPDATE points
SET geom =  
(SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id);

And it never worksdid not work. How do I put these two pieces together?

Update points geometry in PostGIS database snapping them to nearest line

I have two tables in the PostGIS database - points and lines. And I want to update points geometry to snap them to line if they lay within a certain distance.

By now I managed to create a new table with snapped points which has only geometries. This works:

CREATE TABLE new_table AS
SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id;

But what I really need is to update the existing table. So I try:

UPDATE points
SET geom =  
(SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id);

And it never works. How do I put these two pieces together?

Update points geometry while snapping them to nearest line

I have two tables in the PostGIS database - points and lines. And I want to update points geometry to snap them to line if they lay within a certain distance.

By now I managed to create a new table with snapped points which has only geometries. This works:

CREATE TABLE new_table AS
SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id;

But what I really need is to update the existing table. So I try:

UPDATE points
SET geom =  
(SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id);

And it did not work. How do I put these two pieces together?

Tweeted twitter.com/StackGIS/status/1273088218200711168

occurred Jun 17, 2020 at 3:00

Became Hot Network Question

occurred Jun 16, 2020 at 23:46

naming; removed helpme

Source Link

edited Jun 16, 2020 at 22:09

Vince

20.3k
16
48
65

Update points geometry in postgisPostGIS database snapping them to nearest line

I have two tables in the postgisPostGIS database - points and lines. And I want to update points geometry to snap them to line if they lay within a certain distance.

By now I managed to create a new table with snapped points which has only geometries. This works:

CREATE TABLE new_table AS
SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id;

But what I really need is to update the existing table. So I try:

UPDATE points
SET geom =  
(SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id);

And it never works. Please, help me toHow do I put these two pieces together.?

Update points geometry in postgis database snapping them to nearest line

I have two tables in the postgis database - points and lines. And I want to update points geometry to snap them to line if they lay within a certain distance.

By now I managed to create a new table with snapped points which has only geometries. This works:

CREATE TABLE new_table AS
SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id;

But what I really need is to update the existing table. So I try:

UPDATE points
SET geom =  
(SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id);

And it never works. Please, help me to put these two pieces together.

Update points geometry in PostGIS database snapping them to nearest line

I have two tables in the PostGIS database - points and lines. And I want to update points geometry to snap them to line if they lay within a certain distance.

By now I managed to create a new table with snapped points which has only geometries. This works:

CREATE TABLE new_table AS
SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id;

But what I really need is to update the existing table. So I try:

UPDATE points
SET geom =  
(SELECT ST_ClosestPoint(lines.geom, points.geom) as snapped_point
FROM lines
INNER JOIN points on ST_Dwithin(lines.geom, points.geom, 5)
ORDER BY points.id);

And it never works. How do I put these two pieces together?