# Tag Info

37

You can add a computed column like this alter table yourTable add geographyColumn as geography::STGeomFromText('POINT('+convert(varchar(20),Long)+' '+convert(varchar(20),Lat)+')',4326) I have add the conversion from lng or lat because I store the Long and Lats as numbers.

15

There's a good answer over at Stack Overflow, which goes a little something like this: The geography type is a little bit more restrictive than geometry. It can't cross different hemispheres and the outer ring must be drawn counter-clockwise. The rest can be found in Geography data type vs. Geometry data type in SQL Server. A Geometry vs Geography ...

14

This perhaps is most easily done with raster calculations, because the centroid of the land surface is found (by definition) by averaging the geocentric coordinates (X, Y, Z), weighting them by land area. Averaging is done using zonal statistics. To obtain these coordinates and the area scale factor, compute the following grids in geographic coordinates ...

14

Spatial Ed's blog had a concise solution. Here is some SQL demonstrating the transform: DECLARE @geom GEOMETRY = 'POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))'; DECLARE @geog GEOGRAPHY = @geom.MakeValid().STUnion(@geom.STStartPoint()).STAsText() And an excerpt from Ed's post: The key to this behavior is the the STUnion() method. Since this is an OGC-based ...

10

Looks like you want the STGeomFromText() or STPointFromText() SQL method: DECLARE @g geography; SET @g = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326); SELECT @g.ToString(); or DECLARE @g geography; SET @g = geography::STPointFromText('POINT(-122.34900 47.65100)', 4326); SELECT @g.ToString(); Create your Geometry or Geography field, then ...

8

SQL Server 2012 You can do it like this, SELECT geography::Point(lat, long, 4326) For more information see my post on Database Administrators, "SQL Server Point Constructor or version of ST_Point(x,y)?"

8

I use the GEOMETRY::UnionAggregate function in SQL Server to do this for our Elem, Mid and High boundaries In the dbo.SchoolBoundaries table below, the ELEM_NUM value is what I want to group on, and values of 0 are non-district boundaries (which I filter out): SELECT elem_num , GEOMETRY::UnionAggregate(shape) as shape FROM [dbo].[SchoolBoundaries] ...

8

If you've got a polygon you want to use as a declared variable and intersect it with a table containing existing geometry, your query (including your polygon variable declaration) would look something like this: (MSSQL Server syntax) declare @polygon geometry = 'POLYGON((-9486683.581 4810152.256, -9282073.762 4821688.121, -9262037.786 4625578.413, -9477576....

7

I'm assuming that you are using Geometries, but the methodologies remain mostly the same. When tuning spatial queries, these are the steps I take This is the most important step. Check the indexes are suitable for the table. If you have SQL Server 2012+ then I would suggest that you use the AUTO GRID. This gives you a finer grid. Make sure the extents ...

7

SCHEMA is a "layer creation option", specified with the -lco parameter. This should do the trick (split into multiple lines here for readability): call ogr2ogr -overwrite -skipfailures -f MSSQLSpatial -lco SCHEMA=geoserver "MSSQL:server=myserver;database=mydb;trusted_connection=yes;" "C:\temp\MapVegas860\Buildings.shp"

6

You should create a GEOMETRY data type column in your table to store the coordinates as spatial objects. And I'll assume you have your coordinate pair values in columns called LAT and LON... and I'm naming your source and destination columns arbitrarily based on your data above...see the SQL below. You should be able to do this: alter table ...

6

No experience with MSSQL but for PostgreSQL it works by selecting the datatype serial and the index PrimaryKey. See screenshot. I suspect the datatype should be identity with index PrimaryKey. See also this documentation. Big StackExchange fan but you will have better chances getting a good answer on the Safe Software forum. Lots of knowledge and activity.

5

You cannot do that. Geometry and Geography are two completely different types of data. You probably did read up on it..., but here is an explanation... Geometry are points on a flat surface. If you would like to calculate the area of your bedroom, i.e. 3.5m by 6.8m, it would result in 23.8m2 - if you would have drawn it in a CAD program, you would start ...

5

From past experience, I wouldn't trust that error message to strictly relate to database creation - I would treat it as just implying that there is a permissions issue of sorts with that connection string. The first thing I'd do is use ogrinfo to check whether a connection can be made at all to that server/db using those credentials. Try running the ...

5

MS SQL Server includes spatial functions, but the functions are usually named differently, for example PostGIS's intersect function is named ST_Intersect and in SQL Server it is called STIntersection. Your best bet to migrate would be to use ogr2ogr to move your data from Postgres to MS-SQL, then you'll have to re-write any queries you're using in Postgres.

5

Maybe check out the properties of Projected Coordinate Systems, to get a view into their utility? There are 3 aspects or properties presented in Projected Coordinate Systems that establish their utility and rationale. Any projection of 3D space on a 2D surface will of course exhibit distortion versus reality. Depending on your application, using a specific ...

5

It's not supported (yet). Db manager only supports Postgres, Spatialite and Oracle databases.

4

The following steps works in nearly every case for this procedure. It may be a good idea to repair geometry if you find this process is not producing your desired results. There is the condition of truncated field names should they be greater than a certain length. Generally this is not as severe a problem as the actual transfer, though. Open ArcGIS. ...

4

Don't take buffers. You can identify all your "near to each other" points like this (if you have a spatial index on geom, it'll run quite fast): SELECT a.id FROM pts a, pts b WHERE ST_DWithin(a.geom, b.geom, 3) AND a.id != b.id; Be careful that you don't delete legitimate crossings that also happen to be close to each other. Your 3-5m rule might not be ...

4

There's no quick and easy way but for the benefit of someone else with the same problem the solution is to convert the geography type to a geometry type, take the bounding box (envelope) of the geometry and pull out the 1st (min) and 3rd (max) points. CGMinLong AS (CONVERT(float, geography::STGeomFromWKB( geometry::STGeomFromWKB(contactpolygon.STAsBinary(),...

4

There's a piece of the puzzle that your workflow does not consider: Enterprise geodatabase shapes all pass though the ArcSDE API, which performs standardization of geometries (integer encoding, dropping duplicate vertices, reordering incorrectly wound rings, merging inversions into their parent ring, etc) This impacts you in two ways: The vertex ordering ...

4

I have stumbled on this issue lately, and if you set the MSSQLSPATIAL_USE_GEOMETRY_COLUMNS environment variable to NO, then the error message disappears. Related documentation here. Hope it helps somebody later.

4

It is an absolute requirement for ArcGIS that layer sources (geometry tables or views) only contain one topology class (point/multipoint/polyline/polygon). Topology class is a fundamental property of a layer; you cannot convince ArcGIS to render a source where both points and polygons are returned in a single query. There are many reasons to honor this ...

4

To insert a feature, you would need to use geometry spatial type because your coordinates are defined in a projected coordinate system. The Sweref99 coordinate system you refer to is probably is sweref99tm which has epsg code of 3006. So, it will be: geometry::STGeomFromText ('POINT(673318 6580874)', 3006))

3

I can write to SQL Server 2008 with GDAL 1.10.1 using below command. I'm using SQL Server auth entication and a user that has connect, create procedure, create table, create view, select and showplan. I suspect that I only need, connect and create table for below. Although I have also had to delete the geometry columns and spatial ref system tables at ...

3

I do this from time to time in QGIS: Add an OpenLayers layer (in this case OSM); Set the project projection to the desired coord system (i.e. WGS84 UTM 36S); Create a new temporary shapefile or Spatialite layer (tempWKT here), also set to the correct coord system; Start editing the temp layer and add the desired polygon: Select the polygon using the ...

3

At least in PostgreSQL it is much faster to do a left or right join on st_intersects instead and use "where id is null" to find those that "don't have a friend" edit: Ok, here comes an example tested in SQL Server 2014 express: There is 2 polygons and 4 points. 2 of them is inside the one of the polygons and oine point on the border of the other ...

3

Answering my own question. In the WHERE clause I had to change T.STIntersect(S) to T.Shape.STIntersects(S.Shape) = 1 Full example: SELECT T.TreeID, T.ServiceAreaNumber AS RecordedServiceArea, S.ServiceAreaNumber AS ActualServiceArea FROM TREE T, SERVICEAREA S WHERE T.Shape.STIntersects(S.Shape) = 1 AND T.ServiceAreaNumber != S....

3

They're two different representations of the same binary data. SET bytea_output = 'hex' will switch to the hex representation that it sounds like you want. Here's a couple of examples: postgres=> SET bytea_output='escape'; postgres=> SELECT ST_AsBinary('POINT (24.3 41.7)'::geometry); st_asbinary ...

3

Make a user-defined function (UDF): CREATE FUNCTION [dbo].[GetGeoFromLatLng] ( @lat decimal(9,6), @lng decimal(9,6) ) RETURNS geography WITH SCHEMABINDING AS BEGIN DECLARE @ret geography select @ret = geography::STPointFromText('POINT(' + convert(nvarchar(25), @lng) +' ' + convert(nvarchar(25), @lat) + ')', 4326); RETURN @ret ...

Only top voted, non community-wiki answers of a minimum length are eligible