1

I've used OSM2MSSQL to fill db with 2 cities streets. Now I have the following tables in my DB :

tNode 
tNodeTag
tRelation
tRelationMember
tRelationTag 
tTagText 
tWay 
tWayCreation 
tWayTag 

How can I get all the streets that are in a specific city ?

  • 1
    related: stackoverflow.com/questions/6291246/… – Mapperz Dec 13 '12 at 15:40
  • 1
    You would need a Cities table with at least attributes for name and spatial extent (as a polygon). Then you would perform a join between Streets and Cities based on spatial overlap/contains. The query result is all roads with a corresponding city name attribute. For a single city the documentation example for STOverlaps should be very close to your needed query. – bhell Dec 13 '12 at 20:27
  • Thank you bhell. What I did is creating a geographic POLYGON and using STContains on roads. – Alophind Dec 15 '12 at 8:06
1

As bhell suggested I did the following query to get the streets in a specific city:

  1. Creating polygon of city in QGIS
  2. Using SQL Query

    DECLARE @POI geography;
    SET @POI = geography::STGeomFromText('POINT(36.81120 -1.27531)', 4326);

    DECLARE @BOUNDS geography;
    SET @BOUNDS = geography::STGeomFromText('POLYGON((36.68936531 -1.354740152,36.93951706 -1.354740152,36.93951706 -1.196323211,36.68936531 -1.196323211,36.68936531 -1.354740152))', 4326)

    SELECT [ID]
    ,[NAME]
    ,[geom]
    ,@BOUNDS.STContains(a.geom)
    FROM [TABLE_STREETS] as a
    WHERE @BOUNDS.STContains(a.geom) = 1

Thank you for your help.

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.