locked
Need lat/long pairs from data derived from buffers in feet RRS feed

  • Question

  • This post seems to indicate we can have both geom datatypes on the same table:

    http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/1efd0162-96ed-4cdb-91c3-2c151dbd65fc/

    Is that correct?

    High level view is I want to be able to query for buffers measured in feet/meters but I want to get the dynamic result from .STAsText() in long/lat pairs for further processing back on my client desktop, primarily to build KML for display in google earth.

    My default data is in California State Plane V Feet. I can easy export SQL Server 2008 R2 Geometry data from my GIS package http://mainfold.net. I can easily change the projection inside the GIS package from State Plane to Lat/Long but I just wind up with two tables that use different projections.

    I can also create shapefiles for import for use with ShapeToSQL but when I tried to import the State Plane data the tool complained the XY was out of range if I tried to create Geography data.

    Any way...  I am floundering around trying to get lat/long pairs out of data I need to develop from buffers using feet. I don't think the two geo types would help for the dynamically created buffers in any case.

    Any suggestions as to how I should tackle the problem?

    Thursday, August 19, 2010 11:21 PM

Answers

  • You don't need two columns. KML expects geographic coordinates with datum WGS84 (srid 4326), "lat/long" as you call them.

    Create your column as geography, STBuffer expects meters as entry parameter

     

    • Marked as answer by Bob Heitzman Friday, August 20, 2010 3:17 PM
    Friday, August 20, 2010 5:57 AM
  • If you use the geometry datatype then there is a single unit of measure associated with all your data, according to the SRID you're using. If you're using California State Plane V Feet, then that unit is obviously the foot. So your coordinate values will be supplied (and returned) in feet, the value that you input to STBuffer() will be in feet, the distance returned by STDistance() will be in feet, etc . etc.

    If you use the geography datatype, then there are separate units for angular measurements and for linear measurements, according to the SRID that you choose. For SRID 4326 (which is the spatial reference system on which KML coordinates must be based), then coordinate values are supplied (and returned) in angular coordinates of latitude/longitude measured in degrees, whereas the value input to STBuffer() and the distance returned by STDistance() etc. will be in metres. You can find out the linear unit of measure associated with each SRID by looking at the sys.spatial_reference_systems table.

    If you want to create KML then, as vIndEx says, store your data using the geography datatype and SRID 4326. When you supply a buffer value measured in feet, simply multiply this value by 0.3048 to convert to metres before supplying it to STBuffer()


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Bob Heitzman Friday, August 20, 2010 3:16 PM
    Friday, August 20, 2010 6:30 AM
    Answerer

All replies

  • You don't need two columns. KML expects geographic coordinates with datum WGS84 (srid 4326), "lat/long" as you call them.

    Create your column as geography, STBuffer expects meters as entry parameter

     

    • Marked as answer by Bob Heitzman Friday, August 20, 2010 3:17 PM
    Friday, August 20, 2010 5:57 AM
  • If you use the geometry datatype then there is a single unit of measure associated with all your data, according to the SRID you're using. If you're using California State Plane V Feet, then that unit is obviously the foot. So your coordinate values will be supplied (and returned) in feet, the value that you input to STBuffer() will be in feet, the distance returned by STDistance() will be in feet, etc . etc.

    If you use the geography datatype, then there are separate units for angular measurements and for linear measurements, according to the SRID that you choose. For SRID 4326 (which is the spatial reference system on which KML coordinates must be based), then coordinate values are supplied (and returned) in angular coordinates of latitude/longitude measured in degrees, whereas the value input to STBuffer() and the distance returned by STDistance() etc. will be in metres. You can find out the linear unit of measure associated with each SRID by looking at the sys.spatial_reference_systems table.

    If you want to create KML then, as vIndEx says, store your data using the geography datatype and SRID 4326. When you supply a buffer value measured in feet, simply multiply this value by 0.3048 to convert to metres before supplying it to STBuffer()


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Bob Heitzman Friday, August 20, 2010 3:16 PM
    Friday, August 20, 2010 6:30 AM
    Answerer