Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 ?

share|improve this question
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
add comment

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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