topic Re: Geography data type issues. in Data Management Questions

Geography data type issues.

MarceloGomes — Wed, 13 Mar 2013 18:55:12 GMT

I need to get Lat/Long values back from the Geography filed on my SQL Server 2008 R2;

I have tried this:

Select [Shape].Lat, [Shape].Long FROM [sde].[sde].[Teste]

And I got this error message:

Msg 6506, Level 16, State 10, Line 3
Could not find method 'Lat' for type 'Microsoft.SqlServer.Types.SqlGeometry' in assembly 'Microsoft.SqlServer.Types'.

Do you guys have any idea of what is going on?

Thanks,
Felipe

Re: Geography data type issues.

VinceAngelo — Wed, 13 Mar 2013 20:05:42 GMT

Microsoft documents the accessor functions for their GEOMETRY and GEOGRAPHY types.
I doubt GEOMETRY supports Lat or Long requests (these are Geography properties).
Perhaps if you use a cast...

- V

Re: Geography data type issues.

MarceloGomes — Fri, 15 Mar 2013 12:22:47 GMT

Perhaps if you use a cast...

My field is GEOGRAPHY...and what king of cast??
I'm a beginner in SQL Server.

Re: Geography data type issues.

VinceAngelo — Fri, 15 Mar 2013 13:04:29 GMT

The SQL-Server error reports the column is "SqlGeometry."

A Microsoft forum would likely be of more use on this topic.

- V

PS: Once you get the issue resolved, though, you'll want to follow best practice
and remove all user data owned by the 'sde' login/user/schema, and instead
create multiple logins/users/shemas and roles to own and manage data.
The 'sde' user should be exclusively reserved for geodatabase administration --
allowing the 'sde' user to own GIS tables can result in corruption of the entire
geodatabase (especially as a database/ArcSDE novice).

Re: Geography data type issues.

MarceloGomes — Fri, 15 Mar 2013 15:19:02 GMT

Thanks VAngelo for the help..

The Shape field was created automaticaly by the ArcGis export to server(Single) tool.

And i don't know what to do to get this values.

Re: Geography data type issues.

VinceAngelo — Fri, 15 Mar 2013 15:38:15 GMT

Unless you modififed the DBTUNE environment, it's not a GEOGRAPHY, it's a GEOMETRY
in the table (which are distinct types in SQL-Server). Only a Geography type supports the
Lat and Long methods, so if you want to use those methods, you'd need to cast the column
in the SQL query (for each term). I'm not familiar with that particular syntax, but I'm sure
a search on "cast SqlGeometry as SqlGeography" will get you started.

- V

Re: Geography data type issues.

ShannonShields — Fri, 15 Mar 2013 17:36:02 GMT

Like Vince said, it looks as if your data is stored using the Geometry type rather than the Geography type. In the case of Geometry you'd use the STX and STY methods to get the individual coordinates of each point.

You can check the properties of your feature class in ArcCatalog to see what the underlying spatial type is, or you can query directly to SQL Server like this:

[INDENT]select data_type from information_schema.columns where TABLE_NAME = <your_table> and COLUMN_NAME = 'shape'[/INDENT]

-Shannon

Re: Geography data type issues.

MarceloGomes — Fri, 15 Mar 2013 19:14:49 GMT

The spatial type is GEOMETRY.

Using the STX and STY i got these values back -5056571,984 and -2707830,6178.

Vangelo, how do i modify the DBTUNE?

Thanks for helping me guys!

Re: Geography data type issues.

VinceAngelo — Fri, 15 Mar 2013 23:01:53 GMT

If you didn't get back decimal degrees from GEOMETRY, a cast isn't going to help
(nor is changing the geometry type). There are dozens of ways to extract Lat/Lon
values, with or without ArcGIS. Before you can deproject, you uneed to know what
your source projection and geodetic datum are (a new thread on deprojection
would be more appropriate than morphing this one -- be sure you specify the
software you have available along with the existing coordinate sysstem in your
initial post).

A google on "how do i modify the dbtune" returns over 5000 hits, and the first five
all look like they'll answer your question. These Forums are really for when the
documentation *doesn't* meet your needs.

- V

Re: Geography data type issues.

MikeSmith7 — Wed, 27 Mar 2013 15:02:40 GMT

To keep things easy we exported all the x,y from the geodatabase and added two columns to the sql table and populated them. Once we had all the data we needed in SQL. I ran the following sql commands:

ALTER TABLE Facility ADD SHAPE Geometry
CREATE SPATIAL INDEX Spatial_Index ON Facility(Shape) USING GEOMETRY_GRID WITH ( BOUNDING_BOX = ( 0, 0,1000000 , 1000000 ))
UPDATE Facility SET shape = geometry::STPointFromText('POINT(' + convert(varchar,x_coord) + ' ' + convert(varchar, y_coord) + ')', 2954)

SELECT shape.STX as X_coord, shape.STY as Y_Coord, shape.STSrid as SRID from facility

Hope this helps,
Mike