OSM roads

Hi , is there someone who know how to filter postgresql query to show up data only for all USA roads, or even is it possible to be done in OSM ? I try to filter data with
SELECT … WHERE
ref LIKE (‘I %’) or
ref LIKE (‘US%’) or
name LIKE (‘State %’) … ,
but this not work fine … it filtering most of the roads but there are still roads that not match the filter … mostly roads that don’t have characters in shields only digits , or state roads because in every state same road have different shield (characters are diff… , digits are same … ). So is it even possible to filter all USA roads somehow ?

Hi,

Use spatial query ST_Intersects with USA polygon.

Hi again,
thank you for reply, but Im new in that and I will need help for this spatial query. Can you give me some more details or redirect me to any link where I can learn how exactly to do it ??? Im with Ubuntu 10.04 server , mapnik2 and postgresql 8.4 and have installed postgis 1.4.0.

What kind of schema are you using? With the “snapshot” schema (see Osmosis documentation on the wiki) you can use the hstore function to do most of the queries you want to do (if I’m understanding your question correctly). Is your current database for the US only? If you want only roads you can use something like “SELECT w.id FROM ways w WHERE w.tags @> ‘highway =>*’” (will return all ways tagged with “highway” which will also give some foot/bicycle paths IIRC). If you want only roads that allow some motorized traffic try something like this:

        "SELECT w.id FROM ways w 
        WHERE w.tags @> 'highway=>motorway' 
        OR w.tags @> 'highway=>motorway_link'
        OR w.tags @> 'highway=>trunk' 
        OR w.tags @> 'highway=>trunk_link' 
        OR w.tags @> 'highway=>primary' 
        OR w.tags @> 'highway=>primary_link'
        OR w.tags @> 'highway=>secondary'
        OR w.tags @> 'highway=>secondary_link' 
        OR w.tags @> 'highway=>tertiary' 
        OR w.tags @> 'highway=>tertiary_link' 
        OR w.tags @> 'highway=>residential' 
        OR w.tags @> 'highway=>service' 
        OR w.tags @> 'highway=>road' 
        OR w.tags @> 'highway=>unclassified' 
        OR w.tags @> 'highway=>living_street' ;

Again this assumes you are using the snapshot schema and have the hstore function loaded. The operators “@>” and “=>” invoke that function which is very efficient for working with the tags since hstore indexes them. I’m not an expert with Postgres and PosGIS but I had to figure out some of this with the help of the forum, it can be tricky to understand and work with. Hope the above makes some sense, let me know if that works as there may be a syntax error or two (been a few months since I worked with Postgres.).

I have made it like JRA say (thx for idea by the way :slight_smile: ) … with ST_Intersects and its work but I made polygon from http://openlayers.org/dev/examples/vector-formats.html and polygon is not perfect. I want to make polygon directly from USA borders … can somebody help me with that … :expressionless:
List of relations
Schema | Name | Type | Owner
--------±-------------------±------±----------
public | geometry_columns | table | syordanov
public | planet_osm_line | table | syordanov
public | planet_osm_nodes | table | syordanov
public | planet_osm_point | table | syordanov
public | planet_osm_polygon | table | syordanov
public | planet_osm_rels | table | syordanov
public | planet_osm_roads | table | syordanov
public | planet_osm_ways | table | syordanov
public | spatial_ref_sys | table | syordanov
(9 rows)
SRID=900913;
I need help with query that will make polygon … in planet_osm_roads there is data for boundaries and I can make polygon from LINESTRING with ST_Polygon but I can’t make right query … :expressionless: , … or can I take directly polygon of USA and from where if that is possible ? I`m really new in that so I need help … :confused: