locked
Examples need - NOT using stored procedure RRS feed

  • Question

  • I'm trying to use SQL Server Spatial (newbe) the way I use SQL Server as a relational database from a client. All the examples/posts I've seen use virtual data in the context of a stored procedure which is just greek to me. None of the examples make sense if you have data loaded in tables and you what to query that data.

    Has anyone seen a tutorial that is just not a repeat of the academic exercises but insgtead use actual tables?

    Is there an easy way to translate the virtual examples to "real" queries?

     

    Wednesday, August 18, 2010 3:24 PM

Answers

All replies

  • Not quite sure what you mean by "virtual" data.... I suspect what you mean is that they use locally-declared variables, e.g. SET @g = geography::STGeomFromText.....

    Using a local variable such as @g in examples make it much easier to demonstrate how spatial methods work, because you don't need to include all the CREATE TABLE..... INSERT INTO TABLE.... guff to create a sample dataset before you can actually use a method on it.

    However, all the examples will work just as well with "real" data - instead of using @g.STIntersects(whatever), just use ColumnName.STIntersects(whatever), where ColumnName is a geography/geometry column in your table.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, August 18, 2010 4:51 PM
    Answerer
  • Problem is the examples do not look like anything in the "real" world but are academic exercises only recognizable to the academics. Newbe types need to learn the acamdemic jargon and figure out how to translate the examples to their application.

    The examples could refer to a pusedo database with tables and columns most would recognize and could adapte to their specific case. For example a State, County, City polygons and City, Store, etc. points.

    For example:

    DECLARE @g geography;
    DECLARE @h geography;
    SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
    SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
    SELECT @g.STDistance(@h);
    
    vs.
    
    select T2.APN, T2.Geom.STDistance(T1.Geom)
    from Parcels_Lite2 T1 INNER JOIN Parcels_Lite2 T2 on t1.APN='052-332-021'
    where T2.Geom.STDistance(T1.Geom)
    between 0 and (1000*0.3048)
    

    Getting from the top example to the bottom is not possible with the information provided in the reference materials.

    And all the isolated examples leave out a lot of important information. For example in the two queiries below a different number of polygons are return:

    select t1.APN,T2.Geom.STDistance(T1.Geom)
    from Parcels_Lite2 t1, Parcels_Lite2 t2 
    where t1.geom.STIntersects(t2.Geom.STBuffer(1000*0.38))=1
    and t2.APN = '052-332-021'
    
    select T2.APN, T2.Geom.STDistance(T1.Geom)
    from Parcels_Lite2 T1 INNER JOIN Parcels_Lite2 T2 on t1.APN='052-332-021'
    where T2.Geom.STDistance(T1.Geom)
    between 0 and (1000*0.3048)
    

    Being a newbe I would have thought the two would return the same set. With my data the first returns a few more polygons. (If someone could shed some light on the reason that would be appreciated.)

    Any way... I don't find the examples in the Micorosft reference materials to be useful as they are disconnected from any context I am familar with.

    Thursday, August 19, 2010 3:30 PM
  • 1000*0.38 vs 1000*0.3048
    • Marked as answer by Bob Heitzman Thursday, August 19, 2010 8:01 PM
    Thursday, August 19, 2010 4:36 PM
  • I accept your point about "real-world" examples, but if I run the MS examples using @g and @h I actually get a result, whereas if I run your examples I get an error "Couldn't find table Parcels_Lite2".... and what's more I don't know anything about the structure of your table - what datatype is the Geom column? what values does it contain? What on earth is an APN value?

    It is an unfortunate problem that, perhaps more than in most other types of data, to really get an understanding of how the spatial methods work, you need a good chunk of data first. The purpose of online method reference is primarily to understand the syntax of how methods are used, the parameters required, and the result returned, and you can do that using local variables just as well as with "real" data.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, August 19, 2010 5:49 PM
    Answerer