I’m sharing below the SQL query we used on an offline data extract, which allowed us to run a few quality checks—such as filtering out some obvious false positives.
To make the process easier to follow, I’ve broken the workflow down into the following steps:
1. Selecting named nodes
We started by selecting all point features that contain a name=* tag and represent real OSM objects (i.e., not deleted or legacy IDs).
2. Excluding lifecycle, deprecated, or non‑current features
Nodes whose tags indicate demolished, abandoned, disused, proposed, construction, or otherwise non‑active states were excluded.
This also includes objects tagged with past‑tense markers (e.g., was:*) or temporary states.
3. Filtering out nodes where the only tags are secondary, technical, or non‑descriptive
We removed points that have tags which are known not to represent standalone mappable features.
Examples include:
structural or metadata‑like tags (e.g., level, location, indoor)
-
functional markers (e.g., craft=*, club=*, utility=*)
-
golf tagging elements, amusement‑ride components
-
piste, advertising, industrial, telecom, pipeline, marker tags
-
various temporary or prefix-form highway tags
-
ref-specific or operational-only tags
These tags indicate auxiliary data rather than a primary feature.
4. Restricting to nodes without any primary feature tag
We kept only the nodes without primary tags such as:
amenity, shop, landuse, railway, historic, tourism, building, natural, power, office, place, barrier, public_transport, and many more.
If a point had any recognized primary feature tag, it was excluded.
5. Excluding named nodes that likely represent numerical labels
Nodes whose names consist mainly of digits (e.g., streetlamp numbers, parking spot numbers) were removed unless they represent well‑known numeric patterns like “24/7”.
6. Spatial checks to remove nodes attached to linear features
To avoid picking up unnamed components of other mapped objects, we excluded nodes that physically intersect with:
-
highways
-
boundaries
-
railways
-
waterways
This prevents false positives like named bollards, mile markers, or boundary stones.
7. Removing nodes inside special landuse areas
Nodes located within polygons tagged as:
-
landuse=military
-
aeroway=aerodrome
were excluded, as names inside these areas frequently represent internal infrastructure not meant as standalone public features.
SELECT
-- p.*,
p.osm_id, p.name, CAST(p.tags -> 'osm_timestamp' AS DATE) AS last_edit_date,
ST_AsText(p.way) AS geom
FROM {schema}.planet_osm_point p
WHERE
p.name IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM each(p.tags) AS t(key, val) -- remove lifecycle prefixes
WHERE t.key LIKE ANY (ARRAY[
'%demolished%', '%disused%', '%abandoned%', '%ruins%', '%removed%', '%razed%', '%destroyed%',
'%was:%', '%:was%', '%proposed%', '%construction%', '%damage%', '%closed%', '%planned%',
'%historic:%', '%:historic%', '%seamark%', '%:disabled%', '%disabled:%', '%fire_hydrant%'
])
)
AND NOT EXISTS (
SELECT 1
FROM each(p.tags) AS t(key, value) -- remove FP features
WHERE key IN (
'area:highway', 'craft', 'club', 'entrance', 'healthcare', 'emergency', 'heritage',
'utility', 'mountain_pass', 'traffic_sign', 'beacon:type', 'fence_type',
'kerb', 'level', 'location', 'indoor', 'door', 'entrance', 'border_type',
'building:part', 'golf', 'disc_golf', 'tee', 'rough', 'bunker', 'hole',
'fairway', 'green', 'pin', 'out_of_bounds', 'par',
'slot_car', 'roller_coaster', 'dark_ride',
'attraction', 'shop', 'climbing', 'playground',
'piste:difficulty', 'piste:grooming', 'piste:type',
'advertising', 'sac_scale', 'mooring',
'usage', 'substance',
'allotments', 'cemetery', 'length', 'police', 'parking',
'ref:ProRailID', 'ref:ProRailSpoortak', 'temporary:highway',
'not:highway', 'old:highway', 'no:highway',
'bicycle_parking', 'horse_scale',
'removed:highway:2015', 'highway:2018', 'highway:-2019',
'highway:2022', 'highway:-2022', 'highway:-2023', 'geological', 'waterway:sign',
'information', 'university', 'telecom', 'industrial', 'animal', 'checkpoint', 'whitewater',
'community', 'incline', 'pipeline', 'valve', 'actuator', 'pedagogy', 'marker'
)
)
AND p.highway IS NULL
AND p.boundary IS NULL
AND p.layer IS NULL
AND p.amenity IS NULL
AND p.barrier IS NULL
AND p.ele IS NULL
AND p.railway IS NULL
AND p.leisure IS NULL
AND p.historic IS NULL
AND p.junction IS NULL
AND p.military IS NULL
AND p.office IS NULL
AND p.place IS NULL
AND p.power IS NULL
AND p.shop IS NULL
AND p.waterway IS NULL
AND p.aeroway IS NULL
AND p.man_made IS NULL
AND p.public_transport IS NULL
AND p.route IS NULL
AND p.aerialway IS NULL
AND p.sport IS NULL
AND p.building IS NULL
AND p.landuse IS NULL
AND p.tourism IS NULL
AND p."natural" IS NULL
AND p.disused IS NULL
AND p.osm_id > 0
AND NOT ((p.name ~ '[0-9].*[0-9]') -- remove values that have equal to or more than two digits in the name
AND p.name !~ '(24/7|247|365|24)')
AND NOT EXISTS ( -- remove features intersecting with highways
SELECT 1
FROM {schema}.planet_osm_line l
WHERE
ST_Intersects(p.way, l.way)
AND l.highway IS NOT NULL
)
AND NOT EXISTS ( -- remove features intersecting with boundaries
SELECT 1
FROM {schema}.planet_osm_line l
WHERE
ST_Intersects(p.way, l.way)
AND l.boundary IS NOT NULL
)
AND NOT EXISTS ( -- remove features intersecting with railways
SELECT 1
FROM {schema}.planet_osm_line l
WHERE
ST_Intersects(p.way, l.way)
AND l.railway IS NOT NULL
)
AND NOT EXISTS ( -- remove features intersecting with waterways
SELECT 1
FROM {schema}.planet_osm_line l
WHERE
ST_Intersects(p.way, l.way)
AND l.waterway IS NOT NULL
)
AND NOT EXISTS (
SELECT 1
FROM {schema}.planet_osm_polygon military
WHERE military.landuse = 'military'
AND ST_Intersects(p.way, military.way)
)
AND NOT EXISTS (
SELECT 1
FROM {schema}.planet_osm_polygon aerodrome
WHERE aerodrome.aeroway = 'aerodrome'
AND ST_Intersects(p.way, aerodrome.way)
)
Excluded nodes:
- Nodes that have equal or more than two digits in name=* tag (except 24/7, 247, 365 or 24)
- Nodes that intersect or belong to roads (highway=*), boundaries (boundary=*), railways (railway=*) or waterways (waterway=*)
- Nodes inside airports (aerodrome=aeroway) and military (landuse=military) areas
Excluded geometries: line, polygon, relation