15 votes
Accepted

Using geometry rather than geography data types in SQL Server Spatial?

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 ...
user avatar
15 votes
Accepted

How to assign a schema to ogr2ogr?

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 ...
user avatar
  • 1,758
10 votes

How to use STUnion with GroupBy

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 ...
user avatar
9 votes

Create a geography/geometry column from x and y fields - SQL Server 2008

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,...
user avatar
  • 6,701
9 votes
Accepted

SQL Server Spatial Querying

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 ...
user avatar
8 votes
Accepted

Optimizing Geometry.STIntersect Query

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 ...
user avatar
  • 3,370
7 votes
Accepted

Should the QGIS "DB Manager" have Microsoft SQL Server as an option?

It's not supported (yet). Db manager only supports Postgres, Spatialite and Oracle databases.
user avatar
  • 26.3k
6 votes
Accepted

SQL Spatial select record where intersects another record

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 ...
user avatar
  • 29.2k
6 votes
Accepted

Inserting lat/long data into geography column of SQL Server 2012?

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 ...
user avatar
6 votes

How to create autoincrementing primary key with dynamic writer in FME

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 ...
user avatar
  • 3,511
5 votes
Accepted

Accurately find the bounding box of geography polygons

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 ...
user avatar
  • 101
5 votes

Using geometry rather than geography data types in SQL Server Spatial?

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 ...
user avatar
  • 1,119
5 votes

How do I find points that are NOT within a list of polygons using MS SQL?

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 ...
user avatar
4 votes
Accepted

FME SDE Writer changing part sequence of Multi-part polygon on Insert?

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, ...
user avatar
  • 18.9k
4 votes
Accepted

How to migrate spatial tables from MSSQL to PostGIS

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 ...
user avatar
  • 903
4 votes
Accepted

What if 'geometry_columns' already exists in MSSQL Server Database?

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 ...
user avatar
4 votes
Accepted

Dealing with different MSSQL geometry types in ArcGIS for Desktop?

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 ...
user avatar
  • 18.9k
4 votes
Accepted

MSSQL Geography column - Sweref99

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 ...
user avatar
4 votes

How to Cast geometry to Geography in SQL script in MS SQL Server?

I'm not sure this is the most efficient way of doing it, but it certainly works: SELECT geography::STGeomFromText([geom].STAsText(),4326) where 4326 is the spatial reference ID (SRID)
user avatar
  • 601
4 votes
Accepted

Converting SQL geometry data queries into R spatial dataframes

The rgdal package can use any data format supported by the underlying OGR and GDAL libraries. You can see which drivers you have for vector data with: ogrDrivers() In my case I can see: ...
user avatar
  • 52.1k
4 votes

ogr2ogr with GDAL 2.4 not reprojecting correctly

The other GDAL is somehow missing the definitions of ESRI projections. You can read the Proj string from your OSGeo4W installation and use that as -t_srs gdalsrsinfo epsg:102008 PROJ.4 : +proj=aea +...
user avatar
  • 52.5k
4 votes

Speed difference MS SQL Server 2019 to PostgreSQL/PostGIS 12 for spatial data

Spatial algorithms such as buffer are complex, with a lot of moving parts. It only takes one inefficient design decision or data structure to reduce performance. Conversely, sometimes it's possible ...
user avatar
  • 3,736
4 votes
Accepted

Speed difference MS SQL Server 2019 to PostgreSQL/PostGIS 12 for spatial data

My take on the spatial data component of MSSQL Server echoes the documentation - basic spatial capabilities, and anything more complex is handled by 3rd parties. What this means to us is that MSSQL ...
user avatar
3 votes

How do you generate WKT from a graphic drawn on a map?

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 ...
user avatar
  • 14.4k
3 votes

Why does ogr2ogr SHP import error out trying to create a database?

I've been having the same issue while migrating an existing script from a test machine (Win7) to a new server (Win Server 2012), therefore I knew the syntax and spelling were correct. On the non-...
user avatar
  • 463
3 votes
Accepted

How do I find points that are NOT within a list of polygons using MS SQL?

My eventual solution after much trial and error was the following simplified (hopefully not oversimplified!) code. This lists all points from allpoints that do not have matches in containedpoints (i.e....
user avatar
  • 375
3 votes
Accepted

How to cast geometry to varbinary in PostGIS / PostgreSQL

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=>...
user avatar
  • 12.4k
3 votes

Inserting lat/long data into geography column of SQL Server 2012?

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 ...
user avatar
3 votes
Accepted

How to create autoincrementing primary key with dynamic writer in FME

I think @buddiebubba is correct. Here's what my SQL Server definition looks like: Note that it's a dynamic translation. The Feature Type also has a parameter like this: By setting that to NO I cause ...
user avatar
  • 12.4k
3 votes

Using new Australian Datum GDA2020 in SQL Server 2016?

This may be personal opinion, a bit, but I would not recommend modifying your data's coordinates until the production database that you store the data in can handle the new coordinate reference system....
user avatar
  • 13.1k

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