locked
Latitude values must be between -90 and 90 degrees. RRS feed

  • Question

  •  I just have a geometry column and I use below query to convert it to geography

    DECLARE @gm AS Geometry;
    DECLARE @gg AS Geography;
    SELECT  @gm = geometry::STGeomFromText(Cast(Spatial.dbo.MyTable.geom as nvarchar(Max)), 0) 
    FROM [Spatial].[dbo].[MyTable] where ID=1

    SET @gg = geography::STGeomFromText(@gm.ToString(), 4326);

    I am getting below error with this query

    Msg 6522, Level 16, State 1, Line 1
    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
    System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.

    What could I be doing wrong? Could it be that theshapefiles i have used be wrong. Is there any way to make this work?? The shapefile while I have downloaded says:- 

    The projected coordinate system for the data is 
    State Plane of South Texas (Zone 4204) and Datum NAD 1983.

    A single cell in my geom column looks like this


    Friday, October 17, 2014 3:32 PM

All replies

  • The coordinate system that you're using is not a geodetic coordinate system (as the shapefile says), it's a projected coordinate system. So it's not suitable for a geography data type. SQL Server does not perform coordinate system conversions; to do this you need a product like SAFE software or a tool like OGR2OGR (from http://www.gdal.org/index.html ).

    Hope this helps, Bob

    • Proposed as answer by Tom Phillips Friday, October 17, 2014 6:21 PM
    Friday, October 17, 2014 3:56 PM
  • Thanks bob for your response.  Is it possible to search of a particular latutude/Longitude in this geom column.

    My application finds Latitude/Longitude through regular google maps and makes a search in the geom column.

    Friday, October 17, 2014 4:56 PM
  • No, not directly. The reason being that state planar coordinate systems (and projected coordinate systems in general) aren't latitude and longitude. They take a point in the bottom left of the state and make it (0,0) and each has its own numbering system. Which is why you have locations over 90. See http://en.wikipedia.org/wiki/State_Plane_Coordinate_System especially the section on "Problems". You could program a conversion algorithm and convert on-the-fly like these folks do http://www.earthpoint.us/StatePlane.aspx but that's essentially what OGR2OGR or SAFE does during load-time. It's almost always better to take the hit at load-time rather than run-time.

    Bob


    Friday, October 17, 2014 5:48 PM
  • Thanks again
    Friday, October 17, 2014 5:54 PM
  • I better question might be, what are you trying to use this data for?  There may already be a different resolution to your problem, using a different data set.

    Friday, October 17, 2014 6:21 PM
  • Sorry can you please expali what you mean by " using a different data set" . Do you mean different shapefiles??

    My application uses Latitude/Longitude from google API and needs to find out if it lies withing certain special districts in USA or not. It was pretty hard to get shape files for this kind of data as it does not seem to be easily available. We did manage to find some files and use them but now it seems they are in projected coordinate system so cant be used with this latitude/Longitude system of our application

    Friday, October 17, 2014 8:44 PM
  • I better question might be, what are you trying to use this data for?  There may already be a different resolution to your problem, using a different data set.

    Sorry can you please expali what you mean by " using a different data set" . Do you mean different shapefiles??

    My application uses Latitude/Longitude from google API and needs to find out if it lies withing certain special districts in USA or not. It was pretty hard to get shape files for this kind of data as it does not seem to be easily available. We did manage to find some files and use them but now it seems they are in projected coordinate system so cant be used with this latitude/Longitude system of our application

    Sunday, October 19, 2014 1:41 AM
  • Getting a different set of the same data, as Tom said, would be the easiest, if the geodetic version of the dataset exists.

    But converting the data you have should be about as easy. OGR2OGR is open source, freely useable software and, though SAFE is a commercial product, they have different levels of product depending on what you want to do. And a downloadable trial version. You could then store the dataset as geography or (the original) geometry of both in the same table. So it's definitely not a show-stopper.

    Cheers, Bob

    Sunday, October 19, 2014 4:28 AM
  • My point was, there may be another source of the data which actually includes lat/long already, if you describe your requirements.

    What "special districts" are you trying to identify?

    Monday, October 20, 2014 3:24 PM
  • There are some special districts that lie in US.. These may be lie within a state or sometimes their area might span more than 2-3 states also. These mostly have some special tax rates.. I am not a tax person so I dont know the details..Can you name a few standard sources for get  shape file data. Might be worth asking them..
    • Edited by mkanchan86 Monday, October 20, 2014 5:33 PM
    Monday, October 20, 2014 5:32 PM
  • There are exceptions, but most US special tax districts are defined by zip code the business resides. 

    Can you give us an example of what you are looking for?

    Monday, October 20, 2014 6:37 PM
  • I realized you are trying to map them.  However, here is a list of them in Texas.  Most fall in a county, city or combination of city/county/zip.

    This should be easy to make your own data table based on these criteria.

    http://www.window.state.tx.us/taxinfo/local/spd.html

    Monday, October 20, 2014 7:51 PM
  • Thank you so much @Tom for helping me out on this.

    Yes these are the special districts I was talking about. But I dont understand how I could get these by combination of city/county/and zip

    For example consider this one row from the link u sent me.

    County Special Purpose District (SPD)  Local Code
    Bell Rogers Municipal Development District 5014512

    How can I cover this SPDs through combination of city/county/and zip and how if given a Lat/Long I can determine if it lies in this SPD or not


    • Edited by mkanchan86 Tuesday, October 21, 2014 1:01 AM
    Tuesday, October 21, 2014 12:50 AM