17 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 ...
csd's user avatar
  • 1,788
10 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,...
Evan Carroll's user avatar
  • 7,071
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 ...
DPSSpatial_BoycottingGISSE's 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 ...
nielsgerrits's user avatar
  • 3,924
6 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)
PeterS's user avatar
  • 678
5 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 ...
Benedek Simo's user avatar
4 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-...
BStone's user avatar
  • 503
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 ...
Alex Tereshenkov's 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 ...
Vince's user avatar
  • 20.1k
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: ...
Spacedman's user avatar
  • 63.9k
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 +...
user30184's user avatar
  • 65.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 ...
dr_jts's user avatar
  • 5,068
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 ...
DPSSpatial_BoycottingGISSE's user avatar
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 ...
codeMonkey's user avatar
3 votes

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

If you don't want to use environment variables (or dont have permissions) adding --config MSSQLSPATIAL_USE_GEOMETRY_COLUMNS NO to the command has solved this issue for me.
Hotpepper's user avatar
  • 876
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 ...
Mark Ireland's user avatar
  • 13.2k
3 votes

Pulling Spatial Data in Geography Format From MS SQL Server 2008 Using ogr2ogr

Telling ogr the tables in the connection string made no positive effect for me. The solution for my issue, which looks to be the similar to yours, was to make an explicit cast of the shape field. ...
Steve's user avatar
  • 1,331
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....
Alex Leith's user avatar
  • 13.5k
2 votes

How to migrate spatial tables from MSSQL to PostGIS

If you are doing a 1-off migration, you could do it in QGIS. Add the MSSQL table as a layer. Open your DB Manager (I had to add my PostGIS connection via the Layer->Add Layer-> Add PostGIS Layer... ...
Jay Cummins's user avatar
  • 14.6k
2 votes

Change polygon "handedness" for SQL 2008 (reverse polygon vertex order)

In >=SQL Server 2012, the ReorientObject() method should accomplish this. For <SQL Server 2012, below is an alternative method. For an existing SQL geography, @g, the code below will extract ...
Baodad's user avatar
  • 263
2 votes

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

I have a partial answer to part 2 of your question 'Why does ogr2ogr throw the error about the geometry_columns table already being created?' I'm performing a .SHP into MSSQLSpatial using OGR2OGR and ...
user3436711's user avatar
2 votes

QGIS MSSQL on OSX?

Connection Name is a name you choose for the connection to be listed in the Browser panel. Basically treat it like a file name in terms of allowable characters. Provider/DSN can contain a connection ...
mmorgan's user avatar
  • 75
2 votes

How to find Nearest point on line from a point cordinate in SQL server

This question is a couple of months old, but in SQL Server 2012, you can try and use the ShortestLineTo method. Example (not tested): DECLARE @a geometry; SET @a = geometry::STPointFromText('POINT(73....
Peter Hill's user avatar
2 votes
Accepted

Simple POLYGON works for Geometry, goes nuts for Geography

Sometimes the key to untangling polygon topology issues can be to use the same vertices to form LINESTRINGs, and render those lines with an arrowhead to view ring orientation. Here's the first ring: ...
Vince's user avatar
  • 20.1k
2 votes

Mapinfo 15.2.2 not displaying all polygons from a MSSQL Spatial DB table

Your MapInfo.mapinfo_mapcatalog probably has the bounding rectangle incorrectly set and doesn't cover the full area in which your polygons reside. The fields DB_X_LL, DB_Y_LL, DB_X_UR and DB_Y_UR ...
Andy H's user avatar
  • 21
2 votes
Accepted

MSSQL - Cast Lat/Long As A Native Geometry Type Into a New Column

I think, You should use static method on geometry type, not geography. Point method on geography returns geography instances, not geometry. Easiest way how to populate third column is simple update. ...
DavidP's user avatar
  • 1,353
2 votes
Accepted

Length of line differs depending on query method

I suspect this has to do floating point numbers (doubles) and how they are stored in the database and then represented using the "various methods". I am not sure if ESRI uses the IEEE 754 standard ...
Jakub Sisak GeoGraphics's user avatar
2 votes

How to create autoincrementing primary key with dynamic writer in FME

Edit: answer by buddiebubba is better. If using a dynamic writer type, it is not possible to simply put serial in there since the schema is feature driven. The easiest way to do this, I found was ...
Lennert De Feyter's user avatar
2 votes
Accepted

STContains always returns 0 when i query from table. But same data works when I manually create a polygon geometry object and select

If you execute this query, you should get 1 and 1. As a comment, you probably would like to use a meaningful spatial reference system id such as WGS84 (WKID 4326) instead of just 0 when you construct ...
Alex Tereshenkov's user avatar
2 votes
Accepted

ogr2ogr MSSQLSpatial truncating attribute fields

The problem was an outdated version of GDAL. I was running into this issue, which has been fixed in GDAL 2.4.0. On Ubuntu, you can easily install it by following these steps: sudo add-apt-repository ...
jelleve's user avatar
  • 303

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