[OSM-talk] Parking symbols: YUCK!

[Dave Stubbs]

On Wed, Feb 27, 2008 at 4:41 PM, Lester Caine <lester at lsces.co.uk> wrote:
>
> Robert (Jamie) Munro wrote:
>  > -----BEGIN PGP SIGNED MESSAGE-----
>  > Hash: SHA1
>  >
>  > Lester Caine wrote:
>  > | Mark Williams wrote:
>  > |> -----BEGIN PGP SIGNED MESSAGE-----
>  > |> Hash: SHA1
>  > |>
>  > |> Lester Caine wrote:
>  > |>> J.D. Schmidt wrote:
>  > |>>> Lester Caine skrev:
>  > |> [big snip]
>  > |>
>  > |>> LOGICALLY - there should never have been a problem created. A POI
>  > element
>  > |>> should consist of a single entity which may have additional area
>  > information.
>  > |>> Even those tags that are currently only defined as 'node' in many
>  > cases WILL
>  > |>> be expanded to include area information at some point. So PLEASE can
>  > we have
>  > |>> some sensible method of identifying PAIRS of tags so we can THEN
>  > decide what
>  > |>> to do with them !!!
>  > |>>
>  > |> Is this not a job for relations? If the pair were related, then we have
>  > |> no problem?
>  > |
>  > | Correct - but how do you identify elements uniquely so you can create the
>  > | relation?
>  >
>  > I'm not quite sure what you mean, but to try to bring this back onto
>  > topic, if you mean "How do we find amenity=parking nodes that are
>  > duplicates of areas?" the answer is that we find all the nodes inside
>  > areas. If there are any that are just outside, we will miss them, whici
>  > is annoying, but not the end of the world. In order to find them, we can
>  > use a Simple postGIS query as suggested by Dave Stubbs (which I have
>  > attempted to reformat a bit):
>  >
>  > select p.osm_id
>  > ~  from planet_osm_point as p,
>  > ~       planet_osm_polygon as a
>  > where a.osm_id!=p.osm_id
>  > ~  and a.osm_id in (
>  > ~    select a.osm_id from planet_osm_point as p, planet_osm_polygon as a
>  > ~    where a.amenity='parking'
>  > ~      and p.amenity='parking'
>  > ~      and a.way && p.way
>  > ~      and intersects(a.way, p.way)
>  > ~    group by a.osm_id
>  > ~    having count(p.osm_id) > 1
>  > ~  )
>  > ~  and p.amenity='parking'
>  > ~  and a.way && p.way
>  > ~  and intersects(a.way,p.way);
>
>  This will only work for the specific case where there is a single matching
>  node that goes with the area, and I believe it will be time intensive when
>  processing a large area. I don't believe there is any mechanism to create
>  a.osm_id = p.osm_id - these will always have different id's? If they HAD
>  matching id's them there would not be a problem.

You're failing to understand the database contents.
This query is designed to act on the data output from osm2pgsql.
osm2pgsql automatically inserts a node into the database for every
parking area (and doesn't check to see if there is one already) with
the osm_id of the parking area way.

So what this query does is find all the parking areas with more than 1
node (ie: it has non-autogenerated ones), then extracts the nodes
which are in those areas and aren't the autogenerated ones (osm_id's
are different). Obviously it fails in the unlikely event that a node
does actually have the same ID as the area it's contained in.... but I
really don't care about that case.

So the query does work. And matches in excess of 5000 nodes.

Dave