1

I have OSM data in postgresql for my local city. I want to change the name of a road which is near my lat long.

Like:

Update planet_line set Name='My road 1' where (if it is a road/way) and (if it is nearst road to (mylat, mylong))

Update should change only one lines name, like the top 1 nearest one. I don't want to affect rivers or other lines, only road.

Can you help me to fix my query?

edit:

-- Table: public.planet_osm_line

not : osm_id  is not uniqe. It can be used in few different rows.

CREATE TABLE public.planet_osm_line
(
osm_id bigint,

access text COLLATE "default".pg_catalog,
"addr:housename" text COLLATE "default".pg_catalog,
"addr:housenumber" text COLLATE "default".pg_catalog,
"addr:interpolation" text COLLATE "default".pg_catalog,
admin_level text COLLATE "default".pg_catalog,
aerialway text COLLATE "default".pg_catalog,
aeroway text COLLATE "default".pg_catalog,
amenity text COLLATE "default".pg_catalog,
area text COLLATE "default".pg_catalog,
barrier text COLLATE "default".pg_catalog,
bicycle text COLLATE "default".pg_catalog,
brand text COLLATE "default".pg_catalog,
bridge text COLLATE "default".pg_catalog,
boundary text COLLATE "default".pg_catalog,
building text COLLATE "default".pg_catalog,
construction text COLLATE "default".pg_catalog,
covered text COLLATE "default".pg_catalog,
culvert text COLLATE "default".pg_catalog,
cutting text COLLATE "default".pg_catalog,
denomination text COLLATE "default".pg_catalog,
disused text COLLATE "default".pg_catalog,
embankment text COLLATE "default".pg_catalog,
foot text COLLATE "default".pg_catalog,
"generator:source" text COLLATE "default".pg_catalog,
harbour text COLLATE "default".pg_catalog,
**highway** text COLLATE "default".pg_catalog,
historic text COLLATE "default".pg_catalog,
horse text COLLATE "default".pg_catalog,
intermittent text COLLATE "default".pg_catalog,
junction text COLLATE "default".pg_catalog,
landuse text COLLATE "default".pg_catalog,
layer text COLLATE "default".pg_catalog,
leisure text COLLATE "default".pg_catalog,
lock text COLLATE "default".pg_catalog,
man_made text COLLATE "default".pg_catalog,
military text COLLATE "default".pg_catalog,
motorcar text COLLATE "default".pg_catalog,
**name** text COLLATE "default".pg_catalog,
"natural" text COLLATE "default".pg_catalog,
office text COLLATE "default".pg_catalog,
oneway text COLLATE "default".pg_catalog,
operator text COLLATE "default".pg_catalog,
place text COLLATE "default".pg_catalog,
population text COLLATE "default".pg_catalog,
power text COLLATE "default".pg_catalog,
power_source text COLLATE "default".pg_catalog,
public_transport text COLLATE "default".pg_catalog,
railway text COLLATE "default".pg_catalog,
ref text COLLATE "default".pg_catalog,
religion text COLLATE "default".pg_catalog,
route text COLLATE "default".pg_catalog,
service text COLLATE "default".pg_catalog,
shop text COLLATE "default".pg_catalog,
sport text COLLATE "default".pg_catalog,
surface text COLLATE "default".pg_catalog,
toll text COLLATE "default".pg_catalog,
tourism text COLLATE "default".pg_catalog,
"tower:type" text COLLATE "default".pg_catalog,
tracktype text COLLATE "default".pg_catalog,
tunnel text COLLATE "default".pg_catalog,
water text COLLATE "default".pg_catalog,
waterway text COLLATE "default".pg_catalog,
wetland text COLLATE "default".pg_catalog,
width text COLLATE "default".pg_catalog,
wood text COLLATE "default".pg_catalog,
z_order integer,
way_area real,
tags "public.hstore",
**way** "public.geometry"(230633732)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.planet_osm_line
OWNER to osm;

GRANT ALL ON TABLE public.planet_osm_line TO osm;

GRANT SELECT ON TABLE public.planet_osm_line TO PUBLIC;

-- Index: planet_osm_line_index

-- DROP INDEX public.planet_osm_line_index;

CREATE INDEX planet_osm_line_index
   ON public.planet_osm_line USING gist
   (way)
TABLESPACE pg_default;

-- Index: planet_osm_line_pkey

-- DROP INDEX public.planet_osm_line_pkey;

CREATE INDEX planet_osm_line_pkey
   ON public.planet_osm_line USING btree
   (osm_id)
  TABLESPACE pg_default;
0

Given the fairly limited information in your question here's a generic query that will do what you want to do. I'm assuming you have the postgis extension installed at this point.

Generic query

Replace anything in <> with the relevant parameter. Remove the <> if you do so.

UPDATE <table>
SET <column to update>='<text to update column with>'
WHERE <the table's primary key column> IN (
    SELECT s.<the table's primary key column>
    FROM (
        SELECT DISTINCT <the table's primary key column>,
        (
            ST_Distance('SRID=<Your SRID identifier>;POINT(<lon coordinate of point> <lat coordinate of point>)'::geometry,
            <table's geometry column>)
        ) as d
        FROM <table>
        WHERE <feature type column>='<feature term 1>'
        OR <feature type column>='<feature term 2>'
        ORDER BY d
        LIMIT 1
    )
s);

Sample query against a test dataset

UPDATE test
SET name='success'
WHERE id IN (
    SELECT s.id
    FROM (
        SELECT DISTINCT id,
        (
            ST_Distance('SRID=4326;POINT(25 25)'::geometry,
            geometry)
        ) as d
        FROM test
        WHERE type='road'
        OR type='way'
        ORDER BY d
        LIMIT 1
    )
s);

Query explanation

UPDATE test SET name='blabla' is the standard update statement as we know it for most database systems.

WHERE id IN may appear strange for some but is well explained in the documentation as a subquery

For the subquery we are actually nesting two subqueries and making use of an inline view. This allows us to initially return multiple columns where the WHERE subquery only accepts a single column in our case. We merely select the column we want from the multi-column response.

(SELECT s.id FROM
    (SELECT DISTINCT id,<distance> as d
    FROM table
    WHERE type='way'
    OR type='road'
    ORDER BY d
    LIMIT 1) s
)

We order the result, filtered by whatever attribute(s) we want by the distance (explained below) and limit the result to 1 - returning the first entry only.

(ST_Distance(
    'SRID=4326;POINT(<lon> <lat>)'::geometry,
    geometry)
) as d

ST_Distance calculates the distance between the two closest points of two geometries. We create a point in the desired SRID (same as our geometry column) using the lon/lat coordinates we desire and compare it to our geometry column.

Ran against a quick test dataset and appears to work fine. You'll have to adapt the various parameters to suit your exact implementation. If these are standard column names for OSM data let me know which ones they are and I'll amend the example to make it specific.

  • hello.. Thanks for your answer. There is one problem, planet_osm_line table have no any uniqe primary key column. How can we do that without use primary key column ? – user1639830 Dec 1 '16 at 15:30
  • I'll have a think - could you update your question with a list of the columns you have in the table. Would greatly help in redesigning the answer. – Phil G Dec 1 '16 at 15:33
  • hi Phil. I added columns info into my question. – user1639830 Dec 1 '16 at 16:09
  • way is geometry column. kind of uinqe. how about this syntax? UPDATE planet_line SET name='blabla' WHERE way IN ( SELECT way FROM ( SELECT DISTINCT way, ( ST_Distance('SRID=4326;POINT(25 25)'::geometry, geometry) ) as d FROM planet_line WHERE type='road' OR type='way' ORDER BY d LIMIT 1 ) s); – user1639830 Dec 1 '16 at 19:12
  • ST_Distance('SRID=4326;POINT(25.456 26.456)'::geometry,way) this gives error : ERROR: Operation on mixed SRID geometries – user1639830 Dec 1 '16 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.